Dynamic Column-FineReport Help Document

Last update： 2022-01-11

I. Overview

1. Application scenarios

In the process of using column chart to realize visualization, we may encounter the following business requirements:

1）There are too many columns to show in a single column diagram.

2）There are too many columns to show all at once.

3）Dynamic display is needed to enhance the performance of the chart.

2. Anticipate result

Let the columns of the column graph switch within a certain time interval to dynamically display all the columns. The effect is shown in the following figure:

3. Idea to achieve

The core idea to achieve this effect is to find a constantly changing quantity, which is written in the SQL where condition statement as the query condition. In this way, in the monitoring refresh effect of the special effects, according to the refresh time we set, the data set will continuously execute the dynamic SQL we set.

The server time is the only factor that changes constantly. So we take the second of the server time as the factor that changes constantly and add it to the where conditional statement to become the dynamic SQL we need.

II. Steps

1. Report design

1) Drag the column chart to the dashboard, as shown in the following figure:

2) Create a new dataset ds1. The dynamic SQL statements in the example are as follows:

select *,`No` as id* from Bill

where

id%2 = cast(substr(cast(current_timestamp as varchar),19,1) as int)%2

Note 1: sort and number the records to be displayed, and then write the second of the server time as a query condition into the where condition. The SQL of different databases may be slightly different, but the idea is the same.

Note 2: because SQL involves server access time, the dynamic effect cannot be loaded by uploading the built-in dataset template. Here we only talk about SQL upload, and the reader can convert it by himself according to the type of database he uses.

3) Bind column chart data, as shown in the following figure:

4) Set the dynamic refresh time in the interaction properties, which corresponds to SQL well, otherwise the refresh may have problems, as shown in the following figure: