Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Remove Rows With An Empty Field Post Reply Post New Topic
Author Message
arminkhodaei
Newbie
Newbie


Joined: 30 Aug 2012
Online Status: Offline
Posts: 5
Quote arminkhodaei Replybullet Topic: Remove Rows With An Empty Field
    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 ....


Edited by arminkhodaei - 30 Aug 2012 at 10:56pm
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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


Edited by hilfy - 31 Aug 2012 at 4:06am
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
arminkhodaei
Newbie
Newbie


Joined: 30 Aug 2012
Online Status: Offline
Posts: 5
Quote arminkhodaei Replybullet Posted: 31 Aug 2012 at 7:57am
OK, Thanks.
 
But Can you explain more about "Previous" Function ?
 
How Does it work ?
 
For better Understanding ....
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet 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);

...

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
arminkhodaei
Newbie
Newbie


Joined: 30 Aug 2012
Online Status: Offline
Posts: 5
Quote arminkhodaei Replybullet 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 ??
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
arminkhodaei
Newbie
Newbie


Joined: 30 Aug 2012
Online Status: Offline
Posts: 5
Quote arminkhodaei Replybullet Posted: 09 Sep 2012 at 2:51am
Thanks Hilfy. Problem Resolved ! :D
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.018 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.