SSRS Multi parameter with boolean

Question

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'

Answer 1

Hi Lio,

I have created detail level post follow this link below:-

https://msbitips.wordpress.com/2016/04/12/ssrs-multi-parameter-with-boolean/

In brief, 

You need to write dynamic SQL query to get the column name dynamically based on parameter value selection.

Thanks

Prasad

Answer 2

Thank you very much

The table that I give you was only example.

Do you think that your solution is going to work with a table of 1000 rows

Answer 3

isnt this enough

SELECT *
FROM TableName
WHERE ((Driver = 'true' AND @Parameter = 'Driver')
OR (Administration = 'true' AND @Parameter = 'Administration')
OR (Employee = 'true' AND @Parameter = 'Employee'))

And pass the Parameter as it is from SSRS to the query

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

Hi Lio,

Yes, it will work.

replace my sample query with u r table 

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

replace with below query:-

Declare @sqlstr nvarchar(max)
set @sqlstr='select * from Yourtable
where '+ @Position+ '=''True'''
EXECUTE sp_executesql @sqlstr

Thanks

Prasad

Answer 5

Thank you very much

your query works

I tried to extend it like

SET @sqlstr = 'SELECT Company,* FROM myTable
WHERE ' + @Position + '=''True'' AND Company = ''' + @Company  + ''' AND CONVERT(datetime, startdate, 101)  between ''' + @BegDate + ''' AND ''' + @EndDate + ''''
EXECUTE sp_executesql @sqlstr

And I got the following error:

Conversion failed when converting date and/or time from character string.

Answer 6

Hi Lio,

Replace above code with below code:-

SET @sqlstr = 'SELECT Company,* FROM myTable
WHERE ' + @Position + '=''True'' AND Company = ' + @Company  + ' AND CONVERT(datetime, startdate, 101)  between ' + @BegDate + ' AND ' + @EndDate + ''
EXECUTE sp_executesql @sqlstr

Thanks

Prasad