Top N in View or Crystal Reports?

0 votes
asked Jun 23, 2010 by andyd273

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?

7 Answers

0 votes
answered Jan 23, 2010 by tom-h

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

SELECT TOP 5
    col1,
    col2,
    ...
FROM
    qryTranHistory
WHERE
    tid = 45
0 votes
answered Jan 23, 2010 by jonh

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.

0 votes
answered Jan 24, 2010 by mark-bannister

You can do Top N processing in Crystal Reports, but it's a little obscure - you have to use the group sort expert (and in order to use that, you need to have groups and summary fields inserted into the groups.)

Doing the Top N processing in the query should be more efficient, where possible.

0 votes
answered Jun 23, 2010 by josaph

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
0 votes
answered Jun 12, 2012 by john-price

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.

0 votes
answered Jan 19, 2013 by sun

If you have a small recordset, you can create a running total that counts the change of rows (field1), then in Section Expert in Details, tell it to supress RTotal0 (your running total variable) to > 5

0 votes
answered Jan 26, 2015 by sanrns

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!

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...