Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Distinct records w/ too many selected fields Post Reply Post New Topic
Author Message
ewiner
Newbie
Newbie


Joined: 14 Sep 2007
Location: United States
Online Status: Offline
Posts: 2
Quote ewiner Replybullet Topic: Distinct records w/ too many selected fields
    Posted: 14 Sep 2007 at 1:42pm
This is my first time using CR, so I apologize if this is obvious...

I'm getting information from a SQL Server 2000 database using a fairly simple query (just a few inner joins). There's one field in a table (I'll call it AnnoyingField) that has possible values of NULL, 0, or 1, and that row should only show up in the report if the value is NULL or 0. I put IsNull({AnnoyingField}) OR {AnnoyingField}=0 into the selection formula and it seemed to do the trick. The problem is that sometimes there are two rows in the underlying data, one with the NULL value for AnnoyingField and one with a 0, and otherwise identical. This is intentional (I didn't design this DB of course), but in this particular report, it needs to show up as one row. All the other values are the same, and AnnoyingField isn't even displayed in the report, so it just looks like a duplicate entry.

Normally, I'd accomplish this with a SELECT DISTINCT, but that doesn't work here, because Crystal Reports adds AnnoyingField to the list of fields being SELECTed from the database, even though that's unnecessary. The current query is

SELECT DISTINCT firstname, lastname, etc, AnnoyingField FROM tables WHERE AnnoyingField IS NULL OR AnnoyingField = 0

and it should be

SELECT DISTINCT firstname, lastname, etc FROM tables WHERE AnnoyingField IS NULL OR AnnoyingField = 0

Is there any way to do this? Thanks for any help.

-Eric
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 14 Sep 2007 at 2:18pm
CR lets you enter your own SQL query. In the list of database tables, select Add Command instead of selecting actual tables. Then copy the proper SQL to the command object.


Edited by BrianBischof - 14 Sep 2007 at 2:19pm
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 14 Sep 2007 at 3:17pm

Or, you could set up your grouping so that it's at a level above the AnnoyingField and put your data in the group header instead of in the details.  That way you would get your distinct data.

Another way is to set up a suppress formula on the line where your data resides that looks somewhat like this:

{table_with_AnnoyingField.key field} = previous({table_with_AnnoyingField.key field})

If you use either one of these methods and you're doing any counts or sums, you may have to use some additional formulas or running totals to avoid inflating them.
 
-Dell
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 14 Sep 2007 at 3:24pm
Ah. Those are good ideas.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 14 Sep 2007 at 3:31pm
I've used them frequently!
 
-Dell
IP IP Logged
ewiner
Newbie
Newbie


Joined: 14 Sep 2007
Location: United States
Online Status: Offline
Posts: 2
Quote ewiner Replybullet Posted: 14 Sep 2007 at 9:36pm
Thanks for the help. I used the suppression formula method, which worked perfectly, and I'm going to try to normalize the database to make this a nonissue in the future.
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.062 seconds.