Adding breadcrumbs to your drillthrough reports

In one of my projects, I was asked to implement a drillthrough report, which had a depth of around 6 pages. After the development, I was sitting with the user who was testing the reports and I noticed that the user was frequently shifting between the sixth and the third report to change the values. As most of us, she was also just using the back button of the internet explorer 3 times to reach the third report from the sixth report. That was when I quizzed her whether this was going to be a common requirement and suggested the idea of using breadcrumbs as a navigation aid. Though I can’t show you the original report, I will illustrate the idea here by making some sample reports. Here, I have created three chart reports using the AdventureWorks database to simulate the drillthrough effect – a) Order by Category Design Mode - Order by Category b) Order by SubCategory Design Mode - Order by SubCategory c) Order by Product Design Mode - Order by Product The Order by Category will be used as the starting point and on clicking a particular category in the chart, it will drill down to the subcategories of that category. Similarly, on clicking a particular subcategory, the chart will drill down to the products of that subcategory. Now, follow the steps detailed below to add the breadcrumbs:- 1) Now in the first report, add a textbox with the text – Order by Category as shown below Text in Order by Category report 2) In the second report, copy the same textbox and just to the right, make another textbox with the text – Order by Subcategory and do the formatting as shown below Text in Order by SubCategory report 3) Right click on the first textbox, select the textbox properties and click on the action property. Now click on the Go to URL radio button and enter the following expression =”javascript:history.go(-1)” Setting the action property for breadcrumbs This particular javascript will retrieve one page back in history. 4) Similarly, in the third report, add the textboxes in the format shown below. Text in Order by Product report 5) Edit the action property of the first textbox (as shown in step 3) and enter the following expression =”javascript:history.go(-2)” For the second textbox, enter the following expression =”javascript:history.go(-1)” 6) Save and deploy all the three reports and view them in internet explorer. Order by Category Order by SubCategory Order by Product As you can see from the images below, the breadcrumbs would be displayed. The active links would be displayed in blue while the inactive (the present page) would be displayed in black. You can click on the breadcrumbs directly to navigate between pages. This makes the drillthrough reports more user friendly and professional. The user for whom I designed this was extremely happy with the end result and gave me a BIG hug for making her life easier. As for me, I have included this trick in my list of best practices when designing drillthrough reports, who knows whether I would get another hug some other day by implementing this again 😉 Title

Posted by SQLJason

1 comment

Hi Jason,

Many thanks for sharing this. I've just tried to implement the breadcrumb in my report deployed via SharePoint, but it doesn't seem to work as expected. It is taking the report back to the List Library. Any thoughts on this?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.