Search code examples
reporting-servicesssrs-tablixreportbuilderiif

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...

Solution

  • 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 :(