I.Overview
1.Problem Description
When previewing the line chart, sometimes it is not necessary to display all the content, but as the time dimension increases, they are displayed in sequence.
As shown below:
Effect 2：
2.Realization Ideas
Set the time parameter in the SQL query statement to filter the data, the value of the time parameter will change with time, and then refresh the chart regularly.
II.Example
1. Option 1: Dynamic Changes of Classification Value
1)Report design
Create a new decision report, create a data set ds1, the database query statement is as follows:
SELECT
Region,
strftime('%Y-%m',Signdate) yearmonth,
sum(shipfee) shipfee
FROM orders_new
where strftime('%Y-%m',signdate)<='${left(monthdelta("1997-01-01",mod((datetonumber(now())-datetonumber(a))/1000,12)),7)}'
and strftime('%Y',signdate)='1997'
and region in ('Tianjin','Qinhuangdao','Nanjing')
group by Region,strftime('%Y-%m',signdate)
Click Template>Template Parameters on the menu bar, and create a new template parameter a with the default value =now.
The layout of the body component of the decision report is changed to an absolute layout. Drag a line chart into the main body of the decision report design, and bind data information as shown in the figure below.
Select the Label for the line chart style, check the Use label, and the text check the Value.
Add regular refresh, select interactive attributes for special effects of the line chart, enable background detection, and the time interval is 1 second.
2)Effect preview
PC terminal
Save the report, click PC Preview, the effect of the line graph is shown in the figure below:
Mobile terminal
2. Option 2: Fixed classification value
1)Report design
The data set ds1 is modified as follows:
select a.Region,a.yearmonth,b.shipfee from (
SELECT
distinct region,strftime('%Y-%m',signdate) yearmonth
FROM orders_new
where strftime('%Y',signdate)='1997'
and region in ('Tianjin','Qinhuangdao','Nanjing')
) a
left join (
SELECT
region,
strftime('%Y-%m',signdate) yearmonth,
sum(shipfee) shipfee
FROM orders_new
where strftime('%Y-%m',signdate)<='${left(monthdelta("1997-01-01",mod((datetonumber(now())-datetonumber(a))/1000,12)),7)}'
and strftime('%Y',signdate)='1997'
and region in ('Tianjin','Qinhuangdao','Nanjing')
group by region,strftime('%Y-%m',signdate)
) b on a.region=b.region and a.yearmonth=b.yearmonth
The other steps can be set according to option1.
2)Effect preview
PC terminal
Save the report, click Data Analysis Preview, the effect of the line graph is shown in the figure below:
Mobile terminal
III.Template Download
The completed template can be found in:
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-en\Primary\Chart\Dynamic_change_of_classification_value.frm
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-en\Primary\Chart\The_classification_value_is_fixed.frm
Click to download the template:
Dynamic_change_of_classification_value.frm
The_classification_value_is_fixed.frm
