In this tutorial we will show you how to create a Drill Down Report in ASP.NET Report Maker. We will use the demo database for demonstration.
We will continue to use the detail and summary report created in Tutorial - Detail and Summary Report. If you have not yet created the detail and summary report, refer to the tutorial and create it first. We use the compact report:
Above report does not show the order details in each order. Now we create a detail report so user can click the sum of Extended Price (the last column) to see the order details of each company or each order.
Steps to Create the Report
1. Loading ASP.NET Report Maker
Open ASP.NET Report Maker and connect to the demo database.
2. Adding a Detail Report (if not already exists)
In this example, we can simply use the source view "Order Details Extended" or even the report "Sales by Customer" itself as detail report, both contains data we want to show in the detail report. However, "Order Details Extended" does not have grouping fields so we cannot group the records by order ID. The report "Sales by Customer" itself has an extra grouping level (Company Name) that we do not need to show in the detail report. So we'll create a new report as detail report.
Right click [Reports] on the database pane then click [Add Report (Detail/Summary)], or click [Edit] in the main menu and then select [Add Report (Detail/Summary)].
Following the same steps in Tutorial - Detail and Summary Report, name it as "Sales by Order".
Note that this time we only use one grouping level as we do not need to show the company name:
We want to see details of each order item, so check [Details and summary]. We also enable [Show grand summary] because we want to know the total price and average quantity of order item for all orders.
Click [OK] to finish.
3. Creating the drilldown
Now click the main report in database pane to select it, the fields will be shown in the right pane. Select the "Extended Price" field (for which we want to create the hyperlinks to drill down), scroll to right and find the [Detail Report] column under [Drill Down] section:
click the [...] button to open the Drill Down setup form, select the just created report "Sales by Order" as detail report. To enable the detail report to show order details, we need to pass the "CompnayName" and "OrderID" field values from the main report to the detail report, so we map the field as follows:
Click [OK] to finish.
If you now click the detail report and inspect the [Parameter] column, you'll see that ASP.NET Report Maker has setup the target fields as parameters:
4. ASP.NET Script Generation
Go to the [Generate] tab, click the [Generate] button and ASP.NET Report Maker will generate the required ASP.NET scripts automatically.
5. Running the ASP.NET Application
Browse the generated site, go to "Sales by Customer" report to see the result. Note that now the "Extended Price (SUM)" values are colored (with hyperlinks):
Click the "Extended Price (SUM)" for each Order ID, you'll see the order details:
Click the "Extended Price (SUM)" for each customer, you'll see the order details of each order of the customer:
Click the Grand Total of "Extended Price (SUM)", you'll see all the order details:
Drill Down Advanced Settings
Note that there are 2 advanced settings for drill down:
Use popup panel for drill down - Use a popup panel to show the detail report in the same page. If disabled, user will be redirected to the detail report directly after clicking the drill down link. If enabled, the detail report will be displayed in a popup panel by default, if you want to go to the detail report directly (not in popup panel), press Ctrl key and click the link. Note that charts does not support Ctrl-click.
Show drill down filters - Show current drill down filters in the detail report.
Drill Down for Charts
You can also drill down the chart values (Y Axis) by setting up a detail report, see Chart Setup. Setting up of detail report for chart is exactly same as setting up detail report for reports.
For example, we add a chart for the crosstab report Quarterly Orders by Product we created in the tutorial Tutorial - Crosstab Report as following:
The generated chart will be like:
Now we create another chart as a drill down chart. We can reuse the underlying view of the report, that is the view orders_by_product, we create a chart for it as the following:
To drill down to the chart only without showing the tabular report, we disable the Show Report option for the view, see Table Setup.
Then we can go back to the Chart Setup page for the crosstab report Quarterly Orders by Product to setup the drill down, click the "..." button of the Detail report row:
The Drill Down setup form will be displayed. We select the view orders_by_product as detail report and we pass the Category (X) Axis value, in this example, category name, to the detail report:
Generate scripts again, click the bars in the chart under the crosstab report Quarterly Orders by Product and you'll see the drill down chart: