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.
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.
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
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
UNION
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
mlmcc
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
UNION
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
mlmcc
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
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?
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
YOu can insert them in the command by dragging them to the right spot
SELECT "PR_EMP"."DATE_HIRED", "PR_EMP"."TERMINATION_DATE", "PR_EMP"."EMPNO", "PR_EMP"."FIRST_NAME", "PR_EMP"."LAST_NAME", "PR_EMP"."EMP_STATUS_ID", 'H' AS "HIREFIREACTIVE", '0' AS "ACTIVEATSTART", '0' AS "ACTIVEATEND"
FROM "IQMS"."PR_EMP"
WHERE ("PR_EMP"."DATE_HIRED">= {?StartDate} AND "PR_EMP"."DATE_HIRED"< {?EndDate})
UNION
...
I don't think you need all these unions. If your database is SQLServer try this query
It will return 2 flags - HiredInPeriod and TerminatedInPeriod
SELECT "PR_EMP"."DATE_HIRED", "PR_EMP"."TERMINATION_DATE", "PR_EMP"."EMPNO", "PR_EMP"."FIRST_NAME", "PR_EMP"."LAST_NAME", "PR_EMP"."EMP_STATUS_ID",
CASE WHEN {?DateFrom}<="PR_EMP"."DATE_HIRED" AND "PR_EMP"."DATE_HIRED" < {?DateTo} THEN 1 ELSE 0 AS HiredInPeriod,
CASE WHEN {?DateFrom}<="PR_EMP"."TERMINATION_DATE" AND "PR_EMP"."TERMINATION_DATE" < {?DateTo} THEN 1 ELSE 0 AS TerminatedInPeriod
FROM "IQMS"."PR_EMP"
WHERE "PR_EMP"."DATE_HIRED"<={?DateTo}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
mlmcc
Or will the query return 2 records for those people
mlmcc
ASKER
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.
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?
mlmcc
Generally hiding a section doesn't affect the report that way.
What is your final query?
mlmcc
ASKER
Very easy to understand
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