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:

Drilldown

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.

drilldown1

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.

del.icio.us Tags: ,,
Comments (12)

  1. ChrisWebb says:

    Sean – it’s not exactly the same scenario, but you might be interested to see this blog entry I wrote on implementing drilldown in SSRS charts based on SSAS data sources:

    http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!4194.entry

    Chris

  2. Just a quick post to reference two interesting reads about designing dynamic drilldown in reports: Sean

  3. phebbles says:

    Hi,

    I know this is off topic but  we are currently having issue on our web application project report which I don’t know if it’s an abnormal behavior, It’s been months now and we still cannot resolved this problem. We have a report that’s composed of two sections (body and footer). Lets focus on the body section, first there is a sub-report that serves as a header then the second one is the main part of the report (a table control). This table has 2 groupings, on the second grouping there are two tables created (left and right horizontally). Left table has 1 grouping and right table none.

    The problem here is that this main table jumps on to the 2nd page when the rendered data does not fit on 1st page which causes a large amount of white space on page 1.

    Hope I could get a response from this. Thank you.

  4. Sphinx says:

    Great tutorial. Wish this was included in the SSRS Step by Step guide by MS Press.

    Very helpful.

  5. Jispira says:

    Hi Guys, Im fairly new to the concept of drill downs/Drill Throughs in SSRS though i set up the above example for one of my backlog reports (only adjusted the parameters to reference my data and also the expressions)

    My source data query looks like this:

    select

    CompanyId, CompanyName, StateId, StateName, BranchName,

    Discipline, StatusGroup, Count(Callno) as 'Count'

    from db_owner.Service_Call_Backlog_V2_V

    where

    Statusgroup not in ('Finished')

    group by

    CompanyId, CompanyName, StateId, StateName, BranchName,

    Discipline, StatusGroup

    I removed the "Where param in (@param) clauses and set up though i am getting "No data Available" when in preview.

    Any ideas?

    Cheers

  6. John says:

    Thanks for this. I get an error when previewing the report:: The processing of FilterExpression for the grouping 'xxxx' cannot be performed. Cannot compare data types of System.Boolean and System.String. Please check the data type returned by the Filter Expression.

    I copied exactly what you have and am using the AdventureWorks sample database.

    Any ideas?

  7. Artabandhu says:

    It is not Drill Down It is Drill Through

  8. David says:

    Regarding the "Cannot compare data types" error. I made the same mistake at first. On the Category Group Properties, be sure to change the type to "Boolean" in the combo to the right of the expression text box.

  9. Kevin says:

    This doesn't seem to work.  As soon as I apply the filter, the chart returns no data.  I have the filter type set to Boolean, as per some of the other comments.  It runs fine, no errors, but just always returns no data.  I built my own datatable but the format is identical to this example one.

  10. maarten van eck says:

    Hi Sean

    I used exacttly your code but I got everytime the descrption no data available. But there is certainly data available.

    Do you know how to solve this

  11. Stephanie says:

    I have setup my drill down charts exactly and everything seems to work, but when i drill down to the next report, my date range parameter changes itself.  I'll have chosen 1/1/2013 – 11/21-2013 and SSRS will change it to different dates such as 10/29/2013 – 10/29-2013 for example.  I'm unsure if it's an issue with SSRS and how i have it setup or whether it's an issue with my network and losing data while passing the parameters through.   Has anyone else experienced this with SSRS and if so, what's the fix?