SUM only "visible" rows

Question

I am using an expression to hide values in a row based on a date field ( if date is bewtween this date and that date show value, otherwise hide it). My problem is now I need to sum all the values, even though some are not visible the sum is still adding them. Is there an expression to check for only "visible" rows??

Answer 1

You can achieve that by duplicating the visibility condition in the Sum aggregate to get conditional aggregation.

 

For example:

Code Block

=Sum( iif( <use the condition of the Visibility.Hidden expression>, 0, Fields!A.Value))

 

 

 

 

-- Robert

Answer 2

Not quite getting it. Here is my field in detail row:

=Fields!CodePurch.Value

then I hide it with this in the visibility property:

=iif (Fields!CodeBill.Value >= Parameters!Startdate.Value and Fields!CodeBill.Value <= Parameters!EndDate.Value, False, True)

 

Here is my grouping with the same visibility condition used above applied to it:

=SUM(Fields!CodePurch.Value)

 

Now I need to Grand total it on footer Minus the invisible data??

 

 

 

 

 

Answer 3

Use the following expression:

 

Code Block

=Sum( iif (Fields!CodeBill.Value >= Parameters!Startdate.Value and Fields!CodeBill.Value <= Parameters!EndDate.Value, Fields!CodePurch.Value, 0) )

 

 

 

 

-- Robert

Answer 4

Robert,

This expression works fine in the grouping subtotal

 

Code Block

=SUM(iif (Fields!CodeBill.Value >= Parameters!Startdate.Value and Fields!CodeBill.Value <= Parameters!EndDate.Value, Fields!CodePurch.Value, 0))

 

But now I need to do a grand total in the table footer and thats where i cannot figure it out. If I use the same code it throws throws back #error and if I just SUM(Fields!CodePurch.value) it it counts the actual data that is not masked by the preceding code thus making the figures wrong. Do you understand?

Answer 5

johnnysako wrote:

Robert, This expression works fine in the grouping subtotal   Code Block =SUM(iif (Fields!CodeBill.Value >= Parameters!Startdate.Value and Fields!CodeBill.Value <= Parameters!EndDate.Value, Fields!CodePurch.Value, 0))   But now I need to do a grand total in the table footer and thats where i cannot figure it out. If I use the same code it throws throws back #error and if I just SUM(Fields!CodePurch.value) it it counts the actual data that is not masked by the preceding code thus making the figures wrong. Do you understand?

 

 

Following formula would probably give you result.. I did max on CodeBill because yu are matching it with date.

If its not date you can use sum or first or what ever aggregate function you thing appropriate as per your requirement.

=iif (max(Fields!CodeBill.Value) >= Parameters!Startdate.Value and max(Fields!CodeBill.Value) <= Parameters!EndDate.Value,SUM( Fields!CodePurch.Value), 0)