SSRS report with Multi Value and Null Parameter

Question

SSRS report with Multi Value and Null Parameter

zombi

SSCrazy

Points: 2605
More actions
September 22, 2010 at 5:26 pm

#229616

HI Guys,
I have got a question for SSRS. I am creating a report and i am adding a drop down menu with a multi value parameter. I want this multivalue parameter to allow null as well. Does anyone know how can i achieve this?
I can not select "Allow Null Value" and "Multi-Value" options at the same time. Thanks for your help in advance.
Regards.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

theSuda SSC Eights! Points: 887 More actions · Answer 1

Assuming you are using SQL Query (and not MDX), you can add a NULL value in your Data. No need to use built in "Allow NULL value" option.

e.g.

select CustomerName as ParamCaption, CustomerID as ParamValue

from Table1

UNION ALL

select "NULL" as ParamCaption, NULL as ParamValue

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

zombi SSCrazy Points: 2605 More actions · Answer 2

If I am not wrong then you are saying to add a null value into the table? right ?

Is there any way by which i don't have to add NULL value into the table?

Thanks for your reply.

theSuda SSC Eights! Points: 887 More actions · Answer 3

Look at my example. I did not add null value to the table, but just to the dataset of drop-down list. Use the same query and you can use existing table without any data changes.

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

zombi SSCrazy Points: 2605 More actions · Answer 4

Hi, Thanks for your reply.

As it looks like you are talking me in the right direction. The thing is i am not getting you where and how can i use your code. Can you please provide more details. Thanks for your effort. I will explain my scenario in more details. and then you can advise me that where and how can i use your SQL statement.

For example, I am creating a report with a SQL statement for dataset1,

Select customername, customerID, customeraddress from table1

and i have got a separate dataset2 which i use for multivalue drop down list. which is,

select customername from table1

Now can you please explain in this scenario how can i use your sql statements and in which dataset.

theSuda SSC Eights! Points: 887 More actions · Answer 5

Sorry for being impatient 🙂

So you are actually on right track. Use my statement in Dataset2 (for drop down list)

But to make it work, you need few little modifications:

1. Are you passing CustomerName to query? Why not CustomerID? Matching numeric values is more reliable than matching strings.

2. Assuming you still want to match CustomeName, do this.

Create two columns in your DropDown Dataset (dataset2) instead of one say ParamLabel and ParamCaption like this:

select customername as ParamCaption, customername as ParamValue from table1

UNION ALL

select "Null" as ParamCaption, NULL as ParamValue

This will give you something like this

ParamCaption ParamValue

-------------------------------

Customer1 Customer1

Customer2 Customer2

Null NULL <--- This will be null value and not a string.

So we are adding Null entry on-the-fly and not updating the table at all.

Now use this dataset for parameters setting. Set Label Field = ParamCaption and Value Field = ParamValue.

If you decide to use CustomerID instead of CustomerName then use CustomerID for ParamValue.

I hope I have made this clear. Ask if you have any more doubts.

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

zombi SSCrazy Points: 2605 More actions · Answer 6

Hi theSuda,

Thanks for your reply. With your help and suggestion i was able to add the NULL as a value into the drop down menu but when i was running with selecting NULL for one of the drop down menu then the query simply doesn't return any records.

Here is the Sample query:

Report Query:

SELECT ProductID, Name, ProductNumber, Color

FROM Production.Product

WHERE (Color IN (@name)) AND (Name IN (@level))

@name Dataset Query:

SELECT DISTINCT Color

FROM Production.Product

@level Dataset Query:

SELECT Name AS ParamCaption, Name AS ParamValue

FROM Production.Product

UNION ALL

SELECT 'NULL' AS ParamCaption, NULL AS ParamValue

when i run by selecting NULL for the @level parameter and SOME color value for the @color dataset then query returns 0 records. So it looks like NULL value added but it not giving the right result.

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 7

Hi Zombi,

Does your data contain NULL?

or do you want to retreive everything when NULL is passed?

I'm not understanding your reqiurement

zombi SSCrazy Points: 2605 More actions · Answer 8

HI Guys,

I have got a question for SSRS. I am creating a report and i am adding a drop down menu with a multi value parameter. I want this multivalue parameter to allow null as well. Does anyone know how can i achieve this?

I can not select "Allow Null Value" and "Multi-Value" options at the same time. Thanks for your help in advance.

Regards.

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 9

Copying and pasting your original post won't assist you,

Your previuos reply was

when i run by selecting NULL for the @level parameter and SOME color value for the @color dataset then query returns 0 records. So it looks like NULL value added but it not giving the right result.

What is the right result?

1. nothing(which is what the dataset is currently doing), which is correct based on your setup

2. everything

3. enter own answer

