| Author |
Message |
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
   Topic: Remove multiple records on report
Posted: 11 Oct 2012 at 7:57am |
Hi, I am in Crystal 2008. In my report, I have some employees who will appear more than one time in my report. My next step is to remove the employees that appear more than once. All the data is in Group footer 3. I've played with doing a count of Employee ID, Distinct Count, or a Running total, thinking I could use Select Expert to select employees where the total equals 1, but I can't get any of the count formulas to work. So, here is how the employee appears:
| Item Changed |
Employee |
Position |
Name |
| Transfer out of |
1234 |
550 |
Jones, Bob |
| Transfer into |
1234 |
550 |
Jones, Bob |
| New Hire |
783 |
888 |
Anderson, Jan |
| Termination |
123 |
530 |
Peterson, Sal |
I need to remove the employee that appears twice. Any recommendations?
Edited by thummel1 - 11 Oct 2012 at 7:58am
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 8:12am |
removing is trickier than suppressing
to suppress
group on employee
place the fiels you want to see in the group header or footer
suppress the detail sectrion and the un used header or footer
or
in the section expert put a conditional suppression fomrula on the detail seciton
table.employeeid=previous(table.employeeid)
note that suppressing rows or data does NOT exclude them from summarizations (counts, sums, averages, etc.)
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 11 Oct 2012 at 8:21am |
Thanks, I will try your second option. I can't really group any more than I have (maybe I could but this is such a complex report I don't want to risk messing it up based on the grouping). Not sure if the data will "remove" if the first data field is different (Transfer in and a Transfer Out record). Basically, this suggests the emplyee was an internal transfer and we don't care about those. We only want employees who are new to the areas specified or have left. Will give this a shot and let you know the outcome...
Edited by thummel1 - 11 Oct 2012 at 8:22am
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 11 Oct 2012 at 8:34am |
|
Nope, this did not work. That actually suppressed everyone in the report.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 11 Oct 2012 at 8:37am |
|
Is there a way to get the same data above to provide a count per number of times the records appears on the report? if I could do that, then I could select criteria based on the count. Or, maybe I could conditionally suppress if I could write a formula to say something like 'if employee ID = Previous Employee ID, and previous (Item Changed) = "Transferred Out"? I could give that a try?
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 8:38am |
is the sample data you showed a group level or detial section level?
what is the exact formula you used?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 8:39am |
|
you can do select criteria on summary data but it will exclude the whole group which is not what you want.
Edited by DBlank - 11 Oct 2012 at 8:40am
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 11 Oct 2012 at 8:45am |
Here's how I started conditionally suppress formula :
({EMPLOYEE.EMPLOYEE})=previous({EMPLOYEE.EMPLOYEE}) and previous({EMPLOYEE.EMPLOYEE}) = "Transferred out of FVMNATC"
I know the formula above is wrong. What I am trying to say is (in simple terms), suppress both employee records If the employee ID = the previous employee ID, and if the Previous employee's "Item Changed" = "Transferred out of FVMNATC"
Make sense?
Edited by thummel1 - 11 Oct 2012 at 8:46am
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Oct 2012 at 8:50am |
|
so in your sample data you do not want to see Bob jones at all?
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 11 Oct 2012 at 8:52am |
|
Correct. This is because by way of him appearing as a transfer out, and a transfer in, this means he transferred from one area to another within the population I am evaluating, which we do not need.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
|
|
