Top N Ratios 1

[swhitten]

I have a Top N report that ranks customers by their sales volume for the specified time period. The report has a parameter that lets the user pick whether to see all customers or only a certain number - i.e. only the top 10.

I have all the running totals working great so that my 'grand total' is actually a running total of the top 10 included in the report.

My issue is that I now need to a ratio calculation of the running total at each customer level to the running grand total for the number of customers selected. If only 5 customers are selected, I need the ratio to be based on total sales for those 5 customers. If all customers are selected, the ratio will be based on total sales for all customers.

Example:

Total sales for all customers is $150,000. Total sales for the top 10 customers is $100,000. Example:

Customer 1 $28,000 Running Total $28,000 28% of total
Customer 2 $23,000 Running Total $51,000 51% of total
Customer 3 $18,000 Running Total $69,000 69% of total
Customer 4 $15,000 Running Total $84,000 84% of total
Customer 5 $ 5,000 Running Total $89,000 89% of total
Customer 6 $ 3,000 Running Total $92,000 92% of total
Customer 7 $ 2,000 Running Total $94,000 94% of total
Customer 8 $ 2,500 Running Total $96,500 96.5% of tot
Customer 9 $ 2,000 Running Total $98,500 98.5% of tot
Customer 10 $ 1,500 Running Total $100,000 100% of total

If the user selects to show ALL customers, the running total for all is $150,000, and the % calculation needs to be done on the ratio of the running total at that customer's level to the grand running total - i.e. $28k/$150k, $51k/$150k, etc.

I can't find a way to make the percentage work. Any suggestions? Thanks in advance for the help.

 

[lbass]

I think you might have to add a subreport in a section above where your results are displayed which recreates the same report and returns the grand running total as a shared variable, which you can then use in your percentage formula:

whileprintingrecords;
shared numbervar rtgrandtotal;

{#percentoftotal} % rtgrandtotal

-LB

 

[swhitten]

I created the sub-report and placed it in the main report Report Header A, and I created a shared variable in the sub-report that is the running total of sales. On the main report, the sub-report displays the correct value for the shared variable - $100,000 for the top 10 customers.

However, when I create the formula in the main report to pull the value of the sub-report and place it in Report Header B, it returns a value of $150,000, which is the total of ALL customers, not just the top 10.

What am I missing? Is the formula pulling the total for ALL customers because I am really still pulling sales for ALL customers (for both main and sub reports), but I'm only displaying the top N customers by using a conditional suppress on the group footer to suppress if groupnumber > top n. The sub-report shared variable is displayed in the group footer section, which means its value is $100,000 for customer 10, but if I displayed all the other customers, I suspect that the last one would have a value of $150,000 (all sales).

So what I feel like I need is a selection based on Top n, but I don't know how to do that.

Okay, I'm rambling. Ideas?

Thanks for your help.
Sherry

 

[lbass]

Okay, I recreated the problem, and I think this is the solution. In the subreport, create a formula {@topNsums} and place it in the subreport group (customer) footer:

whileprintingrecords;
shared numbervar topNsums;

if groupnumber <= {?topN} then
topNsums := topNsums + sum({table.sales}, {table.customer}) else
topNsums := topNsums;
topNsums;

Then in the main report create a formula and place it in the group footer with the other fields:

{#topn grand} % shared numbervar topNsums;

where {#topngrand} is the running total sum of {table.sales}, evaluated for each record, reset never, and placed in the main report group (customer) footer.

You have to have topN set up in both reports, and group footers in the main report should be suppressed with:
groupnumber > {?topN} and in the subreport with
groupnumber <> {?topN} (to keep it small, have all other sections suppressed).

This worked when I tested it with different values for topN. I didn't link the subreport to the main report. I had to enter separate values for the main report and subreport parameters (in the same dropdown list), but wasn't sure how to get around that--anyway it works.

-LB

 

[swhitten]

LB,
Thanks a million! This is wonderful. I have all the % calculations that must be calculated off the topn totals working great. Now I just need to ascertain how to feed all the parameter values to the subreport to avoid having to enter them twice; there are about 8 parameter fields.

Thank you again for all your wonderful help!
Sherry

 

[lbass]

Okay, I think I've figured out how to use only one parameter. First, link the subreport to the main report by the group summary field: &quot;Group #1: Table.Customer - A-Sum of {Table.Sales}.&quot; Then in the subreport create a formula {@sharedtopN}:

shared numbervar topN := {?topN}

You must place this on the subreport for this to work.

Then in the main report, replace your conditional section suppression formula with:

groupnumber > shared numbervar topN

As long as you replace all references in the main report to topN with &quot;shared numbervar topN&quot;, the report will now require only one parameter. Since the subreport fires first, it passes the topN value to the main report for the suppression there.

I'm going on vacation tomorrow, but would like to hear back how this worked for you.

-LB