Group Header Formula Based Upon Subsequent Group Footer Running Total

[MomentSurfer]

To All Those Crystal Gurus Out There:

I could use your help, if you'd be so kind.

I am in the process of developing a report that needs to behave a lot like a bank account statement. The report needs to show the starting balance as of a specified date (StartDateParameter) and then show all of the adjustments until another specified date (EndDateParameter).

Here is an example of what I mean:

Sample Data: All Adjustments to Account:
Jan 1 2005, 10.00
Jan 5 2005, -5.00
Feb 3 2005, 20.00

Sample Report Parameters:
StartDateParameter = Feb 1 2005
EndDateParameter = Feb 28 2005

Desired Output:
5.00
20.00
_____________
TOTAL: 25.00

Comments regarding output:
1) The "5.00" represents the balance for the account AS OF Feb 1 2005
2) The "20.00" is the adjustment made between the Feb 1 2005 and Feb 28 2005

I *ALMOST* have this report working. I am surpressing the adjustments that are dated PRIOR to the StartDateParameter. The total is functionally properly because it is looking at ALL adjustments (from the very beginning: even before the StartDateParameter) until the EndDateParameter. The problem is I need to show what the "opening balance" is (e.g. "5.00", in this case). The opening balance needs to appear BEFORE the adjustments are listed. The opening balance is equal to the Total minus the DISPLAYED adjustments.

I understand it would be very straight forward to perform this calculation, and hopefully it still is, but I'm facing a bit of a struggle for the following reasons:

1) There are multiple levels of grouping (6 to be exact)

2) The details section of the applicable group (the 6th) is suppressed

3) The detail that WOULD normally be shown in the DETAILS section of the 6th group is being shown in the FOOTER section of the 6th group. This is being done because I need to create a MANUAL crosstab report. I actually have 12 "opening balances" and "12 sets of adjustments" and "12 new totals": one for each fiscal month (this is a budgeting system that shows adjustments to budget by fiscal period). Each detail record is an adjustment for a single period. I do not want to have 12 rows created (one for each period). I want to have 12 columns displayed for a single row with each column being the adjustment for a period. I am doing this by using a series of running totals in the 6th section details and conditionally running them when the period is a match for the particular running total. e.g. P1AdjustmentRunningTotal = AdjustmentAmount WHEN BudgetMonth = 1. I then use the group #6 footer to display the running totals side-by-side to mimmick a crosstab. This is working great. I don't mean to over complicate this question by focusing on the manual crosstab features-- I just wanted to highlight why the detail section is supressed and why the adjustments were being shown in a group footer.

My problem seems to be a matter of timing. I want to have a formula, essentially: OpeningBalance = Total - (SUM OF DISPLAYED ADJUSTMENTS). I need to place this formula in the Section #5 HEADER. Can this be done when it needs to be evaluated AFTER the Total is calculated in the Section #6 FOOTER?

I tried creating a Formula Field but the Running Total was not selectable when I tried to insert it into the my calculation. I was going to try using the EvaluateAfter() function. Hopefully I am just doing something wrong.

Any suggestions or insights you may have would be incredibly appreciated. I could really use some direction before I completely alter my approach and just use a stored procedure to precompile all of the data into a temporary table.


David

 

[MomentSurfer]

Okay: all of that to answer my own post.

I found the answer. I was accidently trying to use another formula field when calculating the opening balance. As soon as I mapped it to the proper running total field everything worked as intended. Cleary it's time to call it a day.