Drill Through Your Way

One of the most useful features of the SSRS 2005 Report Builder is the ability to generate reports on the fly. As long as there is a navigational path in the model, the end user can click on a drillthrough-enabled attribute to see more details. This feature is officially called infinite drillthrough.

Sometimes, the automatic drillthrough reports may get in the way. For example, there are performance implications when this feature is used with UDM as a data source. Or, you may need the drillthrough report to bring data from a different data source than the one the Report Builder model is built upon. To meet this requirement, you can wire up a custom drillthrough report. For example, let’s assume that your model has Customers and Accounts entities where a customer can have one or more accounts. You need to define your own drillthrough report that shows the customer’s account details when the end user clicks on the customer entity.

As a first step, you need to create the drillthrough report. If the report will fetch data from the model data source, use the Report Builder to author the report. Once the report is ready, go to the Report->Report Properties menu and select the “Allow the users to drill to this report from other reports” checkbox. This causes the Report Builder to add two input parameters (DrillthroughSourceQuery and DrilthroughContext) to the report. The Report Server will pass the semantic query and execution context to these parameters at runtime (more on this in a moment). Next, save the report to the Report Server. Start SQL Server Management Studio and open up the model properties. Select the Drillthrogh reports page (see attached screenshot) and set the Single Instance or Multiple Instance properties to point to the custom drillthrough report(s) you’ve created.

But what f you want your custom drillthrough report to fetch data from a different data source? Perhaps, your model is based on UDM but you need to display data from the relational database? Fear not, you have a complete control over the drillthrough report as long as it has DrillthroughSourceQuery (you don’t really need this one but it must be present) and DrillthroughContext string parameters. You will most likely need the identifier of the clicked entity in the Report Builder so you can filter data in the drillthrough report. The identifier is passed in XML format under the DrillthroughContext parameter, e.g.:

<DrillthroughContext xmlns=”http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling”>

…

<GroupingValues>

<GroupingValue Name=”Customer”>ABtbQ3VzdG9tZXJdLltDdXN0b21lcl0uJlsyNV0=</GroupingValue>

</GroupingValues>

</DrillthroughContext>

As you can see, the customer identifier is Base64-encoded, so it will take some extra effort to extract and decode it. You can write a simple custom function to load the DrillthroughContext XML fragment in XML DOM and navigate to //GroupingValue[@Name=’Customer’] (watch out for the default namespace!). Then, you can use System.Text.UTF8.GetString(System.Convert.FromBase64String()) to decode the value. For example, in our case, the identifier may look like [Customer].[Customer].[Customer].&[134] (recall that we target UDM as a data source). Finally, you need to parse this string to get to the 134 nugget of truth. Once it is yours, the rest is easy.