Complex Reports

Complex reports integrate various native editing operations and formula configuration capabilities of Excel, support multi-data source access, and through visual drag-and-drop and configuration, users can quickly achieve complex layouts and calculations in the Excel interface for Chinese-style complex reports. At the same time, complex reports are deeply integrated with the platform's existing analysis capabilities, supporting data update synchronization, data filtering linkage, data row and column permission control, and embedded scenario analysis.

Complex reports have the following advantages over ordinary tables:

• Multi-Data Source: Complex reports can analyze and display data from multiple data sources, which can come from one database or multiple databases.
• Diverse Styles: The headers of complex reports support dynamic expansion, diagonal headers, and fields can be grouped and expanded by row or column, thereby achieving various forms of reports such as grouped reports and detailed reports.
• Complex Calculations: Report rows and columns can use advanced calculations from HENGSHI SENSE as well as support calculations using Excel formula functions.

Create Complex Report

In the dashboard, click New Chart -> Complex Report, select the data source and dataset, and click OK to create a new blank complex report.

The newly created complex report contains no data. Unlike other reports, a complex report needs to be opened to be edited. Click the three-dot menu in the upper right corner to open the complex report and enter the editing page.

Editing Complex Reports

The complex report editing page is divided into three parts: the leftmost (red box) is the data area, displaying the data used by the complex report; the middle (blue box) is the configuration area, mainly for configuring report-related data and data display formats; the right side is the cell area, where you can set cell attributes and cell styles, etc. After completing the editing of the complex report, click the preview button in the upper right corner to view the report display style.

Row and Column Data Display

When displaying data in cells, it is divided into two steps: first, select the display position of the data in the table, such as selecting a specific cell, and then drag the fields to be displayed into the row or column display area.

In the example, first select cell A3, then drag the City field to the row field, and the field will be displayed in rows. When dragging City to the column field, the field will be displayed in columns.

• Detailed Data When a field is directly dragged into rows or columns, detailed data information is displayed. Detailed data is the detailed information in the dataset. If aggregated data information is to be displayed, grouping settings can be applied to the fields.

• Aggregated Data After dragging a field into rows or columns, selecting grouping in the calculation achieves aggregated data display. For example, grouping by city results in the following display.

Tip

1. When performing a "cut-copy" operation on the cell configured with data in the cell area, it can carry the configuration data and other dependencies. For example, if cell A3 is cut and then copied to cell A8, the corresponding configuration data will also be displayed in cell A8.
2. There is a one-to-one mapping relationship between fields and their bound cells, allowing for quick association between the two. Clicking on a field highlights its bound cell, and vice versa.

Group By

Field Grouping Criteria

When fields are dragged into complex reports for row and column data display, they are not associated with other fields. Multiple fields from the same dataset or data model are displayed independently without incorporating the corresponding relationships from the dataset. As shown in the figure, when dragging in the "Province" and "City" fields, there is no connection between them, and they are displayed randomly. In reality, there is a correspondence between cities and provinces, such as Hangzhou belonging to Zhejiang Province and Guangzhou belonging to Guangdong Province. During display, they need to be shown in correspondence.

In complex reports, grouping criteria are used to establish a connection between two fields. First, select the cell to be set, and then set the grouping field for that cell in the cell configuration area on the left. After successful setup, the field in that cell will be displayed based on the grouping field. For example, to display cities grouped by province, first click on the cell containing the city field, then click on the cell in the configuration area on the left, and select to group by row group A3 (A3 is the cell containing the province field) in the grouping criteria. During data display, the city field will be grouped and displayed by province.

In the above example, grouping is done by row group. Complex reports also support grouping by column group. In some complex scenarios, both row group and column group grouping may be required. For example, in a sales report for stores in various cities of a large supermarket, sales amounts are displayed separately by product category. Therefore, to calculate the order price, which is the sales amount (highlighted in yellow), grouping is done in the row group direction by C4 (highlighted in blue), i.e., displayed separately by each city, and in the column group direction by D3 (highlighted in red), displayed by product category. The final data display result is shown in the yellow box on the right.

