Reporting Services (SSRS) Part 15 - Matrix

welcome to this wise ell tutorial in this session we're going to teach you how to use a matrix in reporting services reports what you'll learn in this session is how to work with matrix reports item we'll start by showing you how to add one to a report and then how to choose the fields for the matrix then we'll show you how you add row and column totals how you can group by multiple fields and then finally how you can add multiple aggregate statistics to a matrix you've already built so let's get started a matrix report in reporting services is a lot like a crosstab report in Microsoft Access or even a basic pivot table in Microsoft Excel it allows you to group your data both by row and by column and generate some sort of aggregate statistic about items in the group so the example we'll build in our video is we'll group our film records by the studio which made them and the film certificate it was awarded and will generate the average length in minutes we'll also add row totals and column totals and then finally a grand total right down in the bottom right hand corner so that's what we're going to build before you get started with a matrix you need to make sure you've created a data set which includes at least three fields you'll need one field for the column groups which in this case is the film certificate one field for the bro groups which in this case is the studio name and then one field which you can use to generate the aggregate statistics in this case is the film's run time in minutes once you've created the data set you simply need to head to the toolbox and then double-click the matrix tool but I look at my report now I'll find that the matrix is sitting there and I've got three areas for which I need to choose a field so for the row groups I'm gonna choose the studio name you might be able to tell from the icon sitting at the left-hand side that this area is already assigned to a group and if I look down in the wrote groups panel I can see that the studio name has been added there automatically likewise when I choose for the columns the certificate field I'll see that the certificate fields gets added to the column groups area finally I'm gonna choose the film run time in minutes to generate my aggregate statistics which by default will leave me with the sum all I'm gonna do now is modify this to give me the average I can right-click on the text box choose expression and then simply change the sum function for the AVG function instead once I click OK I already have the basic layout of my matrix created and a quick preview will show me the information and show me how much work I've got to do in order to tidy it up so there are several things that I can do to improve the basic layout of my matrix apart from obviously modifying the the formatting of my row headers and column headers so I'm not gonna bore you if they're how to change colors and things but I could add things like a column header which spans the full width of the table and in the case of these are the film certificates I can modify the number formatting to control the number of decimal places and perhaps even more importantly I can add row totals or row averages I suppose and column averages as well as a grand average for the overall table so to do all those things I need to go back to the design view and if I click inside the table I'm gonna quickly modify the width of my my studio name column and then maybe I'll quickly apply a background color so as not to bore you too much I'll just do it for these two headers and then that's as far as I'll go with basic formatting what I'd like to do next is insert a header for the author columns which spans the entire width the table to do that I need to right-click on the row selector of the left-hand side of the table and choose to insert a row now I need to insert this above the road that I've selected now that you've got two choices for that and they're inside the group or outside the group if I was do this inside the group what I would actually do is generate a new heading for each individual column in my matrix what I'd like to do is insert a row that's outside of the group now when I do that it looks as I've only created a single cell I'm gonna type in a quick title of film certificates but when I preview the report now we should see that the heading spanned the entire width of the table so I could send to that heading across the entire table next I can quickly modify the number format of my value cells so if I head back to the design view again I can right-click on the cell containing the average function and choose text box properties on the number tab of the dialog box I can choose to display the numbers as a number rather than the default format and I can control the number of decimal places I'll leave them set at 2 for this example I can click OK and then preview the report again and the number should appear a lot more sensibly and while I'm talking about the numbers I'm going to then add in the column and row totals so to do that I'm going to head back to the design view again and I'm going to right click on this cell and then I can choose to add totals using the menu if I had a total row that will appear at the bottom of the table and will get me the grand average for each certificate so I click roam I'll add the other total row if I right click on that same cell again and choose to add a total column this time there we go I have the total column all I need to do now is get the grand total in this time the grand average for the entire table is to select the film run time minutes field for the bottom right hand corner cell which if I do that knowingly slightly annoyingly at least it has the sum function again because that's the default whenever you choose to add a numerical field to a matrix so I'm going to right click on that cell and choose to modify the expression and I can finally simply update some AVG instead when I click OK that's a quick different background color so we can see that that's clearly different and preview the report I'll have all of the values that I want to see one thing that I finally forgot to do was update the number formats for a grand total as well you can see that it's got more than two decimal places but you should be able to easily work out how to do that so there's our basic matrix bar a bit of font and background because of formatting it looks pretty presentable sorting records in a matrix is a little bit different to what you'll be familiar with from sorting in normal tables it's actually a lot more similar to what you what you're doing and group to table so let's say I wanted to change the order of my studio names to reverse alphabetical order if I head back to the design view ordinarily to sort a table you might expect to right-click the top left hand corner of the table and view the tablet properties then on the sorting table you'd expect to add a sort and there are choose to sort by studio name in red to a order if I click OK and then preview the report it's more strangely that won't have any effect whatsoever I'm selling out of a better call order so after the design view I'm gonna simply undo or what I've just done I'm gonna take that a sauce away from the table and instead I'm gonna use the group's panel down here at the bottom of the design view I'm going to right click on my studio name group and choose to view its properties and on the sorting tab in this dialog box you can see that there's already a sort order they're grouped fields are all automatically sorted in ascending order so if I want to modify this I can simply change the order from HSN to zap tea a choose okay and then finally preview my reports and I'll see that my sorting now has had the desired effect in a matrix it's possible to group your rows or columns by more than one field at the same time so in this example what we have so far is a basic matrix containing groups of film decades and country names and then account of than the films that were released in that combination of decade and country if we have a quick little preview we can see a slightly heavily biased towards towards later years and in the USA but what we have is a chemist fairly simple matrix what we're going to do now though we're going to add in a second column to the to the row groups which will group individually by year so we'll break down each decade into individual years so to do that back in the design view we already have the other data in the data set already he's watching calculated films the film decade year and month these are all derived from the film's release date a Wiggins day is going to drag the film year and something gonna click and drag using the mouse and position it just to the right of the film decade now it's important that you get this the right way around can you see this I move the mouse ever so slightly left and right the icon that I'm seeing this side this this for this blue bar changes its direction I want to make sure that it's pointing towards the left so that it goes in as part of the the groups area not as another value so I make sure it's pointed in the left release the mouse button I ought to get a new column called film here and in the group's panel a new group as well you can see that this is ever so slightly indented below film decade indicating that it said that it's a subgroup of decade so if I preview the report after all that explanation that's the sort of thing we should see so as well as showing each decade we've now got things broken down by year as well and you can carry on with that process if I go back to the design view this will I think make things look a little bit too messy but if I drag from the report data window from my day set I'm gonna drag film month and again I'm going to position this just to the the rights of the film year again making sure that that blue bar is pointing towards the left if I preview porn now I get my data broken down into even further groups so got decade yeah and then month in year but it just shows you how far you can go with a bit more data in my in my database this would generate some slightly more useful information than what I have here as well as having multiple grouped fields in a matrix you can also have multiple statistics or multiple aggregates as well so I've gone back to the example we were creating a theorem which is grouping our films by studio name and certificate and at this point all we have is the average of the films run time in minutes what I also like to do is show the total run time in minutes for each combination of studio and certificate and to do that and its head to the report data window and simply drag in my runtime minutes field again I can position this in one of two places again I can drag it just to the the right hand side of studio name and this time I want to make sure that the blue bar is pointing to the right hand side this time alternatively I can drag it over to the right hand edge so that the sum would go after the average it doesn't really matter which of the two I choose to do as I mean here I'm going to drop it to the right hand side of the average and when I should find as I get a new column if I make the column Y you know if you'll be able to see what it's doing which is the sum of the runtime in minutes so I preview that report now for each certificate I have two columns one is the average and the other is the sum now I could carry on adding more and more aggregates to the matrix just in the same way I've shown here by clicking and dragging in the same numerical field multiple times but at some point I want to add some column headings which indicate which statistic I'm looking at so we head back to the design view and I'm going to insert a new row this sits below the current certificate title so I select any cell in a row in them right click on the row selector I can choose to insert a row which goes below the current mom to begin with it looks so it's just inserted another single cell which spans the width of all of the columns in this certificate but if I right click onto that cell I can choose to split them and then I get one heading for each column having a quick title thereof average one here for total and when I preview that report I'll see they get averaged in total repeated for every Civic it if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 114,321

Keywords: reporting services, ssrs, report, matrix, group, aggregate, wise owl

Id: zERexbgCG5A

Length: 14min 2sec (842 seconds)

Published: Mon Sep 17 2012