Home > Reporting Services > Building Drill Down Reports using the Tablix control

Building Drill Down Reports using the Tablix control


Drill down reports are very useful for data analysis. They allow a form of interactivity for the report viewer and make it easier to comprehend the information in the report, especially when the said report is displaying a large amount of data. Although creating drill down reports in Reporting Services is quite easy, I have decided to outline the steps here for the benefit of those who’ve never created them before. It’s also an opportunity for me to talk a bit about the new Report Designer and Tablix in Sql Server 2008 !!

Report Designer

Report Designer ships with SQL Server 2008, and in the November CTP ( the last at the time of writing ), they’ve jazzed up the interface, giving it a look and feel similar to Microsoft Word. It’s also just as easy to use now as Microsoft Word, so easy in fact , that I think business users will soon be able to create most reports themselves and won’t need us developers !!

I’ve created a simple query using AdventureWorks that we can use :

SELECT  Sales.SalesTerritory.Name as Region,
        Sales.SalesTerritory.CountryRegionCode,
        Sales.SalesTerritory.[Group] as Territory,
        Year(Sales.SalesOrderHeader.DueDate) as DueYear,
        Month(Sales.SalesOrderHeader.DueDate) as DueMonth,
        Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID
 

When you start Report Designer, it will ask you to create a Data Source Connection. This is quite straight forward ( Click on the link in the body of the report to start ). Once thats done , you can create datasets. An improvement in Report Designer is the Data Pane on the left of the screen. It allows you to manage Data Sources, DataSets, Images and Parameters and also lists the “Global” fields for quick reference.  

It should let you create a dataset when creating the data source. You can always edit it later and set the properties. When you right-click and edit the data source , you are shown the dataset properties box. You can setup parameters here as well , so take note of the options.

The Data Pane

Data Pane

The Dataset Properties Window

Dataset Properties

Once the dataset is created you’re ready to proceed. The steps are as follows :

1) Click [Insert] from the top menu , then double-click the [Matrix] button to insert a Matrix into the report. Note that whether you use a table or matrix it’s still the tablix control that you are using.

2) One of the new features of the tablix is that there is a button on each field that you can click and then select the required data field.

Fields in Matrix

3) If you assign Territory to the Rows Group , DueYear to the Columns Group, and TotalDue to the Data textbox, you should end up with a simple matrix that resembles the following ( note I have done some simple formatting ) :

First Matrix
4) Now we need to add the sub-groups. You will notice that there are 2 task panes at the bottom indicating the row and column groups of your matrix. You can right click the Row Group ( Territory ) , Select Add Group , then select Child Group, since we want to add a group at a level below Territory. You will also notice the Adjacent option, this is merely to extend your matrix to add another group at the same level.

Adding Child Group

Name the group Country and select CountryRegionCode as the field to group on. Now when you run the report you will see the second Row Group created and the figures spilt by Country.

Second Matrix
The next step is to make it interactive. To make it easier to do this, make sure that you name all the cells in your matrix. For example, when you’re back in design mode, click in the cell that holds the Territory value and name it Territory. Do this for the other cells as well ( eg. DueYear, and CountryRegionCode ).

We now need to edit the group properties for the child group called Country. On the Row Groups task pane at the bottom, right click the Country child group and select Edit Group. The Tablix Group Properties box comes up. Select Visibility on the left and select Hide to hide the group. We then have to allow it to be toggled by another cell, and the Territory cell which you renamed earlier is the one we use. Check “Display can be toggled by report item” and select the Territory text box.

When you preview the report again , you will see that you now have a drill down function on the row groups.

Third Matrix

Therefore “to create drill down reports, all you have to do is hide a row or column group and set it to be toggled by the cell before it” In the same vein, you can now add a Month child group to the columns and make it such that Year will drill down into Month. Once this is done , I like to merge the unused cells on the top left so that it looks neater.

Final Matrix

You can now play with things like adding sub-totals ( hint : Right Click on the cell and you can add totals for the level you’re on , either before or after the current cell ).

Although creating these drill down reports was just as easy in RS 2005, managing things such as groups and sub-totals are much improved with the Tablix, and I feel that once you’re used to the new control it will be a pain to go back to the old Table / Matrix.

Advertisements
Categories: Reporting Services
  1. Ali
    May 4, 2012 at 3:06 pm

    Nice article. I am having issues showing the total for a paritcular group. I am following step by step but still not able to get the total when i show North America withour regions.

  2. shruti
    June 13, 2012 at 3:51 pm

    Hi Thavash,

    Thanks for the elaborate information.

    I’m trying to modify a tablix report which has 1 row-group (casetype) and 1 column group (campaign). The cells contain the sum of gifts and a total value under the row group and column group. Each cell drills through a detail report that displays all the gifts that add up to that sum. All works fine except for the drill through report in the ‘Total’ cell . Technically, it has to display all the gifts that have the casetype and campaign as the ones that show up in the tablix report. However, it only displays gifts of the first casetype or first campaign.
    To design the drill through report, I have the ‘ACTION’ in the text property of the cell to go to the detail report and I pass giftcampaign and casetype as parameters. Is there a way to adjust the expression to pass multiple parameters? So when i click on the ‘Total’ it takes to a report that shows all the casetypes and not just the first one. I tried using Join but i get an error. My drill through report is set to allow multiple values for the parameters and works fine when run by itself.

    Any tips/ideas is much appreciated.

    Thank you
    Shruti

  3. iby
    August 16, 2012 at 5:40 pm

    Great article, thanks!!!

  4. November 30, 2012 at 12:22 am

    Howdy would you mind letting me know which web host you’re using? I’ve loaded your blog in 3 completely different browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good internet hosting provider at a fair price?
    Thank you, I appreciate it!

  5. Vinay
    December 28, 2012 at 11:50 am

    Good one thanks.

  6. September 5, 2013 at 1:28 pm

    Heya i am for the primary time here. I found this board
    and I to find It truly useful & it helped me
    out much. I hope to give something back and aid others like you aided me.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: