Count Hires and Terms in same report? 3

[loveyoursite]

CRV 10. I'm drawing a blank this morning and would appreciate anyone's advice on how best to do this. I need to write a report showing the number of new hires and the number of terms sorted by year then month. I can't figure out what the grouping would be to count both of these on the same report. For example: If an employee was hired Jan 1999 and termed Mar 2000, how do I get the employee to show up in the Jan "Hired" count AND again in the Mar "Term" count? The employee needs to be counted once as hired and once as termed.

Expected Result:

Year Hired Termed
1999
Jan 72 11
Feb 0 7
Mar 32 3
etc.
2000
Jan 108 17
Feb 174 29
Mar 93 6
etc.

 

[MisterMo]

group by year first followed by month and last by employee

create summary for each employee

Mo

 

[Madawc]

I think you'll need to do a Manual Cross Tab, using Running Totals that count the number of records that fall within the criterion.

Madawc Williams (East Anglia, UK)

 

[lupins46]

You cannot report one record in two groups.
I think you have two choices.
Do more work in the database to build queries that give you separate records for starters and finishers.
You will also need a query to pull out every year and month that exists in a start date or term date.
You can then build a report based on left joins from the dates list to the other two lists.

Alternatively you can build arrays in CR to accumulate totals for each month and year.
In this case you will need a known range of dates so that you can assign a specific array position to each year-month. Then print the arrays.

Neither is simple, but the approach based on queries is the most flexible and easier of the two if you have access to the database.

 

[Madawc]

I was assuming that hires and terms were record types within a single table. It makes a big difference how the data is stored.

Madawc Williams (East Anglia, UK)

 

[loveyoursite]

The hire date and the term date is in the same table (EMPLOYEE).

 

[Madawc]

Then my method ought to work.

Madawc Williams (East Anglia, UK)

 

[loveyoursite]

Thanks everyone!