Sometimes you will need to use multiple IIF in expressions when creating reports in SSRS, Reporting Services. For the past week I’ve been sitting with SSRS, trying to create a report that will show all licensed Microsoft products per client, with primary user, e-mail and what organization they belong to. This post will just handle a small portion of the report and I might write a few others on this subject later on.
Lets say you have the following structure in your Active Directory:
To get the OU-path on a client from SCCM, you would use the following in SSRS:
“MAX(v_RA_System_SystemOUName.System_OU_Name0)”
If you don’t use “MAX()”, all OUs where the client is registered will be returned.
For example a client in “Site 1” under Organization 2 will return the following:
- config.local/Company/
- config.local/Company/Organization 2/
- config.local/Company/Organization 2/Site 1
When using “MAX()” only the longest path will be returned, and in this case, this is what we want. Otherwise, the column that holds the results, will have every client listed three times instead of just one.
Now, if you were to hand over the report to your boss he or she would most likely send it right back to you since the column that lists the organization will show the complete OU-path of the client, not the actual name of the organization.
To solve this, the following expression will replace the string that is the OU-path and replace it with what every you want, in our case the name of the organization.
=IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 2/SITE 1", "ORGANIZATION 2", FIELDS!FieldName.VALUE)
If the field “FieldName” contains a value that is equal to “config.local/Company/Organization 2/Site 1”, the value will be replaced with “Organization 2”
This is good, but not great, we still have five other OU-paths that we need to change in the report, so how do we combine the following six expressions into one?
=IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 1", "ORGANIZATION 1", FIELDS!FieldName.VALUE) =IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 2", "ORGANIZATION 1", FIELDS!FieldName.VALUE) =IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 2/SITE 1", "ORGANIZATION 2", FIELDS!FieldName.VALUE) =IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 2/SITE 2", "ORGANIZATION 2", FIELDS!FieldName.VALUE) =IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 3/SITE 1", "ORGANIZATION 3", FIELDS!FieldName.VALUE) =IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 3/SITE 3", "ORGANIZATION 3", FIELDS!FieldName.VALUE)
Let’s start with combining the first two expressions for Organization 1. The complete expression will be:
=IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 1", "ORGANIZATION 1", IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 2", "ORGANIZATION 1", FIELDS!FieldName.VALUE))
Now we have created an expression that says; If the value of “FieldName” equals “CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 1” the value should be changed to “ORGANIZATION 1” IF the value of “FieldName” equals “CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 2” the value should be changed to “ORGANIZATION 1” then save the new value to “FieldName”
Continuing on this track, the complete expression that should be entered in the field is the following:
=IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 1", "ORGANIZATION 1", IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 1/SITE 2", "ORGANIZATION 1", IIF(FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 2/SITE 1", "ORGANIZATION 2", FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 2/SITE 2", "ORGANIZATION 2", FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 3/SITE 1", "ORGANIZATION 3", FIELDS!FieldName.VALUE="CONFIG.LOCAL/COMPANY/ORGANIZATION 3/SITE 3", "ORGANIZATION 3", FIELDS!FieldName.VALUE))))))
Important here is that everything is case sensitive, the OU-path is always returned in uppercase, so it needs to be the same in the expression, same with “FieldName”.
It took me some time to figure it out, so don’t be alarmed if you get an error or if it doesn’t work. Just copy the expression into a text-editor and verify that all parentheses are in the right place and that you haven’t miss typed anything.
