Enabling Drilldown on (SSRS) Reporting Services 2008 Charts

 

Enabling Drilldown on (SSRS) Reporting Services 2008 Charts

In this post I'm going to spend some time covering a subject that comes up quite a bit on our forums, which is how to enable drill-down on a chart in Reporting Services. In this example, I'm using the Analysis Services 2008 AdventureWorks cube. 

First, create a connection to the Analysis Services database, and in the report, create a dataset based on the following query:

SELECT NON EMPTY
{ [Measures].[Internet Sales Amount] } ON COLUMNS,

{[Customer].[Customer Geography].[Country].members,[Customer].[Customer Geography].[State-Province],[Customer].[Customer Geography].[City]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This returns a dataset that looks like the following:

The dataset is similar in structure as a query you could author against a relational database using the GROUP BY clause. 

The next step involves inserting a chart into the report.  In this example, I'm using a column chart.  Once the column chart is inserted into the report, add the country field to the category groups and then add the Internet Sales field to the data fields section of the chart. 

Another piece we'll need in this sample is a set of parameters to track where we are in the hierarchy as we drill.  Create three parameters in the report named pCountry, pState, and pCity and set their visibility to hidden, allow null values and no default values supplied. 

Next, return to the category groups on the chart.  As we drill on the bars, the category grouping on the chart should change from country, state and city, and the dataset should be filtered to the correct level as well.  To set up the category grouping on the chart to use the following expression.

=IIF(isNothing(parameters!pCountry.Value),
Fields!Country.Value, IIF(isNothing(parameters!pState.Value)=True,Fields!State_Province.Value,Fields!City.Value))

Use a similar expression for the label text option which is right above where you define the category groups.

To set up the filtering, select the filter option on the category group and add a filter and set it's expression to the following:

=IIF(IsNothing(Parameters!pCountry.Value), IsNothing(Fields!State_Province.Value),
IIF(IsNothing(Parameters!pState.Value), (IsNothing(Fields!State_Province)=False AndAlso Fields!Country.Value  = Parameters!pCountry.Value),
(IsNothing(Fields!City.Value)= False AndAlso Fields!State_Province.Value=Parameters!pState.Value)))

Set the operator to "=" and set the value to "=True" (no quotes required). By setting up the filter expression in this way, we are effectively doing  a check on our level parameter, and depending on it's value, we are passing a different expression along to the filter operation.  In the simplest example, if the country parameter is set to Nothing, we simply pass the IsNothing(Fields!State_Province.Value) expression to the filter.  All records where that expression evaluates to True will not be displayed. 

Finally, we need to set up the action on the chart series so that when the user clicks on a column, we redisplay the report.  To do this, select the series and set up the page as shown below.

In this example, we are specifying our existing report as the "go to report" and are passing the current state of our parameters to the new instance of the report that will execute when the user selects one of the bars on the chart.

In closing, this is just one option for implementing a drilldown on a chart.  Another option here, that I would recommend for larger data sets would be to actually set the query to an expression based on similar logic.  If you have other alternatives for implementing this type of a scenario, add a link in the comments section or contact me via the blog.  I'm interested to hear how others implement this scenario. 

I've uploaded this file to my SkyDrive account.  You can get it here.

Published Monday, February 09, 2009 12:25 AM by Sean Boon

Boon Blog : Enabling Drilldown on (SSRS) Reporting Services 2008 Charts