I. Overview
1) Report drill-down is applied in a variety of scenarios. The function is usually used to view the breakdown of some data in a master report:
In an account receivable (AR) report, for example, accounts wish to view the cost breakdown of a specific account after clicking the amount on the account.
In an HR report, for example, HR specialists wish to view detailed personal information after clicking an employee name.
Generally, drill-down can link two or more related reports (often master and sub reports) and help users view summary and breakdown data in a hierarchical manner. Drill-down reports that meet the business logic can better display business operations.
2) Taking order management for example, this article exhibits how to make and use a drill-down report in FineReport.
3) Basic design idea of drill-down reports:
In this instance, four reports are designed, including:
Order List report
Logistics Information report
Order Detail report
Product Detail report
a) In the Order List report, you can drill down to the Logistics Information report and the Order Detail report corresponding to an order ID by clicking the order ID. In other words, by passing the parameter OrderID, you can find the data on logistics and the order associated with the corresponding OrderID from datasets in the Logistics Information and Order Detail reports, and display such data.
b) In the Order Detail report, you can drill down to the Product Detail report by clicking a product ID. To put it another way, by passing the parameter ProductID, you can find the data on a product associated with the corresponding ProductID from datasets in the Product Detail report, and display such data.
c) Drill-down and return are enabled through parameter passing and inheritance in hyperlinks.
II. Make the master report (Order List report)
1. Prepare data
Create a new DB Query ds1 and select [FRDemo] as the database by inputting the following SQL statement:
SELECT * FROM SIndentwhere OrderID>10100 and Owner_region = 'South China'order by Order_date ASC
2. Design the report body
1) Type the title and field names, and drag fields [OrderID], [Order_date], [CustomerID], [EmployeeID] and [Paid_or_not] in ds1 into the report body:
2) Use cell A4 where OrderID is located as the left parent cell, which is default and needs not to be changed.
3) Set the format of cell B4 to date.
4) Freeze the header: select Rows 1~3[A1] , right-click them and select [Set Repeat Title Rows]. Click [Template] > [Repeat and Freeze] and check [Freeze No.1 Row to No.3 Row].
3. Optimize report style
1) Merge the cell range A1:E1 as the report title, set background color to purple, increase the font size and cell height, set text color to white.
2) Increase the font size and cell height of the cell range A3:E3 and use the cell range as the header.
3) Set the background color of the cell range A4:E4 to grey.
4) Add white interior and exterior borders for the cell range A1:E4.
4. Preview the effect
The title and header are frozen and the Order List report can be scrolled.
III. Make the Logistics Information report
1. Prepare data
1) Create a new DB Query [ds1] and select [FRDemo] as the database by inputting the following SQL statement:
SELECT * FROM SIndent WHERE OrderID=${Order_number}
2) Here, a dataset parameter Order_number is generated, of which the default value can be set to 10248.
2. Design the report body
1) Drag data columns into corresponding cells and input text as shown below. Merge cell ranges A1:F1, A3:F3, A7:F7, A11:F11 and D12:F12.
2) Set the data dictionary for cells D4, F4 and B12. In the example of cell D4, the actual value is a customer ID while the display value is a customer name.
3. Optimize report style
1) Modify the background and text colors of A1.
2) Increase the cell height and font size of Rows 1, 3, 7 and 11.
3) Set the background color of cells where field names are located to grey.
4) Add white interior and exterior borders for the cell range A1:F14.
4. Add a hyperlink to the Logistics Information report in the master report
1) Add a hyperlink to cell A4 where OrderID is located in the master report Order List and set the type to [Web Report].
2) Click [Select] and set the Web report (i.e., the target report to which you jump) to Logistics Information.
3) Pass a parameter to the target report and set the parameter name to Order_number and its value to $$$.
4) Set the name of the hyperlink to Logistics Information.
5. Add a hyperlink back to the master report in the Logistics Information report
1) Click cell F14 and add a [Web Report] hyperlink.
2) Set the target report to Order List.
6. Preview the effect
1) After clicking a product ID, choose the hyperlink named Logistics Information to jump to the Logistics Information page.
2) Click [Back to previous] on the Logistics Information page to jump back to the Order List report.
IV. Make the Order Detail report
1. Prepare data
1) Create a new DB Query [ds1] and select [FRDemo] as the database by inputting the following SQL statement:
SELECT * FROM SIndentDetail WHERE OrderID=${Order_number}
2) Here, a dataset parameter Order_number is generated, of which the default value can be set to 10248.
2. Design the report body
1) Drag data columns into corresponding cells as shown below and merge the cell range A1:G1.
2) Set the data dictionary for cell B4, so that the display value is a product name.
3) Add formulas to cells F4 and F5:
Input the following formula in F4: C4 * D4 * (1 - E4).
Input the following formula in F5: sum(F4).
3. Optimize report style
1) Modify the background and text colors of A1.
2) Increase the height and font size of Rows 1 and 2.
3) Set the background color of cells containing data columns and summary data to grey.
4) Add white interior and exterior borders for the cell range A1:F5.
4. Add a hyperlink to the Order Detail report in the mast report
1) In the master report Order List, add a [Web Report] hyperlink to cell A4 where OrderID is located.
2) Click [Select] and set the Web report to Order Detail.
3) Pass a parameter to the target report, set the parameter name to Order_number and its value to $$$.
4) Set the name of this hyperlink to IndentDetail.
5. Add a hyperlink back to the master report in the Order Detail report
1) Click cell F7 and add a [Web Report] hyperlink.
2) Set the target report to Order List.
6. Preview the effect
1) After clicking an order ID, choose the hyperlink named IndentDetail to jump to the Order Detail page.
2) Click [Back to previous] on the Order Detail page to jump back to the Order List report.
V. Make the Product Detail report
1. Prepare data
1) Create a new DB Query [ds1] and select [FRDemo] as the database by inputting the following SQL statement:
SELECT * FROM SProduct a left join Supplier b on a.SupplierID=b.SupplierIDwhere ProductID=${Product_number}
2) Here, a dataset parameter Product_number is generated, of which the default value can be set to 17.
2. Design the report body
1) Drag data columns into corresponding cells and input text as shown below. Merge cell ranges A1~H1, A3~H3 and A7~H7.
2) Set the data dictionary for cells A5, B5, C5 and A9.
3. Optimize report style
1) Modify the background and text colors of A1.
2) Increase the cell height and font size of Rows 1, 3, 4, 7 and 8.
3) Set the background color of cells containing data columns to grey.
4) Add white interior and exterior borders for the cell range A1:H9.
4. Add a hyperlink to the Product Detail report in the Order Detail report
1) In the Order Detail report, add a [Web Report] hyperlink to cell B4 where ProductID is located.
2) Click [Select] and set the Web report to Product Detail.
3) Pass the following two parameters to the target report: Product_number and Order_number. Set the value of Product_number to $$$ and that of Order_number to $Order_number.
5. Add a hyperlink back to the Order Detail report in the Product Detail report
1) Click cell H7 and add a [Web Report] hyperlink.
2) Set the target report to Order Detail and check [Inherit Report Parameters].
6. Preview the effect
1) Click a product ID in the Order Detail report to jump to the Product Detail page.
2) Click [Back to previous] on the Product Detail page to jump back to the Order Detail report.
Download the templates
