How/Can CR 8.5 use TopN without grouping?

[AdventurGurl]

I want to be able to pull the top 3 records from a result set that is sorted by date descending. I am better in T-SQL and would say:

select top 3 * from tablename where...

Is there a way to do this in CR 8.5 without grouping first?

Thanks!

 

[IdoMillet]

You can conditionally suppress anything beyond the 3rd record (using the suppress expression of the section where you placed the data).

You can also lean on your SQL skills and use a Command as the data source.

- Ido



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

http://www.MilletSoftware.com

 

[AdventurGurl]

I did not know about the Command data source. I was hoping that I could use more T-SQL instead of the Crystal Syntax.

I looked in the CR 8.5 users manual under Data Sources, but did not find Command... nor under Command alone.

As for the conditionally suppression... what would be the Crystal Syntax to use?

 

[Madawc]

Right-click on a section and choose Section Expert. Then choose the formula icon (x+2 and a pencil) for suppression. Enter a formula.

The simplest method would be a summary count, suppress when the count was greater than 3.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[AdventurGurl]

I want to show the first three records and supress after that...so I only want to see a patient's newest 3 orders.

What is the syntax to write in the Formula Editor?

Thanks, Carrie

 

[Madawc]

Choose Report > Sort and set the order for date.

You'd actually need a running total, not a summary count. Right-click on the field and choose INsert > Running Total. THe formula would be @TotalCount > 3

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[AdventurGurl]

Ok, I have a sort set for date in descending order.

I have the "Create Running Total Field" screen open --> clicked Summary = count. Then in Evaluate --> I didn't know what @TotalCount was... a parameter? If so, I need to create that first, right?

 

[lbass]

Just set evaluate to "for every record" and then in the reset section choose "on change of group": Patient ID--assuming you want the first three records per patient. Then in the section expert->details->suppress->x+2 use:

{#yourrunningtotal} > 3

-LB

 

[AdventurGurl]

Very cool... it's working. Thanks Everybody!

 

[bigfoot]

AdventurGurl, you will not find command in Crystal 8.5, it was added later I was told.

 

[AdventurGurl]

Which is why I couldn't find Command... thanks Bigfoot. Do you know of a way to write SQL syntax instead of Crystal or Basic syntax in CR 8.5 without using stored procedures?

 

[lbass]

You could use a union statement in the Show SQL query where you disable the first part of the statement by adding a where clause like: where 0 = 1. You would have to follow the rules for creating a union where the first half of the query has the same number and datatype of fields used in the second half of the query.

-LB