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 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.[Group] as Territory,
Year(Sales.SalesOrderHeader.DueDate) as DueYear,
Month(Sales.SalesOrderHeader.DueDate) as DueMonth,
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.
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.
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 ) :
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.
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.
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.
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.
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.
I normally use the Money data type when designing databases , as I’ve always read that it was specifically designed for currencies. One of the advantages , for example , is that you can use a currency symbol with it. Recently I came accross something very interesting while browsing the net. Apparently there are accuracy issues with the data type. I have below a simple example to demonstrate. Note that this isn’t my example but something that I found on the web which I thought I’d share :
declare @m money
declare @d decimal(9,2)
set @m = 19.34
set @d = 19.34
So what would the results be ? Well if you’re expecting 19.34 for the money variable , you’d be wrong !! You actually get 19.30. Yup, I was surprised as you are right now. I’ve even tested this in the Katmai CTP and it does the same thing. So from now on I’ll try to use decimal and specify the precision I need , when creating tables. Any feedback on why you’d want to still use Money ? (I’m sure that there’s a reason )
UPDATE : HussainSafal sent in a little tip – if you add a decimal point to the numbers in the operation ( in this case the denominator ) , the operation will produce the correct result when using a variable of type money. If that made no sense – then look at this :