I. Overview
1) Click the category or series of the chart to link or jump to the corresponding web report to view the details.
2) Suggested reading: Data Drilling，Drill Map，dataset parameters，Web Report
| You will learn |
|---|
|
II. chart drill-down to Detailed Report
1. Report making (master report)
1) Taking the dashboard and column chart as an example, create a new [dashboard] and drag in the [Column Chart]
2) Create a new data query named ds1 and use the default FRDemo database. Enter the following query statement:
SELECT Category_name, sum(Order_quantity*Unit_price)as Revenue
FROM ProductCategory join Product
on ProductCategory.CategoryID=Product.CategoryID
group by Category_name
3) Double-click the column chart in the report to edit it. Click the [Data] tab on the right, select [ds1] for [Dataset], select [Category_name] for [Category], select [Use Field Value] for [Series Name], select [Revenue] for [Value], and do nothing for the rest .
4) Click [Style]>[Title] on the right, and enter the chart title as [Category Revenue].
5) Save the dashboard as [MasterChart.frm] in the reportlets folder of the working directory.
2. Report Making (subreport)
1) Create a new ordinary report and a new data query named ds1, and use the default FRDemo database. Enter the following query statement and set the parameter '${Para}' to filter Category_name:
SELECT Category_name, Product_name, sum(Order_quantity*Unit_price)as Revenue
FROM ProductCategory join Product
on ProductCategory.CategoryID=Product.CategoryID
where Category_name='${Para}'/*'${Para}' is the parameter controlling field value of Category_name.*/
group by Category_name, Product_name
2) Merge report cells A1:C1, enter the title: =$Para+' Revenue Detail', and the position of $Para will be dynamically replaced by the drilled Category_name.
3) Enter column names: Category, Product, Revenue in cells A2:C2 respectively. Drag three fields of ds1 dataset in cells A3:C3. Adjust the style of cells.
4) Save the report as [Subreport.cpt] in the reportlets folder of the working directory.
3. Add a hyperlink
1) Open the master report [MasterChart.frm], and double-click the column chart to enter the editing interface. Click [Special effect]>[Interaction] on the right, drag to the bottom [Hyperlink], and click [+] to add a new web report.
2) Click the [Select] button on the right of the [web report] field, and select the subreport [Subreport.cpt] just made
3) [Open a Link at] can be selected as a new window, dialog box or current window. Here, we select [Dialog Box], and the subreport will be displayed in the pop-up dialog box on the current page. Enter [Detailed Report] for [Dialog Title], and adjust the size of the dialog box to 600 in height and 400 in width. You can also set the pop-up position of the dialog box to be centered or custom, but here we choose “centered”.
4) In the parameter area, click [+] to add a parameter. The name is Para; Click the icon, select [Formula], and enter the parameter value as CATEGORY.
The Parameter name passed to the subreport must be consistent with the parameter name (Para) just set in the subreport.
The parameter values passed to the subreport can be CATEGORY, SERIES, VALUE and AREA_NAME, which can be identified as parameters in the formula. When there are multiple category names, there is a category array to choose from when setting the value.
5) The name can be modified by double-clicking the hyperlink of the added web report. Here we modify it to [Detailed Report].
6) Save the master report.
4. Preview
Preview the master report [MasterChart.frm], and you can see the column chart Category Revenue. Click any column, such as Desserts, and you can see the pop-up dialog box with the title Revenue Detail of this category, and the content is sales details of this category.
III. Chart Drill-down to Chart
1. Report making (master report)
For this step, please refer to 1. report making(master report) of II. chart drill-down to Detailed Report
2. Report Making (subreport)
1) Create a new ordinary report and a new data query named ds1, and use the default FRDemo database. Enter the following query statement and set the parameter '${Para}' to filter Category_name:
SELECT Category_name, Product_name, sum(Order_quantity*Unit_price)as Revenue
FROM ProductCategory join Product
on ProductCategory.CategoryID=Product.CategoryID
where Category_name='${Para}'/*'${Para}' is the parameter controlling field value of Category_name.*/
group by Category_name, Product_name
2) Click [Insert] → [Floating Element] → [Insert Chart] in the upper toolbar, and select [Pie Chart] to insert.
3) Double-click the pie chart to enter the editing interface, click the [Data] tab on the right, and a dialog box will pop up asking for the default parameter value. Here we can just click [Cancel] instead of making any selection.
4) Under the [Data] tab, select [ds1] for [Dataset] and [Category_name] for [Category], and the category of the pie chart will be confirmed by passing the value of parameter Para. Select [Use Field Value] for [Series_name] and [Product_name] for [Series], and the product details will be displayed on each sector of the pie chart. Select [Revenue] as the value, which represents the size of each sector of the pie chart. Do nothing for the rest..
5) Click the [Style]>[Title] tab on the right, and enter the chart title as $Para +'Revenue Detail'. The position of $Para will be dynamically replaced by the drilled Category_name.
6) Enter the [Prompt] tab, check and display [Percentage], so that you can see the percentage of the pie chart sector (product sales) when you move the mouse over it.
7) Save the report as [SubChart.cpt] in the reportlets folder of the working directory.
3. Add a hyperlink
1) Open the master report [MasterChart.frm], and double-click the column chart to enter the editing interface. Click [Special effect]>[Interaction] on the right, drag to the bottom [Hyperlink], and click [+] to add a new web report.
2) Click the [Select] button on the right of the [web report] field and select the subreport [SubChart.cpt] just made
3) [Open a Link at] can be selected as a new window, dialog box or current window. Here, we select [Dialog Box], and the subreport will be displayed in the pop-up dialog box on the current page. Enter [Detailed Chart] for the [Dialog box title], and adjust the size of the dialog box to 800 in height and 800 in width. You can also set the pop-up position of the dialog box to be centered or custom, but here we choose “centered”.
4) In the parameter area, click [+] to add a parameter. The name is Para; Click the icon, select [Formula], and enter the parameter value as CATEGORY.
The Parameter name passed to the subreport must be consistent with the parameter name (Para) just set in the subreport.
The parameter values passed to the subreport can be CATEGORY, SERIES, VALUE and AREA_NAME, which can be identified as parameters in the formula. When there are multiple category names, there is a category array to choose from when setting the value.
5) The name can be modified by double-clicking the hyperlink of the added web report. Here we modify it to [Detailed Chart].
6) Save the master report.
4. Preview
Preview the master report [MasterChart.frm], and you can see the column chart Category Revenue. Click any column, such as Desserts, and you can see that in the pop-up dialog box, the title of the chart is Revenue Detail of this category, and the content is the pie chart of sales details of this category. Move the mouse over the corresponding sector, and there will be a prompt for the corresponding data.
IV. Reference template
1) Take Chart Drill-down to Chart as an example, and attach templates of master report and sub report (pie chart).
2) It is recommended to save this template to FineReport_10.0\webapps\webroot\WEB-INF\reportlets
3) It is recommended to save this template to FineReport_10.0\webapps\webroot\WEB-INF\reportlets
Previous：Chart Zooming