Tip

1. The grouping criteria do not have to be adjacent cells; any cell in the current complex table can be used as a grouping criterion.
2. Complex reports support selecting cells to the left and above as grouping criteria. For example, cell E4 can only set A(1-4), B(1-4), C(1-4), D(1-4), E(1-3) as grouping criteria, and cannot select cells in column F or later, nor can it select cells in row 5 or below, nor can it select itself as a grouping criterion.

Text Grouping Basis

In some cases, text content in complex tables also needs to be set with grouping basis, associated with other fields, and expanded along with other fields.

As shown in the figure, in the sales data of various provinces, 'Total Sales' and 'Province Total' are text information, but the header D3{{商品列表.类别}} is a field, which will expand according to the field content. In this way, 'Total Sales' and 'Province Total' will also change with the expansion of {{商品列表.类别}}.

First, let's look at 'Total Sales'. As it expands horizontally with the expansion of {{商品列表.类别}}, it is set to expand horizontally in the expansion direction.

Next, we look at 'Province Total'. The province total will expand with {{商品列表.类别}}, such as there will be a province total for fresh produce, and there will also be a province total for food products, etc. Therefore, 'Province Total' needs to be grouped by {{商品列表.类别}}.

The dynamically expanding field {{商品列表.类别}} needs to be displayed within the range of the 'Total Sales' cell, so the grouping basis of {{商品列表.类别}} should be set to D2, establishing a connection with 'Total Sales'.

Data Calculation

Complex report fields inherit HENGSHI SENSE's original calculation capabilities, supporting various advanced calculations, and also support using Excel formula functions for calculations.

As shown in the complex report displaying sales data for various cities (the yellow area in the figure), to calculate the total sales data for each city, you can use HENGSHI SENSE's built-in calculation capabilities (the blue area) or use Excel's SUM function for calculation (the red area).

Tip

1. When using Excel functions, if you are calculating fields configured for cells, the function formula needs to add {{}}, and the formula will take effect on all data expanded from the cell fields. If you are only calculating a specific cell value, you do not need to add {{}}.
2. The standard Excel functions supported by complex report fields are Sum, Count, Average, Max, Min, Product, StdDev, StdDevp, Var, and Varp. Other functions are currently not supported.

Multi-Source Complex Reports

Unlike other charts, complex reports support the simultaneous display and analysis of multiple data sources, which can come from the same database or multiple different databases.

As shown in the figure, click the ＋ icon on the right side of the data model to add new data. The left configuration table only displays the field configuration information related to the selected data model. For example, if the Income model is currently selected, it shows the fields used in the Income model. To configure or view the fields used in the Expense model, you need to click the Expense model and switch to the expense model to select the field configuration.

Header Display

Complex report headers can be categorized from a perspective of change into static headers and dynamic headers. Static headers do not contain field information for configuring data and do not change after being set. Dynamic headers include data field information and change as fields are expanded. Below are examples of both types of headers.

Static Header

The static header consists of pure text information and does not contain field information, so it does not dynamically expand. The number of cells in the header is fixed. As shown in the sales data report for various cities, the header consists of three columns: province/city, city, and total sales, and only these three columns are present when the report is generated.

Dynamic Headers

Dynamic headers refer to headers that contain field information, and the headers change dynamically as fields are expanded. For example, in a sales data report for various cities that needs to display sales data for different types of products, the product category field is dragged into the header cell. This field dynamically expands based on the product categories when generating the report, and the corresponding upper-level title "Total Sales" also expands dynamically with the product categories. This type of header is a dynamic header.

In dynamic headers, it is important to pay attention to the grouping basis of the fields. If the field is at the top level, it expands without being restricted by other cells. If there are other text information above it, as in the example, the dynamically expanding field <span v-pre>{{商品列表.类别}}</span> needs to be within the scope of the "Total Sales" cell. Therefore, the grouping basis for <span v-pre>{{商品列表.类别}}</span> should be set to D2, establishing a connection with "Total Sales".

