SSRS Tutorial 37 - Use Stored Procedure with Multi value Parameter in SSRS Report

welcome to Tech Brothers with Amir today we are going to learn how to use a store procedure with multivalue parameter in SSRS report so the major part of the this video is going to be how to create a store procedure that can accept multiple values so let's go ahead and create a store procedure and understand what exactly we need to do here is my select query that is a getting some columns and we have sales sales uh person first name last name and items sold and other column such as country region and we getting this data from a total sale table and it is in the sales database so if I run this one it is returning me 11 records to make it multivalue if you guys remember we have to write a we Clause so we write where and I am looking forward to use the region as my parameter and I will say region in and that's why we will say AIA comma then we provide other value let's have single course around it and Europe and now we can go ahead and run this query it is going to return us the results or records for Asia and Europe as we have provided the values here in the in the Clause now the problem is when we will accept multiv values from the uh SSRS report parameter it is going to return something like this Asia and Europe so if we will put these values here in the in Clause it is going to fail it will not return anything to us because you don't have Asia with the single quotes around here you don't have Europe with the single quotes around here so that's the problem we have to build this store procedure and take the same values let's say these are the values coming in we need to make them something like this so people has different ways to do it uh some people take these values as it is and create a function and that function will be used here in the right here so they will use as inner query and that will uh pass this one as a as another table so this will return Asia Europe and that's the way to do it I mean they can use function here but in my case I had been thinking like we can use a function but then I said okay let me use the dynamic SQL so Dynamic SQL is another way to do it so let's take this value and start building our store procedure so let's first of all we have to say create procedure sorry um okay so create procedure and procedure name so we can call this one SP total sale and then we have a parameter in this case I'm going to call this one region and work car and uh let me make it 500 and now we say as and then we say begin and then at the end we have to say let's remove this part we don't need this uh we are going to use something here and now we say end as of now we don't have anything here I'm going to use this query as a dynamic so once I have the region as remember we are going to get region as Asia comma so we will be getting something like Asia comma Europe that's how we will be getting uh and North America let's say another so there are three values we are getting this is what we are going to get what I want to do I'm going to replace this one with the something with the single quotes and comma together so I'm going to place something like this so I can build this entire thing and each comma will be replaced by these single quotes on the each side and then at end I will go ahead and here I will put that single quotes in left and right so let's create some variable so I'm going to call this one declare at the rate I let me call this one War uh region and it is what are 500 and now we have to set I'm setting the value of this one so where region I'm saying okay is equal to then here and I will be saying replace and replace what so region this region I want to replace and here if you see that I will go ahead and say comma and uh I want to replace this value what we are going to get as a normal so what we want to do we want to replace with the here I will say replace a comma with the and I will put comma here and then another three columns here and parenthesis Clos so let me um comment this so right now we don't have think about this is just a commented part it's not contributing in the store procedure so if I can go ahead and print this value and run the store procedure you can see this very clear what I'm doing here so if I will say print and this one now let's pass let's create the store procedure as it is we are doing step step by step thing and let's execute this store procedure and I'm going to say execute and if I will pass these values here with just as it is let me remove this part now if I will pass this value you will see what exactly happened these commas will be replaced with the comma and the single quotes around it so let's uh run the store procedure with these input and we can see that so I have printed these values so we have a single quotes around all of them but we are missing single code at the end and beginning that we are going to use the next so let's uh declare another SQL variable we are going to say declare SQL and Watcher so we can save our query and this can be Max or 2,000 whatever you like depending on your query size now this one take this part and let's set this value so now we are going to say set at the red SQL we are preparing our complete query with the uh by using this at the region parameter with the single quotes so it can run and give us the required result so this one will be equal to and you put single quotes here and then you come here all the way and then here we need to put remember we are missing one single quote right here that we need to put so I'm going to use three single quotes plus sign here so the two of them is going to be kind of put one single quote here and then we will be using this word region variable that we have used as we have replaced this single quotes multiple uh quotes around it and single quote so that's going to take care of it and now we have plus sign and then you need another three and parenthesis close and single one now if I run this let's take this select uh variable and print this so we want to see what it is going to return us now if I go ahead and alter this store procedure and now let's see what happened now it is done and let's run this one with the values and right now we are not executing anything we are just running it so the very first we pass the value it was uh replaced with single quotes and uh with comma now this is our final query we have select query and in and you can see that each value is U surrounded by those single codes so so it is used as a string so we are good now and next part is that we just need to do one thing we say execute and say at the rate SQL and let's remove this print we don't need print anymore and uh it was just to show you guys like our store procedure how we are working on this one and also remove this print and now go ahead and alter your U procedure what happened now let's go back here say execute here is a parenthesis missing that's fine let's put the parenthesis and uh now we are good and run this store procedure and now you see all those results are coming if I will go ahead and remove the North America now I should see only the results for Asia and Europe so this is the way we will be writing our store procedure we will be handling a simple string and we need to handle uh in this way it can be used as a string values in the in Clause now we are all good here I will have this script in the post as well and you can check the description and uh you can copy for yourself let's go ahead and create a report now so if we go ahead and create a report in here we need a share data source that's pointing to our sales database so we have it if you don't have it you can go ahead and create one and now click on the reports and go to add once you are in the add new item and here create an empty report we call this one multivalue parameter store procedure multi value parameter SP we are good now here on the data source tab right click here add your data source you can create the embedded one that will be local to the report but we can use the shared one as we are pointing to the same database the other reports are and we have it so why not to use it and uh I hate this when I do it all the time and I have to rename it uh I like to have a nice name for this one so always put a nice name for your data sources and data now go ahead and create a a data set right click on the data set add data set and here we are going to name this one D set sale total and select use data set embedded in the report and we are selecting the data source here now click on the store procedure and then select your store procedure and hit okay once you do it you should be able to see the fields here in the data set and the parameter so we see the parameter but we do not see the fields so there is something wrong with this store procedure and in my case what I'm thinking maybe store procedure is not correct so let's go ahead and run the store procedure in SS SMS and what we see it is returning the results so looks good um let's take a look in the query designer if we go to the query designer and here run our store procedure it uh it should run fine so we can take a look and it is running just fine so there is some issue so what what what I was trying to figure out U I have taken the same store procedure I ran on my laptop and it was just working fine to be straightforward with you guys and then uh I'm also on that laptop I'm using um SQL Server 2014 I'm using uh ssdt SQL Server data tools there is no really difference I feel that and this is Windows 8 on this machine I'm also using the same thing but I can't see the fields so that's a there is some problem and what is happening I don't see even uh the the on the database level I have something different going on like some settings but anyways with the I some I did some research and I found out okay there could be scenarios where Dynamic SQL can can give this problem so if you are using a lot of dynamic SQL you you might encounter this type of issues so I I see that in query designer the store procedure is running just fine in ssms it is running fine but the reporting services are not able to get those fields from the store procedure so what one thing uh I was thinking like if I will give the select query at the end and uh that's how maybe make the life EAS for reporting services and let's say we do some extra work and uh by doing it if that that just work fine for reporting and uh that will help so one thing we can do here we can put these result set in the temporary table and then use the select query at the end and let's see if that will work so I'm going to go ahead and say into and say temp table so now we are putting the results set in the global temp table the dynamic SQL runs its own session so if I will put in the temp uh local the say once the session will be done it will be not available in after after the execute so that's what one reason I'm putting in the Global Temp and here it will be available till we have this session open so it is it is going to be open this even after Execute of SQ so I'm going to go ahead and say select star from temp table and also as the store procedure will run multiple times and if it will create one time the temp table it will be available so it will start giving us error so we need to drop this table at the end once we are done with the selecting the records so drop table now we are good let's go back here alter our store procedure let's take a look run our store procedure couple of time just to make sure it is coming fine yeah it is working now come back to the ssdt SQL Server data tools hit okay and even I I have to refresh Fields let's say now we hit okay fine no big deal now still I don't see the things uh refresh field let's provide some value and see if that that will bring the fields so let's see okay so that did work and uh we once we provide AI as a variable uh value it brought those fields for us so you can delete and then recreate it and that should work fine and uh this work for my case scenario there would be scenarios maybe it would not work for you I'm I can't assure you with Dynamic SQL okay this is the best way to do it but uh if you can can do without Dynamic SQL that's another way uh remember I was telling you uh you can go ahead and maybe write a function and then use that function here you don't have to use Dynamic SQL you will be just selecting everything from here and then uh here you will be using that function and providing this as a input parameter to that function that will be uh string function that will take the values such as this one and convert to the uh table so you can use that one and I I thought okay Dynamic seal is one way to do it why not to give a try and show you guys so that's fine let's go back here now we all done here let's insert the table uh and uh bring the fields on second field and I'm going to bring the region just to so show if um things are coming in the right direction and the report is working so let's go back and give us some background color let's expand a little bit so we see our beautiful report okay now here we can see that it is a parameter and uh we can put some value in and it is returning us the records so let's go back and make it multi value parameter so right click here parameter properties and allow multiple values now if we preview our report it can accept multiple values but we don't want to write those values I would recommend go ahead and provide those those values by the query so I'm going to go ahead and right click add a data set and now I will be saying D set and I will say region I'm going to select the distinct values for the region and here we will use the embedded data set okay select distinct region from dbo sale total I I hope this is the right table name okay that's correct name and uh we can take a look from here this is the same table from where we are selecting every record but uh in for our parameter we are selecting only the region so we are good here let me go back and now we go to the parameter again right click parameter properties and available available values so now we are getting the values from the query fine provide the data set name here the set region and value field is region and label fi field is also re region so label is something that you're going to see in the dropdown if you have multiple columns one you can use for the value and the value will be passed to the query and label will be just there in the drop down so you can see the the field how they look in the parameters let's let's assume that if we have uh EU in a value in one of the column and as in the value and na as a value so these are short names and we we have them as a value and in the label field maybe there is another column in the table called North American region and say okay European region and Asian region so those that that big descriptive column can be used as a label and the other column can be used as a value so hit okay preview the report and now click here we can go ahead and select Asia and it should show us the results or records for Asia we are fine here let's go back and select Asia and Europe so two of them and now we are seeing Asia and Europe records and let's go ahead select all of them and take a look so in this case we are seeing all the results coming correct so this is how you will be creating a multivalue parameter uh and store procedure and use that in your SSRS reports thanks very much for watching this video and I'm going to see you guys in next video

Channel: TechBrothersIT

Views: 53,785

Keywords: Use Stored Procedure with Multi value Parameter in SSRS Report, SSRS Tutorial Step by Step, SQL Server Reporting Services Tutorial for Beginners, SQL Server, MSBI Tutorial, TechBrothersIT, Microsoft SQL Server (Software), SSRS, Stored Procedure, Parameter, Multi value parameter, SSRS Training Step by Step, SQL, Server, Top 100 SSRS Interview Questions

Id: 4QI-TVyX2UA

Length: 18min 30sec (1110 seconds)

Published: Mon Jul 20 2015