Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.
- Understanding the elements of a report
- Grouping table regions
- Joining data from multiple tables
- Displaying data in a matrix
- Customizing report parameters
- Filtering and sorting data
- Creating charts
- Adding sparklines and data bars
- Creating at-a-glance reports with indicators
- Using Maps in Reporting Services
- Configuring report security
- Printing and exporting reports
Skill Level Advanced
When we have repeating data in our report, when we know that we have multiple rows of data to show, we're going to end up using one of three different options here in Report Builder-- the Table, the Matrix, and the List-- and these are known as data regions. So what's the difference? Well, the list is the simplest. This is just a free-form region that will repeat whatever you drag and drop into it, and we'll see that one later. Tables and matrixes, or matrices, are more common, more interesting, and more configurable. But whether you select a table, a matrix, or a list, you're really using the same thing.
It's an element called a tablix. So working with these is all very similar. I've already shown a couple of table examples, and what I'm looking at right now is the Products By Category report I created earlier. It doesn't really matter. There's nothing special about this one. But if I grab this table by clicking anywhere inside it and then clicking that top-left section, I can actually see that this comes under the name of Tablix, and if I open up the Property Pages, I see it's Tablix Properties, not Table, because it's the same element for tables, matrixes, and lists.
So what does it mean to be a table? Well, when I'm in Design view and I defined this report, I decided how many columns were shown in it, in this case 2-- Category and Name-- although it could have been more. But the columns are fixed. They're static, but table rows are dynamic. What I mean by that is when we run this, the columns will always be two, but the rows will differ depending on how many pieces of data we have. It might be a dozen; it might be a thousand. So the table will expand vertically down the page based on how many rows have been returned from the database, but it's always two columns.
A matrix, on the other hand, is dynamic both ways. It can expand both vertically and horizontally, allowing us to cross-reference and drill into the data. If you're a Microsoft Access or Excel user, it's similar to a cross-tab or a PivotTable; but if those terms mean nothing to you, no problem. Let's see an example of a matrix. So I'm going to leave this report alone and just click Report Builder to make a new one. Again, Blank Report for right now, because we don't need to use any of the wizards.
I'll just give this a title, Matrix Example, and of course repeating this general process of adding a data source. This will be embedded in the report, and I'm just going to point it to AdventureWorksLT and create the dataset to define the data that I want. Well, again, what I'm going to go for is Products information. This will be embedded inside the report. I'll use the data source we just created.
And in the Query Designer I'm going to drill down into the Products table and I'm just going to go for two pieces of information here: the Name and the Color. Close that down, and then from the ProductCategory Table I'm going to go for Name. Based on what we talked about earlier, this should join the two together. If I run the query, it certainly looks about what I would expect to see. We've got a product with a color in a particular category. As I start to scroll down, we should see different categories showing up. If I wanted to verify the SQL itself, I can click the Edit as Text button and see where it's doing the inner join between those two tables.
Now, I'm purposely making this dataset as basic as possible, because it's really common to see matrix examples to go completely over the top with massive complex data, and we really don't need that just to understand the idea of a matrix. So just having our Product Name, Product Color, and Product Category, that will give us enough to work with, because I could show this same data in a table or in a matrix, and in fact it's quite useful just to compare the two. So, very quickly, I'm going to jump to the Insert tab and just go through and create a table, then delete it, and then do a matrix.
Table, I would base from the dataset. I could drag my Product Name into the values and Color into the values. Drag ProductCategory into the Row groups. We've already seen this. This is how I would group things together in a table. I'll just accept the default options here, Finish, drag that a little wider so we can see it, and test it, because I'm going to delete this in just a second. So this is the Dataset being presented, split up into categories. I can expand each of these. If there's a color for it, it will show up; if there is not, it won't.
So here's a basic Table idea. But we've already seen this, so what's the difference between this and a matrix? Back over into Design view. I'm going to grab this table first by clicking anywhere in it, click the top corner, and then hit Delete, and now I'm going to go up to the Insert tab and actually select the Matrix Wizard option instead of the Table Wizard one. Actually, here's a secret. There is absolutely no difference between the two; both the Matrix Wizard and Table Wizard give you the same window, which is the New Table or Matrix.
So again, I'll do exactly the same thing. Choose the Dataset, just as I did a moment ago. Click Next. This looks exactly the same because it is. The only difference between a table and a matrix is do you have something in Column groups? Do you have something that will allow this to expand not just up and down, but left and right? Well, what I'm going to do here is drag on the ProductCategory_Name into the Row group, so grouping them together by Category, which is what we did before. I'm going to drag the Product Name into the Values. I want to see the Product Name there.
And I'm going to drag Color, instead of into Values, which is what I did a moment ago, but into Column groups. This is now a matrix. If there's something in Column groups, it can expand left and right. However, it won't work yet, and if I try and click the Next button, I'm going to get an error message. If one or more column groups is defined-- meaning if you're making a Matrix--all the fields in the Values section must have an aggregate function specified. So what does it mean by this? Well, if we're making a matrix, the assumption is we're trying to not show every single miniscule piece of data.
We actually want to aggregate or group them in some way. Something needs to be totaled or counted or averaged, because we're trying to cross-reference this information. So I might be wanting to see sales totals broken down by region and by salesperson. Or in this case I'm trying to allow myself to view these products broken down either by category or by color. So I'm providing an aggregate function for this. And let's say I'm going to just count them. So from this dropdown arrow, I can select one of the common aggregate functions.
And in fact, what I'm going to select is Count, to just count the number of products. What this will allow us to do is drill into this data in two ways. How many products are in each category, down the left, counting the numbers of it, and then how many products in each color, along the top, and again giving me the numbers for it. And I'll get totals of all of these, plus a full cross-reference between them, and that's a matrix. So, click Next. I'm going to accept the options here, Show subtotals and grand totals. Click Next.
And I'll just select a different style. Let's go with Slate and Finish. That's it. Doesn't look very different, but remember, this is going to expand up and down and left and right based on the data. So when I run this we'll see a very different look and feel here. So I can go down the left-hand side to see the grouping by category. In the category forks we have 3 options, and the total is representing--and no color has been actually added to them--a total of 3 here. If I come down a little bit, say into the Mountain Bikes section, we've got 16 showing up, in this column 16 here, with a total of 32. Again, following them up, we can actually drill down and see the amount of products in each of the column categories as well, and the full cross -reference between them. This is a matrix.
We can take the same data, display it as a table, display it as a matrix. You can take it deeper. You can have multiple levels of grouping, multiple ways to drill down inside this information, multiple row groups and multiple column groups, and it will support all of that in Reporting Services. But before we get to that, we're going to see how to add some more interactivity into our reports.