Suppress group if duplicate names in detail
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22151
Printed Date: 01 May 2024 at 8:50am
Topic: Suppress group if duplicate names in detail
Posted By: wanttoknowmore
Subject: Suppress group if duplicate names in detail
Date Posted: 18 Nov 2016 at 8:05am
|
I have a fairly simple report, that I need to complicate. [IMG]smileys/smiley5.gif" align="middle" /> It's for our payroll department, and she wants to show duplicate ACH accounts, for employees. She's checking for data entry errors.
Report uses 2 tables, employee and bank account, and I'm printing rows for any bank account that has at least 2 employees with that same account. (This is normally a couple that is using the same bank account - we are a school division so lots of married employees.) So usually there will be a group header (bank acct# and the count of duplicates), and 2 detail rows (emp#, name, bank account). There are a few groups that have 3 rows (I guess 2 parents and a child). I have this in the group selection formula: Count ({EMACHDEPST.EBNK_ACCT_NBR}, {EMACHDEPST.EBNK_ACCT_NBR}) > 1 And honestly - I don't even know why/ that^^ works, but it does. (If anyone wants to explain that to me, please feel free! Yes I wrote the report but I can't remember how I came up with that!) I then have this in the "suppress drill down" formula, in the Group Header: If Count({EMACHDEPST.EBNK_ACCT_NBR}) < 2 then TRUE So if there are 2 or more employees with the same bank account, they are printed. THAT works. Now the complication. User wants to suppress the group if the last name is the same. So say I have this... 12345 Jones Adam 1122334455 54321 Jones Mary 1122334455 Bank acct 1122334455 2 32156 Smith John 998877 5462 White Sara 998877 Bank acct 998877 2 So I would want to suppress the first group - the Jones - due to the duplicate name. Racking my brain here but I'm stumped. How do I suppress the group for a duplicate name? And if there are 3 records, and one is different...I might need to also look at the home address and suppress on that as well. Appreciate your help!! |
Replies:
Posted By: kevlray
Date Posted: 18 Nov 2016 at 9:40am
|
The group selection formula is pretty easy. Only show records where the count of {EMACHDEPST.EBNK_ACCT_NBR} grouped by {EMACHDEPST.EBNK_ACCT_NBR} is greater than one. Unfortunately I cannot think clear enough for your second question (I think you need a sub-report). |
Posted By: DBlank
Date Posted: 21 Nov 2016 at 3:07am
|
look at your grouping and start using comparatives of counts vs. distinct counts at that group level until you get all criteria you want.
I am not entirely following all the criteria so here is an example of one possible criteria... Count(lastname,EMACHDEPST.EBNK_ACCT_NBR) <> DISTINCTCOUNT(lastname,EMACHDEPST.EBNK_ACCT_NBR) |
Posted By: wanttoknowmore
Date Posted: 21 Nov 2016 at 4:02am
|
I'm sorry - not following.
My groups are based on account numbers. If 2 or more people have the same account number, then that group is printed. However, if 2 of the people in the group, have the same last name, then I want to suppress that group. I feel like I need to add something to check the last names for duplicates, but I don't know where or how. |
Posted By: DBlank
Date Posted: 22 Nov 2016 at 2:40am
| so if the count of the last name in that group = distinct count of last names in that group all of the people in that group have a unique last name. if not, at least two people share a name. Use that logic for the show/suppress |
Posted By: wanttoknowmore
Date Posted: 22 Nov 2016 at 8:27am
|
I understand what you are saying, just that I don't know where to do that. Sorry I am not a Crystal expert at ALL.
Where I have this: Count ({EMACHDEPST.EBNK_ACCT_NBR}, {EMACHDEPST.EBNK_ACCT_NBR}) > 1 Do I put a second count in there? |
Posted By: DBlank
Date Posted: 22 Nov 2016 at 10:34am
|
You can completely exclude groups using the group select or you can suppress sections using it.
I don't know what your preference is nor where you are currently using the condition of Count ({EMACHDEPST.EBNK_ACCT_NBR}, {EMACHDEPST.EBNK_ACCT_NBR}) > 1 You would keep your existing condition and append the rest to it with an "AND ..." |
Posted By: wanttoknowmore
Date Posted: 22 Nov 2016 at 10:56am
|
[URL=http://s297.photobucket.com/user/chesapeakemom/media/crystal3_zpswbwn9we0.jpg.html][IMG]http://i297.photobucket.com/albums/mm235/chesapeakemom/crystal3_zpswbwn9we0.jpg" />
|
Posted By: wanttoknowmore
Date Posted: 23 Nov 2016 at 5:50am
|
Still hoping for some guidance here. I have removed the stuff that I had added to get back to a working report. I'm hoping that if I present a clearer picture of what I have, someone can help. (FWIW, our Crystal report "guru" here is also stumped by this!!
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_page_zpspjvdfkzh.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_page3_zpsmqijimbt.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_group_zps7jdtgcxg.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_date_convert_zpst6lrdrkr.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_group_count_zpsu9adie1f.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystal_suppress_single_accounts_zps17asfcbb.jpg.html"> 
http://s297.photobucket.com/user/chesapeakemom/media/Crystal/crystalGFsuppress_zpsmi21qo84.jpg.html">[IMG">http://i297.photobucket.com/albums/mm235/chesapeakemom/Crystal/crystalGFsuppress_zpsmi21qo84.jpg" /> So those are all the "pieces" I have in the report, right now. And for just printing the employees that have the same accounts, it works. I've not been able to figure out how to suppress the groups where the last names match. I only want to print a group, if the last names are different. |
Posted By: DBlank
Date Posted: 23 Nov 2016 at 6:54am
|
You can add it all into the group selection process (formula) if you want.
I think all 3 conditions are met by using: DISTINCTCOUNT({EMPLOYEE.Last_Name}, {EMACHDEPST.EBNK_ACCT_NBR}) > 1 |
Posted By: wanttoknowmore
Date Posted: 23 Nov 2016 at 7:43am
|
Oh my gosh that WORKS!!! Thank you!!!
So that I understand...I thought I only had 2 conditions: 1) print employees by account, where there is more than one employee 2) in that ^^ group, suppress the ones having matching names What are the three conditions that you see? (Just trying to see this more logically as I have a feeling I am going to have to do more reports in the future!) Oh...another question...do I leave that Group Header suppression formula in there? Is that still needed or does the group selection take care of it? I took it out, and it does look like it's not needed now. Just want to be sure. :-) 
|
Posted By: DBlank
Date Posted: 23 Nov 2016 at 8:21am
|
sorry, I meant two conditions.
You can remove the group header suppression as it is redundant to the group selection condition (in this case). |
Posted By: wanttoknowmore
Date Posted: 28 Nov 2016 at 3:36am
| Excellent. Thank you so much for all your help. |