Crystal Reports: Including the same record in multiple groups

I am writing an employee turnover rate report, and I have run into this issue:

I am grouping employees that were hired within a specified date range, and employees that were fired within a specified date range. The problem is, many of these employees were both fired and hired in that same date range. So if I sort into groups, I end up with my first group containing all employees that were not only hired between date A and date B but also fired between those dates as well, and my subsequent group contains those employees who were fired between date A and date B but ONLY those who were hired outside of the date range, since those hired within the range have already been assigned to the first group.

I thought about just dealing with it and putting a third group into the report of "Employees both hired and terminated between xx and xx" however that ended up giving me a FOURTH sorted group of people who were hired in the date range and are still active. Which i have no idea why that happened.

I am attempting to group by the following fomula field:
If {PR_EMP.DATE_HIRED} in {?datefilter} then '0'
Else If {PR_EMP.TERMINATION_DATE} in {?datefilter} then '1'

and you cannot group on two separate fields, so it's not like i can make one formula field for checking if they were hired in the range and another field for checking if they were terminated, and group on those. If I could I would be ecstatic, and frankly I don't understand why you can't. (Group one: sort into this group if FieldA=1, Group two: sort into this group if FieldB=1)

It seems the only way i could do something like this is to make separate subreports, one for hires and one for terminations, but I don't know how to pass values from parent to child or frankly anything at all about subreports, and i feel like this should not be necessary anyway.

Any help is greatly appreciated.
Who is Participating?
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.

Ess KayEntrapenuerCommented:
why not?

Just make a new field in the query

Case  When hired before blah and fired before blah then "Before apocalypse" else "Post apocalypse" as NewGroup

Group by NewGroup
If {PR_EMP.DATE_HIRED} in {?datefilter} then and {PR_EMP.TERMINATION_DATE} in {?datefilter} then 1
else If {PR_EMP.DATE_HIRED} in {?datefilter} then 2
else If {PR_EMP.TERMINATION_DATE} in {?datefilter} then 3

this will return 1 for hired-fired, 2 for just hired and 3 for just fired
In Crystal a record can only be in 1 group.

First you need to determine what the report is supposed to show.
Your selection criteria will help determine that.
What is the selection formula?

An Employee can fall into 3 categories
Hired in the date range  (still active or fired outside the range)
Hired and Fired in the date range
Fired in the date range

Are you using the same date range for both date checks?

One way to get an employee into "both" groups is to use a Crystal command or a database view/stored procedure for the report

Something like

SELECT EmployeeName, "H" as HireFire
FROM EmployeeTable
WHERE HireDate >= Date1 and HireDate <= Date2
SELECT EmployeeName, "F" as HireFire
FROM EmployeeTable
WHERE FireDate >= Date1 and FireDate <= Date2

You can then group on the HireFire field

Vasto's code has a minor error in it

If {PR_EMP.DATE_HIRED} in {?datefilter} and {PR_EMP.TERMINATION_DATE} in {?datefilter} then 1
else If {PR_EMP.DATE_HIRED} in {?datefilter} then 2
else If {PR_EMP.TERMINATION_DATE} in {?datefilter} then 3

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ntobinAuthor Commented:
Thanks for your response mlmcc,

I knew I would have to do some crafty query mangling, but I have one issue then doing it that way,

With duplicate records available, what would be the best way to get a total count of active employees at the beginning and at the end of the date range without counting the duplicate entries? I haven't done much advanced work in Crystal, but is there a way to run a query from a function to just count the records manually rather than crafting strange formula fields and running totals and not dealing with duplicate entries? Sort of like MS Access's Dlookup function

Also, I actually currently have my code doing 3 groups just to see what results I could get,  with the following code:

If ({PR_EMP.DATE_HIRED} in {?datefilter} and {PR_EMP.TERMINATION_DATE} in {?datefilter}) then '0'
Else If {PR_EMP.DATE_HIRED} in {?datefilter} then '1'
Else If {PR_EMP.TERMINATION_DATE} in {?datefilter} then '2'

which is basically your code except I used 0,1,2 instead of 1,2,3. But as I mentioned above, it is sorting into 4 groups and I don't understand why. if someone is hired in the range and has not been terminated, the if-then statement does not process it and does not assign any number (0, 1, or 2 - the field is blank)

I will be putting your query suggestion to use and see if I can work around the duplicates for my counts (so I can actually calculate the turnover rate %), any ideas on how to do that in Crystal would be awesome. Like I said, I'm more familiar with Access (I know they are not the same kind of program but the workings are similar) but this is probably the 10th report or so I have had to write, every one has been one heck of a learning experience so far
Are you selecting ALL employees or just those that are hired/fired in the given period(s)?
If you want the active at start and end you will need to include all employees.

If you are limiting the report to just hired/fired employees in the period, the fourth group is employees that don't meet any of the criteria.  This occurs in 2 ways

