Top two records in group using date descending 1

[ladporter]

Hi

Reasonably new to CR. I have a report that is grouped by a PersonID displaying transactions sorted by the date descending. What I want to do is have the report display the 2 records with the most recent date. In Cyberquery you could put in a counter (sort of like a running count) and have record selection criteria where runningcount =< 2. Tried TopN but it only seemed to give the top 2 persons and all their transactions rather than every person with their 2 most recent transactions. I have tried different combinations of grouping summaries in the report. Any help would be appreciated. Sorry if it's a basic question.

Thanks in advance

ladporter

 

[Madawc]

Summary totals are calculated before you have access to them. I think you need a Running Total, which would let you identify the second item in a group. (Probably simiar to your runningcount, but I don't know Cyberquery.)

The use of Crystal's automated totals is outlined at FAQ767-6524.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I think you could go to report->selection formula GROUP and enter:

{table.date} >= nthlargest(2,{table.date},{table.personID})

Another option would be to insert a running total {#top2}that does a count of the personID, evaluate for each record, reset on change of group: Person ID, and then go to the section expert->details->suppress->x+2 and enter:

{#top2} > 2

I would try the group selection first.

-LB

 

[ladporter]

Thanks for the prompt replies. I tried the group selection as lbass suggested and it does exactly what I wanted thank you. Just one other question. The report now look like below:

personid fullname recordid applicationdate issueddate expiredate

4 Joe Blow 22729 10/06/2008 11/07/2008 16/09/2010
4 Joe Blow 18025 16/01/2007 10/01/2007 16/09/2008

6 John Smith 28782 1/06/2010
6 John Smith 22824 13/06/2008 8/09/2008 20/09/2010

We are a not-for-profit childrens organisation. The government here issues cards giving clearance for adults to work with children. If issuedate and expiredate are blank it means that an application has been sent to gov't and we are waiting for approval. If they are not blank it means the card is issued and has expiry date listed. I want the report to list all card holders where the expiry date has been reached but also to print the previous line to show that even though the date has expired that the application has been lodged. Basically a report of all people not currently allowed to work with children but noting that the application has been sent.

I tried using "previous" in group but it said had to be evaulated later. Tried creating second instance of application table and linking to it all appeared on one line but couldn't seem to get to work.

Any chance of another few helpful tips or point me in the general direction.

Thanks again, ladporter

 

[lbass]

Is this for the same report? Which date field did you use for the nthlargest? I guess you could try the following:

(
isnull({table.expiredate}) or
{table.expiredate} <= currentdate
) and
{table.applicationdate} >= nthlargest(2,{table.applicationdate},{table.personID})

-LB