TOP N as a Variable 2

[BurnsORegan]

Greetings All.....

Has anyone tried doing Top N as a variable? If so please share you knowledge.....

Thanks

BurnsORegan

 

[lbass]

Your brevity is an obstacle in this case. Please be more specific about your question. You can set up the "N" as a parameter, you can set up alternate fields for use as the topN sort field, etc. What is it you want to accomplish?

-LB

 

[IdoMillet]

Simply use TopN to sort all groups, but use a parameter to control the suppress attribute of the Group Header/Footer so that only the top {?N} are visible.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[BurnsORegan]

Thank you for your comments.....

I am using the Top N feature to simply drive the number of records displyed. My report has 7 groupings, each of which has graphical and data information. In each of the 7 Groups there are between 2 and 4 sub-reports to facilitate "drill down".(Advanced Tab - therefore no drill down)

I guess the best way to describe what I need is for the user to define (Parameter) how many records are to be shown. Can the "N" set up as a parameter be used to drive all sections and sub reports?

Is the supress option expressed by IdoMillet the way to go or can, as suggested by lbass, the Top N feature use a parameter field.

I hope this explains it.

 

[lbass]

My first suggestion is actually the same as Ido's. First you would do a topN sort, choosing "All," instead of topN, so that all groups are ordered in descending order. Then you would create a discrete number parameter {?topN}. Then go to format->section (or section expert)->group header( and then details and group footer sections if otherwise displayed)->suppress->x+2 and enter:

groupnumber > {?topN}

-LB

 

[BurnsORegan]

lbass

Thanks for that it works great.....just got over 50 sections to suppress now, but it is atleast a working solution.

 

[BurnsORegan]

lbass and IdoMillet

Please help again......

I have it working that it only brings back correct number of records, however they are not the TOP N. It appears to assign the group number before the sort "All" and then returns the first 10 (GroupNumber) which seem to be in the order they show in the database?

Any thoughts?

Burnsoregan

 

[lbass]

Group number" is just a running total and so will always display in numerical order. I think you should give us some information about your report design--your groups, the summary you are using for topN, etc. Please be sure to explain how the subreports fit in as well.

-LB

 

[BurnsORegan]

This is going to be a long one.......

The report is a combination of 1 main and many sub reports: The primary purpose of the report is sales and needs to report the following (in brief)

Lets start by saying this, the group levels will represent "level views" of data.

Group Level
GLOB Level
Divisional Level
Sales Team level
Sale Manager level

What I have been asked to facilitate at each of these levels is:

Sales by Customer
Sales by Period by Customer
Sales by Forecast by Customer

So thus far I have found the best way is by using sub-reports at each of the levels as Drill Down ability is required. (Have found that using the advanced tab on Charting and drill down dissapears)

The sub Reports each have their own data selection and are linked to the main report only on the Parameter fields being passed. (Sales TEam, Sales Manager, Requested Date and TOP N!)

What has then been asked is to have each of these sections not printing all the dat but only the TOP N! Where (they have asked) N is a variable. ie. Can change each time the report is run, without going into the design.

The data is stored in SQL tables (JDE) with custom tables (part of the same data set) We are running Crystal 9.


Does this help? Would it work if I had to add a formula filed to the report which resets at each change in header? Then sort "ALL", and supress based on this field?

I hope all of this makes sense????

Regards

BurnsORegan