This article introduces best practices for advanced applications of dataset parameters in Chinese-style reports.

Background information #

Chinese-style reports are a type of report with unique characteristics and complex requirements. They are typically based on Excel files and generated through a series of processing steps. As an integral part of data management and presentation for most traditional domestic enterprises, they are also referred to as "complex reports." The presentation format of Chinese-style reports differs from DataWind dashboards and more closely resembles the style of pivot tables.
Characteristics of Chinese-style reports:

• To meet the diverse and refined management needs of domestic enterprises, Chinese-style reports usually involve large volumes of data, densely covering numerous detailed and summary data points.
• The calculation methods are complex and varied, including multiple mathematical computations and logical judgments.
• They feature rich statistical dimensions, requiring comprehensive analysis and presentation of data from multiple perspectives and levels to satisfy the diverse needs of different departments and organizational tiers.
• They place a strong emphasis on data integrity and accuracy, striving to present all relevant data with precision and without error.

This complexity, along with selective calculations under specific conditions, makes it difficult for most agile BI charting features to directly and effectively support and display such reports, presenting certain challenges for data presentation and analysis.
As shown in the following figure, the format of Chinese-style reports includes multi-level summaries and calculations under specific conditions, which existing agile BI or visualization tools cannot easily address through front-end solutions:

Feature introduction #

DataWind supports using custom SQL to prioritize the processing of required fields, then joining them with the original table to form a new table. The results can be displayed through the basic pivot table functionality on the frontend, thereby achieving the effect of Chinese-style reports in DataWind. Additionally, the feature of inserting parameters into direct connection datasets can be used, making the final display results more flexible and superior compared to traditional Excel.
By leveraging the custom SQL functionality, Chinese-style reports can be presented on the DataWind dashboard:

• Before—display results when the original table is not processed: A simple pivot table-style chart that cannot meet the requirements for Chinese-style report presentation.

  

• After—results after processing: Using the custom SQL functionality, the original data source is read, the required fields are processed and joined with the original data, and the resulting dataset is created as needed. By using the parameter-passing capability of direct connection, preset parameters can be introduced in advance, enabling flexibility when displaying charts on the frontend.

Connect to a data source #

1. Go to the "Data Preparation" – "Dataset" page, click "Create" dataset, and create a ClickHouse dataset.

   warning

   Since the Insert Parameter feature in the Direct Connection Dataset is required for this task, ClickHouse data must be selected.

   

2. After adding, enter the following page and select custom SQL and direct connection mode.
   

Build the required dataset #

According to the actual business scenario, process the fields that require further processing using SQL statements, then join them with the original table.
Example scenario: For instance, if the business requires summing the sales amount under certain conditions, and there are multiple scenarios for summing, each field to be summed needs to be constructed separately and then joined together.

1. Single-field construction.
   Operation instructions: Filter data where the shipping method for specific order dates is "Level 1" or "Level 2". Group by "Province/Autonomous Region, Shipping Method 2, Order Date", and sum the sales amount and quantity.

   select
         'sum' as `province/autonomous region`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('first-level', 'second-level')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
   

   tip

   The reason for renaming "Mailing Method 2" is that the "Mailing Method" condition is used in the WHERE clause. If it is not renamed, this condition will not work.

   Generate data in the following format:

   Province/Autonomous Region	Mailing Method 2	Order Date	Sales amount	Quantity
   Total	Level 1 + Level 2	2019-01-01	xxxx	xxxx
   Total	Level 1 + Level 2	2019-01-02	xxxx	xxxx
   ......
   Total	Level 1 + Level 2	2019-06-30	xxxx	xxxx

