New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Displaying data in a matrix

From: SQL Server Reporting Services in Depth

Video: Displaying data in a matrix

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.

Displaying data in a matrix

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.

Show transcript

This video is part of

Image for SQL Server Reporting Services in Depth
SQL Server Reporting Services in Depth

40 video lessons · 9473 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 12m 6s
    1. Welcome
      1m 9s
    2. What you need to know
      1m 36s
    3. Exploring SQL Server Reporting Services components
      4m 17s
    4. Reviewing SQL Server versions
      5m 4s
  2. 49m 41s
    1. The elements of a report
      3m 10s
    2. Creating a report with Report Builder
      12m 11s
    3. Grouping table regions on a report
      6m 1s
    4. Joining data from multiple tables
      4m 33s
    5. Formatting report elements
      4m 34s
    6. Using functions in a report
      11m 0s
    7. Displaying data in a matrix
      8m 12s
  3. 24m 9s
    1. Filtering data and adding parameters to a report
      5m 35s
    2. Customizing report parameters
      5m 4s
    3. Sorting data in a data region
      4m 7s
    4. Applying interactive sorting
      4m 57s
    5. Creating a drillthrough action to connect reports
      4m 26s
  4. 49m 57s
    1. Introduction to charting in Reporting Services
      4m 16s
    2. Creating a column chart
      8m 35s
    3. Adding a generated average to a chart
      4m 5s
    4. Creating a pie chart
      8m 19s
    5. Using sparklines
      6m 38s
    6. Adding a sparkline to a drilldown matrix
      14m 34s
    7. Adding data bars
      3m 30s
  5. 21m 48s
    1. Adding indicators to a report
      7m 52s
    2. Using and configuring gauges
      5m 30s
    3. Using maps in Reporting Services
      8m 26s
  6. 38m 14s
    1. Creating modular reports with report parts
      4m 36s
    2. Adding and updating report parts
      4m 37s
    3. Using subreports and nested regions
      4m 28s
    4. Configuring headers and footers
      3m 9s
    5. Printing and exporting reports
      3m 45s
    6. Using page breaks
      5m 37s
    7. Creating and using shared data sources
      8m 11s
    8. Creating and using shared data sets
      3m 51s
  7. 27m 20s
    1. Organizing reports in Report Manager
      3m 1s
    2. Adding users and configuring report security
      5m 24s
    3. Configuring subscriptions
      5m 13s
    4. Creating a linked report
      4m 8s
    5. Using Report Designer in SQL Server Data Tools
      9m 34s
  8. 1m 2s
    1. Goodbye
      1m 2s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.