Conditional formatting using iif

Posted on 2011-05-02
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
    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

    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

    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
    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 100

    Expert Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (  This article explained how …
    Written by Valentino Vranken. Introduction: In a previous article ( I announced that I would writ…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now