Report Builder- Nested If Statements with Multiple Values to Categorize

Question

I am working in Report Builder and having issues creating a calculated field to categorize data from another column.

To simplify and explain my goal:

I’d like to create a calculated field with 4 distinct categories and I’m assuming the best way to do that is a nested if statement. Feel free to correct me if that is not the best function to use.

Category 1: Let’s just call it “A”
Category 2: “B“
Category 3: “C“
Category 4: “D”

Values from the other column:

Simplified Example-

• Numbers 1-10 would be category A,
• numbers 11-20 would be B,
• numbers 21-30 would be C,
• numbers 31-40 would be category D

However in my particular case the values aren’t nicely organized in those 10 consecutive ranges. For example, I have a 33 value that would be an A category, which makes it so I can’t use the greater than or less than operators.

Having explained my issue and goal- my question is how to write the syntax for an if statement when I have multiple discrete values that aren’t neatly organized in consecutive numerical order?

I hope this question makes sense.

I tried using just one argument to get it going and got stumped when it didn’t work:

Iif(field data = 1,2,3,4,5,6,7,8,9,10,33, “A”, “Other”)

It doesn’t work with the commas and I tried inserting the Or Operator between each value and that didn’t work either.

Thanks for any syntax tips you can provide.

Answer 1

There are a few ways you can do this.

---

Option 1: In your database design

The best way, in my opinion, is to do this in your database. Create a table with these values/category pairs and simply join to that whenever you need to include the categorised view of the data.

---

Option 2: In your report design

If you really have to do this in the report design, then using SWITCH() will probably be easier, certainly to read.

Given your second example, and expanding it a little you could do something like this...

=SWITCH(
        (Fields!myData.Value >=1 AND Fields!myData.Value <=10) OR Fields!myData.Value = 33, "A",
        (Fields!myData.Value >=11 AND Fields!myData.Value <=15) OR Fields!myData.Value = 34 OR Fields!myData.Value = 39, "B",
         True, "Other"
        )

SWITCH uses pairs of values, when the first value in the pair evaluates to true the seconds value in the pair is returned.

The final True, "Other" acts like an else. If not previous criteria matched, then the final pair always evaluates to true so "Other" would be returned.