Expression-Based Dynamic Drillthrough Actions

The Reporting Services demo during the "BI Power Hour" session at the Microsoft Business Intelligence Conference was a lot of fun and well received.  My colleague Carolyn Chau did great on stage presenting the demo we had put together.  In my chalk talk the next morning, I then spent a few minutes explaining the key components of the demo and how everything worked together - I will cover more parts in future blog postings. 

One of the features that attendees of my chalk talk seemed a bit surprised was expression-based dynamic drillthrough reports.  This powerful feature has been present since the initial release of Reporting Services, but not many report authors know about it apparently. 

Dynamic drillthroughs were used in one part of the overall demo scenario.  Specifically, I used chart data points with marker images and wanted to ensure that a particular data point in the chart can only be clicked at most once.  In other words, a drillthrough should be available initially on every data point of the grid, but once clicked, the blue "water" background is overlayed with a data point marker to indicate hit or miss (it was a game after all), and there should be no further drillthrough action.  The image on the right shows a small subsection of the overall chart used in the demo.  In the specific example, all grid cells except for (1,1), (2,2), and (3,3) should still have active drillthrough links.

This can be accomplished by using an expression-based dynamic drillthrough action: if a particular cell wasn't clicked yet perform a drillthrough to the current report itself, otherwise don't show any drillthrough link by setting the drillthrough target report name to Nothing (null).  In the specific demo example I used the following expression as a drillthrough report action:

=iif(Fields!CellClicked.Value, Nothing, Globals!ReportName)

Note that Globals!ReportName represents the name of the current report - it will drillthrough to itself with the specified drillthrough parameter values.  More information about all built-in global collections accessible from RDL expressions is available in SQL Server Books On Line. 

Another usage scenario is a parent-child recursive grouping hierarchy with dynamic drillthrough target reports based on the current recursive level in the hierarchy.  In an upcoming posting I will explain how to use parent-child hierarchies from an Analysis Services cubes in Reporting Services reports.

Update: the full Power Hour demo is now available here.