Quick links:
An Excel-based report is a powerful tool for building various types of reports.
The main idea of this report is as follows:
- Using the Data Source Expressions, the necessary data are extracted from the Registers and Catalogs and the primary processing of this data is performed: grouping, sorting, calculations, etc.
- Then, the data obtained in the first step is transferred to the report template based on the Excel file. Excel tools perform additional calculations and data processing.
- The result is displayed for viewing in the form of an HTML page directly in the Hubcloud service. It is also possible to directly download the resulting Excel file.
Create a Report Template
Select Menu > Constructor > Report templates. The Constructors submenu is available only with the Designer right.
The list of Report templates looks like many other lists in the system. At the top there is a command panel with the Add, Change, Delete and Copy buttons.
To create a New Report template click Add button and select an Excel-based option.
In the New Excel report popup form, you must enter the Title of the New Report template, the Name will be generated automatically. If necessary, the Name can be adjusted manually. The maximum length of the Name is 20 characters, the Name must not contain spaces, special characters except the underscore “_” and must begin with a letter. The Name is entered with lowercase characters (lowercase characters).
Add a Memo as needed. You also can add a comment later after creating the Report template.
Click Save button to Save New Report template and start setup the Report.
The settings in the report template are grouped by the following tabs:
- Get Data – contains a list of data sources used in the report.
- Settings – on this tab, the template is downloaded and the list of report filters is configured.
- Pivot table – contains pivot table settings. The report may not contain a pivot table.
- Other – other settings.
Data sources
You must configure the expressions of the data sources that will be executed when generating the report.
Click the Data sources tab to edit the expressions of the data source.
You can use multiple data source expressions to get different data sets or individual values in a report. Click the plus button to add more data sources, see Fig. 1.
Each source must have a unique Name. The results of the data sources will be transferred to the Excel file by the names of the data sources.
Сlick the “X” button to delete an unused data source
The syntax of the data sources is described in the section Data Source Expressions. We recommend using the Console and Data Source Constructor to debug data sources.
Setup Dynamic Reporting
Dynamic Reports are implemented through filters functions that define the components of a report. You can customize two types of custom filters in reports:
- by Period or Date
- by Catalogs
Click the Settings tab to setup filters.
If you need to filter report data by period or date, select Filter Mode by Period. You can select the following options:
- Not used
- Period – user can choose different periods
- Date – user can choose a date
If you need to filter report data by Custom Dimensions, select Filters by Catalogs. Click the “+” button to add New Filter by Catalog.
Each filter has the following settings:
- Name – enter the name of the variable used by the filter in the data source expression.
- Title – enter the filter name displayed in the report.
- Catalog – select a catalog whose items will be used to select in the filter
- Empty– check, if this filter value must be empty by initial
After filter settings add filter functions to report data sources. See more:
Filter variable names by period are predefined by the system, always use them:
- &dateStart – Period Start Date
- &dateFinish – Period End Date
Catalogs filter variable names used with – &
For example:
Money | Period (&dateStart, &dateFinish) | СompanyAccount (&account) | GroupBy (CompanyAccount, Amount) | GetTitle ()
Setup Excel File Template
Click the Settings tab to edit the Excel File Template Settings.
Create Report template in Excel file.
Important! For viewing in HTML, only the first sheet of the Excel template is displayed. Service data and intermediate calculations should be performed on other sheets of the Excel template.
In the cells to which you want to transfer data, you must set the labels according to the following syntax:
- {{data_source_name}} – for data sources that return a value. For example, Sales | Period (ThisMonth) | Sum (amount)
- {{data_source_name.row_name}} – for data sources that return a table. For example, Sales | Period (ThisMonth) | GroupBy (Product, Quantity, Amount) | GetTitle(). See Figure 2.
If in the Report it necessary to display titles of Catalogs Items, you should use the _title postfix. For example, if we have a product column you should use product_title column with titles.
Save the file and upload it, click Select file > click Upload button.
If you need to edit an existing template, click Download button > save file and then you can edit it.
How to configure some report types use following links:
Other settings
The following options are available on the other settings tab:
- Name – enter or edit report name;
- Title – enter or edit report title;
- Excel engine – select the engine which is used for HTML report layout creating (see more …)
- Memo – enter or edit comments for report users;
- Execute with full rights – check if this report available only for users with full rights (see more User Access privileges );
- Published – check if this report available on a dashboard;
- Icon name – enter or edit report Icon name. You can select more than 1 500 icons from the free library fontawesome.com. Use the name like “align-center”, “angle-down” and so on.
- Icon color – enter or edit report Icon color. Use the Hex color codes like ( #E6E4E4 ) see more htmlcolorcodes.com