AnalyticDB:FineReport

After you connect to the AnalyticDB for MySQL cluster, you must create a dataset. The dataset consists of data that is retrieved from an existing database by using SQL statements. The data is stored in a two-dimensional table that is displayed in the dataset management panel.

1. Click the Template Dataset tab and then click the + icon in the upper-left corner of the tab.

2. Select DB Query from the drop-down list.

3. In the DB Query dialog box, set Name to ds1. Select the AnalyticDB for MySQL cluster from the drop-down list in the upper-left corner. In the left-side list, click the corresponding table. In the right-side editor, enter the data query statements. In this example, the select * from demo_sales statement is used to query and retrieve all the data in the demo_sales table, as shown in the following figure.

4. Obtain the dataset that consists of the results of an aggregate query. For example, you can create a dataset named ds2 and then query and retrieve the total sales of each store by using the following query statement: SELECT <Store name>, SUM(Revenue) AS <Total sales volume> FROM demo_sales GROUP BY <Store name>.

5. Click OK. The retrieved data is displayed in the dataset management panel.

In the following section, the imported data is used to create a demo report:

1. Import data from the d1 and d2 tables to FineReport. The data is sorted by payment type and contract type. The maximum sales volume of each payment type and the total sales volume are calculated. Two tables named d1 and d2 are imported.

   1. Execute the following statement to categorize the data in the d1 table by product type, contract type, and payment type and obtain the maximum sales volume under each category:

      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. Categorize the data in the d2 table by product type and contract type, and calculate the total sales volume of all payment types, as shown in the following figures.

2. Perform the following operations to design the table:

   1. Title: Merge the A1 to D2 cells and enter the following title in the merged cell: Contract Sales Overview. Set the font size to 15 and make the title bold and centered.

   2. Angled lines: Use angled lines to separate the category names in the upper-left cell. Merge the A3 and B3 cells, right-click the merged cell, and then choose Cell Element > Insert Slash. In the Slope Line dialog box, enter the following content in the field: Payment Type | Contract Type | Product Type. You can add spaces to adjust the positions of the text.

3. Add formulas to cells.

   1. Drag the columns that have an uneven distribution of values to the corresponding cells.

   2. Click the C5 cell, and click the Insert Formula icon in the shortcut bar above the parameter pane. In the Formula Definition dialog box, enter the following formula in the editor: SUM(C4). This sums up the values in the C4 cell. The C5 cell is used to calculate the total sales volume of each product.

   3. Repeat the preceding operations to add the SUM(D4) formula to the D5 cell. The D5 cell is used to calculate the total sales volume of all products.

4. Set the expansion directions of cells.

   1. Click the C3 cell. In the panel, click Cell Attributes. In the Cell Attributes dialog box, click the Expand tab. On the Expand tab, set Expand Direction to H in the Basic section. The product type data expands in a horizontal manner.

   2. Repeat the preceding operations to set Expansion Direction of C4 to No Expansion. This is because the sales volumes in the A4, B4, and C3 cells are unique. The expansion direction of the C5 and D5 cells must also be set to No Expansion.

5. When data of various datasets is presented in the same report, you must associate the datasets by adding data filter conditions. The following section describes how to associate multiple datasets:

   The data of the report is stored in the ds1 and ds2 datasets. Both of the datasets have the product type and contract type fields. Therefore, the two fields are used to associate the two datasets. Double-click the D4 cell. In the Data Column dialog box, click the Filter tab. On the Filter tab, add a common condition to the D4 cell to associate the product type and contract type fields of the ds2 and ds1 datasets.

   

6. Design the chart.

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

   2. Click the chart. In the panel, click Cell Element. In the dialog box that appears, click the Data tab. Set Data Source to Cell Data, Category to =B4, Series to =C3, and Value to =C4.

      Note

      You must add an equal sign (=) before a cell number when you set the Category, Series, and Value parameters. Otherwise, the cell number is processed as a string.

      

7. If you want to preview the report, you must save it first. Click Save in the top shortcut bar. Then, click Preview and select Pagination Preview from the drop-down list. The report is opened in your web browser.