Conditional Sum / Array formulae equivalent.

[paddydog]

I have a report which (simplified) looks a bit like this...


Job Number: AB1234
VALUE SOURCE RESERVED INVOICED?
100.00 costing R-TIM- Y
356.00 costing R-TIM- N
500.00 costing A-TIM- Y
-50.00 costing U-TIM- Y
350.35 stock Y
122.00 puchase Y
999.00 puchase N

It's easy enough to sort the data and get subtotals for these in the usual way. However, the above report is actually a subreport, and I need to pass the result of each subtotal as a separate, shared variable to a main report. I think I can only declare the "automatic" subtotals as a shared variable once, so the wrong info would be passed to the main report if I used the obvious method of subtotalling...

I could work out a conditional sum / array formulae in Excel to create "manual" subtotals based on the possible combinations of SOURCE / RESERVED / INVOICED but am not sure if a similar / equivalent can be done In Crystal (8.5)? I would like to think think such a "manual" formula could be placed in a report footer to pass the relevant results to a main report?

I'm new here (first post) and a relative novice with crystal so be gentle with me...

Thanks.

 

[lyanch]

Some clarification..

You want to sent the subtotal of *each* group in the subreport to the main report?

Is there a static set of possible groups in the subreport?

Lisa

 

[kenhamady]

I have created formulas to capture a series of 'automatic' subtotals (using if-then logic to assign each to the correct variable), but what are you going to do with them in the main 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

 

[paddydog]

lyanch: i'm not exactly sure what you mean but the report will be repeated, possibly hundreds of times. There will, however, be only about six or seven sets of subtotals for each (maximum) - there may only be one - the rest returning zero.

kenhamady: the 'if-then' type of formula / logic is what I'm looking for. The report will have other subreports based on other database tables; I need to compare the subtotals from this report to to the other subreports. I'll be surpressing the detail in this report to get summary figures on one line.

hope this helps - thanks for your interest.

 

[paddydog]

Oh, and for clarity, the SOURCE / RESERVED / INVOICED items are not part of the actual data - they are the results of 'if' formulae based on various bit of each record...

 

[kenhamady]

Place the variable formula on a group footer and use a formula like:

If {group} = "Costing" then
CostingVar := Sum ({numeric}, {Group})
else

If {group} = "Stock" then
StockVar := Sum ({numeric}, {Group})
else
...

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