Home > Home > Optional parameter usage (either/or first parameter or second parameter) in SSRS

Optional parameter usage (either/or first parameter or second parameter) in SSRS

Got this question from MSDN, link to original question click here

Breif:-

In my report I have two parameters PID and patientName, but user want to enter either PID or patientName. When I preview the report user can only enter one parameter not both. Is there any way  to create two parameter available but users  can enter only one parameter value?

Create Sample report using below SQL query:-

select * from

(

select ‘row1′ id, 1 monthnumber,’Jan’ MonthName,10 val

union

select ‘row1′ id, 2 monthnumber,’Feb’ MonthName,20 val

union

select ‘row1′ id, 3 monthnumber,’Mar’ MonthName,30 val

union

select ‘row1′ id, 4 monthnumber,’Apr’ MonthName,10 val

union

select ‘row1′ id, 5 monthnumber,’May’ MonthName,50 val

union

select ‘row1′ id, 6 monthnumber,’Jun’ MonthName,20 val

union

select ‘row1′ id, 7 monthnumber,’Jul’ MonthName,10 val

union

select ‘row1′ id, 8 monthnumber,’Aug’ MonthName,60 val

union

select ‘row1′ id, 9 monthnumber,’Sep’ MonthName,100 val

union

select ‘row1′ id, 10 monthnumber,’Oct’ MonthName,19 val

union

select ‘row1′ id, 11 monthnumber,’Nov’ MonthName,25 val

union

select ‘row1′ id, 12 monthnumber,’Dec’ MonthName,35 val

)T

where ((monthnumber= case when @monthnumber=” then monthnumber else @monthnumber end)  and (MonthName= case when @monthname=” then MonthName else ltrim(@monthname) end))

and (@monthnumber <>” or @monthname <>”)

It will create two parameters as given below:-

  • Monthnumber
  • Monthname

Set both parameters default value as =” “, it’s shown in below screen:-

1

Run the report , enter the value in monthnumber parameter only as shown in below screen:-

2

Now clear the value in monthnumber parameter and enter the value in MonthName parameter.

It will show the report like below screen:-

3

So, we able to run the report with value in one parameter only.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: