Upping Your Drill-Down Game

Upping Your Drill-Down Game

Thanks to the power of Web Intelligence, you can build a single report that presents data at a very high level and drill down to a very low level, courtesy of Navigation Paths being set up in the Universe. If you haven’t seen it at work, here it is:

1. Enable drill mode in the Analysis tab ...

2. Hover the cursor over the cell value you want to drill into ...

3. Click on the value to drill down, and note teh Value of Orders is Automatically recalculated ...

So drilling down is a really useful feature in WebI and every Universe Designer should plan for it being used, however it’s not without it’s limitations. To start with, you need some form of agreed hierarchy to be built into the Universe Navigation path – it might not be practical to do this for all the possible hierarchies within your Business; you are also limited to drilling with the same data in your tables without committing yourself to complex objects that change values and headers based on the drill level you are at. If you compare the 2 reports below, you will see one is a sales overview for each country and the other is an order detail report.

You can’t easily drill down from what the first report shows down to what the same report shows through the normal drill down method. What you will find as a result is a people building the second detail report as a separate entity and if you were looking at a figure in the first report, you would then either have to filter the second report to get the detail behind the first report, or use some other method to get at the same information. It makes the reporting harder to follow and reduces the user experience.

There is another way. By employing hyperlinks, we can allow the user to move from one high level report to a detail report, retaining the context or what they were looking at to make the experience of looking at the reports as quick and easy as possible. Every document stored in the BI platform has its own ID and can be accessed directly via a URL without having to first go to the portal. We can use this ability to go straight to a report from either an intranet page, a dashboard or another WebI report.

First things first, the detail report needs to filter based on the dimension value we will pass it from the higher level report, so Country in this case. We add a filter to the query and set it to being equal to a prompt. This means the report will always ask for the Country when the report is refreshed.

Next we need to set the report to refresh on open, so new data is always requested when the report loads.

Saving that report, we then go into the first report and set the link up for the detail – this is done by right-clicking on the cell we want to apply the link to, selecting ‘Linking’ in the popup menu, followed by Document Link (note, not Hyperlink – we could select that, but Document Link does a lot of the work for us, making it an easier process).

This produces a 'Create Hyperlink' box and we can click 'Browse ...' to pick the report we want to link to.

Once we’ve selected the report, the prompts are automatically recognised and we can chose objects to assign to them. This is where it gets really beneficial using this method as it avoids us having to play around with hyperlinks and parameters – especially as you can specify tool tips and whether it opens in a new window or not.

With the objects assigned to the prompts it now drops the linked attributes in to the report - and if you look at the formala assigned to the cell, you can see it's put all the HTML encoding in for you as well.

Clicking on any one of those values will now load the drill report up, using the Country as the prompt value.

You can add this functionality to any WebI report and it makes a really big impact on your reporting output when you can control more of the flow of report consumers.

Posted By Mike Hobson, Senior Business Intelligence Consultant on March 26, 2019