Complex Report Interaction

Image Settings

Complex reports support image recognition of data and image display. You can follow the steps below to set up images.

1. Set the cell to display the image. In the example, the image is displayed in cell A2.
2. Drag the image information field from the dataset into the cell where the image is to be displayed, and set this field as the basis for grouping in the image display cell. In the example, the image-related field is dragged into cell I2 (this column will not be displayed in the chart and will be hidden later), and the grouping basis in cell A2 is set to I2.
3. Check the "Show Image" option in the image settings.
4. The image setup is complete. Click "Preview" to view the setup effect.

QR Code Settings

Complex reports can generate QR codes or barcodes from the report content. Currently, the following types are supported: QRCode, PDF417, GS1_128, EAN8, EAN13, Code93, and Codabar. Below is an example of how to use the QR Code settings feature with a QRCode.

Setting up a QR code involves three steps:

1. Select the cell where the QR code will be displayed. In this example, the QR code is displayed in cell A1.
2. Drag the field to be displayed as a QR code, and set the content to be displayed in the QR code cell as the grouping basis. In this example, the C1 order code is displayed as a QR code, so the grouping basis in cell A1 is set to C1.
3. Choose the type of QR code to be displayed. In this example, the QRCode is selected.
4. The QR code setup is complete. Click Preview to view the settings effect.

Pagination

When the data content of complex reports is extensive, pagination can be set up for complex reports. Each page displays content of a fixed page size, with content exceeding the page size being shown on the next page. Users can view the content of each page through a pagination control. To use the pagination feature, the pagination switch needs to be turned on.

To make the data presentation more aesthetically pleasing, smooth, and complete during pagination, the following settings are supported:

• Merged cells displayed across pages
• Specified cells repeated display
• Specified cells jointly displayed
• Pagination without breaks

Merged Cells Display Across Pages

When merged cells are split across two pages, the content is usually only displayed in the cell on the first page, while the cell on the second page remains blank. As shown in the figure, there is no data within the red box on the second page, which may lead to confusion when viewing the data, as the content might be missing and require flipping back to the previous page to obtain the merged cell content.

Complex reports support displaying merged cells across pages, meaning that when a merged cell spans across pages, the content is displayed on each page. In the above example, after setting "Jiangsu Province" and the corresponding "Province Summary" to display merged cells across pages, the data will be shown on every page. First, select cell B4 {{订单.省份}} (red box), then check the option for displaying merged cells across pages in the pagination settings. Apply the same setting to the order prices summarized by province (blue box). The preview effect is shown in the figure, where the cell "Jiangsu Province" and the corresponding "Province Summary" display data on every page.

Specify Cell Repeated Display

Specify Cell Repeated Display refers to certain cells being repeated on the page, commonly used for header or footer information. As shown in the figure, when the student score table is displayed in pages, it is desired to show the header and explanatory information on each page. Therefore, the header cell B2 (red box) is set to follow cell B5 (blue box) for display, meaning that the header will be shown on each page when displaying student scores. Similarly, the explanatory information cell B7 is also set to follow cell B5 for display, meaning that the explanatory information will be shown on each page when displaying scores.

The pagination effect after setting is shown in the figure, with the header and explanatory information displayed on each page.

Specify Cell Co-display

When a report is displayed in pages, data exceeding the system-specified page size will be split, and some related data may be displayed across two pages, resulting in orphaned lines on one page, breaking the continuity of data information. As shown in the figure, the report displays the sales performance of products in stores across various cities. During the report display process, the city of "Hangzhou" and its subordinate stores are split across two pages.

To ensure that a city and its affiliated stores are displayed together, pagination settings are applied to the cell B3 (city) to co-display it with cell B4 (stores). After setting, the city "Hangzhou" is no longer displayed as a single line but is shown together with its affiliated stores.

Pagination Without Break

