displaying one record per person

[mperera]

Hi,

I have a table that records various 'statuses' about people.
So, the table stores the personId, status, dateInserted, etc., and a person can have multiple status records.

What I need to do is to display only the most recent status record per person (ie, by the max(dateInserted) for that personId's records). Can I do this type of grouping in Crystal, or do I need to do that calculation in sql?

Thanks.

 

[IdoMillet]

You could use either approach.

In SQL, you can restrict the result set
to only those records where the date_inserted is
equal to the max(date_inserted) for each person.

In Crystal, you can group by person, sort by date
so that the last record is at the bottom of the group,
move all the information you want to the group footer, and suppress the detail.
There are other options, but this is probably the simplest one.

Cheers,
- Ido
ixm7@psu.edu

 

[dgillz]

Another option you can use that does not require grouping is this -- create a running total field to count the number of records, reset upon change in employee. Then conditionally format the details section to supress if {#count}>1

Email me if you have questions.

 

[Ngolem]

This is not a trivial problem and one that I just wrestled with. Your problem is not that you want the maximum value of something (summary functions handle this very well) but that you want the records associated with a maximum value.

My solution was to run a subreport. In your case it would be to determine the PersonID and Maximum Status value.

Assumming that you have less than 1000 people to deal with create a shared array in the subreport called

subreport name : Maximum Person status

create an initialization formula, @Initialize

****************** start formula **********

//create the array with aleast 50% more elements
//than you currently need initialize the array to " "
shared stringVar array PersonStatus := [" "," ",....," "];
numberVar counterflag := 0;
shared stringVar Warning := " " // see calc formula

****************** End formula **********

now a calc formula , @Calc

****************** start formula **********
// we shall assume you initialize 500 elements to
// array PersonStatus
shared stringVar array PersonStatus;
numberVar counterflag;
shared stringVar Warning;

counterflag := counterflag + 1

if counterflag <= 500 then
PersonStatus[counterflag] := {table.personID} +
maximum ({table.status),
,{table.personID})
else
warning := &quot;Warning more than 500 personID encountered&quot;;

****************** End formula **********

In the subreport

1. create a group based on PersonID
2. place @initialize in the report header
3. Place @Calc in the group footer for PersonID
4. suppress all sections of the subreport

In the main report

1. create your report such that it draws data for all the PersonID/ status records
2. Group on PersonID...plus any other groupings you wish
3. Place that subreport in the Report header with appropriate linkage if needed...do not suppress the Subreport...or the main Report header (but make this section as small as you want...the report is not returning anything to the physical report)...remove the subreport borders so nothing is shown (I usually color code my subreports red so I can find them later (do it conditionally in the Background Color by using a formula:
if 1 = 1 then crNoColor else crRed
4. NOW!!!

in the section expert....for the conditional suppress for each section within the GROUP for PersonID...including the group/detail/footer sections put the following formula:

****************** Start formula **********

WhilePrintingRecords;
shared stringVar array PersonStatus;

not({table.PersonID} + {table.Status} in [PersonStatus])

****************** End formula **********

5. in the page footer place a warning formula @warn

****************** Start formula **********

whilePrintingRecords;
shared stringVar Warning;

Warning; // prints a blank until you exceed the max array

****************** End formula **********

this may seem complicated at first but I save the subreport and tweak it to fit other such reports...It works pretty fast too.

 

[dgillz]

Ngolem,

That seems enormously complicated. If he sorts descending by date (or whatever to get the most recent status record for that person) and then supresses all with a count>1, he would have what he wants, right?

This assumes there would be no other grouping or totally as supressing the data would not take the unseen values out of the summary or grand total calculations. He did not mention the need for any summary, just show the most recent status record.

Am I missing something? Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Dgilsdorf#altavista.com
714-348-0964

 

[Ngolem]

You are right...my way is more complicated but in my case I needed some other info along the way...which I captured in the subreport.

Your way is better as I reread it...

 

[mperera]

Hi,

Thanks to all of you for your suggestions. I handled this by creating the running total and suppressing where count>1. I believe that Ido's suggestion using grouping would have worked, too - but this gave me a chance to use running totals!

Thanks again.

 

[LoaferMan]

Try the Top(N) summary function, and choose the Top 1 date field. That may do the trick.

LoaferMan

 

[dgillz]

Loafer-

A Top-n report will not work for this instance. A Top-N, with n=1, would return ONLY the individuals with 1 status record in the database. He wants ALL the people in the database, with just the most recent status record.

Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Dgilsdorf#altavista.com
714-348-0964

 

[IdoMillet]

Actually, a top-n report with n set to 1
would work provided that you group on person and within person by date (resulting in 1-record groups if each person has at most 1 record per day).

Then apply the top-n option to the date group within person,
resulting in showing just the top date within each person.

Cheers,
- Ido
ixm7@psu.edu