formula based on subtotal

[crystalpro]

I have a report in which I show the sales by customer for each sales rep for a specified date range. The sales is a formula:
{@Gross Sales} = {JrnlRow.Quantity} * {JrnlRow.UnitCost}

I need to report the percentage of sales the sales amount (gross sales column)is.

Report should subtotal by customer, then subtotal by Sales Person and a Grand Total

Group #1 is the Sales Rep
Group # 2 is the Customer

My columns are:
Customer Invoice Item Gross Sales % of Sales
Joe 222 444 50.00 20%
Jack 245 554 100.00 40%
Sam 342 565 100.00 40%
Total for Sales Rep X 250.00 56%

Joe 242 444 50.00 25%
Jim 231 444 50.00 25%
Scot 221 554 100.00 50%
Total for Sales Rep Y 200.00 44%
Grand Total 450.00

How can I get a formula to work for the % of Sales column for the customer group and then one for the total sales rep?

 

[kenhamady]

When you go to write the formula, the subtotals and grand totals will appear in the list of fields. They are the fields that have a Sigma in front of them. You can use these to do your calculation by dividing your subtotal by your grand total, etc. 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

 

[crystalpro]

My Gross Sales column is based on a formula. Therefore, using the subtotal of Gross Sales does not work when trying to get the % of total sales.

I did add some running totals. One for the customer sub-total, one for the sales rep subtotal and one for the grand total of gross sales. The running total amounts appear correctly on the report, however, when I use the running totals in a formula to calculate the % of total sales, the percent is usually off by about .6-1.7%.

How can I correct this?

 

[kenhamady]

You can't use running totals to do percent of total. You have to use Crystal Summary fields.

What is the actual formula for Gross Sales.
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

 

[crystalpro]

The formula for gross sales {@Gross Sales} is:

{JrnlRow.Quantity} * {JrnlRow.UnitPrice}

 

[kenhamady]

There is no reason why you can't total this formula using Insert Grand total and Insert Summary. Then you can divide one by the other to get a percentage. 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

 

[crystalpro]

I have two groups and inserted the subtotals for each and also a grand total in the report footer. The problem with trying to get % of sales is that crystal won't take the Grand Total for gross sales as the denominator. It looks only at the group.

 

[kenhamady]

What is your percentage formula? 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

 

[crystalpro]

My percentage formula uses the running total I created: {@Gross Sales}/{#Total Sales}, the Total Sales is in the Report Footer.

If I insert summary for all groups and grand total, should the grand total field be available for formulas?

 

[kenhamady]

Why are you using a running total for Total Sales?
If you use a regular grand total it should work? 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