subtotals of "Top N" only in a report 2

[tonyvee1973]

Hi All

I have a report which shows all our customers spends by month by year.
I have used the group sort expert to only show the top 100 (Top N) and it works great.
However, on the totals it shows me the totals of ALL the customers, problem is that I need the totals for just the top 100 shown - is this possible?

Many thanks

 

[briangriffin]

This is the problem with group selection - all the records are still there (look at the record count) but some are hidden.

You'll need to create a couple of formulas for the grand total. If you needed subtotals you would need another formula, but for now:

//Put this in the group footer and suppress it:

whileprintingrecords;
currencyvar v_total;
v_total := v_total + [b]sum(YourTotalField,YourCustomerField);[/b] 

// If you've created the summary field then you can just pull this 
// from the Report Fields in the formula editor.

Then in the report footer:

whileprintingrecords;
currencyvar v_total;
v_total

 

[tonyvee1973]

Hi brian, worked like a charm so thanks.
However, I have 3 different "Yourtotalfield" to apply this to in my report. I have tried using the above on all 3 but doesn't work and just gives one total - anything I can do here?

 

[briangriffin]

You can use multiple variables in one formula:

whileprintingrecords;
currencyvar v_total;
currencyvar v_anothertotal;
currencyvar v_thirdtotal

v_total := v_total + sum(YourTotalField,YourCustomerField); 
v_anothertotal := v_anothertotal + sum(YourSecondTotalField,YourCustomerField); 
v_thirdtotal := v_thirdtotal + sum(YourThirdTotalField,YourCustomerField);

However, you will need to create three different formulas to display the results - just change the name of the variable.

 

[tonyvee1973]

ok brian, that again works perfect so thanks!
Final one if you would be so kind. I have percentages (percentage of sum of @curryearsum) on each line and I need to add all the percentages up and have a total at the bottom. Again, I need just to add the percentages for the top 100 not all. I have looked at using the same formula but where this is a percentage of a "sum of" it doesn't like it - any ideas?

 

[briangriffin]

Can you please show some sample data of how it looks now and what you expect your new formulas to return? Thanks.

 

[pmax9999]

Because the calculation for the total of the TopN groups is not known until after the last of the N groups, the main report would need to be repeated as a sub report placed in the report header, the total of the TopN groups calculated and a shared variable used to make that total available in the each of the groups of the main report so as to calculate the percentage.

Hope this helps.

Cheers
Pete

 

[tonyvee1973]

Hi Pete
I can add the same report as a sub report in the report header but as for the rest I am a little lost, apologies but can you detail?

 

[pmax9999]

Using the v_total variable as an example, amend the sub report formula follows:

whileprintingrecords;
Shared currencyvar v_total;
v_total := v_total + sum(YourTotalField,YourCustomerField);

Then in the main report, each Group total as a percentage of the TopN total can be calculated using the following code:

whileprintingrecords;
Shared currencyvar v_total;
sum(YourTotalField,YourCustomerField)%v_total

By way of explanation, where variables are declared without explicitly defining the scope, it is by default a Global variable which makes it available throughout the report, but excluding between the main report and sub reports. Shared variables are available beween sub reports and the main report, and Local variables only within the formula itself.

Hope this helps.

Cheers
Pete

 

[tonyvee1973]

Thanks Pete
On the code below I get the "Division by zero error"

whileprintingrecords;
shared currencyvar v_total;
sum({@curryearsum},{SL_ACCOUNTS.CUCODE})%v_total

I tried:
if {@curryearsum} = 0 then 0 else sum({@curryearsum},{SL_ACCOUNTS.CUCODE})%v_total

but that didn't work. where have I gone wrong?

 

[pmax9999]

This indicates that the v_total variable is being returned from the sub report as 0. Please check to ensure the sub report is in the Report Header section.

To avoid the likelihood that the v_total variable is also being calculated in the main report, change the variable name in the sub report formula and new main report formula to something different than what was originally being used, such as v_TotalN. The two formulas should look like this:

[Code SubReport]
whileprintingrecords;
Shared currencyvar v_TotalN;
v_TotalN := v_TotalN + sum({@curryearsum},{SL_ACCOUNTS.CUCODE});
[/Code]
and
[Code MainReport]
whileprintingrecords;
Shared currencyvar v_TotalN;
sum({@curryearsum},{SL_ACCOUNTS.CUCODE}) % v_TotalN
[/Code]

This should fix it. Let me know how it goes.

Cheers
Pete

 

[tonyvee1973]

Hi Pete
I hid the report header but if I show then it does not get the error, however it still doesn't work and subtotal the percentages.
I think I will leave this and find another way around, thanks anyway.

 

[pmax9999]

OK. That's easy to fix. Hide all sections in the sub report, then format the sub report to "Supress Blank Sub Report" (right click on sub report and select Format Sub Report, then go to the "Sub Report" tab).

You can also suppress the section the sub report is in (if nothing else is in that section) by formatting the section to "Suppress Blank Section".

Hope this helps.

Cheers
Pete

 

[tonyvee1973]

Still doesn't return the correct percentage. Sure its me doing something wrong so will leave it.
Thanks anyway for your assistance.

 

[pmax9999]

I'm more happy to keep working with you to get it right - I will just need to understand the results it is returning and what it should be.

The more information you can give me about the report structure, the results you are getting and the correct results the quicker I will get to the bottom of whatever is causing the problem.

It is getting late here (Aus) - happy to pick it up again in the morning if you need it.

Regards
Pete