Reporting Services (SSRS) Part 11 - Basic Parameters

welcome to this wise ell tutorial in this session we're going to teach you how to create and use basic parameters in Microsoft sequel server reporting services what you'll learn in this session is all about how to create simple parameters we'll show you how to add parameters to a report and then how to use those parameters to filter the results of a table we'll show you how you can make a parameter optional and also how you can create default values for a parameter to avoid having to fill them in each time we'll spend a little bit of time talking about text parameters and how you use wildcards to find partial matches briefly show you how to set the order of parameters and a couple of the downsides that and finally how you can do something like conditional formatting using parameters so let's get started one of the most common reasons for using parameters and reporting services is to give your users the opportunity to filter the results of a report so here we've got a simple table of film data and we're going to give our users the opportunity to filter it based on first of all the running time and then the release date and then a couple of other fancy things as well so to do those things we need to head back to the design view and the first thing we'll do is add a parameter that filters our table based on the running time in minutes to do that I first of all need to add a parameter to the report I can make this in one of two ways I can either add a parameter to the data set but it's slightly easier and quicker to add it directly to the to the parameters folder I right-click the parameters folder and choose to add a parameter and I get this dialog box asking me to define its properties I'm going to give the parameter and name I'm going to call it min runtime so the value that will type in will be the minimum running time of films that we want to see the prompt usually need to be something a bit more descriptive because this is what your users will see so I'm going to type in something that says show films that are at least this long with us descriptive enough the next property controls what datatype the parameter should hold it's not going to be text the data in the film runtime in its field is a number and as it's a whole number I'm going to use the integer datatype everything else for the time being I'm going to leave it exactly as is the one other useful option here is to allow null values which means you could leave the parameter empty when you run the report if you don't check this box then you always have to provide a value for the parameter so if I click OK I'll see that I get my min runtime parameter added to the report data window and if I run my reports or preview it I should now see that I get my parameter at the top at this point I can type in a number and click the view report button although unfortunately at this point it won't have any effect on the table because we haven't applied a filter yet so that's the next step now that we've created our parameter next step is to use it in a filter so that my table of results is affected by the value that I type in at the top so to do that I need to go back to the design view again and there are two ways I can apply my filter I could apply a filter directly to the table alternatively I could apply the filter to the data set which populates the table I'm going to apply my filter to the data set itself so to do that I right-click the name of the data set and choose to view its properties on the filters tab I can choose to add a new filter and then I can define the air the properties of that filter by saying in this case why the film run time in minutes is greater than or equal to and then I need to refer to the value stored in my parameter probably the easiest way to do this is to use the expression builder by clicking the FX button on the dialog box that appears there's a category called parameters and if you selected it will give you a list of all of the parameters in this current report all I need to do now is double-click the parameter I want to refer to choose ok and choose ok again and the next time I preview my report if I type in a number in my parameter 180 this home and choose to view the report I'll find that my dataset and table is filtered properly now that we've got a basic parameter working the next step is to add a slightly more fancy version so we can type to further parameters which will allow us to filter our results by release date so back to the design view and back to the report data window I'm going to add a first parameter which I'm going to call start date and for the prompt I'm going to tell my user to choose a start date and then I can change the data type from text to hopefully obviously date and time I'll select ok and I've created my start date parameter while I'm here I'm going to right click the parameters folder again and add the second one which I'll call end date again choose an end date and make the data type date and time now the nice thing about date and time parameters is that if I preview my report I'll see that I automatically am supplied with these calendar type controls although I can type in a date in a standard name of year or month date year format is much much more simple to select from a calendar grid before I do that I want to make sure that my air my parameters are actually linked to it filter in the table or in the data set so I need to go back to the design view again and in the report data window I'm going to add my filter to the data set again so I'm going to come right click and choose data set properties and on the filters tab I can add a new filter this time I'm interested in something where the film release date is equal to but is between two dates and when I choose the between option I get two separate dialog boxes or sorry do two separate text boxes turn to the values so in the first value I'm going to use the expression builder again and in the parameters list I'm going to select start date by double clicking choose okay and then the same process for the second text box this time it will be end date if I choose okay and choose okay again I can now preview my report I have to enter values for all three parameters and then I can choose dates for the start date and end date it's quite laborious having to scroll forwards and backwards one month at a time so a neat little feature of these calendar controls in this version reporting services is you can click on the header at the top of the calendar to zoom out to a higher level so if I click once I can scroll by year if I click again I can scroll by decade and then when I want to zoom in to select a specific date I can click first of all on a year and then on a month and then on on a specific day so I'm going to choose to share films that last at least 180 minutes released since the first of January 2000 and I'm going to just select actually today's date as the end date if I bring my report that's the list of results that I'll see as you start to add more parameters to a report it can be quite annoying always having to fill in a value that every single parameter so there are two useful ways you can modify this the first way would be to allow a user to leave out one of the parameters perhaps and we'll show you that using the using the run time in minutes the second way would be to set default values for parameters so making sure that they already are filled in with a with a set value I'm going to start by showing you how to allow null values for the run time in minute so to do that we need to go back to the design view and in the report data window we need to right-click on the parameter we want to modify we can then choose to view its properties and then find the checkbox that says allow null value and ticket if I choose okay and then preview my report again I should find that this time my parameter by default has the null box next to it which is checked now if I simply I'm going to quickly type in a start date and then quickly select an end date for my my parameters if I view my report what I should see is a list of films where the start date and end dates have been used to filter the results but ignoring the running time filter entirely that's one quick way of avoiding having to fill in a parameter for every sorry fill in a value for every parameter now another way to avoid having to fill in a value for each parameter every time you run a report is to set a default value for the parameters we're going to do that for both of our date parameters so again we go back to the design view and in the report data window right click on the parameter that we want to modify I'm going to modify my start date parameter and choose to modify its properties on the dialog box that appears we need to select the default values tab and I have three choices I'm going to choose the one that allows me to specify a value for the parameter and then I can click the Add button and then specify the value that I'm interested in I can quickly type in a symbol bate here and I type in a fairly early days I think the earliest film in my in my database is in 1933 so I'm going to enter a fairly early date which should um should always allow me to handle deal this film is unlikely I'll add any films that are earlier than that date I can choose okay and then I'm going to I'm going to also set the default leave my entered a parameter right click and choose parameter properties and head to the default values tab again and specify a value I'm going to add one but this time I'm not going to type in a fixed value because I might be adding further films in the future I want to make sure that my my default value always remains current so it always sets itself to whatever today's date is the best way to do that is using the expression builder I click the FX button I can delete whatever is in the dialog box already and then use the common function section and the date and time category which will be fairly familiar with if you've watched our videos on creating calculated fields the function that I want to use is called today so if I double click on that it types in the syntax or most of the syntax of the function for me the one thing that I do have to do is close an actress out of round brackets at the end if I then select ok and ok again I know that that parameter will always calculate what today's date is so if I finally previous report one more time I should find that my start date and end date parameters are filled in automatically and I'm allowed to leave my running time value as null so viewing my report was immediately show me some values in the results so far we've seen how to use numbers and dates in parameters the next step is to show you how to work with text and in particular I want to focus on how you use wildcards to find partial matches for text so I'm going to head back to the design view and in the report data window I'm going to add a new parameter which will allow us to enter part of a film's name so the name of the parameter will be called film name and the prompt will be something like type in a part of a film name the data type is going to be text and I'm also going to allow a blank value you he's different to allowing null values you only get this option when you have a text parameter if I allow blank value it means I might allowed to pass in a zero length string or an empty string you're going to be quite useful for what we're going to do shortly at this point if I click OK I can then add a filter to my to my data set to begin with I'm going to filter it so that on the filters tab if I add a new filter so that my film name is equal to the value of my parameter so I find my parameters list in the expression builder double click the film name parameter choose OK and choose OK again I can go back now and preview my report I have my extra parameter if I type in an exact film name so let's say let's pick a good one there we go die hard and if I hit the V report button as long as all of the other parameters are met Ivan I've Leslie add the runtime empty and it falls between my start date and end date as long as the title matches exactly what I've put him I'll get the film in the results what I really want to do though is to be able to enter part of a film's name and all let's make that work I've got to tweak the filter on the data set so to modify the filter to take into account partial text matches once again we need to go back to the design view and in the report data window I'm going to right click onto the data set and choose to view its properties on the filters tab there are two things I need to change about my my filter using the film name first of all I'm not looking for things that are exactly equal to what I type into the parameter I'm going to look for something that is like so it's like a partial match on top of that I need to make sure that whatever I type into the parameter is is contained within a pair of wildcard characters so I'm going to use the FX button to launch the expression builder and then either side of the parameter so emitting equal sign and iya and the parameter name I'm going to enter an asterisk inside a set of double quotes space it out with a space and then use an ampersand to concatenate that to the start of the parameter so that the asterisk in reporting services represents the wild-card character because that's being built into a string of text of literal text I've got to enclose it within a pair of double quotes and to join it to the parameter value oops to join it to the parameter value I have to use an ampersand I'm also going to put one at the end at the parameter so put another ampersand and inside another seven double quotes put another asterisk and if I finally click OK and ok again the next time I preview my report I should now be able to type in any part of a word if I just type in the word die for instance and view my report what I should find is there any film containing the word die anywhere within it will appear in my list one final useful thing about the way you set about a text parameter is that because we've allowed a user to leave this box empty not not to allow null but to Pat in a zero length string and because we've applied wild cards to our criteria if I take away all the text from this box and choose to view the report then I will find basically all of the films are returned so I'll leave the Box blank I'll return any film name containing any string of text so that's how to use wildcards with text parameters and reporting services when you've created a large number of parameters it can be quite useful to be able to change their order it's quite a simple process but it's worthwhile quickly mentioning how it's done if you go back to the design view of a report and in the report data window select the parameter that you want to move so I'd like to move my film name to be the first parameter in the list if I select it I can then use these arrow symbols at the top of the report data window to position their parameter where I want if I preview the report I should find that my film name parameter is now the first one that's nice and simple one slightly unfortunate feature of what I've done with this particular set of parameters however is that although previously we'd found that our end date parameter was being calculated properly remember we have the expression equals today in this parameter it won't calculate properly now until I've modified the film name parameter because the film name parameter can't contain null so I'm not allowed to miss it out the expression for end days won't calculate until I've modified something in this parameter if I type in for instance a single letter and then click onto a different parameter I'll find that the end date now calculated properly and I can then take the letter away in order to allow me to find films with any word in their title so something do you need to be slightly careful of you need to be need to watch out for the order of things it will probably be better in this example to have the date parameters at the top and then have the film name appearing after the calculated ones but that's just something to watch out for so far in this tutorial all of the parameters that we've created have been used to filter the results in the report but for the final example we're going to show you that you can use parameters to do conditional formatting as well but it's formatting this control by the user so we'll create a new parameter which allows a user to type in a number and then the number that is typed in will use to highlight all the films whose Oscar wins matches that number so if I typed in the number 11 I'd see there where the film Titanic is highlighted in a different color so to do that once again back to the design view and the first thing to do is to create the parameter in report beta window I'm going to right click parameters and choose to add one and I'll call the parameter Oscar wins and for the prompt I'll type in something like highlight films with this many Oscars the datatype of this parameter will be integer I'm not going to allow the parameter to be null but I'm going to set a default value for it I set a default value and specify a value of zero say what that will mean is that by default all of my films will be all of the films with zero Oscar wins will be formatted immediately as soon as the report appears so I choose okay that's the basic parameter created all I need to do now is create the format that will read in the value of the film Oscar wins parameter I'd like to highlight the entire row of data for each film so I'm going to select the entire row for using the yet using the selector button at the left-hand side of my table and then I'm going to use the properties window to find the filled color or background color property which is somewhere around here and there we go background color sets no color at the moment if I click on the drop down arrow I can choose to use an expression to calculate the color in the expression builder dialog box I'm going to remove the the calculation that's there and instead I'm going to type in equals I I F and open a set of round brackets so the the if function which may be familiar with from a previous tutorial my condition this time that the expression is asking me for here I'm going to ask if the film Oscar wins field so that's the field from the data set is equal to the value in my Oscar wins parameter so that's either true or false if I type in a comma and if that value is true I want to apply a specific color to the row of data I'll cover it in this this lovely lime-green color if I then type in another comma I need to choose a color that films without that number of Oscar wins are colored in I'm going to choose basically white so that it doesn't look as though those are colors in at all if I close around brackets and then choose okay and finally preview my report a game because of the order of my parameters I'll need to enter anything into the film name box in order to get the end day to update and then remove that if I finally preview our view the report I should see that all the films with that number of Oscar wins are highlighted in that lovely green color if you've enjoyed this training video you can find many more online training resources at ww-why 0 UK

Channel: WiseOwlTutorials

Views: 163,500

Keywords: ssrs, reporting services, parameters, filter, conditional formatting, wise owl

Id: c9bOO109bY8

Length: 23min 12sec (1392 seconds)

Published: Mon Sep 10 2012