0

As a precursor to this i read: SSRS Conditional Reporting, however I have tried and I'm just not getting anywhere.

I originally tried supplying the data in the table query using a switch statement, which failed, so i have now brought all the data into a table, and now i just want to run a switch statement for Age Ranges and Count the age ranges!

Data is: [Customerid] = int32 [age] = int32

I have created a table in my report:

enter image description here

I have tried: iif statement:

=iif(Fields!Age.Value < 18,"< 18", 
iif(Fields!Age.Value <30, "18-30", 
iif(Fields!Age.Value < 45, "30-45", ">45")))

enter image description here This is obviously not working, as ages 16 arent appearing in the <18 section

I have also tried this with a switch(expression,string) expression and not having any luck there either!?

Ideas?

Glenn Angel
  • 381
  • 1
  • 3
  • 14
  • Your expression is correct, you can check it by adding a column next to age to check the expression. I think that maybe there is something wrong with your group values – niktrs Sep 01 '17 at 07:46
  • Thanks @niktrs. So i have one group which is the iif statement, and then the subgroup under that is just the default details group.. The IIF statemnet should be the group expression shouldnt it? Thats what i have currently – Glenn Angel Sep 01 '17 at 08:02
  • Yes the expression should be set both as group expression and group value. What about the column named group3? Is it part of the detail? – niktrs Sep 01 '17 at 08:17

1 Answers1

2

Your group expression should be 

=SWITCH(
Fields!Age.Value <18, "< 18",
Fields!Age.Value <30, "18-29",
Fields!Age.Value <45, "30-45",
True, ">45"
)

Report design something like ...

enter image description here

The expression in column 1 is also the same as the group expression.

This gives you this as output (based on your sample data) enter image description here

You'll need to probably do something with the group order but I'm in a rush !

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • omg you have made my week. So i believe what i was doing wrong in my switch expression was using AND (eg value < 30 and value > 18) etc. I still dont know why my IIF statement didnt work, but either way, THANKS YOU. Ill look into group ordering now :) OMG im so happy – Glenn Angel Sep 01 '17 at 08:46
  • 1
    The group order expression can probably just use the same `SWITCH` expression but replace you age bands with numbers (swap "<18" for a 0 "18-29" for a 1 etc and that should (untested) do the job – Alan Schofield Sep 01 '17 at 09:05
  • yup did that a few mins ago :) Thanks again – Glenn Angel Sep 01 '17 at 09:06