Summarize Formula by Group (see example)

[Roni1]

GROUP Region (East)
GROUP REP Product A $100 X 5% commission (formula) = $5
GROUP REP Product B $50 X 10% commission (formula) = $5
SUBTOTAL GROUP Region (East) $10

GROUP Region (West)
GROUP REP Product A $100 X 5% commission (formula) = $5
GROUP REP Product C $70 X 10% commission (formula) = $7
SUBTOTAL GROUP Region (West) $12

GRAND TOTAL $22


I know how to get the GRAND TOTAL of $22, what I don't know is how to get the SUBTOTALS $10 and $12.

Thanks,
Roni

 

[bdreed35]

Roni:

1. Insert -> Subtotal
2. Pick your formula field
3. Pick your Region for the grouping
4. Click OK

Subtotal will be added to the Region Footer


~Brian

 

[Roni1]

Brian,

You cannot summarize on a formula field that has canculations in it.

That is the main problem!!!

 

[bdreed35]

Can you post your formula? ~Brian

 

[bdreed35]

By the way, you want a Subtotal, not a Summary field. ~Brian

 

[Roni1]

Brian,

The formula is just a field (number) mutiplied by another field (number), as I mentioned in my initial post.

Roni

 

[bdreed35]

Roni:

Then what you want is a Subtotal, not a Summary Field. You may be experiencing and issue when trying to create the Subtotal if you have any object selected on the report. Before following the instructions above, make sure you click in a blank area of the report so that you deselect and objects. ~Brian

 

[Roni1]

Brian,

Subtotal or Summary ... it doesn't matter ... you cannot have a subtotal or summary on a calculated field.

The calculated formula is not available for selection.

Please try on your end, and let me know the results.

Thanks,
Roni

 

[kovas]

Roni check out this thread

thread149-160340

 

[Roni1]

Brian,

That thread is for a different purpose.

Roni

 

[bdreed35]

Roni:

Please copy your formula and post it. ~Brian

 

[bdreed35]

Roni:

I created a report off of the xtreme sample db that comes with CR. I am using the Orders table. I grouped the report on EmployeeID and put the order amount in the detail section. I then created this formula:

{Orders.Order Amount} * .05

I placed the formula in the details section. I then right clicked the formula field in the details section and selected Insert -> Subtotal. In the dialog box that appeared, I left the Group field that same and selected the check box for Grand Total. I then clicked ok and it created the Subtotal and Grand Total for me.

Perhaps you can try creating a simple report like this and see if you can get it to work, and then we can go from there. ~Brian