SSRS Report Builder Part 11.5 - Multi Value Parameters and Stored Procedures

welcome to this weizel report builder tutorial in this video we're going to cover how to deal with multi-value parameters in store procedures we'll start the video with a quick reminder of how to use multi-value parameters with basic queries and then how you can pass multiple values into a stored procedure and why that goes wrong when you do we'll then explain a couple of techniques for splitting a string into separate values within your stored procedure first of all using a built-in function that's available from sql server 2016 and later and then how you can create your own custom user-defined function to do the same thing if you're working with an earlier version of sql server so let's get started here's a basic example of the type of report we'll create in this video the main table shows a list of films and we can change which films get displayed by selecting from the two multi-value parameters at the top of the report so whichever genres i select from this drop-down list and whichever countries i select from this one will affect the results when i click the view report button now there's nothing really surprising about that if you've watched earlier videos in this playlist multi-value parameters are nothing new the thing that's different about this report is that the data set which gives the results to this table is populated using a stored procedure and there are a few slightly tricky things about passing multi-value parameters into a stored procedure and having those results interpreted correctly but we'll explain how that works as we work through the video if you would like to follow along you'll need a copy of the wisel movies database and just a quick reminder that you can learn how to install that using this video and there's a link in that video's description that you can use to download the script file that you'll need assuming you've done that already the first thing i'm going to do is head over to a blank report in report builder and then add a data source to connect to that movies database i'll call my data source movies and then i'll use a connection that's embedded in the report pointing to a microsoft sql server and then click the build button to get some help with the connection string i'll type in dot backslash to shortcut to the local host and then the name of the instance of sql server i'm using which is sql 2017 i can then select my movies database from the drop-down list here click ok and ok again to create my data source to create the data set i'm going to start by using a basic query stored in this report and then we'll convert that into a stored procedure later on so i'm going to right click on my movies data source and choose add data set i'm going to call this one films and then in the query designer i'm going to select from a range of different tables so from the tables folder i'll head to the film table first and i'll select the title and the release date and then from the country table i'd like to select the country and then from the genre table i'd like to select the genre so i'd like to display all those results in the main table i display to the end user i can click ok to create that basic data set and then click ok again and then i'll display those results in a table in the report i'll tidy up first by removing the page footer and then removing the placeholder title text box right click in the body of the report and choose to insert a table and then i'll populate the columns with the fields i've selected from my data set so title release date country and genre just change some column widths here as well to make sure that i can see everything clearly when i run the report and then i want to make sure that i don't encounter the font rendering book so that when i run the report i actually see all the text in the table you may have encountered this yourself i've certainly encountered it in previous videos if i select all of the cells in the table and then switch the default font to any other font and then back to the default that should ensure i see all of the text when i run the report a bit of very basic formatting i'll format the release date in that date format and then some background colors for the header row and then i can run the report just to check that i'm seeing sensible results at this stage and there we go next i'd like to add a parameter to filter this set of results based on the film genre to do that we can head back to the design view and i can right click on the films data set and choose data set properties i can then add a where clause to my query so let's just zoom in so you can hopefully see that more clearly at the end of the select statement i'm going to say where and then i'm going to reference the genre id field so i'm going to say film dot genre id and then because i want this to be a multi-value parameter i'm going to use the in operator and then in a set of round brackets i'm going to pass in a reference to a new parameter name that i'm about to make up so i'll type in an app symbol let's call this one genre ids and then i can close the parentheses all the round brackets and when i click ok i'll generate a corresponding report parameter also called john ids i'm going to modify a couple of basic properties of this parameter so i'm going to double click on it and then i'm going to change its data type so that it's using an integer type the genre id column is an integer rather than a string so change from text to integer and i also want to allow multiple values so at this point i can click ok and then i can run the report and if i click on the drop-down list i can type in a list of individual genre ids it's difficult to know what numbers to type in but i'm going to go for the number 1 2 and 3 inventively and then when i click the view report button it will show me a list of films in the three genres science fiction which i believe is the number three drama the number two and then there should be some westerns in there as well which is at number one in my list multi-value parameters tend to work better when you provide a list of available values for your user to select from so let's create another data set that will populate this list with the names of the genres but store the genre ids we can head back to the design view and then we can right click on the movies data source and choose add data set i'll call this one genre list i can spell that correctly and then if we head to the query designer button from the tables list i'm going to pick the genre table and then select the genre id and the genre i'll click ok and then i'm just going to add a quick order by clause as well so that i can see my genres sorted alphabetically by genre name so i can say order by genre once i've done that i can click ok and then head to the genre ids parameter i can head to the available values page choose to get values from a query select my genre list data set set the genre id as the value field and then the label which is what the user will see will be the genre again this is not particularly surprising if you followed the previous videos in this playlist so if i run the report at this point i can now select from a range of genre ids let's uh select the same ones we saw earlier on i'm going to go for westerns and dramas and science fiction and then if i click the view report button we'll get the same list of values we saw when we just typed in the numbers one two and three okay now that we've got the basic part working i'd like to convert my simple select statement in the data set of this report into a stored procedure so let's head back to the design view and rather than recreating all the code for our query let's right click on the film's data set and choose data set properties and then i'm just going to copy all of the text from the query box to the clipboard and then click ok i'll cancel it doesn't really matter we then need to head over to sql server management studio so i've got an open instance of management studio connected to my sql 2017 instance so the same instance that we connected to for our data source i've got the movies database selected here i'm going to choose to create a new query and then give myself a couple of blank lines at the top of that query and then paste in all of the code i've just created i'm going to add a couple of extra statements up at the top i need to convert this into a stored procedure now when you create a stored procedure it's worthwhile ensuring that your stored procedure gets created in the database you want one way to control that is to select the database you want to use from this drop-down list at the top left hand corner but it probably makes more sense to enforce that in your code i'm going to say use movies and then go before we then write the code that will create the procedure itself to create the procedure is fairly straightforward we write a statement that says create proc or you can also say procedure if you prefer but i prefer the shorthand version create proc we can optionally say which schema this store procedure will belong to so i can say dbo for example followed by a full stop and then state the name that i want to give to my stored procedure i'm going to call mine films multi-value parameters a bit of a long-winded name but it's nice and obvious and descriptive one more thing we need to do is to declare a parameter which can accept the correct data type for the column we're comparing it against so we're referencing a parameter in the where clause here it makes sense to copy that parameter name and then below the create proc statement we can say genre ids and set the data type to the data type of the genre id field if i hover the mouse cursor over genre id you can see that its data type is an int so i can say genre ids int i then simply need to say as and then the code that will create this procedure is now correct i'm just going to highlight the select statement from top to bottom and then hit the tab key to indent it once space i just prefer that layout personally and then if i hit the execute button that stored procedure will be created you can check that the procedure exists by expanding the movies database finding the programmability folder then the store procedures folder and you should find that when you expand that for the first time your new store procedure is sitting in that list i've got another one there this is the one that i was using for the test or the the example report i showed you at the start of the video if you don't find your store procedure there you may need to refresh the store procedures folder so i can right click on the store procedures folder and choose to refresh it and that will update it to ensure that it definitely does appear okay now that we've created the procedure let's go back to the report and use that to populate our data set so i'm going to go back to my report in robot builder i can right click on my films data set and choose data set properties and then rather than using a text query i'm going to choose to select a stored procedure and then from the drop down list which appears i can choose my film's multi-value parameters procedure i can then click ok and that will update the way that data set is populated at this point we can run the report and we can test it in a basic fashion if we click on the drop down list and select just a single genre and we hit the view report button everything works perfectly well apart from the fact that we've returned twilight movies but that was my fault so let's um get rid of those what happens if we select more than one genre however this is where we start to encounter problems if i select multiple genres and then click view report the report no longer works we can see the error message by clicking the details button here and it's complaining about a conversion of data types from nvarchar to int so i'm just going to click ok and then head back to the design view and then try to explain what's going on here to help explain what's going on here i'd like to use another tool in sql server management studio that can show us which store procedures have been executed and what values have been passed to them when we run our reports in report builder so to do that i'm going to head back to sql server management studio i'm going to head to the tools menu and choose to open the sql server profiler when that launches i'll need to connect to an instance of sql server so i'm going to make sure that's the same one that holds my movies database and the stored procedure we've just created click connect and then i need to configure what it is i want to trace when i start running the profiler now there's lots of information displayed by default by the sql server profiler so i'm going to go to the event selection and narrow this down somewhat i don't really care about anything other than stored procedures that have been executed so this particular item here rpc completed remote procedure call completed i'm just going to uncheck these other boxes i don't really care about tracing those and then i'm going to make sure that i include the text data of the remote procedure call as well having done that i can also filter a particular column to make sure or try to narrow down at least to procedure calls made from a particular application now there's lots of background remote procedure called running in sql server all the time and if i want to avoid seeing a long list of extra procedures that i'm not interested in i can apply a column filter to the application name column so i've clicked column filters i've got application name selected here and then i can expand the like box and the name of the um the application as it's listed in the sql server profiler for report builder it's more to do with the um the provider we're using to connect to sql server so it's all about the data source connection type we've used so i'm going to type in dot net followed by a percentage symbol so using a basic sql server wildcard so the name of the application will begin with net followed by any other bit of text okay so having done that i'm going to click ok and then i'm going to click the run button to start running my trace so you can see now i've got a new window that shows me my trace being started so now if i interact with report builder if i head back here and run my report and then if i choose a single item from the list let's just choose so let's uncheck everything and then check for example action and click view report so that one will work and we can see that we've got all the action movies if i switch back to sql server profiler you can see that i've completed a remote procedure call and this is the statement that was executed an exec statement execute this store procedure and pass in the id number four to the genre ids parameter so you can see that there has been passed in as an integer as a whole number so that's going to work let's head back to report builder and this time let's choose multiple genres let's go for adventure and animation as well we know this is going to fail we get an error and we can see that we've got this same error message about converting n varchar to int data so let's just click ok and then head back to sql server profiler so we can see we've got some more remote procedure calls one that was that happened automatically to reset the connection and then this one is the one we've executed when we click the view reports button you can see it's passed in not a single number and not even technically a list of different integers it's passing a single n varchar string to a parameter whose data type must be an int so that's why we're getting we're getting this error okay so now we know what the problem is regardless of what the data type of the report parameter is if you select multiple values from it when it's passed into a stored procedure those multiple values are treated as one single continuous n varchar string so that means that our stored procedure needs to be able to accept an n bar chart value rather than just an int so let's head back to sql server management studio and we'll need to make some changes to our stored procedure to make this work first of all i need to change the word create to the word alter i can't run this script again if i click execute it tells me there's already an object with that name in the database so i don't want to create it again i want to change the existing copy of that so i can say alter rather than create the red squiggly which appears there is just because the intellisense hasn't updated itself yet it doesn't really matter the code will still work but if it bothers you you can head to the edit menu choose intellisense and then choose refresh local cache or use the keyboard shortcut ctrl and shift and r and once you've selected that after a couple of seconds the red squiggly should disappear there we go and then you can execute this script as often as you like i'm not obviously making any changes to it yet but it is technically updating the definition of that procedure okay so we know that we need to be able to accept an n varchar value rather than an int for this parameter so let's change the data type from int to n varchar and then let's specify how long the string should be let's arbitrarily put in 255 that'll be more than enough for the range of genres we can select so at this point if i click execute it will update the definition of the procedure and now i can pass a string into that parameter when i call this procedure i'm going to run into another problem of course as we'll see in just a moment but just for the time being let's head back to our report and let's select a different list of options and then click the view report button and find that it does indeed fail again but if you click the details button to see the um see them the message you'll see a subtle difference it's still a problem with converting data types from n varchar to int but it actually shows us the range of values we're selecting this time so actually a little bit further through the stored procedure than we than we got last time i'm just going to click ok and then head back to the design view um this time i'm going to go back to sql server management studio that the profiler is still running by the way and you can see that the trace of the previous call but that's not particularly informative at this point um but if i go back to sql server management studio the problem here of course is that the value of the genre id field in the underlying database hasn't changed that's still an int and what we're trying to do now is check if that integer value is in a single string one comma two comma three for example the next step in solving the problem is to take this single comma separated string and split it out into a list of individual numbers just to demonstrate how that's going to work i'm going to copy this entire select statement from my stored procedure script create a new query and then paste all that code in to that new query page so at the moment what we want to achieve let's imagine we selected the numbers one two and three westin's dramas and science fiction films from our genre list what i want to be able to do is compare those individual numbers as separate individual numbers and if i can do that when i execute the query it will work of course the problem we have at the moment is that we don't have those separate individual numbers we have one single continuous n varchar string so at this point essentially the the in operator is useless we're not looking at the individual values we're really just checking to see if the genre id is equal to that single string which of course it doesn't really need to be proved that this doesn't work we've already established but that's not going to work but that's our problem so how can we split that string into individual values i'm going to write a new select statement at the bottom of this query i'm going to say select i'm going to use the asterisk character to select everything every column from but i'm not going to refer to a table i'm going to use a function that was introduced in sql server 2016 called string split if i open up some round brackets you'll see there are two parameters for this function one is the string containing all the values you want to extract and the second is the separator character which separates the individual items you'll see this function returns a table so it's a table valued function which is why i'm using the select statement to return everything from it so if i just copy that string from my query to there and then provide the second argument to the spring string split function by saying that these items are separated using the comma characters if i then close around brackets if i highlight and execute just that single select statement there i'll see that it converts that comma separated string into a table containing those individual values the column name generated by the string split function is called value so rather than saying select star i could say select value instead and that would do the same thing now technically those values still aren't integers and those are sub strings they're still n varchar characters it won't really matter sql server will be clever enough to work out an implicit type conversion so it will be able to treat these individual strings as numbers when we compare them against the genre id field however it's best to be technically correct about this sort of stuff so let's add a cast around our value field to say cast as int so they will definitely be converted into integers and then we can assign an alias to that let's say as value again okay so we're nearly there what i'm now going to do is take this statement here and replace what we've previously had so i'm going to change my where clause to say in and then open up some round brackets and then i can simply move this slack statement inside those round brackets so even though we're technically referring to a comma separated string here this selection from the string split function will convert that into a list of values and now the entire thing will work again so i'm getting my science fiction films my drama films and hopefully somewhere down the list some western films as well okay so now let's update our stored procedure with this new select statement that uses the string split function i'm going to copy select cast value as in as value from string split all the way to the end of the first close round bracket there i'm going to copy that head back to the script which creates our stored procedure or alters our stored procedure and then change where it says at genre ids i'm just going to paste in what i've just copied i'm then going to modify where i've set n1 comma 2 comma 3 and replace that with a reference to my at genre's parameter so i can remove that replace it with at genre ids just to tidy things up a little bit to make it a little more readable i'm going to split this across multiple lines so there's my select statement so i'm selecting the integer version of the of the values from the results of the string split function splitting the genre id comma separated string using the commas to identify the separator okay so that's a little bit um more complicated than ideally it would be but if we execute this script now to update our stored procedure we can then head back to our report and we can run the report we can select a range of options from this list let's just select them all why not and click the view report button and we'll see this time we do indeed return all the films regardless of which options we've selected bearing in mind that your um your trace is still running in the sql server profile or at least mine still is so we should be able to see now these extra traces that we've just been using passing in either the full list of ids or just that final one there which was that shorter list if you want to stop your trace by the way you can hit the stop button and then just close down the sql server profiler so if you're lucky enough to be using sql server 2016 or later you can simply use the string split function to separate out your multi-value parameter selections into the individual values but what if you're not that lucky what if you're using an earlier version of sql server which doesn't have that function well in that case we've got a little bit more work to do but not too much more work actually and it's a bit of fun we're going to create our own table valued function to split a string into its separate component parts let's head back to sql server management studio i appreciate this part of the video is getting more into sql than report builder but um it's still having useful information so i'm going to include it in this video anyway let's create a new query and at the top of that query i'm going to make sure that i say use movies to make sure that my function gets created in the correct database and then go and then we're going to say create function so similar to create procedure although we can't sadly um abbreviate this to create funk so we must spell out the full word function i'm then going to say that this is in the dbo schema and then we can make up a name for our function the built-in one is called string split so let's be inventive and call ours split string we then need to define a parameter or at least one parameter and get it to find two parameters so the string split function accepts the full string you want to split and then the character which separates the individual entries so let's replicate that functionality here i'll open up some parentheses and then on the next line i'll call my first parameter at full string and that's going to be n varchar max so we can pass in a huge string if we want to and then the second parameter is going to be called at separator i'll spell that correctly separate all and then i'll call that one n varchar and that will be a single character so enva chart one i'll then close the extra set of round brackets for the parameter list and then on the next line for a function we have to say what type of information the function returns so for a table valued function we we need to say returns then we need to make up the name for a table variable so i'm going to start with at and i'm going to call this one t inventively the end user will never see the name of that table so that doesn't really matter at t will be fine and then i can say that it's type is a table and then in some round brackets i can define any columns that table will have so i'm going to call my column value which is the same as the column name in the built-in string split function and i'll set its data type to match the data type of the full string so that's n far char max close a couple of sets of round brackets for that then on the next line i can say as and then i need to contain the definition of my function within a begin end block now technically there's only one more line i need to write in order to make this function valid and that's the keyword return so at this point my function definition is is valid i can pass in a full string and a separator character and all i get back at this point is an empty table with no rows in it next i'm going to declare a couple of variables that will help us to separate out the individual entries from the full string so i'm going to say first of all declare at single string so this will represent a single item in our in our list and i'll set that to be n varchar max again so that could be the full length of the full string that we've passed in i'm then going to declare a variable which will hold the position in the full string of the separator character that we're looking for so i'm going to call this one separate or i'll spell that correctly eventually separator position i'm going to set that data type to be an int so it's just a number which holds the position of a character in a string i'm also now going to initialize that variable by calculating the position of the first separator character in the full string so to do that i'll say equals and then i'm going to use the char index function so the char index function allows you to search for one string within another string so the thing i'm going to look for will be my separator parameters so i'll say at separator i'm going to look for that within my at full string parameter okay so that's the starting point and i've got a couple of useful values that i can now use to return the rest of the results the next set of instructions i'm going to write need to be executed multiple times so at this point i'm going to begin a loop structure in the function that will continue repeating instructions until some kind of condition has been met so to create a conditional loop in sql you can begin with the word while and then on that same line we need to write some kind of logical test to determine when the loop structure will end the condition i'm going to use is whether or not i have found the next separator character i'm looking for so when you use the char index function it looks for the position of the separated character in the full string so imagine my string was one comma two comma three for example oops sorry let me type that in again there we go so if i looked for a comma in that string the first value that the chart index function will return is the number two because the first comma is the second character in that string if that list didn't have any commas in it at all then the char index function returns zero so i know that as long as the value of the char index function is greater than zero i have successfully found a comma character or a separator character so my while condition is going to be while at separator position is greater than zero i'm then going to wrap up the instructions for my while loop in a another begin end block and then i can work out what it is i need to do inside that loop the first thing i want to do inside the loop is strip off any characters from the full string that appear to the left of the first separator character that i've found and i'm going to store that value in the single string variable so i'm going to say set at single string equals then i'm going to use the left function which returns characters from the left of a string of text and i'm going to say the expression i'm stripping my characters from is the full string so i can say add to full string and the number of characters i want to get is one less than the separator position so i can say at separator position i'll spell that correctly minus one so just to again demonstrate what that means if i typed in a string let's say a comma b comma c if i've looked for the position of the first comma that returns the number two so separated position is the number two and then i've subtracted one from that so the result is one of course i mean i can do that mental arithmetic and then i've said get one character from the left of the full string so the result of that statement will just be the letter a so what i can do now is insert that single string into my table variable to start building up my list so to do that i can say insert into at t there's only one column to populate so i don't need to to write out a column list i can just go straight to the values section and then in a set of round brackets say at single string okay so that's the starting point of building up my list of separated values next i want to modify what bit of text is held in the full string variable by getting rid of any characters up to and including the first separator character so to do that i'm going to say set at full string equal to and then i'm going to use the sub string function so i've got three options or three parameters to fill in for the substring function the first is the bit of text i'm getting my substring from so that's also going to be at full string the second is the starting position for the substring i want to return and that i want to be one character after the separator position so let me just copy and paste that bit separator position and then rather than minus one we'll say plus one we can then type in another comma and say what length of string do we want to return well i just want to return everything all the way up to the end of the current full string so a quick simple way to ensure that is to calculate the length of the full string using the len function so i can say len at full string then i can close two sets of round brackets to finish that line so again if we think about that um that sample string that imaginary string a comma b comma c my separator position is currently the number two that was the position of the first comma in that list so i've added one to that so i've said begin at essentially character number three and then return all the characters all the way up to the end of the string so the end result will basically be the deletion of a comma so i'm left with b comma c the final thing i need to do inside the loop is recalculate the position of the separator character so i've modified the full string i want to now update this to find the next separator position so to do that is fairly straightforward i can say set at separator position and i can make that equal to the same expression i used to assign a value to that in the first place so chart index at separator in the full string so i'll calculate the next position and then go back round to the top of this loop check that i have i have actually found the next separator position so that i've got a value of greater than zero and if so repeat that process to insert the next item in the list and that loop will continue going around until the remainder of my string has no more separator characters in it so there's one more thing we'll also need to do if we imagine we'd reach the end of this loop we we have a string that just has the letter c in it and we set the separator position by looking for a comma in that single character string then the separator position becomes zero so it goes back to the top of the loop it establishes that the separator position is not greater than zero so it wouldn't insert that final character that final item in the list so just to ensure that our final list item gets inserted into our table we're going to say insert into at t i'll just copy that line and then rather than single string we can say whatever remains in the full string we know that we'll have stripped off everything else prior to the last separator character so inserting that will guarantee that we'll get the full list inserted into the table okay so there's the full function created let's execute this script to create it so i can click the execute button to hopefully commands completed successfully and then if i look in my programmability folder in the movies database head over to the functions folder in there and then look in the table valued functions folder i should find my split string function is sitting there again if it doesn't appear by default you can right click on the table valued functions folder and choose to refresh it to update that list i'd also like to give this function a quick test so i'm just going to copy the function name and then create a new query window and here i'm going to say let's say select value from so i know that i've called the column from the table value so the value from and then i'm going to paste in my function name and then i'm going to open up some round brackets and i'm just going to type in that manually comma separated list a comma b comma c followed by a comma and then say that the separator character is a comma and then close around brackets and the red squigglies are indicating that this function name doesn't yet exist and again it's simply because the intellisense hasn't refreshed you could head to edit intellisense refresh local cache just to update the intellisense and then just get a bit of reassurance that it's going to run so having done that i can hit execute and we will see that the results of that function are indeed a list of separate values using my single separator character so now that we've established that our function is working we can replace our string split function in our stored procedure by calling on this new split string function so we can copy split string head back to the script which we're using to alter our procedure and then find where we've got the string split function and replace that with our inventively named split string function instead the parameter list is still the same so we're passing in a long string searching for a single character which separates the individual entries so i can execute this entire script to update my stored procedure then head back to report builder and let's run the report let's start by selecting a single genre first and then click view report check that works it certainly does let's select a couple of different genres and then view the report again that's certainly working so if we just select all the genres we should find that that works equally well as our built-in native sql servers 2016 string split function so at this point adding more parameters to the stored procedure is pretty easy let's head back to sql server management studio i'll just hit ctrl r to hide my results panel and let's add in a new parameter to accept a list of country ids so i'm going to add a new parameter i'll call this one at country ids and it's going to be an n varchar and i'll make it 255 in length again so having done that i now want to create or add to my where clause to check where the film country id is in a list of the um the individual entries returned from my split string function so probably the simplest thing to do here is just copy that entire condition from the where clause stick the and keyword in there somewhere and then paste in what we've just copied so i'm going to check where the film country id is in the list of values returned from our split string function but referring to the country ids parameter okay so having done that we can execute this procedure or execute this script to update the procedure then we can head back to our report builder report and head back to the design view we should then be able to go to the film's data set properties and just hit the refresh fields button to make sure everything's been updated and click ok and that will force our parameter to be created i'm just going to double click on the parameter and change its data type to an integer and allow multiple values we'll deal with populating the available values shortly but just for now if i run the report i'll select all the genres and then for the country ids i'll just type in a couple i'll type in 240 which is for the uk and 241 which is for the united states and if i click view report everything should just work exactly as we would expect so if i get to the the end of the list i'll find the united states films in there as well the final thing i'd like to do is populate the available values of this parameter so that we don't have to guess what the id numbers of the countries are let's head back to the design view and start by creating a basic data set to select the list of countries and country ids from the country table so i'll right click on movies and choose add data set and i'll change the name of that data set to country list and then i'll use the query designer to quickly pick from my tables folder and then from the country table the country id and the country having done that i can click ok and then ok again i can then go to the properties of my country ids parameter choose available values get values from a query and then set the data set to country list the value fields to country id and the label field to country click ok and then run the report and i'll see that i can now let's just select all the genres quickly and then from the country ids list i can select from the countries in my list one minor problem with populating my country drop-down list as i have is that i've selected every single country from the country table regardless if there are any films in my database made in that country so my country table is just populated i think i just copied a table from wikipedia for all the countries in the world so if i scroll further down this list you'll see a huge number of countries which have no matching entries in my my database in my film table so i'm going to modify that data set to return only the countries in which films have been made so let's head back to the design view and i'm going to find my country list data set right click on it and choose data set properties i'll go to the query designer first and then a simple way to restrict the list of countries to only those which have been used in the film table is just to include any column from the film table in the query designer so i'll just go for film id and then click ok and that will update the from clause to join in the film table i could then just delete the film id column i don't need that to be displayed anywhere so that will limit the list to countries which appear in the in the film table i'll also add an order by clause here as well so i can order by the country column so i'll say country click ok and then when i run the report my country id list will indeed be filtered to only those countries in which films have been made but you can see another problem now is that i've got duplicates for each country um so i'm seeing one row for every film in the film table rather than for each unique or distinct country so one final change to make back in the design view i'll go back to the data set properties of the country list at the end of the select keyword i can add the word distinct to select just the unique country ids and country names so that when i click ok run the report again i'll select just a couple of different genres and then from the country ids list is now nice and short and populated only with those countries in which films have actually been made so there we go that's how you deal with multi-value parameters when they're passed to store procedures quite a lot of sql involved there rather than report design but hope you found it useful anyway thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 12,346

Keywords: ssrs, report builder, reporting services, report server, sql, sql server, microsoft, report, paginated report, rdl, parameter, multivalue, multi value, multiple values, stored procedure, split string, string_split, comma separated, user defined function, udf, table valued function, create proc, create procedure, create function, custom function, training, tutorial, course, free training, free tutorial, free course, wise owl

Id: C1F7dkuAL4I

Length: 44min 46sec (2686 seconds)

Published: Mon Mar 15 2021