Summaries in Top N reports 3

[Mike Lewis]

I am working with CR 8.5 against an Oracle database.

I have produced a report showing sales per product. The input table is a list of transactions. The details section shows the value of each sale. I am grouping this on product, with total value per product in the group footer, plus grand totals in the report footer. I have suppresed the details section.

This gives me exactly what I need.

I now want to limit the report to show the top 20 products by sales value. I have added Top N sorting to the report, and this also works fine.

However, I need to show the total sales value, not for the entire product range, but only for the top 20. As things stand, the grand totals reflect the sales of all products, not just the top 20.

I couldn't see any way of summarising just the top 20, so I wrote a formula (called Update) which maintains a running counter for the sales value. The formula simply adds the individual amounts into a variable. I placed this in the details section, and added another formula (called Display) to display the final value of the variable in the report summary.

This also works fine.

My first question is: Is this the best way to handle this situation? Or is there a simpler way of showing the grand totals to the top N groups?

My other question is: Assuming I go with the above solution, how can I display, for each product, the sales of that product as a percentage of the total sales for the top 20?

I thought I could add a formula (call it Percent) to the group footer, showing the current product's sales as a percentage of my Display formula. The problem is that, when that new formula is evaluated, the final Display figure has not yet been determined. We don't get that until we reach the report footer.

I tried telling Percent to evaluate after Display, but that made no difference.

Am I going about this problem in the right way, or is there something I am missing?

Thanks in advance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[lbass]

Mike,

I think you have to use the variable method in order to summarize the topN only.

As to your second question, one solution is to save the report as a subreport and add it to the report header of the original report. Then create a shared variable formula set to the subreport {@display} formula and add it to your subreport. In the main report, you can then reference the shared variable in formulas placed in your group header or footer, as in:

whileprintingrecords;
shared numbervar sumtop20;

Sum({table.sales},{table.product}) % sumtop20

-LB

 

[Mike Lewis]

Hi L,

I should have known you'd come up with the answer. As always, your solution is ingenious.

I followed your steps, and it worked perfectly. There was only one small glitch. When I went to suppress the sub-report, all the percentages came out as zeroes. I guess that's obvious -- if the sub-report is suppressed, it's formulas don't get evaluated.

In the end, I went into the sub-report and suppressed the individual sections. That worked fine. I suppose I could also have hidden the sub-report behind some other object such as a graphic.

So, many thanks. I just wish I had asked sooner.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[lbass]

Mike,

In addition to suppressing all sections within the subreport, you can drag the borders of the subreport to make it as small as possible. You can also format the report header to "Underlay following sections".

Glad it worked for you.

-LB

 

[Mike Lewis]

L,

Those are both good ideas. Thanks again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[GarettH]

Hi Mike,

What you describe in your initial posting is exactly what I am trying to do. (I have sales data with multiple lines per customer, I have hidden the data grouping to show only 1 line per customer and I have done a Top N on the grouped line - works fine) As well I am trying to summarize the sales value of only the Top N amounts to show the totals of either the bottom N or top N performers.

I was wondering if you could show me some details on the content on your formula as I am not experienced enough in them.

Thanks very much,

Garett
NB - Canada

 

[lbass]

It's a little unclear whether you want to show the bottom N or the total set or the bottom N of the subset of topN. Assuming you mean the former, create a number parameter {?N}and then create three formulas:

//{@sumtopN} to be placed in the group section:
whileprintingrecords;
numbervar sumtopN;
numbervar sumbotN;
numbervar counter := counter + 1;

if counter <= {?N} then
sumtopN := sumtopN + Sum ({table.sales}, {table.group});
if counter >= distinctcount({table.group})-({?N}-1) then
sumbotN := sumbotN + Sum ({table.sales}, {table.group});

//{@displtopN} to be placed in the report footer:
whileprintingrecords;
numbervar subtopN;

//{@displbotN} to be placed in the report footer:
whileprintingrecords;
numbervar subbotN;

-LB

 

[Mike Lewis]

Garett,

The formula that Lbass has given you is very similar to the one that works for me (except I don't prompt for the value of "N" -- I have it hard-coded).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[GarettH]

Thanks to both Lbass and Mike for the fast turnaround!

I looked at my posting and saw that I was not clear on a couple of things... I am doing 2 separate reports - a TopN and BottomN and I will be showing only 20 on each. My intent is to show only the totals of the "N's" that are shown on either report. Lbass has shown an interesting twist that I was not expecting.

Thanks again and I will work on tweaking this to be based on the more simple need of summarizing the "N's" that are showing on the report.

Garett

 

[lbass]

You should be able to simply remove the clause you don't need in the first formula.

-LB