VinceV Right there with Babe Points: 791 More actions · Answer 10

You haven't provided enough details for me to tell why you weren't getting any results but I see one error that should be corrected. From your original question, it seems that you needed to select cases where the Product Name is NULL. In order to make that happen, you should edit your codes as follow:

SELECT ProductID, Name, ProductNumber, Color

FROM Production.Product

~~WHERE (Color IN (@name)) AND (Name IN (@level))~~

WHERE (Color IN (@name)) AND (COALESCE(Name, 'NULL') IN (@level))

@name Dataset Query:

SELECT DISTINCT Color

FROM Production.Product

@level Dataset Query:

SELECT Name AS ParamCaption, Name AS ParamValue

FROM Production.Product

UNION ALL

SELECT 'NULL' AS ParamCaption, ~~NULL~~ 'NULL' AS ParamValue

The reason for the change being that NULL represent an unknown value and you cannot logically do equality tests on unknowns. COALESCE(Name, 'NULL') will effectively substitute the string 'NULL' when product name is NULL and compare it to the 'NULL' string from your drop-down. Of course, this assumes that you don't have any product named 'NULL' in your table.

zombi SSCrazy Points: 2605 More actions · Answer 11

Hi,

I have copied and pasted original question because that's what i want to achieve. and the query which i have posted is just a sample query using adventureworks sample database of SQL server 2008.

For more information, when you use SSRS you can not select the allow null option and multivalue option for the report. So what i want to achieve if there is anyway that even if i don;t select any parameter from one drop down menu then report should run with the parameter passed with the other drop down menu.

In this case i have got two drop down menu NAME and VALUE. If i want to run report with either of the drop down parameter ( This is for user flexibility) then it should work. Thanks for your help.

VinceV Right there with Babe Points: 791 More actions · Answer 12

So to paraphrase your question, you want to show two drop down boxes but you only need the user to select from one of the two? If that's the case, I can think of a couple of solutions.

1) You can add a place holder in each of your two drop-downs. Something like 'IGNORE THIS PARAMETER' and have it as the default value. You would then write a VB expression to generate the SQL based on how the user responded. As long as the user leave that default value checked, your VB expression will leave out the corresponding limit in the WHERE clause. Not an elegant solution though. For example, if the user set both drop-downs to 'IGNORE THIS PARAMETER', your SQL will return all rows. That may be a problem if you have many rows in your table. SSRS does not have much in way of parameter validation support so you won't be able to show a dialog to tell the user to select from at least 1 dropdown (you can in BIDS, but not after it's deployed).

If you have a small number of items in each drop-down, some of the following solutions may work better...

2) Show just one drop-down but populate it with concatenated values of the two database fields. Example, say your two parameters are ItemName and Color, you could show the following concatenated pairs in the single dropdown

Pencil-Yellow

Pencil-Red

Pen-Black

Pen-Blue

..

3) Show both drop downs but by default select all items in each drop-down then let the user deselect what they don't want.

4) Cascading parameters. Show two drop-downs. User will select from the first then SSRS will update the second with just values that relates to the first. From the example above, the first drop-down would have Pencil and Pen. If user choose Pencil, the the second drop-down will update to show Yellow and Red. If user choose Pen, it will update to Black and Blue.

I am just giving some high level ideas. One of these may work for you depending on your exact requirements and the database structure and volume of data you're querying against. See if you can decide on one solution and I can help with more details.

zombi SSCrazy Points: 2605 More actions · Answer 13

This didn't help me.

The thing is actually i have got 5 drop down menu. Sometime user wants to select only 3 of them and leave the other 2. But if user doesn't select the rest 2 parameter then report will not run and says that enter a value because i have selected to allow multiple values for the drop down.

I also tried to add null values as discussed in the forum above by which user was selecting NULL values for 2 parameters but report was not returning any output.

Hope you are getting my question?

VinceV Right there with Babe Points: 791 More actions · Answer 14

So you have 5 dropdowns instead of 2, but the basic question is still the same and some of the ideas I gave still apply. The most elegant and user-friendly solution will involve writing a custom parameter page using html/javascript/etc and use SSRS only as your report processor. Short of that you are probably limited to the options I presented.

I think that some of us were initially thrown off by the way your question was asked - when you said you wanted to select both Multi Value and Allow NULL. The Allow NULL option doesn't mean what you think it does. In your case, you want to completely disregard one or more parameter (not query based on it). That is very different than saying you want to query for cases where the database field is NULL.

So again, short of writing a custom parameter page your options are limited as SSRS will force your users to enter all parameters and each parameter will be mapped to a limit in your SQL. That's not what you wanted. You wanted to completely ignore certain parameters (and limits in the SQL). Option#1 I presented in the previous post will help you get around this by generating the SQL dynamically.