SSRS Report Builder Part 11.4 - Multi Value Parameters and Null Values

welcome to this weiselle report builder tutorial in this video we'll cover how to use multi-value parameters and null values we'll start by creating the basic multi-value parameter and then explain the problem with allowing null values in that type of parameter we'll then explain how you can substitute nulls with an alternative value how to use the isnal function in sql to test for those alternative values and then how to include those null substitutes in your available value lists so let's get started here's an idea of the type of thing we'll aim to create in the video this report shows a list of actors and we can choose which actors we want to see by selecting their family name and year of birth from these two drop down list parameters i've set both parameters to allow multiple values and i've also added an option for null to the list so if i remove all of the selections first of all and then check the box for null and then also include a couple of actual actor surnames when i click view report it shows a combination of actors with the selected names and also those actors with a null in the family name column i've got a similar system for the year of birth i can select all of the family names and then for the year of birth i can select visible alpha and select all the options and then select this unknown year and then click view report and that shows me a list of actors with a null in the date of birth column again one more similar example that we'll look at a bit later on in the video this report shows a list of films and the country that the film was made in and we can select the countries we want to choose from this drop-down list at the top it's possible that in the database there are some films that have been added to the list which don't have a country assigned to them yet and i've got an option there to include the null countries and if i do that i'll see that name should float to the top of the list and it seemed an appropriate film name to pick for the example i'm demonstrating here i'm sorry for the terrible joke but there we go so that's what we'll aim to create if you'd like to follow along you will need a copy of the wisel movies database so just another quick reminder that this video shows you how to get it set up and there's a link in that video's description that you can use to download the script file you'll need if you've done that already i've got a blank report waiting for me in report builder and the first thing i'll do in here is right click on the data sources folder and choose add data source i'll call this one movies and then i'll use an embedded connection pointing to a microsoft sql server and then click the build button to get some help with the connection string i'll type in a full stop and a backslash as a shortcut to the local host and then the name of the instance of sql server i'm using sql 2017 i will then select my movies database from the drop-down list and then click ok a couple of times to get my data source created the first example we'll have a go at is based on the actor table so let's right click on the movies data source and choose add data set and i'll call this data set actors i can spell actors that would help there we go and then i can click the query designer button so i can simply pick the columns from the act table that i need i will go for the first name and the family name and the dob shop for date of birth column having done that i can click ok a couple of times and then i can display those actors in a table in the report let's let's tidy up i'll remove my page footer first then i'll get rid of this placeholder title text box then i can right click into the report body and choose to insert a table and then assign those three fields the first name family name and dob just change some column widths to hopefully make things a little easier to read and then some very basic formatting as well i want to first of all make sure i will see all the text in the table so i'm going to highlight all the cells and then switch away from the default font and then back to the default font to avoid the font rendering book you might have encountered certainly you will have seen it in earlier videos if you've watched other videos in this playlist and then i'll also format the date of birth as a date and do some very basic coloring in for the header row to make that stand out and then run the report just to make sure it returns some sensible results next i'd like to create a multi-value parameter which allows me to type in a list of family names and then filter this table to show the matching entries to do that i can head back to the design view and we've got a couple of choices here as always we could create a report parameter first and then assign a filter to either the data set or to the table in the report alternatively we could create a query parameter by modifying the select statement that populates the actor's data set the query parameter will automatically create the corresponding report parameter query parameters are usually better because they filter the the data at the source so at the sql server side the data is filtered out before it's returned into the report whereas with the report parameter and the filter all the data is returned to the report first and then the results are filtered out so you may have more network traffic than you need if you use report parameters and filters so let's add a query parameter by right-clicking the actors data set and do data set properties and then i'll click at the end of my select statement and then i'll just zoom in so you can hopefully see this a little more clearly i can add a where clause and i want to check where the family name and i know that i'm going to create a multi-value parameter so i'm going to use the in operator and then enclose the parameter name i'm about to make up in a set of round brackets so i'll call this one at fam name just for short okay so having done that i can click ok you'll see that the corresponding report parameter gets created at the top in the parameters panel and also in the parameters folder just a couple of quick properties to change if i double click on the parameter i can modify the prompt so it's a little more readable i can call it family name the data type is correct that's text and the family name column does indeed contain text and i just want to make sure that i allow multiple values having done that i can click ok and then if i run my report i should find that i can click on this drop down list and then type in a list of values separating each one by pressing enter so if i type in a few common actor surnames like baldwin and fox and harris and oops i can't spell harris there we go harris and jones and lee and those are some good repetitive actor names if i click the view report button i'll find a list of actors with matching any of the entries that i've typed in now i'd like the option to search for actors with a null in the family name column ordinarily to do that you'd head back to the design view and then change another property of the parameter by double clicking on it and then check the box to allow a null value however if you try to click ok you'll spot an immediate problem you're not allowed to mix and match multi-value parameters with null values in report builder as this message tells you even if you could include a null value in that list it would be a little bit pointless at this stage anyway simply because of the way our query works with the in operator just to demonstrate what i mean by that if i head over to sql server management studio i've written a query that kind of simulates what our data set query is doing once we've typed in a list of options in our multi-value parameter so i'm asking to see all the actors where the family name is in this list if i execute that query i'll certainly see actors whose surname is lee and whose surname is fox but i don't see any actors whose surname is a null and that's because the inoperator doesn't treat the null in the appropriate way the inoperator is really a bit of syntax sugar to make writing your where clauses a little bit easier so this is essentially broken out into where the family name equals lee or family name equals fox or family name equals null and of course if you know much about how sql works you'll know that you can't say something equals null by default that never returns any results what you have to do is say is null instead and that would return the actors with a null family name so that's a bit of the background as to why this doesn't work in the first place but that doesn't solve our problem yet does it if i head back to my report builder report i'll need to click ok first and then uncheck the allow null box and then click ok the approach we need to take here to solve this problem is to substitute the null with an actual value that wouldn't ordinarily be found in the family name column so again just to explain what i mean by that i'm going to head back to sql server management studio i'm just going to undo a few things to get back to where i was before back to my in operator with this list of values let's imagine i replaced the null here with let's say an empty string i'll type in a pair of single quotes to place an empty string in that list then what i'd also need to do is check where the family name but not the actual family name i want to substitute any null value in the family name column with an empty string so i can use the is null function to do this so i can say is null open some parentheses family name comma and then type in a pair of single quotes and close the round brackets so this function will replace any nulls in the family name column with an empty string and we'll be comparing that with the in operator to see if any family name is now equal to an empty string so if i execute that we'll see that we now are indeed returning a combination of these and foxes and people with no family name so now we just need to replicate that functionality in our report builder report let's head back over there and the first thing i'll do is right click on my actors data set and choose data set properties and then i'm going to modify the where clause to say where is null open some round brackets family name comma single quotes single quotes and then close the round brackets so any family name that has a null in it will be substituted with an empty string what i can then do is click ok i will now need to allow an empty string for my family name report parameter so i can double click on family name and because i'm using a text parameter i am allowed to use a blank value or an empty string so i can tick that box to allow a blank value i can then click ok and i am allowed to mix and match blank values with multi-value parameters so that if i now run the report and click on the drop down arrow i'll hit enter on the first line to use an empty string and then let's type in lee and fox two nice short names to type in click view report and now we're seeing the combination of people with um lee and fox as a surname and those with no surname at all now let's look at populating our drop down list with some available values to pick from rather than having to type in the list of entries each time we can head back to the design view and then double click on the family name parameter again and head to the available values page i'm going to specify some values for this first basic example and then we'll look at how we can populate that list with a query shortly i'll add a few entries to the list let's add say four or five by clicking the add button five times and then for the first label now remember that the label does not have to match the value of the field you're testing for the label is simply what's displayed to the user when they're looking at the report parameters so for the label i'm going to make the first one say no family name it doesn't really matter exactly what it says the important thing is that the value is just an empty string so i can select the value in that cell and then just hit the delete key to clear its contents for the next view i'll type in some actual real names so let's go for baldwin and i'm just going to copy that and paste it into the value box and then let's do a couple of others let's say fox and then harris don't worry i'm not going to type in the entire list of actor names let's go for jones as well okay it's just enough to demonstrate that the principle works if we now click ok we can run the report and now we'll get a list which allows us to choose specifically no family name but we know that under this behind the scenes that's actually returning an empty string which is being compared to an empty string in the underlying select statement that populates the data set but we can happily mix and match this with any other family names that actually do exist to return the list of results we want now let's look at how to populate the available values using the list of family names that actually exist in the act table in order to do this we'll write a query to populate a new data set and just to again demonstrate how that's going to work i've written another query in sql server management studio so currently it's selecting the family name from the actor table in ascending order of family name so when i execute the query it returns an entry for each individual actor in the act table that's 1 238 rows but you'll notice there are quite a lot of duplicates in that list i've got some nulls and i've got a couple of atoms a couple of afflecks etc so the first thing i'd like to do is make sure i only see one row for each different family name so i can do that by adding the distinct keyword to my query so select distinct family name and when i execute the query again i now see just one entry for each family name now i know that i can't allow no values in this um in this drop down list when i've got a multi-value parameter so i need to substitute the null for the value my query is testing for so i'm testing for empty strings so what i'm going to do here is calculate is null family name comma single quotes single quotes closed around brackets and then i'm going to assign an alias to this so that it says as family name so when you create a calculated column i just run that first i can't reference sorry big pardon i can't reference the family name column because it technically no longer exists this is an expression in the select list so i need to assign an alias to this so that it replicates the original column name so that now returns a list with all the actor names in alphabetical order and the first item in the list will be an empty string and that's actually what the user will see in the drop-down list as well seeing a blank empty string there isn't particularly helpful so as well as having the empty string that the query is going to test for i'd also like to include a label which replaces the empty string with a more descriptive message like no family name or null family name something like that so to do that i'm just going to copy this first expression and then on the next line type in a new comma and paste that in i'm going to change the alias so that it says family name label so i can't have two columns with the same name they've got to be distinct unique and then i can change the value that i return if the family name is null to something like no family name or null family name or any other descriptor that you like so when i execute that query now i've got no family name sitting at the top of the list and then the alphabetically sorted list of family names i'm just going to copy all of that code to the clipboard and then head back to my report builder report head back to the design view i'm going to right click on my movies data source and choose to add a new data set i'll call this one family name list and then simply paste in the query i've just copied into the query box i can click ok and then i want to use the results of that query to populate the available values of that parameter so i can double click on the family name parameter choose available values this time choose get values from a query set the data set to family name list the value field will be the family name remember that's the one that includes the empty string and then the label field will be family name label so i can click ok one more time run the report and now i've got a nice convenient way to select any options that i want for the family name if i choose no family name that returns all the actors with no family name and then any other combination of family names i like i can select a few click view report and they will all appear in the list as well just before we move on to the next parameter i want to give you a quick reminder that you can use those same data sets to set the default values of a parameter so let's head back to the design view and i'm going to double click on the family name parameter head to the default values page and choose to get values from a query i'm going to select the family name list data set and then the family name column for the value field when i click ok and then run the report this time the drop down list will be fully populated with all of the items from the drop-down list eventually when it finally loads there we go so every option in that list has been checked including the no family name option next let's create a parameter which will allow us to filter this list based on the year of the actor's birth this is a little trickier for a couple of reasons firstly because we have to extract the year from the date of birth in order to compare it with our parameter values again a quick query just to show you what i mean by that i've written a query that selects from the actor table and i'm comparing the year of the date of birth to see if it is in this list of numbers so if i were to execute that query it shows me anybody born in the years 68 78 or 88 so we need to replicate this functionality in our report builder report let's head back to the design view of that report and i'm going to right click on the actors data set and choose dataset properties and then i'm going to add another criteria to the end of my where clause so i can say and i'll use the year function to calculate the year of the date of birth and check to see if that is in a multi-value parameters name on makeup i'll call this one at y-o-b then close the round brackets okay so having done that i can click ok that generates my new parameter i'm going to change a couple of quick things here so i'm going to double click on the year of birth parameter i'm going to change the data type from text to an integer it isn't absolutely vital to do this for a query parameter i've mentioned this in previous videos you could leave that set as text if you wanted to if you were using a report parameter and a data set or table filter however you do need to make sure that the data type of the parameter matches the data type of the column you're comparing it with and even if you're just using query parameters i think it makes sense to match the data type of the parameter with the data type of the column or expression you'll notice that that prevents us from allowing a blank value which is another one of the problems we'll need to overcome but we'll sort that out shortly for now i'm just going to allow multiple values click ok and then if i run the report and i click on the drop down list and type in some numbers 1968 and 1978 and 1988 and click view report we'll find a list of actors born in any of those three years that i've typed in now we need to think of a way to include a substitute for nulls so that we can find any actors with no year of birth or no date of birth that's a little trickier because as we saw we're not allowed to include a blank value or an empty string when the data type of the parameter isn't text so what other substitute values could we potentially use well i guess we can first of all look at the data type returned by this function so we have head back to sql server management studio i've got a tooltip popping up when i hover the mouse cursor over the year function that says it returned an int so it always returns a number or an integer so if we can identify a a whole number which would not ordinarily appear if we calculated the year of somebody's date of birth then we could substitute our nulls for that value i think minus one is a fairly safe bet we we're unlikely to find any any actor born in the year minus one so what i'm going to do is say where is null and then open up some round brackets the year of the date of birth type in a comma minus one and then i can check if that is in the list 68.78 and in fact let me just narrow this down a little bit i'll go from 1968 or -1 and if i execute the query i'll find all the actors born in 1968 as well as the one single actor who does not have a year of birth or indeed a date of birth at all so minus one seems like a sensible value to go for let's head back to the report back to the design view and then i can first of all modify the where clause in the query so i can right click my actors data set and choose data set properties then i can wrap an is null function around the year function so is null open some round brackets year of date of birth followed by a comma and then the number minus 1. and then i can check if that value is in the list of options i've returned in the year of birth parameter so having done that i can click ok and when i run the report i can click on the drop down list for year of birth i can type in -1 and then any other year i like i'll get from 1978 this time click the view report button and i'll return all the people born in that specific year but also the one person with no year of birth at all now let's create a data set to populate the available values for this drop down list so that we don't have to carry on typing in the years again i've got a basic query to demonstrate the process we'll go through here so this one first of all just selects the years for the dates of birth for each actor we get one row for every single actor in the list and again we're getting duplicates in this list so step number one is to add the distinct keyword to the select statement and then that shows us just one entry for each year 101 rows next we know that we can't include a null and we've substituted our nulls in the where clause of the actor data set with -1 so let's do the same thing here i'm going to say it's null yeah d-o-b comma minus one and then if we execute that we'll see we get the value minus one that's not a particularly helpful value for our end user to see so what i'd then like to do is create a label column which displays no date of birth or null date of birth or something else that's obvious and plain english so let's copy and paste this and then i'll type in a comma on the next line and paste that in change the alias to say birth year label and then i could attempt to change the negative one to a piece of text which says something like no date of birth for no year of birth something like that anybody who knows anything about data types in sql server will be shouting at the monitor at this point saying no that's not going to work and indeed it isn't um the year function returns an integer and i've tried to replace an integer value if the integer value is null with a string or a char value of r char value in fact and i can't mix and match data types like that so one way to solve this is to wrap the year of the date of birth function in a cast or a convert function so i could say is null cast year date of birth as varchar and then close around brackets so if i execute that now it shows me no date of birth which i can now use as the label field and then the birth year which i can use as the um the value field so again rather than typing all that out again in report builder i'm just going to copy all of this to the clipboard and then head back to report builder and the design view of the report i can right click on my movies data source and choose add data set i'll call this one year list and then paste in the query i've copied into that box there i'll click ok to create my new data set then head back to the year of birth parameter i'll just change the prompt so it's a little more readable i'll call this year of birth and then head to the available values page choose to get values from a query choose the year list data set set the value field to birth year and the label field to birth year label while i'm here i might as well do the same thing for the default values so that it automatically populates all the results in the report so i can go to default values choose to get values from a query set the data set to year list and the value field to birth year i can now click ok and then run the report one more time and we'll see all the results get returned to begin with but i can happily uncheck the select all box check the no date of birth click view report and it will return the one actor with a null in the date at birth field let's move on and look at the example which allows us to select the country in which a film was made this one's a little trickier again because the data set is based on two tables from the database the film table and the country table just to explain what i mean by this let's have a quick look at another query i've set up in sql server management studio this time i'm using the query designer just to show you how the tables are related so the country table has a country id which is its primary key and that primary key value is referenced in a foreign key in the film table so each film indicates the country it was made in by storing the id number of that country if i add this query to my query page by clicking the ok button i can see that by default it uses something called an inner join now we have separate videos which explain all about joins in sql but what this basically means is i'm only going to return a result if there is a matching value in the joined fields between those two tables so as long as the film has a value in the country id which appears in the country id column of the country table then the result will be displayed so i can execute the query and see that all the australian films pop up to the top of the list if i know much about the way my database works i know that there are 1200 films in the film table but i can see that i'm only returning 1100 rows here so there's one film missing and that's because we're using an inner join there's one film with no country assigned to it so it has a null in the country id column and there are no nulls in the country id column of the country table so that film is not displayed to get it to appear in the first place i need to change the type of join i'm using so in this case if i want to see all the rows from the film table the film table is situated to the right of the country table in this join statement so i want to say that the join is a right join or a right outer join if i prefer the outer keyword is optional so if i say a right join that will give me all the rows from the table on the right of that statement so if i execute that again i can now see the one missing film with a null in the country column so i need to create a report which shows that set of data in the first place i think it makes sense at this point to create a brand new report so i'm just going to go back to the design view of my actors report and press control and n to create a brand new report feel free to save yours if you like i'm going to click no i'm not going to bother i will add a data source quickly i'll go through this i won't uh explain too much i'll create a new data source called movies using an embedded connection to sql server click the build button type in a shortcut to my local host and then the name of the instance of sql server i'm using and then select the movies database click ok a couple of times right click on that data source and choose add data set call the data set films and then i can use the query designer to get some basic help constructing it i'll go to the country table first and include the country field and then the film table and include the title the release date and that will be enough i think if i click ok at this point i'll see that the query that gets generated uses an inner join again and again my film table is on the right of the join statement and i know that i want to return all the results from the table on the right so i'll change the word inner to the words right outer having done that i can click ok get rid of my page footer get rid of my title text box at the top insert a new table into the report insert the title the release date and the country fields make sure that i'm not going to encounter this font rendering bug when i run the report just change the column width of that as well so i'm going to highlight all the cells in the table change away from the default font to any other and then back to the default font format the release date how quickly can we do this format the release date and then change the background color of the header row and then we're pretty much there i just run the report there's our basic list of results with the one film with no country assigned to it now let's add a where clause to our data set which will allow us to select the id of the country in which the film was made if we head back to the design view and i can right click on the film's data set and choose data set properties i can click at the end of the select statement give myself a new line and type in where and i'll base this on the film country id field so i could type this in or copy and paste it but i'll say film dot country id it's very important to specify which table your country id column belongs to in this case because we've got two country id fields available in the data set you must specify which table this field belongs to so i'm going to say where film country id in and then open some round brackets and make up a new parameter name let's call it country id close around brackets and then i can click ok i will just double click on the report parameter that's been created for me i'll change its data type to an integer because that's the type of the country id field and i'll allow multiple values in the drop-down list once i've done that i can click ok and i can give this a very quick test by running the report i can click on the drop-down list and if i can remember any of the id numbers of any of the countries i think 240 is the united kingdom and 241 is the united states so if i click view report it looks like i've guessed correctly my memory was good enough now we just need to think of a sensible substitute value for nulls that we can use to check for films with a null country id again this requires knowing a little bit about the design of the database if i switch back to sql server management studio i've gone to the design view of my country table and highlighted the country id column and in the column properties area i can find the identity specification section so i've set up this column to automatically assign a number each time i add an entry to the table the first number that will be assigned to the very first item added to the table is the number one and then it increments by one for each new entry so if i know that i'll know that i can't possibly have a negative number in the country id column so that seems again like a sensible option for a substitute value for nulls let's use -1 as our substitute value i can head back to the report and then back to the design view we can right click the film's data set and choose data set properties and then in the where clause if i zoom in again i can say is null film dot country id comma minus one close around brackets and then the minus one will be substituted whenever the country id is null so i can click ok and then i can run the report i will click on the drop-down list i'll type in -1 and then 240 and then 241 and then click the view report button and we should find that the one film with no country id now appears at the top of the list now let's look at creating a data set to populate the available values of the drop-down list so that we don't have to carry on guessing what the country ids are to make this work we'll need a data set that returns the country id because that's the value we're comparing in the where clause of the films data set and the country name so that we can display that to the end user we've got a couple of choices for this but the obvious one might not necessarily be the best approach i'll just go back to sql server management studio here's a select statement that returns the country id and country from the country table and i've unioned that to the value -1 for our substitute null value and the label no country assigned that seems like a sensible choice and we've used that technique in a couple of previous videos to add an additional option to a list of values so we can see i'm seeing the no country assigned label there with -1 and then a list of the countries which have been assigned to films in the film table however below that i've also got a list of countries in the country table which have not ever been used in the film table i think i pretty much populated this list by copying and pasting a table of countries of the world from a random website probably wikipedia so although i'm sure zimbabwe has a fascinating and vibrant film making industry i haven't put any zimbabwean films in my database so it seems very pointless including that option in my drop-down list so perhaps a better approach is to create a query again that uses both the country table and the film table so that i can see only the countries that have actually been used and assigned to films so at the moment i'm showing just the country country name so i've got country dot country i could also include the country country id so let's say country country id so that was meant to be a full stop rather than a comma but i shall place a comma here as well you can also see i'm getting duplicate values in this list so multiple films made in australia so again i'll add the distinct keyword here so that when i execute this i get a list of just the countries that have actually been used in my film table and because i've used a right outer join to include all of the films i've also got my null value here as well now we know that we need to substitute the country id null with a minus 1 and we can substitute the country null with a sensible message like no country assigned so let's replace the country id first we'll say is null country.country id comma minus one and then we can assign an alias to that as country id so we'll just re-label it with the original column name for the country column we can say is null again and then this time we'll replace this with a message that says something like no country assigned so we'll give that an alias let's call this one something different let's call this one country label and then i'd like to change the order by clause in fact i'll need to change the order by clause because i can't reference that column any longer with the distinct keyword so let's copy the country label alias and place that down there so when we execute the query at this point we'll see that we have indeed returned our minus one and no country assigned but unfortunately it's part way down the list which isn't particularly helpful for the end user it'd be nicer if that item floated up to the top of our list so we could do a couple of basic things here to help our end user and if we didn't want to create another column to sort the results we could just ensure that this value appears at the top of the list by adding some extra characters so that if this were sorted alphanumerically the round bracket symbol ensures that this one appears as the first entry so if i added some round brackets around it you'll see that alphabetically the round bracket comes before a so that one now appears at the top of the list so that is a bit of a cheat way to do it but it works a slightly longer winded way to do this would be to add a separate column so rather than modifying that nicely formatted bit of text there we could do something like this so let's just copy that line and then we'll paste it in on the next line and this time i just want to replace any nulls in the country column with any other character that would appear before the first item in the list alphabetically so we could just use a single open round bracket if we wanted to um a zero also works equally well and then i could change the name of that column so that it's called sort column or sort field and then if i copy and paste that to the order by clause execute the query again i'll see that the label still looks nice and sentible without the extra characters but they're sorted alpha numerically based on the value of that substituted zero um your choice basically whichever you find easiest or most applicable i don't want to have to type all that out again so i'm just going to copy and paste this to my report builder new data set so i go back to report builder head to the design view right click movies and choose add data set i will call this one country list and then paste in all of that code into the query box before clicking ok now i can go to the country id parameter head to the available values page and choose to get values from a query choose the country list data set the value field will be the country id and the label field will be the country label if we wanted to automatically assign all the countries as the default values we can go to default values get values from a query choose country list and choose the country id for the value field okay so one more click of the ok button and we can run the report and we should see that all of the columns are sorry all the countries are selected by default in our drop-down list but we can also choose individual options from the multi-value list to find all of the results based on the items we've selected so there we go a few ways to handle null values in your multi-value parameters of course it's a bit of a cheat isn't it really we're not really using null values we're substituting the nulls for a replacement value that we can reliably test for in the where clause of our underlying dataset queries still it could be potentially useful hope you found it useful thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 7,852

Keywords: ssrs, report builder, reporting services, report server, sql, sql server, microsoft, report, paginated report, rdl, parameter, multivalue, multi value, multiple values, null, null values, allow nulls, optional, isnull, is null, tutorial, training, course, free tutorial, free training, free course, wise owl

Id: aoJtPPasT6c

Length: 41min 2sec (2462 seconds)

Published: Fri Mar 12 2021