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

SQL Server Advent Calendar 12 - Tablix

  • Comments 2
  • Likes

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

image

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)..

image

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.

image 

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

Comments
  • Pour tous ceux qui ont gardé une âme d'enfant ! SQL Server Advent Calendar SQL Server Advent

  • Is it possible to add a second Tablix into the first one, but have data in the second Tablix refer to a second dataset?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment