This page details the process of creating a report using the Asamco B.V. Report Module.
The Report Module supports two main ways to create a report:
- combined datasource and report creation using the Report Wizard
- standard, separate datasource and report creation
In the following section we will guide you through all of the steps these methods are composed of.
Wizard-based Report Creation
The Report Wizard can be found in the main navigation menu on the left side of the screen under Reporting → Wizard:
The Wizard is composed of 5 stages that must be traversed in a fixed order.
Settings Tab
The first stage is the Settings page:
In the Settings page, two properties of the new report have to be specified:
- Name: a unique, human-readable tag by which the report can be identified
- Type: the type of the report, selected from PDF, BIC, DataGrid and PivotGrid. This determines what type of visual output will be generated to display the report.
The "Continue from unfinished..." selection box can be used to load the data from a report that was previously partially made in the Wizard and left unfinished. The data from all tabs in the wizard that were previously completed will be restored when an unfinished report is selected.
Query Tab
The second stage is the Query page:
The main purpose of this page is to take input of the SQL query that supplies the data for the report. This query, written in Transact-SQL syntax, should be provided in the Query field at the top of the page.
Additionally, a Datareader Login has to be selected in the selection box underneath the Query field. This login specification will determine which database will be connected to and what authorization rights apply to the query's execution.
Query Parameters
Input parameters can be used within the query. The value for these parameters can then be supplied by the viewer of the report prior to generating the report.
Parameters have to be referenced in the query using variables in the following format: ##parameter## where "parameter" can be any user-defined tag by which the parameter can be identified.
Testing the Query
The button labeled "Test query" can be used to generate the output of the query, executed using the selected Datareader Login, in the data grid below the button.
It is important to note that in order to be able to test the query, all input parameters in the query first have to be replaced with fixed values. In the example shown in the screenshot above, we would replace the parameter variable ##CompanyNr## with the specific company number 30427 to be able to test the query:
Make sure to replace the filled in parameters with their parameter variable tag again after testing the query.
Filters Tab (optional)
If any ##parameter## tags were used in the query, the next stage in the Wizard in the Filters page. If no parameters were used, the Filters and Defaults pages will be skipped.
The Filters page will display a grid with all filters that were extracted from the provided query. For each of these filters, the user can specify a filter type, selected from Text, Date, Boolean, Number, Lookup and MultiLookup.
The type of the filter will determine what kind of UI input component the user will be presented to input the parameter's value. It also affects the way in which the parameter substitution is made in the SQL query.
SQL substitutions
Depending on the datatype, the way the parameter tag will be substituted in the SQL query will differ:
Type | UI Component | Substitution | Remarks | Usage Remarks |
|---|---|---|---|---|
| Text | Text Box | 'value' | value is surrounded with ' ' | adding extra ' ' in SQL will result in errors |
| Date | Date Box | CONVERT(date, 'value') | value format is 'yyyy-MM-ddTHH:mm:ss.SSS' | directly compare to date as in GETDATE() < ##parameter## |
| Boolean | Check Box | 0 or 1 | ||
| Number | Number Box | value | no additions/alterations | |
| Lookup | Data Grid modal | value | taken from Value Member (see Lookups) | |
| MultiLookup | Data Grid modal | value1, value2, value3 | comma separated concatenation of Value Member values (see Lookups) | usable with IN(##parameter##) |
Defining Lookups
For the Lookup and MultiLookup filter types, an additional screen will be shown to define the user-selectable input values:
A Query has to be provided as a datasource for the user-selectable input values. This happens in the same way as on the Query page of the Report Wizard, although no input parameters can be used. Once again, a Datareader Login must be chosen and the query can be tested using the "Test Query" button.
Two more properties have to be specified:
- Display Member: the human-readable tag by which a user identifies and selects a record in a dropdown list
- Value Member: the technical value that represents the record, that substitutes the ##parameter## tag in the SQL query
The lookup as defined in the example above will look as follows to the user in the parameter input screen:
Defaults Tab (optional)
If any ##parameter## tags were used in the query, the next stage in the Wizard is the Defaults page. This will show you the parameter input screen in the same way as it is shown to the user prior to running the report.
Here you can specify which values will be selected by default in the parameter selection screen that the user is shown prior to running the report.
The types of the selection components depends on the types of the filters that were specified on the previous tab.
For some filter types (Date is used in the following screenshot), dynamic system variables can be selected as a value using a dropdown list on the right of the screen. These variables include things like the current date, which takes the date at the moment the report is run. The value of these variables may differ depending on when the report is run.
When the "exact value" option is chosen, or a value is chosen by hand in the UI component on the left, the fixed value that was chosen will be used and the moment at which the report is run will not affect the value.
The "Clear" option can be chosen to clear the input value. Be aware that the substitution of empty values in the SQL query can have unpredictable results.
Layout Tab
The next page in the Wizard is the Layout page. The content of this page depends on the type of the report that was chosen on the Settings page of the Wizard.
Contents
Report Type specific Layout Editors
DataGrid
The DataGrid layout editor displays the output of the query specified in the Query tab in a Devextreme DataGrid, using the default parameters specified on the Defaults tab.
Changes can be made to the layout of this grid by interacting with its elements, for example, by dragging the border of a column to change its width. Any changes that are made in this way are saved and loaded again when the report is shown. The datagrid that will be shown to the user in the actual report will have any such changes applied to it.
The Edit Settings buttons at the bottom of the page can be used to edit the technical Devexpress configuration settings of the grid or the fields of the grid respectively. Consult the Devexpress DataGrid documentation for information on what each definable property means.
To edit the settings of a column field, select a field in the dropdown box on the lower left and click Edit Settings on the lower right. Consult the Devexpress DataGrid Columns documentation for information on what each definable property means.
PivotGrid
The PivotGrid layout editor displays the output of the query specified in the Query tab in a Devextreme PivotGrid, using the default parameters specified on the Defaults tab.
Click the icon at the top left of the grid to open the Field Chooser, which will enable you to select and categorize fields to be displayed in the grid:
At the bottom of the page, the properties of the Grid, or of a selected column Field, can be edited. This works the same way as in the DataGrid. See the DataGrid section above for more information on this.
For information on definable Grid and Field properties, consult the Devextreme PivotGrid configuration documentation and the Devextreme PivotGrid Fields configuration documentation.
For fields that are marked as Data Fields in the Field Chooser, a summary type can be specified. This will determine how the values of this field from the collection of records associated with the Row Fields of the row in question will be displayed in a summarized format.
Consult the Devextreme Pivot Grid documentation for more information on how the Pivot Grid works and how it can be configured.
BIC/Excel
The BIC/Excel layout page allows you to upload an excel file that will serve as a template for the report excel output file:
The PDF layout page lets you download a layout editor desktop application, export a layout XML file and import a layout XML file again after editing it with the editor:
Finish Wizard Tab
The Finish Wizard page notifies you that your report has been created and gives you the ability to navigate to various pages related to different aspects of the report:
The instructions speak for themselves.
Do notice that the report has been split up into a datasource (containing the query and filter information you specified) and a report (containing the layout information you specified). The report and the datasource can be visited separately in the standard reporting UI.
Standard Report Creation
The following section details how a report can be created using the standard, non-wizard reporting UI. A report is split up into a datasource and a report and each of these has to be defined separately.
Datasource Creation
To create a report, a datasouce has to be created first. This can be done by clicking the add button (+ symbol) above the datasources grid:
When the datasource is created, you are brought to the datasource details page.
Settings Tab
The settings tab allows you to specify a name and (optional) description for the datasource. This does not affect the name and description of the report itself:
Queries Tab
The Queries tab lets you define multiple queries for the datasource. A main query can be selected using the radio button(s). The query thus selected will be mainly used to retrieve data for the report:
Multiple queries are only used in reports with the PDF report type. Relations between the queries have to be specified in the Relations tab to make use of this.
Clicking the edit button for a query lets you edit a query in the same way as in the Query tab of the Report Wizard.
Relations Tab
Relations are used by the PDF layout editor desktop application to specify join relations between queries. For each relation, a pair of tables and the columns they are joined on has to be specified:
For other report types, this tab can be ignored.
Filters Tab
The filters tab shows you the filters defined for this datasource. Filter types can be specified per filter just like in the Report Wizard's filters tab.
A caption by which the user can identify the filter in the filter input screen can also be specified separately in this screen.
Click the "specify default filter values" button to see a modal in which default filter values can be specified the same way as in the Report Wizard's filter defaults tab:
Report Creation
Once all details relating to the datasource are specified, a report can be created. Navigate to the reports grid via the main navigation menu and click Add Report (+ symbol):
Give the report a name (and optionally a description), select the datasource that you created before and select the appropriate report type.
Once the report is created, you will be brought to the report details page.
Report Settings
The report settings page allows you to change any of the properties that were input during the creation of the report. You can additionally specify a category based on which reports can be filtered. The category is not currently relevant for any other purpose.
Layouts Tab
The layouts page gives you the ability to define multiple layouts for the report. When more than one layout exists, a layout can be selected from the list by the user when s/he views the report.
Clicking edit for any of the layouts will bring you to the layout editor specific to the type of the report. Information on how these editors work can be found in the Wizard-based Report Creation section above.
0 Comments