Dashboard to report drill thru is possible but can become high maintenance in an enterprise sized solution.
Consider the above simple example dashboard. The drill through target report needs five parameters to capture the state of Year, Order Method, Product Line, Product Type and Product.
In a real dashboard, this number of report parameters becomes massive. We need to capture the state of any filter the user could apply by any navigation path. Within our dashboards we can use navigation paths to give the user the option of changing any visualisation to any dimension, and level in that dimension.
If a dashboard needs to drill to multiple reports, drill through maintenance becomes time consuming as each drill thru definition must be updated.
At the time of writing 11.2.3 dash drill to report drill through has the limitation off:
- Always opening in HTML
Whilst the user can run the opened HTML into a preferred format, it still means querying twice.
- Opening in the same tab.
Making comparing numbers in the report to the dashboard difficult.
Whilst the Cognos task switcher can be used to switch from a dashboard to a drill thru report the user must remember what they saw in the dashboard, switch and compare to the report. If they do this with multiple drill targets this becomes quite a memory test.
Compare this with opening drill through targets in new windows, where the windows could be arrange so the user can easily view the reports and dashboard at the same time.
To overcome the above issues and give a better drill thru experience a ‘hub’ report can be used. The dashboard drills thru to a single hub report. The hub report then drills thru to any other reports.
The advantages of this approach are:
- Only one dashboard drill thru definition need be maintained.
- Hub report to detail report is report drill thru. This functionality allows:
- Opening in a new tab, so a user can easily compare value to the dashboard.
- Running reports in any formats.
We recognise that:
- Not all detail reports may need the same filters.
- Maintaining the list of dashboard filters in each detail report is time consuming and a potentially high maintenance task.
User Story
Using the Go sales as an example. The user starts in a dashboard.
They have applied a year and order method filter. They have focused on Golf Equipment product line. The user access the Drill Through option.
This drills thru to the hub report.
The hub report shows:
- The filters that are being applied.
- A list of detail reports.
For the detail reports we show a description of the report, and the option to access it in HTML or Excel.
The user can decide from this suite of reports, which report or reports they want to run.
- To demonstrate functionality the Product Details report should only be filtered on the Product Line, whilst the Product Sales should use all filters.
Clicking on the Product Details HTML link runs the product details report.
As seen in the header, this report only uses the Product Line filter. Note the tabs in the browser window screenshot. The Product details report has opened in a new window, allowing the user to easily compare the report to the dashboard.
Returning to the hub report by changing the tab in browser, the user decides to run the Product Sales report, also in HTML. (I use HTML again to illustrate the new window functionality).
Again, we see the report has opened in another new tab, allow all report outputs and dashboards to be compared.
In this report, the header shows all filters are applied. In drill through target reports I tend to put the summary in the header rather than the footer. This allows the user to instantly see the number that they saw in the dashboard and then see it in the report, backing up the concept of drill through.
Contrast this with showing the summary in the footer. The user would drill through, then page down, or end, to get to the number they are familiar with.
If the detail reports took some time to run the user could just click the links of all the detail reports they needed and leave them running in their own browser window and return to the dashboard to carry on working. The hub report will always show the context of when they kicked of the drill through process.
If the detail reports took some time to run the user could just click the links of all the detail reports they needed and leave them running in their own browser window and return to the dashboard to carry on working. The hub report will always show the context of when they kicked of the drill through process.
Implementation Drill Through Target
In this example there are two drill through target reports; Product Details and Product Sales.
Each of these must contain filters for Product Line, Product Type and Product – after all the dashboard user could have drilled and filtered on any of these attributes. (The sales report needs additional filters for year and order method).
What would happen if we added another level of Product Category to the product dimension?
In a normal dashboard drill through we would have to:
- Add Product Category filters to the two reports.
- Update two dashboard drill through definitions.
In an enterprise scaled system this is quite a daunting task, that when we change data module navigation paths we would have to update multiple dashboard drill throughs and reports.
With the hub solution, only one dashboard drill through definition needs to be updated.
How do we reduce the other maintenance tasks, such as filtering the detail reports?
Within the data module we define a filter that can be used by any report author.
This filter accepts all the potential dashboard filters. In this example, within the data module this filter would be updated to include the new Product Category.
How about the component that shows the user what filters have been applied? If you look back at the screenshots that show the filters applied in each report you will notice that these are identical. These components are layout component references to an object in the hub report.
When the underlying object is changed the detail reports will reflect that change.
These two objects are all that are needed to turn a detail report into a drill through report. For that reason, we will not discuss these detail reports further. They are simple list reports.
For an enterprise system with many reports utilising this technique we have removed the need to update the detail reports themselves and reduced the maintenance tasks to:
- Updating a data module filter
- Updating the hub report components of:
- The filter display box.
- The hub to detail report drill throughs.
The Hub Report
The keystone to this solution is the hub report. The hub report must:
- Have a drill through definition within the dashboard.
- Be capable of accepting any filters applied in the dashboard and pass them through to detail reports.
- Only send applicable filters to the detail report (ie not send the year filter to the product details report).
- Show the user what filters are applied.
This should be a component that is usable within all the detail reports.
Accepting All Dashboard Filters
Within the hub report I have defined multi-select text boxes with parameters to hold each value that can be passed from the dashboard:
Users can filter on multiple years, products etc; hence the need for multi-select parameters.
These parameters are contained within a table that has a box type set to none making them not visible to the user.
The dashboard drill through definition passes the appropriate data items into these parameters:
Some Parameters are Not Needed
Sometimes parameters are not needed.
- A user may not have applied a filter.
For example a user could drill through without filtering a year. In this scenario the Year parameter is Not Needed.
- In the Product details reports the Order Method and Year are Not Needed.
In either scenario we end up with parameters that are sometimes needed and sometimes not needed. Within the prompt definitions seen above we define the default selection of the prompt to ‘NotNeeded’.
Filter Applied Text
We can use this ‘NotNeeded’ value in the filter applied text seen at the start of all the reports.
There are five text objects entries in this box – one for each parameter. Each is a report expression follows the same pattern. Here is the one for the Year parameter.
If the value of the Year parameter is ‘NotNeeded’ then an empty text string is shown. If the year parameter contains a value then a string showing the filter is shown. If the dashboard passes through no value, p_Year is NotNeeded and an empty string is shown. The same logic is performed for each parameter. Let’s demonstrate this.
In this dashboard there is no year filter. There is a filter on Order method, product line and product type. The hub report shows:
But if this is the same header applied in all the reports using an LCR, why doesn’t the product details reports show the order method filter?
We will return to this.
Dashboard Filter
We saw earlier how each of the detail reports had a common single filter that appeared to be doing all the dashboard filters.
This filter if defined within the data module.
This filter contains a filter for every parameter. This screenshot shows the beginning of the filter. It continues to include code for Product Line, Product Type and Product.
If we exam the Year filter component. A macro is used to check the value of the Year parameter. If the value is NotNeeded, ie the dashboard has passed through no value so the pompts default value is used, then a filter of 1=1 is applied. 1=1 is a value that is always true so no filter is applied. However, if the value isn’t NotNeeded then a filter is applied to the year data item.
If we think about what this filter would look like with these values:
We would end up with filter that looks like:
1=1 and -- Year filter
page_2.Order_method_type in (‘Fax’) and
page_3.Product_line in ( ‘Camping Equipment’ ) and
page_3.Product_type in ( ‘Cooking Gear’ ) and
1=1 -- Product filter
Hub to Detail Report Drill Through
We can use standard report to report drill through to drill from the hub report to the detail report.
Take the definition of the Product Sales report drill through.
This report can use all of the parameters. The Parameters drill through from the Hub report to the sales report does a 1:1 mapping.
Remember the hub report has all these parameters, and the sales report too has all these parameters as they were all defined in the standard dashboard data module filter.
What would this drill through look like for the Product Details report? We only want the product details report to use the Product Line, Product Type and Product Filter. If the product details report also uses the standard dashboard data module filter would it not also filter on Year and Order Method?
Within the hub report we have a query which contains a single data item:
If we examine the Product Detail drill through definition:
We pass through the ‘NotNeeded’ data item into the Year and Order Method parameters. Regardless of what values the Year and Order Method parameters have in the hub report, we are passing through NotNeeded into the product details report.
By doing so when the standard dashboard data module filter is evaluated in the Product Details report the filter will create a 1=1 for both the year and order method components of the filter. The filter applied text will also not show anything for the order method and year components.
Conclusion
By using a Hub report we have improved the user dashboard drill through experience.
When drilling through a user is given a precis of the filters that will be applied and the ability to run multiple reports in the desired format within new tabs. We can also show a brief report description to aid the user in the “what do you do next” thought process.
From an enterprise reporting system we have reduced dashboard drill through maintenance to just a few tasks.
1. Updating Data module standard drill through filter.
2. Updating the hub report.
Contrast that with the alternative of having to update each individual drill through report.