Allocate costs from 1 group level to another

[stoopers]

Can you allocate costs from group level to another? I am creating a margin analysis report where I am combining revenue and cost data by cust code, warehouse, and order#.

I currently have the following groups:
Customer Code
Warehouse
Order #

I need to allocate costs summarized at the customer code level back to the order # level. The only common threads I have between the cost and the revenue is customer code and project#.

I am using Crystal 11 and iScala VBA Developer.

Any suggestions would be most appreciative

 

[Madawc]

Summary totals are available within a group, including its subgroups. They can be done at different levels. It won't let you display at the wrong level, but it ought to let you use the value in a calculation. Try it and see.

Since you sound new to Crystal, here's a summary of how it works.

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.

It is also possible to get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[stoopers]

I have tried the summary formulas and the shared variables. I have summary formulas at the customer code, warehouse, and order # levels on all of the Revenue and Cost fields.

My problem lies in the fact that the cost does not have warehouse or order# field types associated with it.

 

[Madawc]

Have you looked at Crosstabs? They can do totals that are different from the groupings.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[stoopers]

Altho' my message is not "techy" - I am very familiar with the methods you described above.

I do have summary level formulas for the revenue and costs are each level mentioned above.

My problem lies in the fact that the depreciaiton costs do not have a warehouse or order# affialiated with them. They are summarized at the customer code level. My plan was to allocate the cost to each individual order based upon the % of revenue. I was able to calc the % factor using this formula:

DEPR PCT: IF Sum ({@REV}, {@ORDER})=0 THEN 0 ELSE Sum ({@REV}, {@ORDER})/Sum ({@REV}, {@PKG})

My depr calculation formula was then:

DEPR PCT SUM: {@DEPR}*{@DEPR PCT}

Formula returns 0.

 

[lbass]

This is hard to follow with just explanation. It would help if you provided sample data that showed your group structures, where your calculations are, etc.

-LB

 

[stoopers]

Sample data:


Cust code Order# Warehouse Proj Rev Cos Freight Depr
JAMA005JA 9888 Gulb 990FT 500 150 75
JAMA005JA 9889 Gulb 990FT 650 150 75
S/T Gulb 1150 300 150

S/T JAMA005JA 1150 300 150 700


Simplied version of data. Depr does not relate to Order# or Warehouse. I can link it to cust code by proj# 990FT. A customer may have multiple warehouses - so I need to be able to allocate the depr either back to the warehouse summary or to the individual order#.

 

[lbass]

Please show the contents of your formulas (and any nested formulas within them) in your next to last post. What does PKG stand for?

Also, you may be showing the depreciation only in the group footer, but if you placed it in the detail section I'm assuming it would be a constant per row?

-LB