Top N In View Or Crystal Reports?

February 01, 2024 Post a Comment

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

Solution 1:

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 2:

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

ALTERPROCEDURE dbo.Get_TOP5
    (
    @tIDINT=NULL
    )
AS
IF @tIDISNULLBEGINSELECT TOP 5            
            FIELD1,
            FIELD2

        FROM qryTranHistory 
    ENDELSEBEGINSELECT          
            FIELD1,
            FIELD2

        FROM qryTranHistory 

        WHERE tID =@tIDEND

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!

Solution 4:

Can you put the TOP in your SELECT statement instead of in the view?

SELECT TOP 5
    col1,
    col2,
    ...
FROM
    qryTranHistory
WHERE
    tid =45

Solution 5:

If your table has more then 5 rows I hope this query:

SELECT*FROM qryTranHistory

Returns more then 5 rows because you never mentioned TOP 5. Your question doesn't make a lot of sense as I am not sure waht you are after. You mentioned if you ran your query with WHERE tID=45, it returns nothing, what exactly do you want it to return ?

Read up on TOP in BOL:

SELECT TOP 10 Recs FROM Records WHERE...

By the way you do not want to do this in the report / a form interface, you want to do this in your db layer.

Lab Sql