Reporting Services (SSRS) Part 21 - Pivot Table (Dynamic Matrix)

welcome to this wise hour tutorial in this session we're going to teach you how to create dynamic matrix or a pivot table in sequel server reporting services what you're covering this session and all the steps needed to create a dynamic matrix we'll start with an explanation of what a dynamic matrix is and why you might want to create one we'll talk you through creating the data set required how to set up the parameters which gives your user the choice for which fields they'd like to display and then how you can add the matrix to the report won't it cover the basics we move on to all the areas while you'll need to use expressions so we'll show you how to create the group fields and make those dynamic how to choose an expression for the data fields adding row and column labels using expressions making the the sorting of your matrix dynamic as well and finally as a sort of final flourish we'll show you how you can make the the data field function dynamic as well so lots of you let's get started in a previous video we've shown you how to create a basic matrix in reporting services and it's a quick reminder of what one of those looks like you can choose to group by a row field here is the studio name and also by a column field here is the film certificate and then in the central area you can choose to show some form of aggregate statistics so here is the average of the film length the only downside to this type of matrix is it is quite static if I want to change the group fields or the statistic I have to go back and change the design of my report we're going to solve that problem by showing you in this video how to create a dynamic matrix an idea here hopefully as you can see is that we've used parameters to allow the user to set up each matrix exactly how they want it so if I decide to choose to group by a different row field for example and she's a different data field and even choose a completely different function to apply when I view the report again my matrix will update to show me all of my new choices so that's what we're going to create today so to start building our dynamic matrix we've created a blank report and created a simple data source which connects to our movies database the next step is to create a data set which includes all of the possible information you might want to see in the resulting matrix so I'm going to right click the data sets folder and you do add a new one and I'm going to change the name to called ETS film data choose to use an embedded data set in this example connecting my data source movies and then because I'm including information from lots of different tables by far and away the easiest way to do this is to use the query designer next I need to choose which tables I'd like to include in the data set so to do that I can click the add table button and then select the table someone to use I'm going to include the certificate table country director and studio now these tables that I've included here will all form the choices for my row and column groups in the matrix the final table I'm going to include is the film table and that's going to include all of the fields that I'll use in the data area so for the averages and sums etc so I can close down the add table window now and all I need to do you might can manage it with this slightly messy view used to select the various fields so I'd like to be able to see this certificate the country name the director name you scroll down a little also the studio name now those will all be the as I said the row and column group choices for the statistical information for the data areas I'm going to include some fields from my film table only to film one time minutes maybe the budget and box-office takings and maybe the Oscar nominations and Oscar wins now that I've done all that I can click OK click OK again and my dataset is now created the next stage is to create some parameters which the user can use to choose which route column and data fields they want to see so let's start by creating a row field parameter and right click the parameters folder and do to add a new parameter I'll change the name of this one to be called PRM row field being prompt is what the user will see swell I'll type in something slightly more descriptive I guess with a space field the data type of this parameter will be text and what if I do to do is give the user the choice of the certificate country director or studio fields so to do that I'm going to go to the available values tab and specify some values I'll add the first one this there's a label under value for each parameter so for the first one the value site should be the same certificate and a certificate for the label and the value if I click Add again and the next value of ID C is the country now for the label I'm going to type in just the word country and that's what the user will see in the drop-down list but for the value I have to make sure that this is set to the same as the name of the field this is very important books of the way our dynamic matrix will work it relies on the names of the fields being the ones that are selected in the parameters let's just quickly add two more so we can go for the same approach for the director that will be what the user will see but the field name is called director name which is there one final one and that will be studio and studio name and there we go those are the values I'd like to select for that parameter if I click OK and then I'll quickly create the the column feels and parameter as well because it will have exactly the same values so let's create this one PRM column field and then the prompt will be column field and then there will be text again and then the available values I'll specify my values and quickly type in certificate and the field name is also called certificate and then I can add another value this will be the country but the field name is called country name I can add a director and this will be directing name and then finally studio and studio name and those are my row and column view groups created use a similar approach to give the yeast the choice for which data fields they'd like to display so I can right clear the parameters folder again and used to add a parameter this one we called PRM and data field and the prompt will be called data field it'll be text again and heading to the available values tab to give the user the choice for which values they can select so it's a similar approach the label would be something descriptive that the user will see and then the value will be the exact name of the field that we'd like to use in the matrix so that's filmer enzyme minutes for this example I'll add another couple as well as have a budget and it'll be film budget dollars so at this point I wish I'd chosen a slightly shorter field names but never mind we now box-office and that would be a film box office dollars maybe I'll just add one more I'll I'll add the film Oscar wins so Oscar wins and that will be film Oscar wins and then we carry if I click OK and then preview my report just to show that the three drop-down lists should be working I can choose a field for the row I'm going to choose studio I can choose a field for the column and I can choose a field for the data area nothing will happen if I click view reports because my my report doesn't actually have any data in it yet but we've got the choices set up now we're ready for the difficult bit so the next stage is to create a matrix in the report and make sure that it's row and column and data fields are set based on the value selected in these parameters so let's go back to the design view and I can simply double-click on the matrix tool in the toolbox to add once my report isn't simple I'm going to focus on now are setting the row and column groups properly so I'm going to do that in the group's panel down at the bottom of the screen let's start with the rogue group expression I'm gonna right click on the row group in the group's panel and I'm going to choose to view the group properties on the dialog box that appears I need to make sure I'm on the general tab and looking in the group expressions section over here my matrix is in groups on anything yet so I'm going to use the expression builder to choose which field it is grouped by now this whole example relies on knowing that there is more than one way to refer to a field in reporting services usually when you refer to fields in the expression builder you use this syntax fields and I might say director name dots value and that would be a standard way to refer to a particular field it's underlined in in red because my matrix isn't actually connected to a data set yet now that syntax is no good first what we need to do is use a convert this this syntax into one that cannot accept a piece of text from our parameter so another way to refer to that exact same field will be to say fields open brackets and then inside a set of double quotes the name of the field we want to refer to followed by dot value now that we know we can do that this literal string of text here can be provided from anywhere in our report we could refer to another field we could refer to another value somewhere else what we're going to do is refer to the value of one of our parameters so we're going to replace this literal string here I'm going to take that away and inside the round brackets I'm going to refer to the value of my row field parameter I can leave that by double clicking the the row field parameter here so it will type in parameters exclamation PRM filled lots about you so if I select the direct field it will feed indirect and now if I slide certificate it was repeating the word certificate etc I'm gonna click OK here then I'm going to use exactly the same trick to set the column group as well so I can right click on the column group in the group's panel choose group properties and I'm gonna click on the expression builder for the group on expression I'll start by typing in the word fields open instead of round brackets and at this point I'll go to the parameters category and double click the column field parameter I can then close the round brackets type in a full stop and refer to the value property click OK and click OK again and we've set up the groups for our rows and columns so the final step in order to see at least some information come out in the matrix is to set the expression before the data field so we can do that by right-clicking on the textbox in the matrix itself and choosing to view the expression builder again now in this example we're going to use exactly the same technique as we did with our without row and column groups so we're going to refer to the fields collection and in a set of round brackets we're going to refer to the data field parameter so the parameters category double click the data field parameter those around brackets and then refer to the dots value of the field now that would return the value of the data field that we've chosen what we want to do is pass that into some sort of aggregate function we might want to use sum or average min Max etc just as a quick simple example we're gonna use the AVG the average function and that will calculate the average of whichever field we chosen in the data parameter I'm going to click OK at this point and it's gonna look quite basic and a bit rough and ready but it's enough now to see that the dynamic fields are working if I preview the report what we can do is select studio field perhaps for the Rose certificate perhaps for the columns and then maybe I'll find the average of the running time in minutes when I be the report as I say it's gonna look a little bit rough and muddy but it's enough to see that we are definitely getting values out in the results if I change the data field that I'm displaying maybe I'll view the Oscar wins will see a completely different set of numbers and likewise if we choose different fields for the rows and columns we should see that there is also change the number of rows and columns that we see obviously there's a lot of work to do now with tidying things up so let's get started by adding some appropriate labels so we can actually see what we're looking at adding labels so the matrix uses again exactly the same technique as we've used in all of the other parts we're going to set an expression which refers to the field referred to by each of our parameters so back to the design view I'm going to set the expression for the wrote field label first of all so I'm going to right click the rows group and I'm going to set an expression and I'm gonna make this equal to fields open brackets parameters row field close brackets dot value if I click OK and then I'll do the same for the columns right click columns choose expression equals fields open brackets parameters double click column field close brackets and dr. value and then click OK again I preview the report and a couple of values let's go for studio and certificate and a data field and we should in fact now that we actually see labels come out sensibly it's unfortunate that or not they're not sorted in any particular order so what we can do is add a sorting level to our row groups and perhaps our column groups as well using the gain exactly the same technique so to add a dynamic sorting level to my matrix I need to go again back to the design view and the sorting for a matrix is dealt with in the group's panel again so I think with the wrote groups first of all if I right click row group and choose to view its properties I can head to the sorting tab and I can see at the moment there are no sorting levels added I'm going to add a new sorting level and once again use the expression builder to refer to my parameter so I'm going to type in fields open brackets go to the parameters category and I want to sort by the row field closer brackets and then say dots and value again you might choose okay and choose okay pretty for the report one more time I'll choose let's go for studio first of all and any value will do and I should be able to see this time that the column the row groups are sorted alphabetically by studio if I change that last to director and view reports this time they should be grouped alphabetically by director so dynamic labels and dynamic sorting so we've got our matrix behaving fairly dynamically this point we choose a different row group different column group in a different field to find the average of the one thing that we're lacking though is the choice for which function we apply to the data field we've selected in a pop-up pivot table you can choose sum average min max count etc and I'd like to give our users that exact same choice so to do that first of all we need to create a new parameter which let's say user choose which function to apply to do that I'm going to head back to the design view and then I'm going to right click the parameters folder and used to add a new parameter I'll call this one PRM function and then I'll change the prompts to just the word function the data type will be 'text again and then the available values is where I can set which functions the user can select from so I'm gonna specify some values I'm gonna add now again just as we did with our other parameters we can choose to display a more descriptive label so I'm going to choose to show the label of total then the value I'll use the word some it's not actually that important in this example to make sure that these are exact function names we're going to write a a switch function later which tests the value selected so this does not have to be the name of an existing function just as long as you can remember what it is so I'm gonna add another function I'm gonna change that's the word average I'm gonna change the value to the three letters AVG and then add another one and I can get four highest and I can say that's max and then one more that'll be lowest and I'll set that to be the word min so that's my my parameter created I click okay what I now need to do is change the expression which calculates the aggregate for the entire matrix to do that I'm going to right click on the expression for the data field and choose to view the expression and at the moment it's set to be the average of whatever fields I've chosen for the data field now what I'm going to do is use the switch function to make this conditional so if you can't remember the syntax of the switch function you can always find it in a common functions category and in the program flow section and if I look at the switch function there the examples show me that I can test a single condition and then provide an answer if that condition is met and then I can set another condition and provide a different answer if that condition is met so what I'm going to do is I'm going to write the word switch into the top of my my expression builder and then I'm going to add an open round bracket below that and then right down the bottom I'm gonna add another close round bracket and in between these brackets in between these two lines I'm gonna add in all the various conditions and functions that I'd like to perform as I've already got the function which calculates the average of my selected data fields I steel with that one first so I'm going to indent this a couple of times and what I need to do is check if the value of my function parameter so I can answer by double-clicking is equal to AVG if I type in a comma after that that will separate the two arguments and there we go there's the basic pair of arguments for a switch function if this condition is true so if I've chosen average for my function parameter then I'll apply the average function the AVG function to the data field that I've selected you might data field parameter the nice thing is that every other pair of arguments then for my switch function behaves in a very very similar way I can copy this entire line hit enter type in a comma at the start of the next one and then paste that in and I can simply modify the two little bits that I need to so I can check if I've chosen the sum function I want to apply the sum function answer again comma and pasted him max oops max and then max again and I'm sure you get the idea by now and one more time just to deal with the min and there we go so it handles each of the four choices I've provided to my user and applies the appropriate function in each case if I click OK and then preview my report one more time let's go for the studio these certificates the running time and rather than choosing the average I'm gonna go for the total this time when I view the reports there we go I choose a different function I should get a completely different set of results and there we go so there we go we've made every element of this matrix completely dynamic given user complete flexibility in the way they they view our data the final thing I'm going to do is a bit of formatting just to make the whole report a lot more presentable so I'm going to do all this back in the design view I'm first of all going to modify the number formats form the results of my expression for the data field I'm going to right click and use text box properties and add to the number tab and I'm going to set a simple number format which uses a couple of decimal places and a 1000 separator so a common which separates each unit of thousands just as with all of the other elements of our matrix we could even make this part the number format dynamic as well if I click OK just to show you how you could do that in the properties window you can set you can find if you can find it the the number format there we go so the number section here's the format you're allowed to use an expression to calculate the number format and you could for example show a currency formats have you chosen to view the box office takings from the budget and so what I'm sure you get the idea by now but it's just another idea for you I'm not going to do that in this example I'm gonna just add a few more bits of basic formatting I'm gonna white in this column I'd like to have a sort of report header which sits at the very top of my matrix and I want to make that dynamic as well so what I'm gonna do is right click at the left hand side on the top row and choose to insert a new row which is outside of the creep and above in the new text box which appears I'm gonna right click and choose to view an expression oh sorry to choose a built-in expression I'm gonna make this up or build a per sentence concatenate a sentence based on the labels of my parameters so I'd like it show if I chosen the average of the film's running time I'd like my report header to be average running time so that means I need to group together or join together the values of two parameters so first of all the function parameter now I don't want to choose the value of this remember the value is some AVG max what I really want to see is the label of my parameters so that's total average highest lowest etc I want to join that together with a space so I can use an ampersand and then inside a set of double quotes type in a space and then another ampersand and then I can choose the label or the value of my data field parameter again rather than the value what I really want to do is show the label which is a little bit more descriptive and little bit clearer to read click OK there and then finally a bit of basic coloring in which I can never resist doing so this is a bit boring and I can't resist and nearly done one more to go and there we are so if I preview the report one more time and choose a row field a column field and data fields and a function finally we ought to see some nice sensible attractive almost looking results if you've enjoyed this training video you can find many more online training resources at ww-why lgo UK

Channel: WiseOwlTutorials

Views: 122,905

Keywords: reporting services, ssrs, matrix, dynamic, pivot table, wise owl

Id: vgtVJf21EXU

Length: 25min 8sec (1508 seconds)

Published: Tue Dec 04 2012