Drilling Down and Returning Within a Chart

Expected Effect

In a drill-down map, you can click a state to drill down into counties or cities in the state. How can you create the same effect in a general report?

Take the column chart as an example. The expected effect is as follows.

Initially, the chart displays the sales amount grouped by region (Sales Amount by Region). You can click Northeast to drill down and view the sales amount of all salespersons in that region, and then click a salesperson to drill down further and view the sales amount of the selected salesperson in the Northeast region. Clicking the hyperlinks on the right allows you to return to the previous levels.

Implementation Approach

You can define SQL parameters in different datasets, use Cell Data as the source, and apply formulas to obtain data from the datasets.

Drill-down within a chart can be achieved by adding a hyperlink under Special Effect > Interaction > Hyperlink to the chart. This allows the data displayed by the chart to change with the parameter value.

Note: 

The implementation approach also applies to FVS dashboards and FRM reports. For details about specific settings, download the example templates at the end.

Data Preparation

Create a general report and three datasets from database queries:

ds1: SELECT Region,sum(Sales_Volume) as 'Sales Amount' FROM Sales_Volume group by Region

ds2: SELECT Salesperson,sum(Sales_Volume) as 'Sales Amount' FROM Sales_Volume  where  Region = '${area}' group by Salesperson

ds3: SELECT Product_Type, sum(Sales_Volume) as 'Sales Amount' FROM Sales_Volume  where Region = '${area}' and Salesperson = '${spe}' group by Product_Type

The parameter area is defined in ds2 and ds3, and the parameter spe in ds3, with no default values. Take ds3 as an example. The steps are shown in the following figure.

Drilling Down Within the Chart

Inserting a Chart

Merge the required cells and insert a column chart, as shown in the following figure.

Binding Data with the Chart

Select the chart, choose Cell Element > Data, and set Data Source to Cell Data.

• Set Category Name to the formula: if(len($area) = 0, ds1.select(Region), if(len($spe) = 0, ds2.select(Salesperson), ds3.select(Product_Type))).

• Set Series to the text: 'Sales Amount'.

• Set Value to the formula: if(len($area) = 0, ds1.select('Sales Amount'), if(len($spe) = 0, ds2.select('Sales Amount'), ds3.select('Sales Amount'))).

The meaning of formulas in Category Name and Value is as follows:

If the value of area is empty, the values of Region and Sales Amount in ds1 are used as the category name and category value, respectively.

If the value of area is not empty, but the value of spe is empty, the values of Salesperson and Sales Amount in ds2 are used as the category name and category value, respectively.

If the values of area and spe are not empty, the values of Product_Type and Sales Amount in ds3 are used as the category name and category value, respectively, as shown in the following figure.

Setting the Chart Hyperlink

Select the chart, choose Cell Element > Special Effect > Interaction > Hyperlink, and select Chart Hyperlink-Cell from the Add Hyperlink dropdown box.

1. Set Linked Cell to A1, where the chart is located.

2. Add the parameters.

Setting the Title

Use the formula for the chart title: if(len($area)=0,    "Sales Amount by Region",    "Sales Amount in " + $area + if(len($spe)=0, "", " - " + $spe) ), as shown in the following figure.

Formula Description:

If area is empty, the title Sales Amount by Region is displayed.

When you drill down, the chart title changes according to the values of area and spe and is displayed as Sales Amount in Area - Salesperson.

Previewing the Effect

After completing the above steps, you can drill down within the chart itself. 2. Save the report and click Pagination Preview. The following figure shows the preview effect.

Returning to the Upper Level After Drill-down

You want to return to the previous level after drilling down. To achieve the effect, you can use the dynamic parameter to enable clicking a cell to return to the corresponding level.

Note: Using the dynamic parameter involves refreshing the entire template, and all elements and components referencing the parameter will reload according to its value.

1. In the cell to the right of the chart, input the text Return to the Region Level. Add a hyperlink of the Dynamic Parameter type, and add area to the link without assigning any value.

In this way, if the value of area is empty, the values of Region and Sales Amount in ds1 are used as the category name and category value of the chart, respectively, as shown in the following figure.

2. In another cell to the right of the chart, input the text Return to the Salesperson Level. Add a hyperlink of the Dynamic Parameter type, and add spe to the link without assigning any value.

In this way, if the value of spe is empty, the values of  Salesperson and Sales Amount in ds2 are used as the category name and category value of the chart, respectively, as shown in the following figure.

Effect Display

PC

Save the report and click Pagination Preview. The figure in the "Expected Effect" section shows the effect.

Mobile Terminal

You can preview the report in DataAnalyst and HTML5 clients, as shown in the following figure.

For FRM dashboards and FVS reports, the implementation approach is the same as that for the general report, but the specific method varies with the report type.

The following sections briefly introduce the implementation method. For details, you can download the templates at the end.

Example of the FVS Dashboard

In an FVS dashboard, you need to add a Component Linkage event to the chart, set Scope, Object, and Parameter Setting (the same as that of the general report) of the event, as shown in the following figure.

To return to the previous levels after drilling down, you can add a title component and set a Component Linkage event in the same way. The following figure, which uses Return to the Region Level as an example, illustrates the steps.

Example of the FRM Report

In an FRM report, you need to add a hyperlink of Current Form Object to the chart block, as shown in the following figure.

To return to the previous levels after drilling down, you can set the hyperlink of Current Dashboard Object to the cells in the report block in the same way, as shown in the following figure.

For details, you can download the example templates:

Chart Drill-Down and Return.cpt

Chart Drill-Down and Return.frm

Chart Drill-Down and Return.fvs