Report Builder IIF () function with multiple TRUE values
I run into a problem when I share some RB report.
I have a table where the columns are the hours of the day and the rows are different. I also have a parameter with three values (AM, PM, NIGHT).
The fact is that if the parameter is set to AM, only columns from 6 to 12 are displayed in the tablix, if it is set to PM, the tablix is displayed from 12 to 18, ...
I can display time intervals (6 to 12) with a filter where I tell it "Hour" IN "6, 7, 8, 9, 10, 11, 12". But it doesn't work when I set the filter value like this:
Expression: =Cstr(Fields!ProdHour.Value)
Operator: IN
Value:
=iif(join(Parameters!Shift.Value) = "AM",
"6, 7, 8, 9, 10, 11, 12" ,
iif(join(Parameters!Shift.Value) = "PM",
"13, 14, 15, 16, 17, 18",
iif(join(Parameters!Shift.Value) = "NIGHT",
"19, 20, 21, 22, 23, 0",
false)
)
)
Do you have any idea how I can solve this? Tried to change every number in Integer but didn't work ...
source to share
I found a working solution:
I had to create 2 new fields in the same dataset as the table, I named these fields "ShiftStart" and "ShiftStop".
ShiftStart value: = iif (join (Parameters! Shift.Label) = "AM", "6", iif (join (Parameters! Shift.Label) = "PM", "12", iif (join (Parameters! Shift. Label) = "NIGHT", "0", "0")))
The same with ShiftStop, but with different values (12,18,0). So with these two data, when I select "AM", ShitStart = 6 and ShiftStop = 12, I can now create a filter to display columns where [Hour] is between [ShiftStart] and [ShiftStop].
Just like this!
Thanks guys for the help! Sorry, I can't Uptvote you, not enough reputation :(
source to share
I would suggest changing your parameter binding like (ID, Value), see screenshot below
Now you can use an expression to get the selected value
=Parameters!ReportParameter1.Value
You can also use the below query to bind the dropdown if you don't want to hardcode
Select ID,Value From
(Values('6,7,8,9,10,11,12','AM'),
('13,14,15,16,17,18','PM'),
('19,20,21,22,23,0','Night'))
tblTime(ID,Value)
I think this is what you are looking for
source to share
I would move away from using the In statement in SSRS Filter Expression. I've only had bad experiences with filters using any operator other than "=", and problems with data types.
My preference would be to filter this data in a dataset query using SQL. However, this is not a question.
In SSRS Filters, from hard experience, I now always set the datatype to "Boolean", operator: "=" and Value: "True". Then your job is to code an expression that only returns True for the strings you want to keep.
It could be something like:
=Iif ( ( Parameters!Shift.Value = AM and ("6,7,8,9,10,11,12").Contains(Fields!Hour.Value) )
Or ( ...
Is the Shift parameter multi-select?
source to share