Ensuring 1 of 2 parameters is populated

Question

Ensuring 1 of 2 parameters is populated

jared.smith

Old Hand

Points: 312
More actions
September 30, 2015 at 4:48 pm

#301174

I'm new to SSRS and am wandering how to do something that is pretty straight forward with other reporting tools I have used.
I have two parameters, employee_code and Business_Unit
I want the report to be flexible enough to allow the user to run it for one or the other...BUT....either one must be selected.
How can I ensure that one or the other is selected before processing....
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 1

jared.smith (9/30/2015)
I'm new to SSRS and am wandering how to do something that is pretty straight forward with other reporting tools I have used.
I have two parameters, employee_code and Business_Unit
I want the report to be flexible enough to allow the user to run it for one or the other...BUT....either one must be selected.
How can I ensure that one or the other is selected before processing....

Well, the easy solution is to allow both parameters to be blank so that the report will still run, and then do something like this in the header:

=iif(Join(Parameters!employee_code.Value,",") = "" and Join(Parameters!business_unit.Value,",") = "", "Hey buddy, pick a parameter!", "Normal Report Title")

I don't know of any way to force SSRS to require one parameter OR another; you can obviously make it require any given parameter just by not allowing NULL or blank.

jared.smith Old Hand Points: 312 More actions · Answer 2

Hi, thanks for that. Its not really ideal as the query itself needs to be filtered.

I'm surprised there is no way to achieve this....other than creating two versions of the report, one for each parameter.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

This works, but may not look the way you want...

Create your two parameters and allow both to have blank or null values.

Create a Dataset, with this Query:

select case when nullif(@p1,'') is not null or nullif(@p2,'') is not null then 'Valid' end as result

Map the parameters in the dataset @p1 to Parameter 1 and @p2 to Parameter 2.

Create a third report parameter which does not allow blank or null values.

Set its available values to be sourced from a dataset and choose the one you just created.

Set its default value to be sourced from the same dataset.

The dataset will only contain a value when at least one of the main two parameters has a value, preventing the report from running (parameter 3 requires a value) until either of the first two parameters is populated.

You can give the 3rd parameter a prompt, such as "Please enter at least one value" if you like.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jared.smith Old Hand Points: 312 More actions · Answer 4

That's the tom-foolery I was after 😀

Thanks, I'll give it a try now

jared.smith Old Hand Points: 312 More actions · Answer 5

This is exactly what I'm after. Thanks for that.

It works fine if both filters do not look up a data set for their available values. If one needs to do this, then it still requires an entry despite saying it can have null or empty

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

jared.smith (10/4/2015)
This is exactly what I'm after. Thanks for that.
It works fine if both filters do not look up a data set for their available values. If one needs to do this, then it still requires an entry despite saying it can have null or empty

If you want NULL to be valid for a Parameter that takes it's available values from a dataset, you need to include a NULL in the dataset, otherwise it is not a valid value.

e.g.

select Label, Value

from SomeTable

union all

select '', NULL

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);