2. Multiple-field construction.
   Instructions: Vertically combine the constructed single fields using the union all method.

   warning

   When concatenating fields, the structure above and below must remain consistent.

   select
         'sum' as `province/autonomous region`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('first-level', 'second-level')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
   
       select
         'sum' as `province/autonomous region`,
         'second-level+standard-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('standard-level', 'second-level')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
       union all
   
       select
         'sum' as `province/autonomous region`,
         'first-level+second-level+standard-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('first-level', 'second-level', 'standard-level')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
   

   tip

   All of this content needs to be displayed under Total, so a unified dimension value must be assigned to these fields, and they must be at the same level as Province. Therefore, a unified value of "Province/Autonomous Region" can be assigned to this batch of fields.

Generate data in the following format:

3. Combine with the original table to form a new table that contains all the fields required for subsequent chart display.

   select
     `sales amount`,
     `quantity`,
     `province/autonomous region`,
     `Shipping Method 2` as `Shipping Method `,
     `Order Date`
     
   --It is necessary to change the field names that were previously
   --renamed back here to ensure that the data corresponds to the original
   --table when associated with it.
   
   from
     (
    select
         'sum' as `province/autonomous region`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('first-level', 'second-level')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
       union all
       .....
    select
         'sum' as `province/autonomous region`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30'
         and `Shipping Method ` in('first-level', 'second-level','standard-level','当日')
         and `p_date`='2023-09-25'
       group by
         `province/autonomous region`,`Shipping Method 2`,`Order Date`
     ) as tmp
   
   union all
   
   select
     `province/autonomous region`,
     `Shipping Method `,
     `Order Date`,
     `sales amount`,
     `quantity`
   from
     `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
   where
      `Order Date`>='2019-01-01'
     and `Order Date`<='2019-06-30'
     and `p_date`='2023-09-25'
   

   Generate data in the following format:

   Province/Autonomous Region	Mailing Method 2	Order Date	Order Date	Quantity
   Total	Level 1 + Level 2	2019-01-01	xxxx	xxxx
   Total	Level 1 + Level 2	2019-01-02	xxxx	xxxx
   ......
   Total	Level 1 + Level 2	2019-06-30	xxxx	xxxx
   Total	Level 2 + standard-level	2019-01-01	xxxx	xxxx
   ......
   Total	Level 2 + standard-level	2019-06-30	xxxx	xxxx
   Total	Level 1 + Level 2 + standard-level	2019-01-01	xxxx	xxxx
   ......
   Total	Level 1 + Level 2 + standard-level	2019-06-30	xxxx	xxxx
   Shanghai	Level 1	2019-01-01	xxxx	xxxx
   Shanghai	Level 1	2019-01-03	xxxx	xxxx
   Beijing	Level 1	2019-01-05	xxxx	xxxx
   ......

Create visualization charts #

Use the dataset generated in the previous steps for visualization queries. Only minor adjustments to the table are needed to achieve the style of traditional Chinese reports, that is, to implement the basic display of Chinese-style reports on DataWind.

Add parameters to enhance report flexibility #

After implementing the basic Chinese-style report, parameters can be used to add more flexibility to the charts.

Configure parameters #

Pre-configure the required parameters for easy subsequent use.
Application scenario: Display the required data segments by switching dates and different provinces.
Operation steps: Go to the "Data Preparation" - "Dataset" page, click "Parameter Management" - "Create Parameter", and create the relevant date and province parameters.

Considering scenarios where multiple provinces are selected, the "Province/Autonomous Region" parameter is set to "Allow multiple selection".

Insert parameters #

Go to the ClickHouse data configuration page, and in the custom SQL under direct connection, click "Insert Parameter" to insert the required parameter placeholder at the corresponding position.
Parameter construction steps:

1. Single field construction.

   select
         'sum' as `province/autonomous region2`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`,
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>={start date}
         and `Order Date`<={finish date}
         
         --Here, two date parameters are introduced as future conditional judgments, such as
         start date为2019-01-01 finish date为2019-06-30
         --The actual conditions here are:
         `Order Date`>='2019-01-01'
         and `Order Date`<='2019-06-30' 
         --Consistent with the previous logic
         
         and `Shipping Method ` in ('first-level', 'second-level')
         and `p_date`='2023-09-25'
         and `province/autonomous region` in ({province/autonomous region})
         
         --Another parameter has been introduced here{province/autonomous region}
         --But since the parameter{province/autonomous region} is a multiple-choice value,
         --such as selecting Shanghai and Beijing,
         --here is `province/autonomous region` in ('Shanghai','Beijing'),
         --So we need to use 'in' to make a judgment,
         --not`province/autonomous region` = {province/autonomous region} to make a judgment--  
         
       group by
         `province/autonomous region2`,`Shipping Method 2`,`Order Date`
   

