Top N

[informer30]

Hi All,

I have a crystal report in BOXI Rel2, which has been developed using the following code in the Group
selection to determine its 'Top N'.

What I want to be able to do is create a formula/parameter to allow the user to select the Top N rather than
having a harcode of 10.

Is this possible and how.....

Sum ({@control}, {@employee}) >= NthLargest (10, {@control}, {@EmployeeGroup})

Many Thanks

Ps. Its a 7 year old report....

 

[lbass]

Instead of using group selection, you should be able to do the following:

1-Right click on {@control} in the detail section->insert summary at the {@employee} group level.
2-Go to report->group sort->topN->x+2 and place your number parameter there.

-LB

 

[informer30]

Hi lbass,

that not worked...this is a old report which has been developed before crystal had the 'Expert(s) functionality' available....for example running total is contructed via formula;s like this Top N.

Please any other ideas, will be brill....

Many Thanks

 

[informer30]

Hi lbass,

I have been testing, this nthlargest function and created a simple report,
which has fields employee, section and employeewage.

I have grouped by section which has returned 3 groups which is correct. Then
I have summed employeewage by section.

Then I have gone to GroupSelection and inputted

sum (employeewage, section)>
nthlargest (3,employeewage, section)

I was expecting only tWo group levels sums to appear as the detail line is
supressed.

GroupLevel Sums are:

Section A 10
Section B 20
Section C 30

Please can you advise....

Many Thanks

 

[lbass]

What CR version are you using?

Nthlargest is returning the three largest values WITHIN each group, not the three largest group values, so this approach won't work.

-LB

 

[lbass]

I just realized you said you were using CR XI. You should take advantage of its new features to update the report as I first suggested.

-LB

 

[informer30]

lbass, the issue is not only is this feature developed like this all the report is developed in a older way. Its a core report and to get it changed just not possible at the moment.

Any ideas on a fix for the issues?

Many Thanks

 

[lbass]

Not if you are unwilling to make changes! This would not be complicated to do.

-LB

 

[informer30]

lbass,

the changes you have recommended do not work....I cant use the GroupSort and TopN because rest of the report is setup in a older way.

This is a report with over a 100 formulas and 30 tables....

Thanks

 

[lbass]

You haven't said anything to convince me that you can't use groupsort/topN. I'm guessing that you only want the topN groups to display, but when you do this, some of your manual running totals return the incorrect results.

Save the report under a new name and play with it. Add a parameter and use it for the topN group sort. Then adjust your manual running totals, by adding a clause like:

whileprintingrecords;
numbervar x;
if groupnumber <= {?topN} then
x := x + {table.amt};

You can't fix something without changing it. If I'm offbase here, please explain what the report is currently doing and what you want it to do, remembering that NthLargest does NOT work with group summaries, but with fields within groups, so that isn't part of the solution.

-LB

 

[informer30]

Hi lbass,

thanks for replying, what I am looking for is to return records within a group via a parameter input which detremines the TopN....so e.g.I have 1 Group and the parameter inputted is 4, so all I am interested in is seeing the Top 4 within the group = Top 4 in USA, UK, Italy, France etc.

However, I have a copy report of this report and have tried various things, the best solution I have come across is to place a if-then-else statement in the GroupExpert ie. hardcoding. This only works as after discussing with the business user how much of a TopN is he likely to use....


Thanks for your help....if you have a more flexible solution please can you advise...

Many Thanks

 

[lbass]

Instead of using group selection, sort in descending order by {@control}. Then create a running total {#cntwingrp} that count any recurring field, evaluates each record, and resets on change of group. Then go into the section expert->details->suppress->x+2 and enter:

{#cntwingrp} > {?How Many}

-LB

 

[informer30]

lbass, thats a fab idea....the {@control} formula already in at group level4 and report consists of 9 groups.

In the meantime I have gone back to the business and we have decided to hardcode the report at Groupselection.

Thanks for all your help much appreciated.

Merry Xmas...have a good one..

Many Thanks