This topic describes how to use FineReport to connect to an AnalyticDB for MySQL cluster and manage reports.

Prerequisites

  • You understand the compatibility between FineReport and AnalyticDB for MySQL. For more information, see Compatibility overview.
  • MySQL Connector/J is installed.
  • FineReport is installed. If this is the first time that you install FineReport on your computer, you must install a MySQL server such as Alibaba Cloud ApsaraDB RDS for MySQL to import FineReport metadata. You can use one of the following methods to import data from the MySQL server to FineReport:
    • Method 1: Establish the connection in a data-based decision making system.
    • Method 2: Establish the connection by using the FineReport designer. If you use a MySQL server whose major version is 5.x, see Connect to MySQL.
  • For information about how to connect to an AnalyticDB for MySQL cluster over the Internet, see Apply for or release a public endpoint.

Connect to AnalyticDB for MySQL

  1. Start FineReport. In the top menu bar, choose Server > Define Data Connection.
  2. In the Define Data Connection dialog box, configure the parameters.
    Configure connection parameters
    Parameter Description
    Database The engine of the database. Select MySQL from the drop-down list.
    Driver The type of the driver. Select com.mysql.jdbc.Driver from the drop-down list.
    URL The connection string of the AnalyticDB for MySQL cluster to which you want to connect. The format is jdbc:mysql://hostname:port. Field description:
    • hostname: the public or VPC endpoint of the cluster.
    • port: the port number of the AnalyticDB for MySQL cluster endpoint. The default port number is 3306.
    User Name The account used to connect to the AnalyticDB for MySQL cluster. You can use one of the following account types:
    • Privileged account
    • Standard account
    Password The password of the account.
  3. After you configure the preceding parameters, click Test Connection. After the connection passes the test, click OK to connect to the AnalyticDB for MySQL cluster.

Create a table

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, click the + icon in the upper-left corner of the tab.221040803
  2. Select DB Query from the drop-down list.1
  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. 2021040802
  4. Obtain the dataset that consists of 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.

Report example

In this example, a report is created. It contains tables and column charts that display the largest sales volumes and total sales volumes sorted by product type, payment type, and contract type. The report is shown in the following figure.

2021040804
  1. Download the demo table named demo_contract.
  2. Create a table in the AnalyticDB for MySQL cluster.
    1. Use a column that has an even distribution of values as the distribution key, and a column that contains time data as the partition key. For more information, see Schema design.
    2. The contract_id column is used as the partition key. This is because the values in the contract_id column are randomly distributed. The following statement is used to create the table:
      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`)
      ) DISTRIBUTE BY HASH(`contract_id`) INDEX_ALL='Y' STORAGE_POLICY='COLD'
  3. After the table is created, use Kettle to import data from the XLSX file to AnalyticDB for MySQL.
    1. For information about how to use Kettle, see Use Kettle to synchronize local data to AnalyticDB for MySQL.
    2. After the data is imported, execute the SELECT count(*) FROM demo_contract statement. 668 entries are returned.
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.20210408052021040806
  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. 20210408072021040808
  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. 2021040809
  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 an 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. 2021040811
  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.

    20210408122021040813
  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. 2021040814
    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.
      2021040815
  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.

FAQ

For references of FineReport designs, see Design Ideas.