sql-serverreporting-servicesssrs-expressionssrs-2017

sum based on background color ssrs

In my table, Out of 20 records, I'm getting 4 records "Red" based on the below expression:

=Switch(DateDiff(DateInterval.Hour, Fields!SLA.Value, Now())<=72,"White",Fields!SLA.Value<now(),"Red",true,"White")

enter image description here

I would like to sum the "count_of_value" based on background colur "RED".

Here is what I'm trying. However, its showing error.

="The number is " & IIF("Red",Sum(Fields!count_of_value.Value, "DataSet1"),'') & "."

and 

 ="The number is " & Sum(IIF("Red",Sum(Fields!count_of_value.Value, "DataSet1"),'')) & "."

There are 2 things I would like to achieve :

From the 1st column, I would like to get the last date of color "RED". For example, In this case,02/05/2019.

From the 2nd column, I would like to get the sum of numbers that are having color "RED". For example, in this case, 214519

What am I doing worng? How to achieve this? Thanks

Solution

  • Looks like a bit of an XY problem here. Your expression is coloring the cells based on the SWITCH statement. To solve your summing issue, you need to use the same conditional statement that you use to color the cells. Basically, the expression you need should be the following.

    = SUM(IIF(Fields!SLA.Value<now(), Fields!count_of_value.Value, 0), "DataSet1")

    This expression should isolate the same fields that the SWITCH is coloring red, sum them, and add zero if they do not evaluate to true. Since your comment on another answer stated you were getting an error indicating insufficient scope, you need to include the dataset name with the SUM function.

    As for getting the last date value from the other column, you should just be able to use a similar expression but using a MAX function.

    =MAX(IIF(Fields!SLA.Value<now(), Fields!SLA.Value, Nothing), "DataSet1")