Top N in View or Crystal Reports?

Question

I am wondering if it's possible to use a view to get the top 5 lines from a table. I am finding that Crystal reports doesn't seem to have anything built in to do this, or I'd do it there.

When I query the view Select * from qryTranHistory, it returns the first 5 items, but if I try to select a specific type Select * from qryTranHistory Where tID = 45 it returns nothing, since there are no tID=45 in the top 5 normally.

Is it possible to do this?
Can it be accomplished in a sub report in Crystal Reports?

Solution 1

You can reference a sproc from Crystal Reports. In the sproc, use a conditional on the parameter.

ALTER PROCEDURE dbo.Get_TOP5
    (
    @tID INT = NULL
    )
AS
IF @tID IS NULL
    BEGIN
        SELECT TOP 5            
            FIELD1,
            FIELD2

        FROM qryTranHistory 
    END
ELSE
    BEGIN
        SELECT          
            FIELD1,
            FIELD2

        FROM qryTranHistory 

        WHERE tID =@tID
    END

Solution 2

It is easy to limit a report to the top 5 records. In the menu, just choose

Report --> Selection Formulas... --> Group

In the formula, enter "RecordNumber <= 5" and you are done.

You don't need to have a group field nor summary field to do the group filter. You don't need a sort order, but using top N records without a sort order doesn't usually make much sense. It might not be efficient as OMG Ponies suggested, but for small number of records it is OK.

Solution 3

A simple setting can limit the records to top 5!! Here it is, if you're using .Net 1.1 (similar arrangement of options in higher frameworks too!).

• Right click on the report layout > Reports > Top N/Sort Group Expert > Choose Top N in the Dropdown that asks for the type of filtering/ sorting you wish to do > Set the Value of top N (5 in your case) > Uncheck the option that includes other records. Your report will be filtered for only the top 5 records from the Dataset.

There's another way how it could be done and that is through the Record selection formula where you limit the No. of records, as suggested by John Price in this thread.

Cheers!

Comments

[OMG Ponies]

You can limit the number of records visible in the Crystal Report using a record count comparison, but it's horribly inefficient to pull the entire resultset only to use a small amount of those rows. Better to limit the resultset via SQL if possible.

[AndyD273]

I'm not sure if it is possible to limit it by SQL... My view is simply Select Top 5 * from tblTranHistory And my query in CR is Select * from qryTranHistory where tID = 45, which I hoped would be the same as saying Select Top 5 From tblTransHistory where tID = 45, but apparently not.

[AndyD273]

I don't know of a way to edit the query in Crystal Reports like that. That's why I was hoping to use a view, because I can generally make the view do whatever I want, and then just tell Crystal reports to grab all the needed rows from that. CR kinda sucks when it comes to complex joins and fancy things like Top 5.

[Josaph]

When you run the crystal report, the prompt for the parameter will pop up unless you specify it in a call from your application. This will either give you the top 5 rows if you don't specify a tID, or the row(s) for the tID specified.

[Tom H]

qryTranHistory is a view and within that view it has, "SELECT TOP 5 ... FROM theRealTable..."

[AndyD273]

Ok. Lets say the table tblTranHistory has 1000 rows, with a couple dozen different tID's as a foreign key. If I try Select * From tblTranHistory Where tID = 45 I get 36 rows returned, but I only want the top 5 in my report, and if the user wants the full list they can look in a different report. Since I do in fact want to do it my db layer, and I dont know of a way to change what the query is in CR, I was hoping to just have a query that would return the top 5 fields with a certain tID. So the query in the view is simply Select Top 5 * from tblTranHistory

[leoraelkins]

Nicely done... so easy and clean and worked perfectly for me in Crystal Reports 11... thanks!