0

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

AskMe
  • 2,495
  • 8
  • 49
  • 102

3 Answers3

1

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")
Steve-o169
  • 2,066
  • 1
  • 12
  • 21
  • @AskMe By the way, this is the second or third question of yours I've answered. It'd be great if you accepted an answer as the correct answer on some of your many questions. Click the checkmark to mark the correct answer. – Steve-o169 May 14 '19 at 18:09
  • Thanks a lot. Sure. letme check and definitely. – AskMe May 14 '19 at 18:15
0

Your IIF statement basically just says "If 'Red' = True" which will always return false.

You need to compare it to the 'cell' where you have your colour expression.

If that cell is called myTextBox then your SUM expression would be something like (simplified) 

=SUM(
    IIF(ReportItems!myTextBox.Value = "Red", Fields!count_of_value.Value, 0)
    )

Alternatively you could repeat the entire colour expression in you IIF statement.

If this does not help, edit your question to show an image of the report design, including any grouping and the location of each expression.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • After using this, I'm getting error, "The value expression for the text box 'Textbox4' uses an Aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dateset. – AskMe May 14 '19 at 12:49
  • Edit your question as I suggested. Without seeing the layout and row and/or column groups it's hard to know what the solution is. – Alan Schofield May 14 '19 at 16:17
  • Added layout and additional details. – AskMe May 14 '19 at 16:36
  • Need to see the design, the row and column groups panel where the expressions are used so we can understand the scope of the expression. If there are not row or column groups, please state that. – Alan Schofield May 14 '19 at 17:43
0

Maybe I oversimplified your problem statement, but I tried with dummy data on my local machine.

enter image description here

=DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now)

New temporary column to catch number

=IIF(DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now)<=72,0,
IIF(Fields!SLAValue.Value<now,DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now),0))

Now column

=now

Color column expression

=IIF(DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now)<=72,"White",
IIF(Fields!SLAValue.Value<now,"Red","white"))

Last Count_Value Expression

=Sum(
IIF(DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now)<=72,0,
IIF(Fields!SLAValue.Value<now,DateDiff(DateInterval.Hour,Fields!SLAValue.Value,now),0)))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • Are you doing sum of price having color red? If you can consider top five records and make its background as 'red'. Then sum those top 5 only( having background RED colour) would be helpful. – AskMe May 14 '19 at 12:06
  • My Sum Expression had Type, If you look at my Count_of_Value it's sum is only with Red Columns i.e only those Price>500. – AnkUser May 14 '19 at 12:11
  • More over, if you see my 1st expression in the question, the 1st column is date. So based on that expression, I'm making the background color decision and I would like to sum of RED background as well as need to get last date of RED background color.Hope, I explained this correctly. – AskMe May 14 '19 at 12:13
  • Yes I got your question, but you are anyhow taking as date Difference which is in hours (I believe) and your hours can be Number. you can use this number as Sum. Maybe to help my point I should change my Dataset as close to your problem statement and show end results to you. But my point was you could easily achieve summing and I showed one simple way. – AnkUser May 14 '19 at 12:16
  • Actually, you have done Hardcoding, that is 'price.value > 500', I can not consider Hardcoding any value as it's dynamic and I don't have control on that. However, will give a try. – AskMe May 14 '19 at 12:23
  • alright then what is your criteria to consider Red or white, There might be some condition which has to be respected. Could you let me know? – AnkUser May 14 '19 at 12:28
  • It's the 1st expression in my question. That is the criteria for color coding.if you can make a date in 1st column, then you may get more clear idea – AskMe May 14 '19 at 12:37
  • Thanks. Will check more details as per your answer. I have added few more details to provide more clarity to my question. – AskMe May 14 '19 at 16:39