Post reply

SSRS Matrix - Conditional expression to change Background color based on value

  • March 15, 2022 at 5:50 pm

    #4000101

    I have a matrix in my SSRS 2016 reporting services report.  I am trying to set a conditional expression on background color for the sum(field.value).  This field is formatted as a percent.  Here is my expression which does appear to have any errors:

    Background Color Expression:

    =Switch(Fields!PicturesbeforeFirstSignoutCount.Value<.80,"Red",Fields!PicturesbeforeFirstSignoutCount.Value >= .80 and Fields!PicturesbeforeFirstSignoutCount.Value < .95,"Yellow",Fields!PicturesbeforeFirstSignoutCount.Value >= .95,"lightGreen")

     

    Problem:  It turns everything "lightgreen" even when their should be yellow's and red's in the list.  there is one field that is highlighed as Red, but it should have been Yellow.

    I tried to set this in the field property for background color expression.  That just made everything "light green".

    I changed it to the fill expression in the matrix Text box.  Now I get mostly everything green except the one red field that should be yellow. I checked my numberic values and they are coming across in decimals points when I change the format back to number

     

    Any help would be appreciated.

  • March 16, 2022 at 6:10 pm

    #4000808

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • March 16, 2022 at 7:20 pm

    #4000828

    I'd suggest you create a column for the back color in the report dataset SQL/stored procedure as a CASE WHEN statement, e.g.;

    CASE

    WHEN PicturesbeforeFirstSignoutCount <.8 THEN 'Red'

    WHEN PicturesbeforeFirstSignoutCount BETWEEN .8  AND .95 THEN 'Yellow'

    WHEN PicturesbeforeFirstSignoutCount > .95 THEN 'Light Green'

    END AS BackColor

    Run that in SSMS if you can to check it produces the correct results, then set the report cell's back color to the dataset's BackColor value

  • March 24, 2022 at 7:37 pm

    #4005574

    Try this: I believe you may be better off using the double IIF:

    =IIF(Fields!PicturesbeforeFirstSignoutCount.Value < .80, "Red",

    IIF(Fields!PicturesBeforeFirstSignoutCount.Value >= .80 OR Fields!PicturesbeforeFirstSignoutCount.Value < .95 "Yellow",

    IIF(Fields!PicturesbeforeFirstSignoutCount.Value >= .95, "LightGreen", "White")))

     

    OR if you want to stay with the SWITCH:

    =SWITCH(Fields!PicturesbeforeFirstSignoutCount.Value < .80, "Red",

    Fields!PicturesBeforeFirstSignoutCount.Value >= .80 AND Fields!PicturesbeforeFirstSignoutCount.Value < .95 "Yellow",

    Fields!PicturesbeforeFirstSignoutCount.Value >= .95, "LightGreen", "White")

     

    This should go into the text box properties, Fill expression.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply