SQL Reporting Services - Help with a calculated field

Question

My company recently decided to transition from Crystal reports over to SSRS. Needless to say, I am scrambling to catch up to speed.

I need some assistance with creating a calculated field. In my Crystal report, I created the following formula and then in turn grouped on it:

If {aaUDS7;1.ObsValue}  > "1" and {aaUDS7;1.ObsValue} < "7" then "1. < 7" Else
IF {aaUDS7;1.ObsValue}  >=  "7" and {aaUDS7;1.ObsValue} <= "9" then "2. 7 to 9" else 
IF {aaUDS7;1.ObsValue} > "9" THEN "3. > 9"  ELSE "3. > 9" 

I need to mimic this in SSRS and not sure how to handle it correct. Any insight or help is deeply appreciated.

Answer 1

It appears that NESTED IIF-s may do the job. Link:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/13dc06bd-3387-494b-a9b4-ee382a0d5c84

Expression examples link, including IIF-s:

http://msdn.microsoft.com/en-us/library/ms157328.aspx

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

I tried the Nested IIF and got a message indicating "Wrong Number of Arguments"

My attempt:

= iif Fields!ObsValue.Value  > "1" and Fields!ObsValue.Value < "7" then "1. < 7" Else
iif Fields!ObsValue.Value  >=  "7" and Fields!ObsValue.Value <= "9" then "2. 7 to 9" else 
iif Fields!ObsValue.Value > "9" THEN "3. > 9"  ELSE "3. > 9"

Answer 3

Following is the basic IIF syntax:

IIF ( condition, truevalue, falsevalue)

Assigning values to >=100, 10 - 99, below 10

=IIF(Fields!Field1.Value >= 100, "Expensive", IIF(Fields!Field1.Value >= 10, "Moderate", "Cheap"))

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 4

SQLUSA -

I appreciate the help. I am at a loss as to how I set my coding up to match SSRS conventions. Can you assist me?

Answer 5

You can test the following NESTED IIF expression and fix it if needed:

IIF (Fields!aaUDS7.Value > "1" and Fields!aaUDS7.Value < "7", "1. < 7",
IIF (Fields!aaUDS7.Value  >=  "7" and Fields!aaUDS7.Value <= "9", "2. 7 to 9", 
IIF (Fields!aaUDS7.Value > "9", "3. > 9", "4. <= 1" )))

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 6

This should do the trick

=IIF(Fields!ObsValue.Value > 1 AND Fields!ObsValue.Value < 7,"1. < 7"
, IIF(Fields!ObsValue.Value >= 7 AND Fields!ObsValue.Value <= 9, "2. 7 to 9"
  , IIF(Fields!ObsValue.Value > 9, "3. > 9", "3. > 9)))

The IIF in SSRS acts like a function where the first argument is a boolean and the second two are possible outputs.  Since the last IIF always returns the same thing, you could use this instead

=IIF(Fields!ObsValue.Value > 1 AND Fields!ObsValue.Value < 7,"1. < 7"
, IIF(Fields!ObsValue.Value >= 7 AND Fields!ObsValue.Value <= 9, "2. 7 to 9"
  , "3. > 9))