Home > Home, MSDN, SSRS > SSRS Multi parameter with boolean

SSRS Multi parameter with boolean

This question originally posted on MSDN. Click here to get the original question link.

In brief:-

Multi parameters with boolean value

I have parameter ‘Position’ with values : Driver , Administration ,Employee

MyTable

ID  Driver    Administration Employee

1        true       false                       false

2         false      false                     true

3        true         false                    false

4        false         true                    false

these are three columns with boolean values
How can I create a query to be able to pass the parameter Position 

and if the position is driver the result should be – table with  two records id: 1 and 3

            if administration or employee – table with one record

        select * from MyTable where Driver = ‘True’

We need to write dynamic query to solve this, as value from report parameter will come as column name .

Create a report parameter  ‘Position’  with specify values as Driver, Administration employee as shown in below screen:-

1

Now write the SQL query as Dynamic query as given below:-

Declare @sqlstr nvarchar(max)
set @sqlstr=’
select * from
(select 1 ID ,”true” Driver, ”false” Administration, ”false” Employee
UNION
Select 2 , ”false”, ”false”,”true”
UNION
Select 3,”true”,”false”,”false”
UNION
select 4 ,”false”,”true”,”false”

)T
where ‘+ @Position+’=”True”’
EXECUTE sp_executesql @sqlstr

@Postition value is passed through report parameter and  which become as column of the table and we check “True” condition.

 

Then run the report , select Driver is the value

1

 

run the report with different parameter value :-

1

 

So it working, if you like it or you need further help  please add comment.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  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: