TopN Parameter 2

[mwake]

I am using CR10, creating a sales by item report:

Product # Store ID Qty Sold Units Margin
1 500
2 250
2 100
3 75
4 50
5 45

I have over 2 million product numbers, so I want to create a parameter where I can enter a number (100) and the report returns the top 100 products by qty sold. How do I do it??

 

[lbass]

One approach would be to insert a group on product and then right click on {table.qtysold} and insert a sum. Then go to report->topN/group sort and choose "sum of {table.qtysold}"->descending. Do not enter an "N"--leave the N = "All". Then go to the section expert->group header (or footer--wherever you have your sum)->suppress->x+2 and enter:

groupnumber > 100

Suppress the details section and the group footer.

This does, in fact, bring back all the records, but displays the top 100.

-LB

 

[goranm]

Hi !

If you are using Oracle as database and have version 8i or later you can try this:

When you create your report choose "Make New Connection" and "Add Command".

There you start to create a parameter
(For example "Limit" with number as "Value Type")

Then you enter your SQL-query like this:
SELECT
Productnumber,
StoreID,
QtySold
FROM (SELECT Productnumber, StoreID, QtySold
FROM YourTable
ORDER BY QtySold DESC
)
WHERE
rownum <= {?Limit}

This will only bring back the numbers of records that you choose.

/Goran

 

[chelseatech]

Use the TopN feature to do a Descending SORT of your summary field.

Then to use a parameter to select only some of your top values, use a GROUP SELECTION formula of
GroupNumber <= {?TopN Groups}


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm