Dynamically Selecting Columns and Querying Values in FVS

This document applies to users who have installed the FineVis Data Visualization plugin to learn FVS template functions.

Application Scenario

When handling large data reports, if there are many data columns, users can dynamically select the required columns and query the corresponding values, thus quickly focusing on key information and improving browsing efficiency, as shown in the following figure.

Note:

To ensure a smooth experience during use, it is recommended that the number of dynamically selected columns and data volume should not be too many or too large; otherwise, browsing may be sluggish due to performance issues.

Implementation Method

(1) Use JavaScript to control the visibility of widget components and to control query conditions.

setVisible(boolean) is used to determine whether to visualize a widget. The option includes true (visible) and false (invisible).

This API is valid for all FVS components. For details, see General API for Widgets.

var value = duchamp.getWidgetByName("Widget name").getValue();//Get the widget value.
duchamp.getWidgetByName("Widget name").setVisible(true);//Set the widget to be visible.
duchamp.getWidgetByName("Widget name").setVisible(false);//Set the widget to be invisible.

(2) Trigger widget and component linkage through parameter values to dynamically display column data based on query conditions.

Template Creation

(1) Choose File > New FineVis Visualization Dashboard in the upper left corner of the FineReport designer.

(2) Click Create Blank FVS Dashboard, customize the template name and size, and choose Built-in Style > Dark Blue in Template Style.

(3) Click Create Dashboard.

Data Preparation

(1) Create a dataset ds1 with the SQL statement select * from Sales_Volume. ds1 is used as the source of option values for the parameter widget.

(2) Create a dataset ds2 with the following SQL statement. ds2 is used as the source of data values for the cells in the report.

select * from Sales_Volume

where 1=1

${if(len(area) == 0,"","and Region = '" +area + "'")}

${if(len(xsy) == 0,"","and Salesperson = '" +xsy + "'")}

${if(len(cplx) == 0,"","and Product_types = '" +cplx + "'")}

${if(len(cp) == 0,"","and Product = '" +cp + "'")}

${if(len(xl) == 0,"","and Sales_Volume = '" +xl + "'")}

For details about how to define dataset parameters, refer to Dataset Parameter.

Table Component Design

(1) Choose Other > Table in the component area to add a table to the canvas, and resize and relocate the table appropriately.

(2) Select the table component, and choose Content > Edit Component on the right configuration panel to enter the table editing page.

(3) Enter formula values in cells A2, B1, B2, and add borders, as shown in the following figure.

B1: split($col,",")

A2: ds2.select(#0)

B2: ds2.value(A2,B1)

(4) Set Expansion Direction to Horizontal for cell B1, and to Vertical for cell A2, as shown in the following figure.

The design of the table is completed, and the table content will change with the parameter values during preview. For related simple examples, refer to Dynamic Column by Functions.

Designing Widget Components

Adding Column Widgets

(1) Choose Other > Title in the component list bar to add five title components to the canvas, set the title content and component name of the title components respectively, and relocate the components appropriately.

Take the Area title component as an example, as shown in the following figure.

(2) Choose Widget > Dropdown Box to add five dropdown box widgets to the canvas, set Bound Parameter and Name for the dropdown box widgets respectively, and relocate the components appropriately.

Take the area dropdown box widget as an example, as shown in the following figure.

Note:

 Ensure that the values of Bound Parameter for the dropdown box widgets are consistent with the parameter names defined in "Data Preparation."

(3) Set the option value sources for each dropdown box widget. Take the area widget as an example. The setting steps are shown in the following figure.

Adding a Widget for Selecting Columns

(1) Choose Template > Template Parameter on the menu bar, and add the template parameter col, as shown in the following figure.

(2) Then add a title component and a dropdown box widget to the canvas, choose Widget > Query to add a query button to the canvas, and adjust them to the appropriate positions. Set Title Content of the title component to Column to be selected, and set Bound Parameter and Name to col for the dropdown box widget, as shown in the following figure.

(3) Set the option value source for the dropdown box widget col. Set Type Setting to Formula on the Option Source page, set Actual Value to TABLEDATAFIELDS("ds1"), and select Multiselect and Support Select All, as shown in the following figure.

(4) Select all widgets in the Associated Widget area for the query button, as shown in the following figure.

Hiding Column Widgets

Select the column widgets such as area at the same time, and click Combine on the right of the canvas to group these components. Set the group to be invisible, that is, hidden during initialization. Later, the group will be displayed according to JavaScript statements, as shown in the following figure.

Adding a JavaScript Event

(1) Add an Edit End event for the col dropdown box widget, which is used to control the hiding or display of column widgets such as area, as shown in the following figure.

The JavaScript code is as follows:

//Get the drop-down box widget area. 
var area = duchamp.getWidgetByName("area"); 
//Get the drop-down box widget xl. 
var xl = duchamp.getWidgetByName("xl"); 
//Get the drop-down box widget cplx. 
var cplx = duchamp.getWidgetByName("cplx");
//Get the drop-down box widget cp. 
var cp = duchamp.getWidgetByName("cp");
//Get the drop-down box widget ywjc. 
var ywjc = duchamp.getWidgetByName("ywjc");
//Get the drop-down box widget xsy. 
var xsy = duchamp.getWidgetByName("xsy");
//Get the selected value of the drop-down box widget. 
var a = this.getValue();
//Determine whether a contains the corresponding field, and return true or false. 
var a1 = a.includes("Region");
var a2 = a.includes("Sales_Volume");
var a3 = a.includes("Product_types");
var a4 = a.includes("Product");
var a6 = a.includes("Salesperson");
//alert(a1,a2,a3,a4,a6);
//Hide or display different widgets. 
if(a1) {area.setVisible(true);duchamp.getWidgetByName("Area Title").setVisible(true);} else {area.setVisible(false);duchamp.getWidgetByName("Area Title").setVisible(false);}
if(a2) {xl.setVisible(true);duchamp.getWidgetByName("Sales Volume Title").setVisible(true);} else {xl.setVisible(false);duchamp.getWidgetByName("Sales Volume Title").setVisible(false);}
if(a3) {cplx.setVisible(true);duchamp.getWidgetByName("Product Type Title").setVisible(true);} else {cplx.setVisible(false);duchamp.getWidgetByName("Product Type Title").setVisible(false);}
if(a4) {cp.setVisible(true);duchamp.getWidgetByName("Product Title").setVisible(true);} else {cp.setVisible(false);duchamp.getWidgetByName("Product Title").setVisible(false);}
if(a6) {xsy.setVisible(true);duchamp.getWidgetByName("Salesperson Title").setVisible(true);} else {xsy.setVisible(false);duchamp.getWidgetByName("Salesperson Title").setVisible(false);}

Triggering Linkage Between Column Widgets and the Table

When you use the value function directly between widgets and table components in an FVS template, the linkage effect will not be triggered. In this case, you can use the formula $area to trigger the linkage effect.

(1) Select the table component, and choose Content > Edit Component on the right configuration panel to enter the table editing page.

(2) Take the column widget parameter area as an example. Insert the formula $area in any cell on the right side, and insert the remaining four formulas in the cells of the same column by the same operation, as shown in the following figure.

(3) Click the header of the column to be hidden, right-click the mouse, and click Hide from the pop-up menu, as shown in the following figure.

Effect Display

PC

Save the template and click Preview. The PC preview effect is shown in section "Application Scenario."

Mobile Terminal

Adjust components for the mobile terminal and enable the FVS Mobile Layout. For details about the preview method, see FVS Template Preview and Mounting, as shown in the following figure.

Download the completed template by clicking Dynamically Selecting Columns and Querying Values in FVS.fvs.