3

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

Indian
  • 529
  • 1
  • 12
  • 25
Mask
  • 53
  • 1
  • 1
  • 9
  • have you tried using a switch statement instead of a series of nested iif statements? – SFrejofsky May 20 '15 at 11:30
  • I haven't, I will give a try and give feedback. Thanks – Mask May 20 '15 at 11:45
  • The SWITCH function only allows 1 value for "true" of "false" i think.. It's the same as IIF() statement. I took the exemple here from microsoft site: SWITCH(Customer Type = "I", "Individual", Customer Type = "S", "Shop") In my case, i would need that if Customer Type=I, value would be " individual, Indiv1, Indiv2,..." You know what i mean? I need it to give the same result as if I used a filter set as following: Expression: =Fields!Hour.Value Operator: IN Value: 6,7,8,9,10,11,12 – Mask May 20 '15 at 11:48
  • 1
    I am not sure that I am following you. In your question your iif statement returns a comma separated string. I am not seeing the problem with using the switch as you are returning one string value. and evaluating 1 true false statement per segment of your switch – SFrejofsky May 20 '15 at 12:34
  • 1
    It looks like you are misunderstanding the switch function @MaximeCoppée. It allows you to return one value for the first true condition of any number of conditions. And a comma-separated string is "one value". So you can do MyField = "AM", "1,2,3", MyField="PM","4,5,6",MyField="NIGHT","7,8,9". However, that said, I would suggest you try changing the "false" at the end of your nested IIF expression to an empty string. – Tab Alleman May 20 '15 at 13:55

3 Answers3

2

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

Mask
  • 53
  • 1
  • 1
  • 9
1

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

Naveen Kumar
  • 1,541
  • 10
  • 12
  • I think we are going on the right way @Naveen Kumar. I tried this, it correctly display "6,7,8,9,10,11,12" if I put =Parameters!Shift.Value(0) in a text box but in my column filter it doesn't display any columns... As i can't post images here, you can see screenshots following this link: http://s9.postimg.org/ml5depkvj/Filterprod.jpg Here is what the output report should display if for exemple i select "AM" in parameter: http://s14.postimg.org/igusyu8b5/tablix1.jpg When I try your proposition and all the above, it doesn't display anything .. :/ – Mask May 20 '15 at 15:01
  • and it's normal that it doesn't have 9,10 and 11, its because the columns have no data so it doesn't shows it. – Mask May 20 '15 at 15:04
  • It looks like, even if the value output of the parameter is "6,7,8,9,10,11,12" it's not considered the same as if you manually put in the filter "6,7,8,9,10,11,12" with the IN operator (see 1st image in comment above) – Mask May 20 '15 at 15:07
1

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?

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • If Shift (I hope that is the right spelling, not the one you used above :-) ) parameter is multiselect, it would look like this: `=Iif ( ( Join(Parameters!Shift.Value , "," ).Contains("AM") And ("6,7,8,9,10,11,12").Contains(Fields!Hour.Value) ) Or ( ...` – Mike Honey May 22 '15 at 00:26