Drill down reporting is a commonly used technique in database-centric applications. It is easy to incorporate drill-down reporting in your APEX applications, and this article intends to show just that.
Drill down from a chart to an Interactive Report
I will walk through a simple example to illustrate how clicking on a chart (ex – pie chart) will drill down to an interactive report containing only those records which correspond to the part of the graph a user clicked.
Let us look at the pie chart I have created for this demo, as shown in Figure 1 below. It is based on the EMP table and displays the distribution of employees according to their job title.
Let us now look at the query which generates this 3D pie chart. Here is the page where this chart region is located.
Double clicking on Chart as shown above, and then clicking Attributes will lead us to the following screen where we can configure the chart region.
The query for the chart is simply the following:
SELECT NULL LINK, JOB LABEL, COUNT(empno) VALUE
FROM EMP GROUP BY JOB;
The Job field has the alias of LABEL; this is what identifies the label for each sector of the pie chart, and hence, why job titles are shown in the chart. We are counting the number of employees according to job title, and the VALUE column accomplishes this.
In order to setup the drill down to an interactive report, we must configure a few things first.
1) Adding a hidden item on the interactive report page
In order to enable the filtering of records when a user clicks a certain part of the chart, we must create a hidden item on the interactive report page to store the value that was passed when the chart was clicked. The value we wish to pass in this example is JOB, since when we click a part of the employee distribution chart, the job title (ex – ANALYST) will be passed to the hidden item on the interactive report page. This passed value enables the filtering, aka “drilling down” when a user clicks the chart.
To create this hidden item, navigate to the interactive report that you wish to drill down to. The image below shows the report I built for the purposes of this demo. It is this report that will be drilled down to depending on what a user clicks on the pie chart.
To create the hidden item, click the Edit Page link in the developer toolbar. In the report region, right click the name of your interactive report, and click Create Page Item, as shown below.
Select Hidden Item, and click Next, as shown below.
Give a name to your Page item (Ex- P2_JOB) and click Next
On the following screen, change the value for Value Protected to No. Click Next.
On the following screen, for the item Source Used, select Always, replacing any value in Session State. Leave the other items at their defaults and click Create Item.
You will receive a confirmation that your page item was created and see it in the report region, as shown below.
Now that the hidden item has been created, we must add this hidden item to the query of the interactive report. Simply double click the interactive report you are drilling down to, and access its query. The query of the interactive report which is built on the EMP table is shown below.
Notice the usage of the hidden item in the WHERE clause, as shown in red above. This modification will allow the interactive report to display all records when so desired, and only those requested when drilling down from a chart.
2) Establishing the Link between the Chart and the Interactive Report
Now that our hidden page item is created, we must setup the link between the chart and the report. To do this, we must navigate to the attributes secton of our pie chart. The figure below shows the chart attributes page in my demo application. This page can be reached by double clicking the name of your chart region, and then selectng Chart Attributes. The area where we need to make modifications is the Action Link section.
Take a look at the figure above and make the following modifications:
- Action Link – Select Link to Custom Target
- Target – Select Page in this Application
- Page – Select the page you wish to drill down to. In my example, the interactive report is located on Page 2, so i select 2 as the destination page.
- Clear Cache – Since our destination is Page 2, enter 2 in this field to clear the cache on it
- In the Item 1 field, select the name of the hidden item you created on your interactive report page (in this example, the hidden item is called P2_JOB). For value, select #LABEL#. This will pass the value of the Job label clicked to the hidden item.
That does it for the configuration.
Let’s now test this drill down configuration. Let’s try for example clicking the ‘SALESMAN’ part of the pie chart.
As shown below, our drill down functionality works correctly as the report is filtered to display only those employees with a job title of SALESMAN.
In the image above, clicking the ‘Employees’ tab in red will refresh the report and display all employee records.
There you have it. Simple drill down reporting from a chart to a report.