[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Conditional formatting using iif

Posted on 2011-05-02
Medium Priority
Last Modified: 2012-06-27
I am trying to format the background of my matrix field using the folllowing iif, but nothing works
except for a 2 part "Red", Green type format. I need an expanded range of "colors to values" to determine the color background.  such as 10 to 20 blue,  20 to 30 red, 400 to 50 yellow,  etc  the value of the field is computed using

=round(iif( Fields!DOMAIN.Value  = "3.1 Security Policy",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 5)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.2 Organisation of Information Security",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 17)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.3 Asset Management",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 4)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.4 Human Resources Security",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 13)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.5 Physical and Environmental Security",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 19)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.6 Communications and Operations Management",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 27)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.7 Access Control",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 18)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.8 Information System Acquisition, Development and Maintenance",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 24)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.9 Incident Management",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 6)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.10 Business Continuity Management",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 8)  * 100) - 100) * -1,
iif( Fields!DOMAIN.Value  = "3.11 Compliance",((Count(Fields!OBJ_ID_R.Value)/(CountDistinct(Fields!OBJ_ID_R.Value) * 10)  * 100) - 100) * -1,"N/A"))))))))))),0)&"%"

and no matter how i break this up into an iif statement i can only get it to work wth 2 colors  any ideas????  
Question by:rschmehl
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35507803
Can you give some more information. I don't exactly understand what you tried so far, or what exactly you are trying to accomplish. Can you upload the definition of the report (copy the .rdl, then rename it to .xml and upload it, EE doesn't support the upload of .rdl files).

Do you know that you can use an expression for the background property as well. You can enter an expression there that returns a color based an the values in your query.

And what version of SQL Server are you using?

Author Comment

ID: 35507858
i can;t upload the report, but i am trying to set the background of the cell in the matrix, which uses the IIf statement i sent to different coloors based on the vale of the cell.  I have tried to modify the iif I sent to something like
IIF( round(iif(.........))))))))) < 50, "red",
Iif(round(iif(......)))))))) >90. green,
iif(round(iif...  )))  >75 and iif(round(iif.....) < 90,  "Blue", "yellow")  I can et on t work for just 2 colors but sure how to use the iif to set up a range of the  values.
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 35510610
Please check the attached example. Rename the file to .rdl and change the datasource to test it.

This has multiple colors. I use an expression for the background color. I use the Swith function instead of nested iif because I think it is easier to read. Also build it up from 50 to 75 to 90 and not from 50 to 90 and back to 75, this makes is much more complicated.

You could also consider using customer code. This is less complex than a bunch of expressions. See
for more information.
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35511765
Please see attached text box property window. In that you can use expression for background image or other purpose. please explore it for more detail.
LVL 101

Expert Comment

ID: 35892791
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question