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 Chinese-style reports with complex layouts and calculations in the Excel interface. 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.
• Styling Diversity: The headers of complex reports support dynamic expansion, diagonal headers, and fields can be grouped and expanded by rows and columns, thereby realizing 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 Excel formula functions for calculations.

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, open the complex report, and enter the editing page.

Edit 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 properties 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 and column display areas.

In the example, first select cell A3, then drag the City field as a row field, and the field will be displayed in row form. When dragging City as a column field, the field will be displayed in column form.

• 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, select grouping in the calculation to achieve aggregated data display. For example, grouping by city will result in the following display.

Tip

1. When performing "cut-paste" 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 pasted 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 the bound cell, and vice versa.

Group By

Field Grouping Criteria

When fields are dragged into complex reports for row and column data display, they do not associate with other fields. Multiple fields from the same dataset or data model are displayed independently without carrying over the corresponding relationships from the dataset. As shown in the figure, when dragging in the fields for province and city, they are displayed randomly without any connection. In reality, there is a corresponding relationship between cities and provinces, such as Hangzhou belonging to Zhejiang Province and Guangzhou belonging to Guangdong Province. When displaying, 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 this field in the cell on the left configuration area. After successful setup, the field in this cell will be displayed based on the grouping field. For example, to display cities grouped by province, first click on the cell where the city field is located, then click on the cell in the left configuration area, and select to group by row group A3 (A3 is the cell where the province field is located) in the grouping criteria. When displaying data, 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 cases, it may be necessary to group both by row group and by column group. 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 (yellow box), grouping is done in the row group direction by C4 (blue box), i.e., displayed separately by each city, and in the column group direction by D3 (red box), displayed by product category. The final data display result is shown in the right yellow box.

Tip

1. The grouping criteria do not have to be adjacent cells; any cell in the current complex table can be used as the grouping criteria.
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, cannot select cells after column F, cannot select cells below row 5, and cannot select itself as the grouping criteria.

Text Grouping Basis

In some cases, text content in complex tables also needs to be set as a 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{{Product List.Category}} is a field that will expand according to the field content, so 'Total Sales' and 'Province Total' will also change with the expansion of {{Product List.Category}}.

First, let's look at 'Total Sales', which will expand horizontally as {{Product List.Category}} expands, so it is set to expand horizontally in the expansion direction.

Next, let's look at 'Province Total', which will expand with {{Product List.Category}}, such as there being a province total for fresh produce, and a province total for food items, etc. Therefore, 'Province Total' needs to be grouped by {{Product List.Category}}.

The dynamically expanding field {{Product List.Category}} needs to be displayed within the range of the 'Total Sales' cell, so the grouping basis for {{Product List.Category}} 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 supporting the use of Excel formula functions for calculations.

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

Tip

1. When using Excel functions, if you are calculating fields configured for cells, then the function formula needs to add {{}}, the formula will take effect on all data expanded for 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, the fields used in the Income model are displayed. If you need to configure or view the fields used in the Expense model, you need to click the Expense model to switch to the expense model for field selection and configuration.

Header Display

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

Static Headers

Static headers consist of pure text information and do not contain field information, so they do not dynamically expand. The number of header cells is fixed. As shown in the sales data report for various cities, when editing the header, it consists of three columns: Province/Municipality, City, and Total Sales. When the report is generated, there are only these three columns.

Dynamic Headers

Dynamic headers refer to headers that include field information, and the headers change dynamically as fields expand. For example, in a sales 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 during report generation based on the product categories, and the 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 will expand without being restricted by other cells. If there is other text information above it, as in the example, the dynamically expanding field {{商品列表.类别}} needs to be within the scope of the 'Total Sales' cell. Therefore, the grouping basis for {{商品列表.类别}} 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 supported types include QRCode, PDF417, GS1_128, EAN8, EAN13, Code93, and Codabar. Below is an example using QRCode to illustrate how to use the QR code settings feature.

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, QRCode is selected.
4. The QR code setup is complete. Click Preview to view the setup effect.

Pagination

When the data content of complex reports is extensive, pagination can be set for complex reports. Each page displays content of a fixed page size, and content exceeding the page size is displayed on the next page. Users can view the content of each page through the pagination control. Pagination needs to be enabled by toggling the pagination switch.

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

• Merge cells across pages
• Specify cell repetition display
• Specify cell joint display
• Pagination without break

Merged Cells Display Across Pages

When merged cells are split across two pages, the content is usually only displayed in the first page's cell, while the cells on the second page are blank, as shown in the figure. The red box on the second page contains no data, which may cause confusion when viewing the data, as the content may be missing and require flipping back to the previous page to obtain the content of the merged cell.

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 each 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 each page.

Specify Cell Repeat Display

Specify Cell Repeat Display refers to the repetition of certain cells 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 description 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 description information cell B7 is also set to follow cell B5 for display, meaning that the description information will be shown on each page when displaying scores.

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

Specify Cells to Display Together

When a report is displayed in pages, data exceeding the system-specified page size will be split, and some related data may be displayed on two separate pages, resulting in orphaned lines on one page, breaking the continuity of data information. As shown in the figure, the report displays the sales of goods in stores across various cities, with the city of "Hangzhou" and its associated stores being split across two pages.

To ensure that a city and its associated stores are displayed together, pagination settings are applied to the city's cell B3, specifying that it should be displayed together with cell B4 for the stores. After setting, the city "Hangzhou" is no longer displayed as a single line but is shown together with its associated stores.

Pagination Without Break

During the pagination process of reports, some complete information may be divided 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 case of a salary slip display for 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 continue uninterrupted from the header cell C6 to the end of the salary details row F13 (blue box).

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

Tip

The "Pagination Without Break" setting requires selecting all uninterrupted cell data, setting the range value of the display cells. In the above example, the uninterrupted range set is from C6:F13. If there is only one row of data, set the entire row.

Print

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

On the print page, you can set the number of pages, page size, margins, headers/footers, and worksheets. Clicking print will pop up a print dialog where you can set the printer for printing. The functions of each print item are 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.