SQL Server 2008: The New Report Designer and Tablix

I’ve had some time to experiment with the new report designer in the November CTP and continue to believe this will be a reasonable alternative to the Visual Studio interface for business users who participate in the report development process, as I mentioned in my post Looking Forward to Report Designer Features in Katmai’s Reporting Services. I think hard-core report developers will be happy about some changes and not happy about others which I’ll explain shortly. In the November CTP, the new report designer features (as well as the Tablix report item) are available only in a separate client tool. Today I’ll comment on my general experiences without getting into a deep dive on everything there is to know about the report designer and Tablix. For that sort of detailed information, check out Books Online here for the report designer and here for Tablix.


The first thing that tends to throw people is the absence of a Tablix control on the Insert menu.



You actually get it when you add insert either a Table or a Matrix into your report. There’s no such thing as a table or matrix element anymore. They have both been replaced by the tablix element which is a hybrid of the two. By choosing table or matrix, you define your starting layout for the tablix but you can change your mind as you go. I like this feature a lot because one of my clients inevitably wants a report that started its life as a table to turn into a matrix.


Here’s a screenshot of a report I was working on:



The next thing I really like is the ability to put captions into the column headers for rows in my matrix. In the example above, the captions I added are Products and Total. My client who likes to see data in a matrix more often than not also likes column captions – so I am constantly fussing with rectangles and textboxes in the matrix to get it right. (It’s not hard the first time, and it’s not hard to add new columns “headers”, but it’s a pain to take them away I have found. Thank goodness SQL Server 2008 Reporting Services offers a new and better solution!)


Another nice feature is the ability to have different sets of static columns. Traditionally (i.e. SQL Server 2000 and SQL Server 2005), you can present columns of numeric data as a single static group underneath the group(s) defined on columns. Here you can see I’ve created something independently of the dynamic columns. The Avg Sale column is to the right of the CalendarYear group. You can tell it’s outside of the group because the bracket in the column handles includes only CalendarYear. Compare this with the brackets on rows – one group is indicated for EnglishProductSubcategoryName and a second group including the first group is indicated for EnglishProductCategoryName.


Notice also you don’t see standard expressions in the textboxes. Instead there are expression placeholders, like <<Expr>>. I’m not sure I’m wild about this, but it may be less intimidating to business users. For hard-core types, you can open the Properties pane and see the expression you’re accustomed to seeing in the Value property box. The Properties pane isn’t visible by default so timid business users don’t need to be frightened by its presence unnecessarily. To set properties, they can use Office-like dialog boxes. Now I found that to be a relatively tedious process (by my standards) to set something simple like a currency format with 0 decimals as compared to a simple C0 in the Properties pane, but that’s the hard-core developer type in me coming out.


Speaking of panes, notice the Data pane on the left. It contains your data source and dataset information. To edit, you just right-click the applicable item. The Built-in Fields folder contains all the global variables like ReportName, PageNumber, etc., making it much easier to enhance your report with these items.


Another huge improvement is the addition of the Row Groups and  Column Groups panes. Instead of having to navigate through the dialog boxes to get to your group properties – or even to see the hierarchical arrangement of them when using multiple groups – all is available right next to the report design surface. Just right-click on a group and click Edit to access its properties. To create a group, by the way, you just drag a field from the Data pane into the Row Group or Column Group and position it where you like. That’s about as easy as it gets!


Other cool features – when you enable the ruler, you get more than markings on the vertical and horizontal edges. The position of your cursor becomes a crosshair that travels along those edges so you can get incredible precision on the report design surface. And if you’re having difficulty seeing the details, the slider in the bottom right corner lets you zoom in as much as you like. I can’t tell you how many times I wished I could do this in earlier versions.


The report designer has the same paradigm as the standard BIDS designer – design view versus preview. Here’s a section of the report in preview mode:



Nothing unfamiliar here. However, I did notice that the ReportName global variable didn’t show up here – a little CTP bug – although the ExecutionTime global variable did appear correctly. ReportName did show up when I deployed the report to the server.


So now that you see the final report, you can see more clearly that this is a hybrid of a table plus a matrix… table+(matr)ix…tablix! How so? In a standard matrix in previous versions, the row groups would appear in separate columns. Here they appear in a single column – which I can easily do with grouping in a table. I have standard matrix style with dynamic columns but a new capability with the non-grouped column for Avg Sale at the end. Of course, you can nest a tablix inside of a tablix, which can make for some very interesting possibilities, I’m sure!


That’s it for today. More to come on Tablix in future blog posts. –Stacia

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.