Referencing specific group totals in formula

[itsaj]

I am using CR Professional 9.2.

I have a need to extract group totals for specific group values from another grouping level in the report.

For example:

Group Header A1

Group Footer B1 5000 + about 28 more columns!
Group Footer B2 1000

Group Footer A1 4000 (@formula = B1-B2)

Group Header A2

Group Footer B3 2000

Group Footer A2 6000 (@formula =(B1-B2)+B3)

What is the most efficient way to handle this. I do not want to create hundreds of running total fields. Is there a way to reference an array of group totals for a column and groupname, like Sum({field},{group},{get the total from THIS group value}) ?

Any ideas appreciated...

CK

 

[kenhamady]

No, but you could store the values from that group to an array while you are on that record. And then you could use the array later. This will only work if the group you are storing occurs above the place they need to be used.

If you need to use the values before they appear on the report then you need to use an If-Then formula that says:

if {Group} = "X"
then {Amount}
else 0

A grand total of this column will make the subtotal for group x available to any formula in the report.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[itsaj]

Thanks Ken. I was thinking about using an array, but was having difficulty figuring out the correct way to initialize, and then to increment. Do I need to have an initialization formula, and then a "While reading records" formula? Also, can I have two dimenional arrays, such as:

BudgetSum( {curr_field_val}, col ) := ....
ActualSum( {curr_field_val}, col ) := ....

Where {curr_field_val} would actually evaluate to a number between 1 and 10 in this case.

Any small example would be very helpful. Sorry, it's been a while since I've used Crystal!

 

[kenhamady]

Is this a financial statement by any chance?

I will guess that you have a series of subtotals on the report that you want to store. You would use a formula like:

WhilePrintingRecords;
NumberVar Array Revenue;
If {Group} = "Revenue"
then Revenue := [ Sum( {Field1}, {Group} ),
Sum( {Field2}, {Group} ),
Sum( {Field3}, {Group} ) .... ]
;0

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com