SSRS Tutorial 40 - Default Multi Value Parameters are Not Selected in SSRS Report

welcome to tech brothers Adama today we are going to learn a very special scenario where we have a multi-value parameter and then we have set some default values for that of parameter 1 once we set those of values of what happened we do not see any record shown in the table or detail so we need to figure that out so let's go ahead and create that reporter and then we will figure out them so let's go to the SS MSM sequel server management studio and run our query so we have this query that returns us some results and here is our parameter that flew where that's what we are going to use them as a multi-value parameter and these are the records so we will select any random columns from here so let's take all of the columns and go ahead and create a reporter now here in the SRS projector we have a shared data source oh and it is pointing to the sales database so we are good our table is in the sales database so we can use that one now come back to D SS DT sequel server data tools and if you don't have it you can right click here and the create your shared data source so what you have to do you have to provide the server name and the database name let's go to the reports now and here we will be selecting new item select your empty report and give some proper name in our case we are going to say default values multi-value parameter and I will say not working add empty report will be created here we have to create embedded data source or we can use the shared one so I'd click here a data source and in our case we are going to use the shared one so I'm going to rename this one I will call this one the RDS sales and here we will select in sales data source so that's our share data source so it ok and next part we have to create a query for our reporter so we will be using this query and that will return us some results that's called data set so we call it D set and I'm going to call this one say hello we are all good here is going to be embedded only used for this report and we call it these sales that's our data source and let's paste our query here now to make this a query or report multi-value the parameter now what we have to do we have to write one line here we say where regional in and then we have to provide the parameter name so we can call it at the rater region name you can provide any name what you like but therefore multi-value you have to say in that's how you do it if you are creating single value you will be same equal so that's the difference now we are all good let's come back here say insert and the table now table is inserted and let's bring couple of columns and I'll actually more than couple so I want to more bring region here in this one and the lesser bring a product name here just to at least our report look some something like report instead just have two columns now we are all good here just click here make it bold give some background color to the header columns and review the report once you preview the report you will see a a parameter is created up and we can provide some value if I will provide the Asia it will return me Asian records oh now if I put comma and say Europe it should return me records too because it is multi value by right now it's not multi value to make it multi value we have to go to design and go to the parameters and here we go to the region name parameter properties and here we have to tell her okay this parameter is multi valium I would say at one point maybe SSRS team should consider that okay and say ok if they're using in clause and the you know those from read from there and make it to multi value so I mean I don't know but anyways that could remove and this tab you could have remove it and they just automatically select that part okay I saying okay as if you are using in class in your query which is going to create a multi value parameter and you don't come back here and change it but who cares go to the hit okay not the view it here now it is multi-value if I go ahead and provide some value and now if we go and we report it is returning us all the records for multiple region so maybe this is a great idea to write all those names here but at some time if we have client names and all that maybe and you have to write ten of them then it will take some time and that we make mistakes with commas and enters and the death that can mess up our results so how about there having a drop down for this is a multi-value parameter now go ahead and create a new data set in this data set we are going to call okay do you data set region so we call it B sorry region and this is going to be select the data source since like distinct region from give your total sale we are all good here this is a returning order let me copy this one and show you what exactly it is returning so let's place this one select it is returning except for rakers one of them is Nava and go ahead and use it or hit OK now we can preview our reporter the very first thing here is it did not take that data set automatically so we have to go and map that result setter so go to the parameter again properties and here you will say available values get values from the query and the data set region ok values come in region and the label is also regional so it ok now I if I go ahead and preview my report I had the drop down to be very first thing what happened that knowledge missing so we can't really select the null why because the SSRS don't let us select the null and that's the reason so we can't see the null here so what we can do we can go ahead and maybe we are missing some property let's go to the parameters and say parameter property alone all values of so if we do that maybe it will help so it okay so now we see that that we cannot use multi value parameter including the null values so that's a restriction that's why it did not show that non value in the drop down even our result is a returning the nala with the you can see that the quad is returning for results and one of them is that no so that's the reason we can't do much about it I have a video on that how to use a tunnel in multi value parameter so you go and go ahead and watch that video but here we are going to play a little different so we are going to see now it's fine we don't care about now and we are fine we would never wanted to get actually think about that scenario we don't want to get all the any result that has nava so we are fine with this report but one thing I want to do every time I come here I want to select all the records and then preview the report or why not set some default values them so let's go to design and here we'll go to parameters and say parameter values available values and then we have a default values err and you guys have seen that in the available values I have used d set region in the default I can also use the same data set so D region desert region and the region that's the value it will be taken up so now hit OK and the previews a reporter now all the sudden what happened it should bring some values so it is not shown as any results so if you select all of them these are same values that our parameter has other SS ldt or real studious is kind of stuck and i hope i could come back okay so it's in my trousseau real studio is busy come back up so I killed the processor or veal studio and then reopen it up so if you guys remember we were taking a preview and with default values it did not show us anything up but we can go ahead and select all the values and see if it returns them so we can see that report is working just fine but the fall part is not working up so let's go to the parameter one more time and right click parameter properties and the available values we are using the same data set so we I shouldn't be using a D set regional in default we are using still the same data set there is no difference between them but when we will go ahead and preview the reporter for default is not taken those are values of if we go ahead and select it is shown as to everything so the problem is them if you remember here we have a query that we use for a deceptive region and one of the value is analog SSRS does not accept a null value for de or for the multi value parameter so that's the reason up here in the earth preview if you guys take a look it never showed the null so that's fine but if we will encourage it to use that value in that default it will not show us the results so that's the problem so if I go ahead in the data sector and now we go to the d set region and say were region is the not no so now we're getting only the values which are not now it okay and now preview the report and it came just fine so it took the default values now so if you have a null value in your parameter values for multi value parameter in the defaulter you will have some problems or so you have to eliminate that none so thanks very much for watching this video and if you want to use a null maybe you like to convert that as a stranger and in the parameter as well as in the data main data set the remainder act whatever you detail data set whatever you call which one is returning all the field is set to chain that the some string value say gnarley' string value and then it will be comparing and it will bring in all of that so I will encourage you go ahead and watch that video where I have created a report how to use a null value in the multi-value permitted in SSRS and I have showed that technique how to handle those null situations thanks very much for watching this video and I will see you next video

Channel: TechBrothersIT

Views: 20,322

Keywords: Default Multi Value Parameters are Not Selected in SSRS Report, SSRS, Tutorial, SQL Server Reporting Services, SSRS Training, Parameters, Default Value, Available Values from Query, SSRS Parameter Errors, MSBI, Free Training Step by Step for SSRS 2014, SQL, Server, Database

Id: WUnBl0PrWpE

Length: 11min 3sec (663 seconds)

Published: Tue Jul 21 2015