CODEWITHSUNDEEP
reporting-servicesssrs-tablixreportbuilderiif
Mask
Mask

Reputation: 53

report builder IIF() function with multiple TRUE value

I'm encountering an issue while develloping some report on RB.

I have a tablix that where the columns are the hours of the day, and the rows are different products. I also have a parameter with 3 values (AM, PM, NIGHT).

The point here is that if the parameter is set to AM, the tablix only display columns from 6 to 12, if it's set to PM, the tablix display from 12 to 18,...

I can display time intervals (6 to 12) by using filter where i tell him "Hour" IN "6, 7, 8, 9, 10, 11, 12". But it doesn't work when i set the filter value as following:

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 could solve this? Tried to change every number in Integer but didn't work...

Upvotes: 3

Views: 33606

Answers (3)

Mike Honey
Mike Honey

Reputation: 15017

I would abandon using the In operator in the SSRS Filter Expression. I have only had universally bad experiences with Filters using any operator apart from "=" and also issues with datatypes.

My preference would be to filter this data out in the dataset query using SQL. However that is not the question.

In SSRS Filters, from hard experience, I now only always set datatype: "Boolean", operator: "=" and Value: "True". Then your challenge is to code an expression that only returns True for the rows you want to keep.

That 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?

Upvotes: 1

Mask
Mask

Reputation: 53

I found a working solution:

I had to create 2 new fields in the same dataset as the table,I named those 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")))

Same with ShiftStop but with others values (12,18,0). So with those 2 data, when I pick "AM", ShitStart= 6 and ShiftStop=12, now i can create a filter to display columns where [Hour] is between [ShiftStart] and [ShiftStop].

Simple as that!

Thanks guys for you help! Sorry I can't Uptvote you, not enough reputation :(

Upvotes: 2

Naveen Kumar
Naveen Kumar

Reputation: 1541

I would suggest change the binding of your parameter like (ID,Value) see screen shot below

enter image description here

Now you can use the expression to get selected value

=Parameters!ReportParameter1.Value

You can also use below query to bind your dropdown, if don't want to hard code

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 that is what you are looking for

Upvotes: 1

Related Questions