During the pagination process of reports, some complete information may be split into two parts. In such cases, you can use the "Pagination Without Break" setting to ensure that the complete information is displayed together. For example, in the salary statement of a certain unit, it is desired that each employee's information can be fully displayed on one page without being split by pagination. In the header cell C6 (red box), set the pagination to ensure that the information from the header cell C6 to the end of the salary details row F13 (blue box) is not broken.

Without the "Pagination Without Break" setting, the information of employee "Yan Yiqi" is displayed across two pages. After setting, the information of employee "Yan Yiqi" is fully displayed on the second page.

Tip

The "Pagination Without Break" setting requires selecting all the cells that should not be broken, setting the range of cells to be displayed. In the above example, the unbroken range is set from C6:F13. If there is only one row of data, set the entire row.

Print

Complex reports support printing. When you open a report in the App Market, there is a print button in the lower right corner, which you can click to initiate printing.

On the print page, you can set the number of pages, page size, margins, headers/footers, and worksheets. Clicking the print button will pop up a print dialog where you can set the printer for printing. The functionality of each print item is not described in detail here.

Complex Report Publishing

Complex reports support operations such as data filtering, sorting, column hiding, editing, insertion, and copying in the published state.

Interaction with Other Controls

Complex reports do not currently support interaction with other controls.

Usage Restrictions and Considerations for Complex Tables (Complex Reports):

1. Row Limit Not Effective: The system setting that limits the number of rows for data export in tables will not take effect in complex tables. This is because complex tables support multiple datasets being rendered through templates to produce results, and the export row limit for a single table is not directly applicable here. This limitation is currently not supported for complex tables.
2. Data Source Row Limit: The process of rendering templates with data in complex tables involves calculations, which are memory-intensive. Therefore, we have imposed a row limit on the data used for template rendering, with a default of 500,000 rows. This limit can be modified via the configuration item COMPLEX_TABLE_DB_QUERY_LIMIT, but considering memory usage, it is recommended not to set it too high.
3. Display Format Settings: The display format is set within the cell, unlike other charts where it is set on the axis.
4. Exporting Chinese Characters as Gibberish: If Chinese characters appear as gibberish when exporting to PDF or Excel, the server needs to have the corresponding Chinese fonts installed, and the configuration item GRAPECITY_GCEXCEL_FONTS_PATH should be set to the parent directory where the fonts are located.
5. No Cross-Source Calculations with Templates: Cross-source calculations across multiple data sources using template formulas are not supported. For such requirements, the data sources need to be combined at the Dataset level before being used in complex tables.
6. Overall Border When Printing: Printing a complex table will include an overall border. If this is not desired, it is recommended to download the data first and then print it.
7. Null Value Display Issues: Null values entering the template may be converted into strange numbers and can sometimes cause the browser to freeze. It is recommended to replace null values before data rendering, such as replacing null values in the Dataset.
8. Conditional Formatting Fails with Pagination: Conditional formatting will not work when pagination is enabled. It is recommended not to enable pagination in this scenario.
9. QR Code Recognition Fails with Pagination: QR code recognition will not work when pagination is enabled. It is recommended not to enable pagination in this scenario.
10. Image Recognition Fails with Pagination: Image recognition will not work when pagination is enabled. It is recommended not to enable pagination in this scenario.
11. Double Equals Formula Template Fails with Pagination: Double equals formula templates (e.g., {{==round(B8-C8,2)}}) will not work when pagination is enabled. It is recommended not to enable pagination in this scenario.
12. No Non-Group Aggregation for Text and Date Types: Text and date types in the axis of complex tables can only be grouped; they cannot be aggregated using functions like max. Such configurations will cause display anomalies. It is recommended to perform the aggregation in the Dataset and then render the complex table with the details.
13. Only One Sheet Supported: Currently, only one sheet is supported in the template; multiple sheets are not supported.
14. Details and Aggregated Data are Calculated Separately: In complex tables, if one axis is for details and the other is for aggregated calculations, they should not be set as grouping criteria for each other, as this will not take effect.