SSRS Tutorial 39 - Show Null value for Multi Value Parameter in SSRS Report

welcome to tech brothers with Ahmed today we are going to learn how to use the null value for multi value parameter in SSRS report so we will create a simpler tabular report and then we'll use now value another multi value parameter so let me show you carry that I'm going to use up so here is our query that will be used enough it is returning as some records and one of the record with the region is the null and the rest of that we have the values for the region of so we are going to create a sail reporter so we have sail first for a salesperson first name last name and some products sold by that person and then we have region M so we are going to use a region as a parameter and we will use this one as multi value parameter so let's go ahead and take this query and create our report so open SS DT sequel server data tools and now in the report projector we need a share data source so here so we have already created the share data source so that's pointing to the sales data base them you can create a embedded data source as well when you want but if you are creating multiple reports from the same database I recommend using a shared data source so now click on the reports and the add new item and here you select a report it will create empty report let's call this one null value and we say multi-value reporter add the very first thing we have to do here we have to create a data store so so we will be right click in here and add data source so this window can be used to create embedded data source over with this can be used to point to your share data source so in our case we are going to point or use as a reference so we'll be using sales that's our shared data source now you go ahead and provide there some reasonable name so the S sales is a reasonable name for us yet okay next part will be creating a data sector that is that is a result Saturn by the query your store procedure so here we are going to call this one D setter and we call the sales total now we will be using this one as embedded them so go ahead and select the data source and paste it here now once you paste your query here let's create a parameter so we have to do one thing we'll say we're regional in and then we say at the rate region name so that's our parameter name first of all you will be saying after where you will be providing the field name in our case is regional so we provided that one and then we have to say in and then have the parentheses around our parameter so this is this is multi value parameter so what if we provide multi value to this parameter then our query should be able to handle that we are all good here right click here and then get the table so let's get some of fields from here first name last name for deck name and then one more field I want to get is regional so now we are good let's move this one on this side remove this column we don't need that and now we can go ahead and make our Tupelo fancy and here this is looking good click here and bold and a background color so you change that one and now preview the reporter our report is looking good this one is is coming from other reports so don't worry about that I will fix that and here we have to provide the parameter value now we can go ahead and provide Asia and if you guys take a look we are getting is your results even up if I will go ahead and say Europe er let's see if this worker so now you can see that this is not except in a multi values because this parameter is defined as of now is the single value so we have to make some changes to accept of multi values so let's go to the design go to the parameter and right click go to properties and then here we have to say allow multiple values once we do that we can hit OK come back here and now we can go ahead and a writer we say Asia and then we have to put Europe and now we are good to review the report that should bring us some results from both regions so now it is bringing us Asia and Europe results and except in a multiple values er this is greater but every time you have to type it and sometimes we do make mistakes them so especially in my case we have three agents there by n think about a customer name or some other parameter that a there are chances you will make mistakes when you write a list of customers here so why not use a drop-down to provide the values them now let's go back to the parameter and then very first thing what we want to do let me come back actually to the preview and one of the values if you guys remember we have in the table is the knowledge so first of all we want to see if we pass the null value to the parameter what happened so let's go ahead and open it here we say right now and now the view reporter but did not return us anything of this null what we have written here is the string it's not the null that we use the MD table oh so it is a string value and that's why I did not return anything so we have to do some tricks here first of all one thing we notice here in the parameters we are if we go to parameter is saying Allah or null value so maybe this is what we are not using and that's why our null is not working so why not click here and say okay so when we do it except or al our null values it is saying okay your multi value parameter cannot include null values so that that's really kind of limitation we have null values why not to use it and how to use it so anyway so this limit this part is gone so we have to come up with some trick up so next part what I would like to do I'm going to create actually a Dallas that pride the drop down for the region name and then we are gonna handle the null so let's go back here double click sorry right click and say D region the region and LSA uses one embedded data set and now we say select distinct regional trauma give you a sail total total sale actually take a look on the table name so we have total sale that's correct and one more time I would like to tell you guys here if were I'm gonna take this query as well and I will show you in the SSMS what happened so if you run this query you are getting four values so now let's go back here and to say ok and here we will be going to the parameter properties and providing the that values to the parameter so we'll say available values and here we'll say get values from the query and this is D regional so get the values from the data regional data set region so region and label is also region so label is the party one shown in the drop-down and value is the actual value of that that will go to the query so in this case that we have a label and region is the same column so we can use that one it ok now we go ahead and take a look and here we have Asia Europe and North America there is no null value come in here so what happened when we run the query in the SSMS yes it has the four values it has an all better in the report it is not showing those results especially if null values not coming here even we are setting the or getting the values from the regional data set them so that that's a problem right because in the multi value when you have set the multi-value report does not ask for knowledge so it did not even show the novel so that's fine we understand now ok so let's go ahead and check some of their values and we can see all of the values and their results are coming fine but we still couldn't figure out how to use the novel so one thing I would want to do here you want to go back to the regional that's our parameter values and here how about if we convert this to some value if I will say is now if my region is give me something like close to Nala so I would say okay give me this Nala so if you guys see here I'm using a this one has a string value so I have put single quotes around it so it's a value now so it's not the null it is the string dollar so it's literally like it is your name you write in the stringer and there is not equal to null this is just to show you so we are kind of tricking it so it's not actual Nala it's a and you ll it but it is a string value so it okay let's give se as a region otherwise it will complain now we are okay and go to preview and that now we in the drop-down we see the Nala and we see their values as well so let's run out of order and we see that we we can see all the records but one of the record where region was neither that's not showing them so still we did or we were able to provide null value from the parameter but does not have them handle the in actual query so we are going to go ahead and take a look on that one so now I'm going to the main data set and double click here and say okay well now now in the parameter we know that the parameter is going to be is your Europe North America or a string nalva so that we have a value if oh I'm comparing on this side right if I will use the same expression here say is now comma and when the region is nala I want to handle or what I want to replace with the this is spring up so I want to replace with this null stranger so you can write even an army or you can write anything but in our case we want to show the null in the drop-down that's why we are writing a null string here so this is it now we go ahead and take a look go ahead and select values and we are able to get that record John Smith sold a TV and the value is null here now one thing we do not see the null when you hear nulls will be converted with blank space in the SSRS so if you are interested or your company want to show something na or maybe just the null word itself or we have to write some expressions let's go to the design and let me show you real quick oh so right click here and go to the expressions and here we'll be using them some if if statement so we say if for else the kind of thing so here we say III if that's going to work like if else and here that's now I'm saying if this value is not denim that means is now is not ten then do but I can go ahead and provide the null here so I can write an A or I can write the string null here comma if there is a value then I want the value so I want this value if there is no value I want to nog and otherwise I want the value hit okay preview the reporter run the report and now you see that the everything is coming just fine and we see that ok the last value is a nala now maybe we want to color this one has a blue or something that will make more sense okay this is null and it is different from others so let's go back up to the seller here under press f4 that will take us to the properties and now we can go to the founder and the theater is the color is black so I'm going to click here go to the expressions and in the expression I'm going to use the same expressions of so remember we want to say check ie if so if for our field of let's say region is not enough so if this field is nothing what I want to do I want to have this founder as blue okay if that's not right so if there is a value then I want the black so that's how I'm writing my expressions if this expression is true this is what going to happen and if not true for false this is good for this part is going to be displayed or run so now if the color if there is no value we want to describe blue as we have already do to place that value with the novel so our knowledge should be coming up with the blue and if there is a value I want the black color for data phone so so hit okay go back here and then let's select everything now we see that all the values will come in correct and the last values come in as Nala we can see that and it is the blue so that's how you will be creating a multi value parameter and able to pass the null value in the drop-down thanks very much for watching this video and I will see you well in the next video

Channel: TechBrothersIT

Views: 19,701

Keywords: Show Null value for Multi Value Parameter in SSRS Report, SQL Server Reporting Services, SSRS, Parameter, Null, Vaules, Multi Value parameter, SSRS Expressions, Conditonal Formatting in SSRS report, Training, Multi, Value

Id: dNur8q0WvD0

Length: 13min 31sec (811 seconds)

Published: Tue Jul 21 2015