2. Multiple field construction.

   select
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`,
         'sum' as `province/autonomous region2`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>={start date}
         and `Order Date`<={finish date}
         and `Shipping Method ` in('first-level', 'second-level')
         and `p_date`='2023-09-25'
         and `province/autonomous region` in ({province/autonomous region})
       group by
         `province/autonomous region2`,
         `Shipping Method 2`,
         `Order Date`
       union all
       --The logic here is the same as above,
       --using union all to concatenate content with the same table structure,
       --involving multiple fields that can be pieced together in this way--
       select
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`,
         'sum' as `province/autonomous region2`,
         'second-level+standard-level' as `Shipping Method 2`,
         `Order Date`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>={start date}
         and `Order Date`<={finish date}
         and `Shipping Method ` in('standard-level', 'second-level')
         and `p_date`='2023-09-25'
         and `province/autonomous region` in ({province/autonomous region})
       group by
         `province/autonomous region2`,
         `Shipping Method 2`,
         `Order Date`
   

3. Combine the newly constructed fields with the original table to generate a new dataset.

   select
     `sales amount`,
     `quantity`,
     `province/autonomous region2` as `province of affiliation`,
     `Shipping Method 2` as `order channel`,
     `Order Date`
   from
     (
       select
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`,
         'sum' as `province/autonomous region2`,
         'first-level+second-level' as `Shipping Method 2`,
         `Order Date`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>={start date}
         and `Order Date`<={finish date}
         and `Shipping Method ` in('first-level', 'second-level')
         and `p_date`='2023-09-25'
           and `province/autonomous region` in ({province/autonomous region})
       group by
         `province/autonomous region`,
         `Shipping Method 2`,
         `Order Date`
       union all
       .....
       --Here are all the fields that need to be constructed--
       union all
       select
         sum(`sales amount`) as `sales amount`,
         sum(`quantity`) as `quantity`,
         'sum' as `province/autonomous region2`,
         'All Method' as `Shipping Method 2`,
         `Order Date`
       from
         `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
       where
         `Order Date`>={start date}
         and `Order Date`<={finish date}
         and `Shipping Method ` in('first-level', 'second-level', 'standard-level', '当日')
         and `p_date`='2023-09-25'
           and `province/autonomous region` in ({province/autonomous region})
       group by
         `province/autonomous region`,
         `Shipping Method 2`,
         `Order Date`
     ) as tmp
   union all
   select
     `sales amount`,
     `quantity`,
     `province/autonomous region` as `province of affiliation`,
     `Shipping Method ` as `order channel`,
     `Order Date`
   --When splicing, it is important to note that the structure names of the two tables need to be consistent--
   from
     `aeolus_data_db_rangers_202309`.`aeolus_data_table_22_3751_prod`
   where
     `Order Date`>={start date}
     and `Order Date`<={finish date}
     and `p_date`='2023-09-25'
     and `province/autonomous region` in ({province/autonomous region})
   

After inserting parameters, in SQL for constructing table values, according to the selected å**{Province/Autonomous Region}** parameter, the statistical results under different parameter conditions will be automatically calculated.

Dashboard filter adjustment #

By adding parameters to the dashboard, adjusting parameter visibility, associating filters with parameters, and modifying the order of filter conditions, the front-end display can be optimized, enabling more flexible Chinese-style reports to be presented on DataWind.