| Author |
Message |
Ace76
Newbie
Joined: 13 Jun 2013
Location: Canada
Online Status: Offline
Posts: 7
|
   Topic: Supressing records based on specific criteria
Posted: 13 Jun 2013 at 12:36pm |
I am using Crystal 2008.
I am developing a report the displays a list of tasks and their due dates.
There is the potential for tasks to show in the list more than once if they have been assigned multiple due dates.
I want to supress any tasks that duplicate, however I still want to show the entry with the earliest due date.
Please help as I am very new to Crystal Reports and still learning.
Thanks!
Ace
|
 IP Logged |
|
|
praveeng
Senior Member
Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
|
Posted: 13 Jun 2013 at 11:09pm |
Hi Ace,
1. Create a Group on Task field and place Date field in the Group header.
2. Suppress the Details section and Group footer section.
3. Change Task field font type to Normal in Group Header.
--Praveen G
|
|
Praveen Guntuka,
praveen_guntuka@yahoo.com
|
|
IP Logged |
|
Ace76
Newbie
Joined: 13 Jun 2013
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 17 Jun 2013 at 5:21am |
Thanks Praveen, however I am realizing now that I need to be able to filter based on the task code, a reference number and the date.
Following your suggestion I am now missing records that I don't want filtered out as they have a different reference number.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Jun 2013 at 6:19am |
the data is still in the report it is just hidden from view.
You can alter the grouping so it uses a formula to combines the task code and reference number.
or add a second level of grouping on the reference code.
Be aware everytime you group you may change your sorting.
|
|
IP Logged |
|
Ace76
Newbie
Joined: 13 Jun 2013
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 17 Jun 2013 at 6:40am |
By adding a second group I have fixed the problem of missing records where the task code dulicated but the reference number was different for each entry.
How ever now I am missing the records where the task code and reference number is the same but the due date is different. And I only want to show the record with the earliest due date.
Any assitance is appreciated.
Thanks!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Jun 2013 at 7:19am |
add another grouping on duedate
make sure you set it to 'per day'
Edited by DBlank - 17 Jun 2013 at 7:20am
|
|
IP Logged |
|
Ace76
Newbie
Joined: 13 Jun 2013
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 17 Jun 2013 at 8:25am |
Ok this is what I have done so far.
Group Header #1 = Page Header
Group Header #2 = Due Date in ascending order for each day - this section is suppressed
Group Header #3 = Reference Number in original order - this section is suppressed
Group Header # 4 = Task Code in original order - The results are displayed here
However the records that have matching task code and reference number as still showing multiple rows.
How can I hide the row(s) that do not contain the earliest due date?
Thanks!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Jun 2013 at 8:57am |
|
can you post some sample row level data (ungrouped) and explain how you want it displayed?
|
|
IP Logged |
|
Ace76
Newbie
Joined: 13 Jun 2013
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 17 Jun 2013 at 9:24am |
Here is the raw data:
|
REF_NO |
TASK_CD |
TASK_NAME |
DUE_IN_QT |
DUE_IN |
DEADLINE |
DEVIATION_QT |
|
|
T5ZJH20004GPG |
EA-155-1940-0011 / 00 [MOD HAWK/2234] |
MODIFICATION - START VALVE ASSEMBLY |
7000 |
HOURS |
02-FEB-2021, 11:59:59 PM |
0 |
|
|
T5ZJH20004GPG |
EA-155-1940-0011 / 00 [MOD HAWK/2234] |
MODIFICATION - START VALVE ASSEMBLY |
|
CYR |
04-SEP-2012, 11:59:59 PM |
2 |
|
|
T5ZJH20005M2B |
EA-155-6040-0006 / 00 (Part 1) |
INSPECT VHF ANTENNA CABLE CLAMP FOR SECURITY [CSTI/HAWK/131A] |
|
CDY |
23-JUN-2013, 11:59:59 PM |
0 |
|
|
T5ZJH20005M2D |
EA-155-6040-0006 / 00 (Part 2) |
REPLACE VHF ANTENNA CABLE CLAMP SCREWS [CSI/HAWK/131A] |
|
CYR |
26-MAY-2014, 11:59:59 PM |
0 |
|
The Due date column is calculated by Crystal depending on the value in the devation_qt column. If it equals 0 then it is the same as the deadline. If it's greater than 0 then it looks at the due_in column and increased the appropriate part of the deadline depending on what is showing in that column. ie: CYR = year
What I want to see all rows where the ref_no and task_cd combination are unique. And where the combination is not unique I want to show the row that holds the earliest due date.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Jun 2013 at 10:34am |
create a formula
//gouping
table.ref_no + '-' + table,task_cd
group on this formula
sort the details by due date ascending
insert a GHb
move all of your fields onto the group header b
suppress the details and GF section and GHa
Edited by DBlank - 17 Jun 2013 at 10:34am
|
|
IP Logged |
|
|
|
