Reporting Services (SSRS) Part 23 - Report Variables and Group Variables

welcome to this YSL tutorial in this video we're going to teach you how to use report variables in Microsoft sequel server reporting services we'll start the video with a quick look at what report variables are and why you might want to use them in the first place well then move on to show you how you can create a report variable and how to use that variable in an expression in a text box within your report once you've covered report variables we'll then move on and talk about how to use group variables again we'll explain what they are and why you might want to use one then show you how to create them and how to use group variables in expressions within your report so let's get started a report variable is simply an expression which is calculated once each time you report runs and they can be particularly handy when you want to avoid the on demand processing feature of reporting services so to show you what I mean by that I'm gonna insert a new column into this basic table and in the text box in that column I'm gonna insert an expression which calculates the time at which the textbox was displayed on the screen I'm gonna use the now function to do that this calculates the time down to the second if I hit OK and then preview the report I should find that I get the time at which the textbox was displayed shown in the text box itself now that's all well and good but if I switch to the next page what I'm gonna find is that the time has changed to the new time that the page was loaded and this is all due to the on-demand processing feature of reporting services values expressions are only calculated when they are actually needed even worse than that as well as showing a different time on each page if I actually switched back to the first page of the reports it's going to show me a different time to the one that originally showed me and you can imagine if you are creating some particularly time dependent calculations that could be a big issue so what we're going to do in this little video is show you how to use report variables to avoid that sort of an issue to create a report variable you need to be in the design view of the report and you then need to display the report properties one easy way to do that is to right-click in the background of the reports and use the view report properties on the dialog box which appears there's a tab called variables and you need to head to that and then you can simply click the Add button to add in your a new variable think of a sensible descriptive unique name voice I wished on my report variables if letters are V and then some kind of descriptive word which tells you what's contained in the in the variable I'm gonna combine timestamp what we then need to do is provide a value for that variable and you could actually just type in a value a number or a string of text here what I'm gonna do is I'm going to calculate this using the now function again so if you hit the FX button across to the right hand side we can insert the same function as earlier so equals now open and close brackets and I'm like okay if you think like okay again we've created a new variable which is available to this report so I'm gonna do the same thing as I did last time I'm going to insert a new column into the table and I'm gonna add an expression to this new text box but instead of just using the now function itself what I'm gonna do is use the expression builder to select my report variable so in the variables category I'll find any report variables I've created and if I simply double click it inserts a call to that variable as an aggression if I hit OK and then preview the report again what I should find this time is that I get a new timestamp in the in the text boxes and if I switch to the next page I should find that that timestamp is exactly the same no matter how long it takes me to get there so there you go there's one problem solved with the use of report variables it can create fixed time stamps which avoid problems with sensitive time dependent calculations another slightly less obvious thing that's a report variable can help with is the processing time of your report so to demonstrate that I'm going to create a new calculation in this table which is going to calculate the percentage contribution of each film's Oscar wins to the total of all of the Oscar wins so to do that I'm gonna add a new expression to this text box and it's fairly straightforward I'm simply going to take the film Oscar wins value and divide that by the sum of film Oscar wins over the entire data set which is called DTS films so there's that aggression created I hit OK I'm gonna do a quick little bit of formatting to the text box itself to make sure that we've got some some nice formatting going on so that's a apply a percentage format with a couple of decimal places and then if I preview the report what I should find is that each film shows me what percentage contribution its Oscar wins make to the total the small downside with that although we get the right result the downside is that each text box has to calculate the sum of film Oscar wins in order to calculate that value and this this table contains I think about 260 Records which isn't a big hit but in the real world where you have thousands upon thousands of Records that can have a big impact on the processing the time of your report so the next thing to do is using a report variable which will avoid that problem so again to create a report variable we need to head back to the design view of the report and like clicking the background and choose to view report properties on the variables tab I can then click the Add button to create a new variable this one's gonna be called RV total losses and then I can hit the FX button to launch the expression builder in a data sets category I'll find a link to the some film Oscar wins which I can simply double click on to insert all I need to do then is click OK click OK again and my report variable is then available what I now need to do is modify this expression to use the report variable rather than the current expression so I can like click and choose expression and I can take away the parts that I've just generated in my report variable so I sum the film Oscar wins value for the data set I can take that part away and replace it with a call to my report variable called RV total Oscar's quick double-click it's ok and then preview the report again now I should see and I am gonna see in fact exactly the same answer and you probably haven't noticed any difference there in the time it took to make that report appear but this report is now doing much less processing than the previous one because the summer film Oscar wins is only calculated once when the report is loaded now that we've seen a couple of examples of report variables we'll finish the video with a quick look at how group variables work so to get started with this I need to create a grouping level within my report I'm gonna do this using the groups panel down at the bottom of the screen and I'm gonna add a group to the edge of the details row of this table if I right click on the details choose to add a group I'm gonna add a parent group I'm gonna group by the country name that a film was made him and also going to include a header and a footer at this point and then hit OK and there's a basic groups table I'm not going to spend any time tidying this serve although in the real world I ordinarily would but what this gives us is a very basic grouping level so that all the films are grouped by the country in which they were made what I'd like to do now is as well as show each films contribution to the total Oscar wins I would like to show each individual films contribution to the group total of Oscar wins so in order to do that I need to calculate the sum of Oscar wins for each country group and again just like we saw last time if we were doing that on a single row in a basic expression that will be adding the necessary processing it would be calculating that value for every single row so instead what we'll do is we'll add a group variable which will calculate their total Oscar wins for each country group so again to create a group variable we need to head to the design view the report but this time rather than viewing report properties what we're going to do is view the group properties to find the group in the group's panel right-click and choose view group properties again on the dialog box you should find that there's a variables tab and every head to it it works in pretty much exactly the same way as the report variables does so we're gonna add a new name for our group variable I always someone group variables with letters G V and this one's also going to contain the total Oscars so G V total Oscars the value again I'm going to hit the FX button to launch the expression builder and all I'm gonna do is enter the sum function and refer to the film Oscar wins field and this will automatically calculate the sum for the group so the sum for each country if I click OK and click OK again my group variable is now available to be used in an expression so to have my expression which will use my group variable I'm gonna insert a new column into the table and I'm gonna right click in the blank text box and choose expression the expression is again gonna be very similar to what we did earlier we're going to take the film Oscar wins for that row and divide it by the total film Oscar wins for that group and that value is stored in a variable and this way we run into a slight issue with the expression builder if I had to the variables tab you can see the problem it only lists out report variables and not group variables so you've got two choices basically here you can either type at the full syntax that will refer to your group variable or you can cheat if you double click on any existing report variable that you have you can then simply take the actual name of the variable itself and replace that with the reference to your group variable and I've been quite sneaky I've kind of made sure that my group variable only has a single letter difference to the report variable that I created earlier on so what they can do is backspace the are typing the G and that will refer to my group variable total Oscar's slightly disconcertingly the whole thing is underlined in red indicating that that doesn't exist or it's not gonna work but trust me it does if you click OK and then let me just make sure that the formatting of this text box looks sensible so out of the numbers of the dialog box will add a percentage format with two decimal places if we preview the report at that point we should find that our expression works perfectly so anywhere where there is a film with Oscar wins it calculates that film that film's contribution to the total of the group so for most of these actually there we go New Zealand that's a little bit better so we can see that different films have different numbers of Oscar wins I mean at different contributions to the total you've probably also noticed some more annoyingly that certain countries purely by chance don't have any Oscar wins at all and for those we get the na n era rather than just the value of zero so just as a quick little extra well tidy up this issue so that if a country does not have any Oscar wins at all instead of the error message we see a zero in its place so to mask our basic divide by zero error what we can do is head back to the design view and we can modify the expression in the text box when a simple way to do this is using an if statement so instead of just always performing this calculation what I can do is check if using the air the I if function and what I'm going to do is check if the value of the variable the group variable total Oscar's equals zero then what I would like to do is display a 0 as the result otherwise I would like to perform a calculation that I've previously created so if I then click OK and preview the report one more time I should find that everything looks a lot more sensible so there you go there's a an overview of how you use report variables and group variables to create time dependent calculations and the safe processing time when your reports run if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 70,030

Keywords: SQL Server Reporting Services, report variables, group variables, speed, expressions, wise owl

Id: k_WQ4a7J7VY

Length: 12min 59sec (779 seconds)

Published: Tue Aug 06 2013