TopN Grand total

[patty1]

I am doing a top 1000 producers report - but my grand total is totaling everything, not just the 1000 - how can I get this to work?

 

[akabatman2]

Hi Patty,

Are you looking to do something like this?

Top 5
OrderID Order Amount
7 $748,755.94
6 $710,401.48
9 $682,849.21
1 $660,756.95
3 $649,101.99
Others $631,799.77

Top 2
OrderID Order Amount
7 $748,755.94
6 $710,401.48
Others $2,624,507.92

If so, you need to create a group based on you ProducerID and then create a summary and make the summary location the group footer. Then click on the group sort expert in the Expert toolbar and select 'For this Group Sort:' "TOP N'
Fill in 1000 for 'Where N is:'

You will have to hide the detail if you want the output to look like the output above.

Hope this helps,

Tom

 

[patty1]

I have the part you show above but now - and for this lets use top 2 as an example

If I want to grand total only those 2 not "other" - I turned off the "other" option in Group Sort Export but it still adds 748,755 + 710,401 + 2,624,507. I don't want the 2,624,507.

Thanks for your help

 

[akabatman2]

Do I understand that you want to now be able to total just the Top 2 as in:
$748,755.94 + $710,401.48 = $1,459,157.42

If this is the case, try this:
Create a formula called SumOnlyTop2 and place it in the group footer and suppress it.

//SumOnlyTop2
Global NumberVar Amount;
Amount :=Amount+ToNumber(Sum ({Orders.Order Amount}, {Orders.Employee ID}))

Then Create another formula called, 'AllButOtherGrandT'
and place it in the report footer.
While Not OnLastRecord
DO
Global NumberVar GrandTotal;
GrandTotal:= Global Numbervar Amount

Try that..

Tom

 

[patty1]

Thanks - that didn't quite work out for me as "While" and "Do" are not available to me. i am using Crystal 7.1. I tried using If and Then but got 0 for total. But then realized that SumOnlyTop2 gave me "other" total so I created another formula taking the grandtotal and subtracting SumOnlyTop2 and got my number that way.

Thanks for your help. If there was a way I could have used While and Do please tell me so I know for next time.