Print Page | Close Window

Remove Rows With An Empty Field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17468
Printed Date: 03 May 2024 at 8:34am


Topic: Remove Rows With An Empty Field
Posted By: arminkhodaei
Subject: Remove Rows With An Empty Field
Date Posted: 30 Aug 2012 at 10:19pm
Hello All;
First of all, Excuse me for my bad english :)
 
I Have A report from access DB. From 1 week ago, suddenly My Report get Two additional Record.
 
in all of my reports this two record shown.
 
so, i remove this records from my Database. But dont remove from my Reports !
 
This two record always showed in ID 1 and 2 !
 
is like This :
 
 
ID               Name               Family
1                 Joe                  x
1                 Mark                y
2                 Steev               h
2                 Mark                y
3
4              --Other is Good--
6
 
 
I "Suppress If Duplicated" check On ID Field , Then :
 
ID               Name               Family
1                 Joe                  x
                   Mark               y
2                 Steev               h
                   Mark               y
3
4              --Other is Good--
6
 
Now I want Delete This Two Rows !
 
Plaese Help Me ....


Replies:
Posted By: hilfy
Date Posted: 31 Aug 2012 at 3:58am
You'll need to suppress the whole section, not just the field.  To do this, go to the Section Expert for this section, click on the button to the right of the Suppress checkbox (do NOT check the checkbox!).  Enter something like the following formula:
 
{MyTable.IDField} = previous({MyTable.IDField})
 
You should also take a look at how your tables are linked together in the report - there's something in the data in the way that they're linked that is causing the second name to appear.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: arminkhodaei
Date Posted: 31 Aug 2012 at 7:57am
OK, Thanks.
 
But Can you explain more about "Previous" Function ?
 
How Does it work ?
 
For better Understanding ....

Posted By: comatt1
Date Posted: 31 Aug 2012 at 8:04am
if you do wanna clean up using a script for duplicates

DELETE
FROM Foo1
WHERE Foo1.ID IN

-- List 1 - all rows that have duplicates
(SELECT F.ID
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1))
AND Foo1.ID NOT IN

-- List 2 - one row from each set of duplicate
(SELECT Min(ID)
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1)
GROUP BY Field1, Field2);

...


Posted By: hilfy
Date Posted: 31 Aug 2012 at 8:25am
The Previous function looks at the value of a field or a formula in the previous record.  There is also a Next function that looks at the next value of a field or formula.
-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: arminkhodaei
Date Posted: 07 Sep 2012 at 8:46pm
I Do it, Any Effect !!!
 
{Report1.ID} = previous({Report1.ID})
 
I have :
 
ID               Name               Family
1                 Joe                  x
1                 Mark                y
2                 Steev               h
2                 Mark                y
3
4              --Other is Good--
6
 
I would check "Suppress if Dupplicate" of ID field ??

Posted By: hilfy
Date Posted: 08 Sep 2012 at 10:11am
Suppress if Duplicate on the ID field only suppresses the ID, not the rest of the field.
 
 
As I posted above, you need to suppress the whole section by right-clicking in the grey area to the left of the section and select Section Expert.  Click on the formula button to the right of the word "Suppress" (DO NOT mark the Suppress check-box!) and enter a formula like this:
 
{MyTable.IDField} = previous({MyTable.IDField})
 
Replace "MyTable.IDField" with the table and field name of the actual field that you are using.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: arminkhodaei
Date Posted: 09 Sep 2012 at 2:51am
Thanks Hilfy. Problem Resolved ! :D


Print Page | Close Window