AnalyticDB:FineReport

Set up the demo data

1. Download the demo_contract table.

2. Create the demo_contract table in your AnalyticDB for MySQL cluster. Use a column with evenly distributed values as the distribution key and a time column as the partition key. See Table schema design for guidance. In this example, contract_id is used as the distribution key because its values are randomly distributed:

   Create Table `demo_contract` (
    `contract_id` varchar,
    `customer_id` varchar,
    `contract_type` varchar,
    `amount` bigint,
    `payment_type` varchar,
    `register_time` date,
    `purchase_amount` int,
    `create_time` timestamp,
    `product_type` smallint,
    `paid` tinyint,
    primary key (`contract_id`)
   ) DISTRIBUTED BY HASH(`contract_id`) INDEX_ALL='Y' STORAGE_POLICY='COLD'

3. Import data from the XLSX file to AnalyticDB for MySQL using Kettle. See Use Kettle to import data to Data Warehouse Edition. After the import, run the following to verify the data:

   SELECT count(*) FROM demo_contract

   The query returns 668 entries.

Import datasets into FineReport

Create two datasets in FineReport using the imported data.

1. Create dataset d1 to get the maximum sales volume by product type, contract type, and payment type:

   select product_type, payment_type, contract_type, max(purchase_amount) as max_amount from demo_contract group by product_type, payment_type, contract_type having max_amount>0

2. Create dataset d2 to get total sales by product type and contract type: Categorize data by product type and contract type, and calculate the total sales volume across all payment types.

Design the table

1. Title: Merge cells A1 to D2. Enter Contract Sales Overview in the merged cell, set the font size to 15, and make it bold and centered.

2. Angled header: Merge cells A3 and B3. Right-click the merged cell and choose Cell Element > Insert Slash. In the Slope Line dialog, enter:

   Payment Type | Contract Type | Product Type

   Add spaces to adjust text positions.

3. Formulas: Drag the data columns with uneven value distribution to the corresponding cells. Then add the following formulas:

   • Click cell C5. Click the Insert Formula icon in the shortcut bar. In the Formula Definition dialog, enter SUM(C4) to sum total sales by product.

   • Click cell D5 and add SUM(D4) to sum total sales across all products.

   

4. Expansion directions: Control how data expands across cells.

   • Click cell C3. In the panel, click Cell Attributes > Expand tab. In the Basic section, set Expand Direction to H so product type data expands horizontally.

   • Set Expand Direction to No Expansion for cells C4, C5, and D5, because these cells hold unique values tied to specific combinations.

   

5. Dataset association: When multiple datasets appear in the same report, link them using shared fields. Datasets d1 and d2 both contain product_type and contract_type, so use these fields to associate the two datasets. Double-click cell D4. In the Data Column dialog, click the Filter tab and add a common condition to link product_type and contract_type across d1 and d2.

Add a column chart

1. Merge cells A7 to D15. In the top menu bar, choose Insert > Cell Element > Insert Chart. Select Column Chart from the Chart Type list, choose Column Chart in the right-side list, and click OK.

   

2. Click the chart. In the panel, click Cell Element. On the Data tab, set the following:

   • Data Source: Cell Data

   • Category: =B4

   • Series: =C3

   • Value: =C4

   Note

   Prefix each cell reference with =. Without the =, FineReport treats the value as a string instead of a cell reference.

   

Preview the report

Save the report first, then click Preview and select Pagination Preview from the drop-down. The report opens in your browser.