Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Advent Calendar 12 – Tablix

Day 12 of my virtual advent calendar, about stuff I like in SQL Server 2008..

One of the frustrations I used to have with Reporting Services was to decide whether to use a table or a matrix data control, and if I got too far in to a design and it turned out to be wrong I had to pretty much start again.

Although in looks like nothing has changed in this regard in SQL Server 2008 there is in fact only one kind of data grid which is called Tablix (TABL e + matr IX).  Here’s one I made earlier in Report Builder v2


No only does it replace table and matrix reports it is also a lot more powerful – as a tablix control can have multiple independent row and column groups as you can see in this simple example..

I can add an independent group by right clicking on the column group area (bottom right)..


then add  Group –> Adjacent after, and then select group by Year.  That will create a new column into which I can drag the sales amount from the data set on the left into the detail row and then again into the total.  I need to have the year as the column header so I need to enter !Fields.[Order Year].value. in there (the teal coloured box).

If I run it now I get this.


This is simply not doable in SQL Server 2005 reporting services.  Be aware however that I had all of this data in the one data set, as any data region can only depend one dataset.

If you want to have a go, you’ll need adventureworks as the database and then the original report is here and the version I changed it to is here