SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using IIF in Reporting Services

I found an interesting use for the IIF function in a Reporting Services report this week. I needed to provide a report of users and the schedule date for migrating them to our new workstation image or the date that they were migrated. The people viewing the report are mostly interested in the people who have not been migrated yet. So, I created a grouping level based on a migration flag. For the users who have been migrated, we don't care about the scheduled date anymore. For the users who haven't migrated, well, they don't have a migrated date. I like to add a row in the group header right above the detail line for the labels. So, instead of hard-coding "Schedule Date" and "Migration Date", I used the IIF function to display the correct heading. I also used the IIF function to choose which report field to use for the date. I use the IIF function a lot for conditional formatting. For example, alternating background colors for the detail section. I'm sure there are lots of uses I haven't thought of yet. To use IIF, you supply an expression that can be evaluated to TRUE or FALSE, then a value if the expression is TRUE and one if it is FALSE. Here is an example: =IIF(Fields!Completed.Value = TRUE,"Migration Date","Schedule Date")

Advice from Aunt Kathi

Kathi Kellenberger is a Sr. Consultant with Pragmatic Works. She is an author, speaker and trainer.


No comments.

Leave a Comment

Please register or log in to leave a comment.