Reporting Services (SSRS) Part 13 - Null Values in Parameters

welcome to this Wisel tutorial in this video we're going to teach you how to handle null values in parameters in sequel server reporting services we'll begin the video with a quick recap of how basic parameters work how you can allow null values for those parameters and then how to write filters to handle those nulls properly once we finish with the basic parameters we'll move on and talk to you about the slightly more complex drop-down list parameters we'll talk to you about how to create a data set to populate the drop-down list how you can add a null entry to that list of values and finally how you can mask the null entry to make it easier for your users to select the relevant values so let's get started we've dealt with parameters in a couple of previous videos but let's start with a quick recap of how basic parameters work we have a simple report here already which shows us a list of film names release dates and run time in minutes and what I'd like to do is create a parameter which allows us to type in the name of a film and for our report to then show us only films which match the name we typed in so back in design view I can start by adding a parameter to the report right click the parameters folder and use the add one we can give the parameter a sensible name I'm going to call mine PRM film name we can give the parameter a prompt which tells the user what to do type in a film name and we can choose what data type the parameter is so it's going to be text because the film name field contains text data if I select ok that's my parameter created the next job is to apply a filter either to the data set or to the table itself which will limit the list of results based on the value typed into the parameter I'm going to add my filter to my data set so I'm going to right click the data set name and use data set properties and on the filters tab of the dialog box I'm going to choose to add a filter choose the field I'm interested in which is film name I'm going to check where that is equal to the value of my parameter two things I could do here I could either type in the parameter name directly contained in a set of square brackets and with the @ symbol in front of it so at PRM film name like so alternatively if I didn't want to have to type it in I could also use the expression builder by clicking the FX button looking for parameters category in the bottom left-hand corner and then double clicking on the one that I want click OK it types in the parameter name for me click OK again and that's the entire thing created if I preview the report now what I should be able to do is type in the name of any film let's go with one that I know will return several results king-kong either hit enter or click the view report button and then return results which match the text that I've typed in so the next question and kind of what this video is all about is what if I wanted to leave my parameter empty so if I take away my film name and try to view my report again I'll be told that I can't do that because I'm allowed to leave a program as a blank so my first job in order to make this work is to well click OK of course head back to the design view and I want to set my parameter to allow a null value so I can do that by right clicking the parameter name and choosing to view its properties and on the dialog box which appears I'm interested in the section just below where we set the data type there are two check boxes when were working with a text parameter I can either choose to allow a blank value which is a zero length string I'm not sure you can make this out but this is a essentially two double quotes in a row so that allows me to type in no value it passes a zero like string to the parameter the one that I'm really interested in however is the one called allow null value this is available for texts for numbers for dates etc so you use it with any data type if I choose to allow a null value for my parameter and then click OK when I preview my report again I'll find that by default my parameter doesn't have a value in it at all if I wanted to provide one I need to uncheck the null box and let's have another film let's go for a casino royale' and this will again return the air the matching values the problem that I have now however is if I check the null box so don't provide a value from this parameter when I try to view my report rather than show me all of the films as I really want to I simply get nothing so the next job is to work out how to test for if my parameter is null and then return all of the films if that's the case so to handle the null value of my parameter I need to modify the filter that I've added to the data set so I can do that by going back to the design view like clicking the dataset name and choosing to view the data set properties if I go back to the filters page of the dialog box that will show me the filter that I've added earlier on which is searching for where the film name is exactly equal to the value of my parameter and because I haven't got any films whose name is null that's why I don't return any results what I'd ideally like to do here is add another expression which checks if the parameter value is null and if so combine that with the current filter with an or expression but unfortunately I can't do that here I can't add an or expression on the filters list so what I have to do here is delete the existing filter by selecting it and clicking delete and then I need to head back to the query page and I have to build in my my criteria my all criteria in the query box itself Twala criteria to my query I need to add a where clause in SQL the where clause always appears immediately after the from clause so select these fields from this table where these conditions are true I'm going to hit enter here and head down to the next line and then tab in one space just to layout my code a little bit more neatly I could technically write this entire statement as a single long line but I find it easier if I have it separated on two separate lines so my first criteria I'm going to replicate what's our filter did I'm going to check where film name equals at PRM film name so that simply replicates what our filter previously did what I also need to do then is check whether as well as the film name being yours of the parameter name whether the parameter itself is null so or at PRM film name is null the capital letters aren't important here by the way the main SQL keywords are by convention written in all caps so select from where is null or etc but this still will work perfectly happily if you write these in lowercase as well if I click OK at this point and then preview more app or one more time this time I should see immediately even when my prada meter is null to begin with i'm returning my entire list of films if i uncheck the null box and type in a film name let's go for King Kong again female reports my filter works if I check the null box and view my reports I get all of my films again now that we've seen how to handle a single null value parameter it's reasonably easy to work out how to add further parameters and extend our where Clause the handle those as well so let's add a parameter which allows you to specify a minimum running time in minutes we'll head back to the design view and at the parameter first of all I'm going to call my promise at PRM min length and it's going to have a prompt of type in the minimum run time I'm in run time for short the data type of the parameter is going to be an integer so it's going to be a whole number and we're going to choose to allow a null value click OK and that's the parameter created now we have to modify the where Clause of our data set to handle the new parameter that we've added so we can do that by right clicking the date set and choosing to view its properties and in the query box let me just increase the width of the dialog box a little bit to make sure we've got the space of type is in we're going to modify the where clause that we've currently got I'm going to start by adding a set of round brackets or parentheses around the first pair of criteria where the film name is equal to my parameter or the parameter is null after the close parentheses I'm going type in the word and and then on the next line begin a new pair of criteria I'm going to wrap these in a set of round brackets as well and they're going to be very very similar in structure to what we typed in here already so here I'm going to check by the film run time in minutes is greater than or equal to at PRM fitter so mineral and beg pardon not not film length min length if you ever get stuck and you can't remember your parameter names you can always have a quick check over here at the report data panel that should tell you what to type in so while my phone one time minutes is greater than or equal to the value I typed in or where my parameter at PRM in length min length is no if I close the round brackets at that point that's my new pair of criteria created I can click ok now preview my report and then I guess I have to test every combination of of nulls and and values don't I so let's start by adding a film named King Kong and view my report there we go let's say that the minimum running time for my films of King Kong need to be one hundred and thirty minutes seven hundred and thirty and hit enter again and that reduces the number if I make the King Kong if I make the film name parameter null and view my report again I should now be seeing only films whose minimum running time is one hundred and thirty minutes and then if I check the the null box for that parameter as well and either report one last time I'm going to return all films regardless of what their running time is and what their name is so that's the basic process of adding parameters allowing null values for them and then checking for the null values in the criteria of your query and you can continue this process to add as many extra parameters as you like and following the same process we could add a start they parameter which allows you to choose a start date and the data type for this one will be date and time and we're going to allow a null value once you've added the parameter we need to add another criteria to our where clause we need to modify the data set properties and again I need to increase the other width of the dialog box add another and keyword to the end of the previous criteria and on the next line open another set of round brackets and type in your same standard criteria so I'm going to check whether film release date is greater than or equal to PR m at PRM start date or at PRM start date is null and then close the round brackets so hopefully you can see the pattern at this stage you simply add on more criteria parameter that you've created and the end result is a nice flexible dynamic report which allows your user to provide as many or as few parameters as they like so now that we've seen how to handle null values in basic parameters I'd like to move on to show you how you can do the same thing but slightly more complicated drop-down list parameters so in this example we have a report which shows a list of films run sorry release dates and who directed the film I want to create a drop-down list which allows user to select a director name and then to show all films made by that director and of course we also need to handle the case where a user doesn't select any name from the drop-down list in which case we want to see films made by any director to start with we need to create a new data set which will populate our drop-down list so back in the design view of course we already have a data set which populates our table of results what we need to do now is create another data set purely for populating our I will drop down this parameters let's add a new data set I'm going to call this one let's see DTS directors I'm going to use a data set which is embedded within my report linking to the data source which I've created DSC movies I'm going to quickly use the query designer to putting in my director table TBL writer and simply show the director name I suppose I could also add an order by clause goodnight image what my directors are sorted in a particular order so if I select director name I can use the A to Z sort order which adds an order by clause to my query if I click OK and then click OK again now I can create a parameter which uses this data set to provide its available values so I can right click on parameters and choose to add a parameter PRM director the prompt will be something like selects a director the data type will be 'text and I'm going to allow allow null values for this parameter as well to populate the list and its head to the available values tab and I need to say get the values from a query the data set that I'm going to use this one that I've just created DTS directors and because I only have one field in the dataset the value field and the label field will be the same it'll be the directors name I can click OK at this point and I can preview my report and I should see that I get a drop-down list allow me to select a director's name of course I haven't added a filter to the the other data set yet which allows me to add to show films matching the chosen director so that's the next step adding criteria for drop-down list parameters is exactly the same as for normal parameters I need to go back to the design view and I need to add a where clause to the data set which populates my table of results so I need to right-click on the dataset due to viewers properties and in the query text box I need to add on a where clause so immediately after the from clause of my select statement I need to add on a where clause and I'm going to check for example let's see where director name equals at PRM director or having spelled PRM director properly missed the M there we go or that's PRM director is null closer parentheses click ok and that's the system created if I go back to the preview now I should find that I can select any director's name from the list click view report and that will show me a list of all the films made by that director and because I've allowed this parameters to be null and I've written my work laws the handle of it is null I should also be allowed to select no from the drop down this as well shouldn't I the slightly irritating thing however is it because my drop-down list is populated with the results of a query and because that query doesn't return any nulls itself I have no way whatsoever to select a null value from the list so that's the next problem that we have to solve we have to provide a way for the user to select a null value from the drop-down list the way to add a null value to a drop-down list is to modify the data set which populates it so to do that go back to the design view and I need to modify the data set which populates the drop-down list which is called DTS directors so I'm going to right click on it and choose data set properties now what I need to be able to do is tag onto my current list of director names and null value and I can do that by adding a union select to my query so Union joins on another set of values to the current list notice that I've done this in between the from clause and the order by clause so what I want to select in my list is simply null so select all the directors from that are at the table and then join on to that selecting null and then order the entire list by the director name field if I click OK and then preview the report I should find now that I've got the ability to select null from the drop-down this and it is invited at the top one in my list so I can see that if I select that I'm human reports I get all of my directors but if I select any other person let's go with dummy boil again choose view reports I'll only see films by that selected director the slightly disappointing thing for me in this kind of system is that in order to select all of my directors my user would have to know that they have to select null from the list and it's not particularly descriptive it would be nicer if this said something like show all directors so we can make that system work but there are several different steps involved to get to the final result let's start by masking the word null within more descriptive piece of text let's go back to the design view and we can modify the data set for directors by viewing its properties and what we're going to do here is display two different fields for each of the records so rather than just selecting that our actor name I'm also going to choose to display the directors ID or at least select the directors ID so I type in direct ID comma and then they're at a name now the null value that I've selected in the Union place will correspond to the directors ID if I type in a comma here instead of showing the directors name I want to show a piece of text so I type in a single quote and the text one to display something like show all directors and close a single quotes so that's the modification that I need to make to my my query if I click OK at this point and then go back to my preview when I click on the drop-down list at the top I should be able to see no that is this is sorted in alphabetical order of director name I should see that there's an option here now called show all directors now unfortunately what I've done by doing this is broken the link to my criteria in my other data set so the next thing that I have to do back in the design view is modify first of all the parameter that I'm using so right click the parameter and choose viewers parameter properties and look on the available values tab now if I'm getting my values from a query my query now returns two different fields the diets name which is what I want my user to see in my drop-down list so that's referred to as a label field but what I want to store the value that I want to store in the parameter is now going to be the director ID so remember that corresponds to there the null value that I've added on with my Union Select so if I click OK at that point the final change that I have to make now is to modify the criteria of my main gate set one which populates the table of results so I right-click on that parameter sorry that data set and choose to view its properties and now need to check where the director ID is equal to my parameter called director because remember now that I'm storing the the directors ID in my parameter or whether director parameter is null if I click OK and preview the report one more time I default to show all directors so I'm seeing everything in my table again but if I choose a particular director from the list such as let's go for Steven Spielberg for instance and view my report now I can see just films made by the selected director my final remaining irritation with this system is that because we've sorted the data set which populates my drop-down list alphabetically by that to name I'll find that the entry for selecting or sharing all directors is also listed alphabetically whereas were what I would like to see it is at the very top of the list so that I can quickly and easily select it so I can do that but I have to once again modify the data set which populates the drop-down list of director names so I need to start by going back to the design view again and I need to modify the data set which populates my list of directors by viewing its properties and again I'm going to modify the query which populates our list so I'm going to start by adding in yet another field in my select list so I already have the directors ID and I have the directed name I'm gonna type another comma after director name and refer to director name again but I'm going to give this field an alias I'm going to call it as sort field so I'm including my director name field twice but I'm giving it a different name the second time it's going to be called a sort field what I can then do is in my Union select I've selected null which Maps the directors ID I've selected the phrase show all directors which maps to the director name finally what I want to do is add any other value that will be listed alphabetically at the top of my list of Loretto names so I'm going to add in the number zero instead of the single quotes sorry and that will map to my sought field all I need to do now is modify the order by clause so that rather than ordering by director name I'm going to order by my sort field if I select ok preform a report one last time I should find that now show all directors is at the top of the list because alphabetically zero comes before any of my directors names so there you go there's the complete system for allowing null values in drop-down list parameters if you've enjoyed this training video you can find many more online training resources at www but why Zeljko UK

Channel: WiseOwlTutorials

Views: 87,782

Keywords: ssrs, reporting services, parameters, null, drop down list, wise owl

Id: h6WJ4ToBJ8g

Length: 24min 38sec (1478 seconds)

Published: Tue May 21 2013