1.  No termination date (NULL)
2.  Hired in the period and terminated outside the period.

If you are including ALL employees in the report then you also get the 4th group for
1.  Hired outside the period with no termination date
2. Hired outside the period and fired outside the period.

When Crystal encounters a NULL value in an expression the result is controlled by a setting in the formula editor.  You will either EXCEPTIONS OR NULL or DEFAULT VALUES FOR NULL.

Exceptions for Null means the formula terminates evaluation and the result is not determined.
Default Values for Null means a default value is used.  For dates this is generally 1 Jan 1900.

What value for active at the beginning and end of the period are you looking for?
If you mean for all employees then you need to include all employees in the report, if you are filtering to get just those hired/fired in the period then obviously the active at the start is 0.

TO get the active at start and end of the period create formulas and then use a SUM summary on them

To get those active at the start of the period use this formula
Name - ActiveaAtStart
If (IsNull({PR_EMP.TERMINATION_DATE}) OR {PR_EMP.TERMINATION_DATE} >= Minimum( {?datefilter})) AND {PR_EMP.DATE_HIRED}  <= Minimum( {?datefilter})  then

Open in new window

To get those active at the end of the period use this formula
Name - ActiveaAtEnd
If (IsNull({PR_EMP.TERMINATION_DATE}) OR {PR_EMP.TERMINATION_DATE} >= Maximum( {?datefilter})) AND {PR_EMP.DATE_HIRED}  <= Maximum( {?datefilter})  then

Open in new window

Try this for your groupings
If ({PR_EMP.DATE_HIRED} in {?datefilter} and Not IsNull({PR_EMP.TERMINATION_DATE}) AND {PR_EMP.TERMINATION_DATE} in {?datefilter} then 
Else If {PR_EMP.DATE_HIRED} in {?datefilter} then 
Else If Not IsNull({PR_EMP.TERMINATION_DATE}) AND {PR_EMP.TERMINATION_DATE} in {?datefilter} then 

Open in new window

0 - Hired/Fired in period
1 - Hired in Period
2 - Fired in period
3 - Hired outside period and (Fired outside period or Still Active)


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
Ess KayEntrapenuerCommented:
so heres your problem:

you have a start-date and end-date and the following:

1. people hired before start, fired before start-date (ignore)
2. people hired before start, fired after start-date, before end-date
3. people hired before start, not fired before end-date

4. people hired after start, fired after start-date, before end-date
5. people hired after start, not fired before end-date

6. people hired after end date (ignore)

so... solution: make 4 groups

1: Hired & Fired Employees (4)
2: Hired Employees (5)
3: Continuous  Employees (3)
4: Fired Employees (2)
ntobinAuthor Commented:
OK! Well I have written one insane SQL query, i know it is incredibly innefficient but I'm workin with data sets that are less than 100 people

My question now is, I don't know how to get parameters to work correctly with a crystal command. I have the following sql query:

I can run it natively (edit: I mean on the sql table directly) and get the recordset I am expecting, however I need to change the dates to parameters (edit: in crystal for the command), but I cannot seem to get it working.. I am using the following:

TO_DATE ('%{?Pm-?date1}%'. .... ) - I have tried it without quotes, without percents, etc.. doesn't seem to be working. I did set up the parameters in the command builder box. Is it because I am using it in the TO_DATE function, and if so how can I get around that?
Create the parameters in the command and make them datetime type.  Call them SatrtDate and EndDate

YOu can insert them in the command by dragging them to the right spot

WHERE  ("PR_EMP"."DATE_HIRED">= {?StartDate} AND "PR_EMP"."DATE_HIRED"< {?EndDate})

Open in new window

I don't think you need all these unions. If your database is SQLServer try this query

CASE WHEN {?DateFrom}<="PR_EMP"."DATE_HIRED" AND "PR_EMP"."DATE_HIRED" < {?DateTo} THEN 1 ELSE 0 AS HiredInPeriod,

Open in new window

It will return 2 flags - HiredInPeriod and TerminatedInPeriod
Need third flag when you are hired and terminated in the period.

Or will the query return 2 records for those people

ntobinAuthor Commented:
Thanks for all of your help guys, I have one last problem now...

I have everything grouped as you can see in my query. I have 5 unioned queries, one for Hires, Fires, Actives, and then one that counts how many people were active at the beginnning of the reporting range and another one that counts how many were active at the end. The problem is, if I want to use those columns for calculations (the total number of actives at start / actives at end) I have to make them visible on my report... I didn't realize that simply hiding the "Others" group causes that data to no longer be accessible. Is there some way I can use that data while hiding it on the report? it is unnecessary to display, but the data is vital - it gets around the problem of having duplicate records for my totals counts, which keeps the turnover rate accurate.
How are you hiding the OTHERS group?
Generally hiding a section doesn't affect the report that way.

What is your final query?

ntobinAuthor Commented:
Very easy to understand
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.