Automatic Query of Dashboard

1. Problem description

When we introduced the parameter application, we introduced Automatic query. The effect is that after selecting the parameter in the parameter interface, there is no need to click the query button. The filter result will be automatically queried and displayed in the main body of the report. Then whether it is also in the dashboard can it be achieved?

For example: when you select the data in the top few drop-down boxes, the data in the report block changes in real time, as shown in the following figure:

2. Solutions

In the dashboard, you can easily realize automatic query by using widgets in the body to bind data, and then establish a data connection with the report block or chart block. If you want to realize automatic query in the parameter pane, please refer to: JS realizes the automatic query of dashboard parameter pane.

This example explains how to establish data connections when different datasets are used as data sources.

1. Data preparation

Create a new dashboard, and create three database queries in the dataset pane on the right. The SQL query statements are:

Order: SELECT * FROM orders_new2

ds1: select city from client where Region='${Region}'

2. Dashboard design

1) Drag three label widget three drop-down box widgets and a report block component to the body of the decision report, as shown in the figure below:

2) Modify the widget value of the label widget and the widget name of the drop-down box widget to correspond to the parameters region, city respectively.

Note: It must be ensured that the name of the drop-down box widget is consistent with the parameter name.

3) Set up three data dictionaries of drop-down box widgets, the specific settings are shown in the following table:

3. Report block design

1) Table design

Click the Edit button on the report block to enter the report block editing interface, the data column settings are as shown in the figure below, and the table style can be set by yourself.

2)Set Filter

Since the data in the report block and the data bound to the drop-down box data dictionary do not come from the same dataset, there is no filtering relationship between them, and you need to manually add filter conditions to the cells. Double-click the A5 cell where the Order ID is located, and the data column setting interface will pop up. Select the Filter interface to add three filter conditions, and the conditions are connected by AND:

Condition 1: The "region" column is equal to the formula if($region = "", nofilter, $region)

Condition 2: The "City" column is equal to the formula if($City = "", nofilter, $City)

Note: nofilter in the formula means no filtering, that is, when the parameter is empty, it will not filter and display all; when the parameter is not empty, the corresponding data will be displayed.

4. Style modification

1)Save the dashboard at this time, click  PC Preview, you can see that although the effect is achieved, due to the adaptive characteristics of the dashboard, the widgets are displayed on the top, and the arrangement is not beautiful.

Then we can set the top margin of the Padding of the dashboard body to be larger, as shown in the following figure:

2) The customer drop-down box widget on the right is displayed directly to the right, which is inconsistent with the other two drop-down box widgets. So can we also set the right margin so that it is not displayed to the right?

The answer is no. The padding setting of the body is for the entire dashborad and will affect all the components of the dashboard. At this point, we can add a blank label widget on the right side of the daashboard, as shown in the following figure:

5. Effect preview

1)PC side

Save the dashboard and click PC Preview. The effect is the same as the effect diagram in section I.2.

2)Mobile

Both App and HTML5 are supported, and the effect is shown in the figure below:

图

III. Example 2

This example explains how to establish a data connection when the same dataset is used as the data source.

1. Data preparation

Create a new dashboard, create a new database query ds1, the SQL query statement is: SELECT * FROM sales_volume where 1=1 ${if(len(region)=0,""," and region='"+region+"'")}

2. Dashboard design

1)Modify the layout of the body to absolute layout, and then drag the label widget, drop-down box widget, report block and pie chart into the body, as shown in the figure below:

2) Modify the widget value of the label widget and the widget name of the drop-down box widget to correspond to the parameter region.

Note: It must be ensured that the name of the drop-down box widget is consistent with the parameter name.

3) Set the data dictionary of the drop-down box widget to the ds1 dataset, the actual value and the displayed value are both region. As shown below:

3. Report block design

Click the Edit button on the report block to enter the report block editing interface, the data column settings are as shown in the figure below, and the table style can be set by yourself.

4. Chart data settings

Edit the pie chart and set the data source of the pie chart: the dataset is ds1, the series name is salesperson, the value is sales_volume and the aggregation method is sum. As shown below:

5. Effect preview

1)PC

Save the dashboard and click PC Preview, the effect is shown in the figure below:

2)Mobile

Both App and HTML5 are supported, and the effect is shown in the figure below:

The completed template can be found in:

%FR_HOME%\webroot\WEB-INF\reportlets\doc-en\Dashboard\Automatic query of dashboard.frm

%FR_HOME%\webroot\WEB-INF\reportlets\doc-en\Dashboard\Automatic query of dashboard-Chart.frm

Click to download the template:

Automatic query of dashboard.frm

Automatic query of dashboard-Chart.frm