May 3, 2012 at 2:33 pm
I have a report that shows a total amount by client and date.
I created a field named Net Different that subtracts the current amount from the amount on the previous row.
I need the field to recognize the start of a new group, so the first value would show as 0 as shown in example below.
CLIENT ID CLIENT NAME AUTH DATE TOTAL AMOUNT NET DIFFERENCE
123456 Company ABC 4/29/2012 $262,625.82 $0.00
123456 Company ABC 4/30/2012 $136,699.99 $125,925.83
123456 Company ABC 5/1/2012 $117,228.24 $19,471.75
123456 Company ABC 5/2/2012 $117,748.69 ($520.45)
654321 Company ZYX 4/29/2012 $14,232.13 $0.00
654321 Company ZYX 4/30/2012 $5,203.66 $9,028.47
654321 Company ZYX 5/1/2012 $7,961.36 ($2,757.70)
654321 Company ZYX 5/2/2012 $5,983.01 $1,978.35
The Total Amount field is an expression = Sum(Fields!Amount.Value)
The Net Difference field expression now is:
=iif(IsNothing(Previous(Sum(Fields!AMOUNT.Value))), 0, Previous(Sum(Fields!AMOUNT.Value)) - Sum(Fields!AMOUNT.Value))
Any ideas?? Thank you so much in advance 🙂
May 3, 2012 at 2:55 pm
I think RunningValue scoped to your group would do the trick.
May 4, 2012 at 12:13 am
Sarah S (5/3/2012)
I have a report that shows a total amount by client and date.I created a field named Net Different that subtracts the current amount from the amount on the previous row.
I need the field to recognize the start of a new group, so the first value would show as 0 as shown in example below.
CLIENT ID CLIENT NAME AUTH DATE TOTAL AMOUNT NET DIFFERENCE
123456 Company ABC 4/29/2012 $262,625.82 $0.00
123456 Company ABC 4/30/2012 $136,699.99 $125,925.83
123456 Company ABC 5/1/2012 $117,228.24 $19,471.75
123456 Company ABC 5/2/2012 $117,748.69 ($520.45)
654321 Company ZYX 4/29/2012 $14,232.13 $0.00
654321 Company ZYX 4/30/2012 $5,203.66 $9,028.47
654321 Company ZYX 5/1/2012 $7,961.36 ($2,757.70)
654321 Company ZYX 5/2/2012 $5,983.01 $1,978.35
The Total Amount field is an expression = Sum(Fields!Amount.Value)
The Net Difference field expression now is:
=iif(IsNothing(Previous(Sum(Fields!AMOUNT.Value))), 0, Previous(Sum(Fields!AMOUNT.Value)) - Sum(Fields!AMOUNT.Value))
Any ideas?? Thank you so much in advance 🙂
Assuming that your report is group on ClientID, then as below, test for the previous value of the ClientID. If you are grouping on other columns as well expand the expression to include the other columns with &.
=iif(Previous(Fields!ClientID.Value)=Fields!ClientID.Value,
iif(IsNothing(Previous(Sum(Fields!AMOUNT.Value))), 0, Previous(Sum(Fields!AMOUNT.Value)) - Sum(Fields!AMOUNT.Value)),
0
)
Fitz
May 4, 2012 at 3:17 pm
Thanks for the replies Dan and Mark.
I tried the RunningValue function. It does not allow subtraction. The middle argument is a function name.
Mark - I have 2 groups on the report
ClientID
Date --all the fields are in the Date header.
When I tried that code for the expression, I get the error: The Value expression for the text box 'Textbox33' has a scop parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
May 7, 2012 at 4:51 am
Sarah S (5/4/2012)
Thanks for the replies Dan and Mark.I tried the RunningValue function. It does not allow subtraction. The middle argument is a function name.
Mark - I have 2 groups on the report
ClientID
Date --all the fields are in the Date header.
When I tried that code for the expression, I get the error: The Value expression for the text box 'Textbox33' has a scop parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
Sarah, found the same problem as you when I tried this. Then I thought laterally around the issue. In you example we have a three layer table (ClientID, Date, Details rows), the check needs to be done (previous reset to 0) where the date for the current total row = minimum date for that clientID, i.e.
=iif(Min(Fields!Dated.Value,"ClientIDGroup")=Fields!DateField.Value,0,Previous(Sum(Fields!Amount.Value))-Sum(Fields!Amount.Value))
This works on my demo report set up as described above.
Fitz
May 10, 2012 at 8:29 am
Mark Fitzgerald-331224 (5/7/2012)
Sarah, found the same problem as you when I tried this. Then I thought laterally around the issue. In you example we have a three layer table (ClientID, Date, Details rows), the check needs to be done (previous reset to 0) where the date for the current total row = minimum date for that clientID, i.e.
=iif(Min(Fields!Dated.Value,"ClientIDGroup")=Fields!DateField.Value,0,Previous(Sum(Fields!Amount.Value))-Sum(Fields!Amount.Value))
This works on my demo report set up as described above.
Fitz
Thank you so much! That works splendidly! 😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply