Top and Bottom N

[bgul]

Hello - maybe there's a real simple answer to this that I am missing, but I am trying to use the Top N Expert to display both the Top and Bottom N of a particular group(in this case I am trying to count the top and bottom 5 clicked links to see what worked and what did not within a particular marketing effort). I did a search and it did not yield any answers.

The Expert gives me the option of Top or Bottom N. I did try creating a formula and a "dummy" group off the formula but that did not work.

Any ideas?

I am currently using CR 8.5 - all data connections are to a SQL Server 2000 database.

 

[dgillz]

Do you want both to display on one report at the same time?

If so that may be a problem. You could create a paramter field that prompts the user for TopN or BottomN, and get this with 2 executions of one report. For the field to group on, write a formula:

If {?Parm}="Bottom" then -{YourField} else {YourField}

This has the effect or reversing the order of the TopN. Compliments to Bruce Ferguson at

http://www.chelseatech.co.nz

on this one.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[bgul]

Yep - you're exactly correct - I would like to display both on the report. A section would have top 5 links AND bottom 5 links.

I did see Bruce's tip from my searches but it did not apply to my situation.

Maybe the answer is a subreport or more creative use of formulas/groups. I will do some more digging and if I can get something I will repost - thanks for the reply.

B.

 

[dgillz]

OK, heres another idea.

Write a TopN to display every group. Then write a formula field to determine the maximum groupnumber and place it in the report footer.

Finally, conditionally suppress all groups if the group number is in the range of 6 to Max(groupnumber))-5. Basically we are just suppressing all the stuff in the middle.

I have not tested this, but I am sure this will work, you may have to play with things a bit here.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[IdoMillet]

Guys, don't give up that easily... )

Step 1: Create a Running Total (count) that resets for each group.

Step 2: In the suppress expression for the detail section, have something like:
-------------------------------------------------
{#RN1_Count}>5 AND
{#RN1_Count}< Count({some_field},{Group_By_Field)-4
-------------------------------------------------

Obviously, if you want N to be a parameter,
substitute:
5 with {?TopBottom_N} and
4 with ({?TopBottom_N}-1)

Cheers,
- Ido

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

http://www.MilletSoftware.com