How do I capture data with mixed criteria and effective dates?

[deborahyr]

How do I capture first effective date of Job Title and current Organization while in that Job Title?

Data Rows =
Start Date Job Title Organization
01/2009 Consultant-AP Org 5
01/2008 Consultant-XX Org 5
11/2007 Consultant-XX Org 5
6/2007 Consultant-XX Org 3
01/2006 Consultant-CC Org 2

Want displayed only:
01/2009 Consultant-AP Org 5
6/2007 Consultant-XX Org 5
01/2006 Consultant-CC Org 2

Crystal Reports XI

 

[CoSpringsGuy]

I would group by title, sort by date descending and place the fields you want in the group footer. then hide the details and the group header

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[deborahyr]

Your suggestion brings back following for my example above:

01/2009 Consultant-AP Org 5
01/2008 Consultant-XX Org 5
01/2006 Consultant-CC Org 2

I need it to bring back:

01/2009 Consultant-AP Org 5
6/2007 Consultant-XX Org 5
01/2006 Consultant-CC Org 2

Essentially, the first effective date of the job title but the last effective date of the organization for that same job. Thanks much.

 

[CoSpringsGuy]

is the date field an actual date type field?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[deborahyr]

date field = datetime field

Thanks.

 

[CoSpringsGuy]

i duplicated your data in a table...

grouped by Job title
Record sort decending on the start date (report-record sort expert-add date-select descending)
inserted the fields into the group footer
hid the details
hid the group header

and recieved the following results

01/2009 Consultant-AP Org 5
01/2006 Consultant-CC Org 2
06/2007 Consultant-XX Org 3



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[deborahyr]

Right - this is my dilema... the 06/2007 row should have Org 5 not Org 3. This may be impossible to do but I need to confirm. Thanks again.

 

[elsenorjose]

You might try modifying the solution in this post:

http://tek-tips.com/viewthread.cfm?qid=1566801

You'll probably group by Job Title or maybe Org but I've used this solution in the past to get the most recent or first occurrence by modifying the criteria correctly.

 

[CoSpringsGuy]

I actually thought that was a typo in your initial post.

I dont see a coorelation with Org5 and the date 06/07

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[AndyMc]

It appears to me that he wants to show per consultant the earliest date but the org for the most recent date.

 

[lbass]

I think you should group on job title and drag the groupname and the date field into the group footer. Sort by startdate descending, and then create a formula like this:

whileprintingrecords;
stringvar org;
if {table.startdate} = maximum({table.startdate},{table.jobtitle}) then
org := {table.org} else
org := org;

Place this in the detail section and suppress it. Then in the group footer use this formula to display the most recent org:

whileprintingrecords;
stringvar org;

Add a reset formula to the group header:
whileprintingrecords;
stringvar org;
if not inrepeatedgroupheader then
org := "";

-LB

 

[deborahyr]

OH SO Close! This works but I can't get the dates to sort descending. Do I need to create a group for startdate and sort descending? If I do that then the org issue returns...

 

[lbass]

What datatype is the "date" field? Is it a string? Or? What is your current group structure (what are you grouping on)?

-LB

 

[deborahyr]

datatype = datetime It is not a string. I grouped on job title as instructed.

 

[lbass]

Then you should be able to simply go to report->sort records->move "datetime" to the right as your sort field->choose descending order.

-LB

 

[deborahyr]

Which is exactly what I did but the grouping on job title appears above the sort by datefield so it is doing the grouping first. I need to some how get it not to sort by job title first. How do I do this? The only way to get the datefield to sort first is to create a grouping by datefield in desc order and then by job title. Again, doing this does not give me the results I'm looking for for org.

 

[lbass]

You SHOULD be grouping by job title and then sorting by date. If you want an outer group on org, make that your group #1, job title->group #2, sort by date descending.

-LB

 

[deborahyr]

I'm convinced this cannot be done based on responses and interpretation of my original issue. I only want to group by title when the title is the same as next title when sorted descending by datetime field.
Example detail =
10/2009 Job3 Org99
9/2009 Job1 Org7
6/2007 Job1 Org3
1/2007 Job1 Org1
1/2006 Job2 Org88

Result if sorted by job title ascending and datetime desc =
9/2009 Job1 Org7
6/2007 Job1 Org3
1/2007 Job1 Org1
1/2006 Job2 Org88
10/2009 Job3 Org99

Result wanted =
10/2009 Job3 Org99
1/2007 Job1 Org7
1/2006 Job2 Org88

Shows 1st entry date into Job1 but last Org name while in that same job.

 

[CoSpringsGuy]

OK I kind of gave up when other people got involved but maybe an explanation of the fields might help.....

Do you always want the 1st entry date of the job?

Do you always want the last Org name?
If so, will the last OrgName always correspond to the last start date?

If the answer to those is yes then do this. I tested it on your provided data and it seems to work

group by title

create a running total
//
field to summarize - start_date
type of summary minimum

Evaluate - For each Record
Reset on change of group

create another running total
//
field to summarize
field to summarize - Organization

Evaluate - Use Formula - click the X-2 button and enter
Maximum ({Start Date}, {Job Title})={Start Date}

Reset on change of group

Place the start date field from the database and the two running totals in the footer. hide details and hide group header

let me know if that works

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

the first running total isn't really necessarily now that I look at it. You could just do a summary(minimum) of that field to the group footer. But the second one should get you the organization value you are trying to get

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection