Group header variable to return detail information

I have a collection of requests with a current status and status history.
the history table is linked to the request table using the request # as a key.

Data in the report is grouped by request # with the status history date/timestamp and status value as the detail records.

There are 12 possible statuses a request can have. Each request can enter a status an infinite number of times.

I need to have a distinctly store the most recent date timestamp when the request was in each status - so 12 fields in total. If a request has never been in a status i want to default it to 1/1/1900

From there each of these 12 fields will be stored in a group header (or group footer)..

Using the threads below as a reference point:
http://www.experts-exchange.com/Database/Reporting/Crystal_Reports/Q_23657542.html

I have the following formulas / variables

**variable date history - placed in detail section and in group header.
 //this is returning the first value returned in the detail section when in the group header...
 // the detial section is returning the correct value....
WhilePrintingrecords;
 Global stringVar itsr_history_date;
 Global stringVar itsr_history_date1;
 global stringVar itsr_history_status;
itsr_history_status := {ITSR_Scope_Def_Status_History_Status_admListValues.ListValue};


If itsr_history_date = totext(cdatetime(1900,01,01,01,01,01),"MM/dd/yyyy hh:mm:ss") and
itsr_history_status = "Business Approval Pending"

then itsr_history_date1:= totext({ITSR_Scope_def_StatusHistory.ChangedDate})

else itsr_history_date1:= totext(cdatetime(1900,01,01,01,01,01),"MM/dd/yyyy hh:mm:ss");

itsr_history_date1

**variable reset - this is placed in the group header and detail section. the detail section is returning the expected results
WhilePrintingrecords;
 Global stringVar itsr_history_date;
 Global stringVar itsr_history_date1;
 Global stringVar itsr_history_status;
//
    itsr_history_date := totext(cdatetime(1900,01,01,01,01,01),"MM/dd/yyyy hh:mm:ss");
    itsr_history_date1 := totext(cdatetime(1900,01,01,01,01,01),"MM/dd/yyyy hh:mm:ss");
    itsr_history_status := "NULL";


any ideas??? ANY help is sincerely appreciated.

Reference the attached image file that contains a screenshot of the data set being returned.
status-history-image.png
jmgsheridanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
The group header only has 1 record to work with.  That is why the value matches the first record in the detail section .

Try this idea

Add a formula to the detail section
Name - Bus_Appr_Pend
If {ITSR_Scope_Def_Status_History_Status_admListValues.ListValue} =  "Business Approval Pending"  then
      {ITSR_Scope_def_StatusHistory.ChangedDate}
Else
      Date(1900,1,1);

Open in new window


Right click it
Click INSERT --> SUMMARY
Choose MAXIMUM as the summary
Put it in the GROUP FOOTER
CLick OK

Drag it to the group header if you want it there.

Repeat that for each status

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmgsheridanAuthor Commented:
Thank you so much! This works fabulously!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.