Print Page | Close Window

Distinct records w/ too many selected fields

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=1320
Printed Date: 27 Apr 2024 at 7:18am


Topic: Distinct records w/ too many selected fields
Posted By: ewiner
Subject: Distinct records w/ too many selected fields
Date 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


Replies:
Posted By: BrianBischof
Date 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.


-------------
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>

Posted By: hilfy
Date 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


-------------
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: BrianBischof
Date 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>

Posted By: hilfy
Date Posted: 14 Sep 2007 at 3:31pm
I've used them frequently!
 
-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: ewiner
Date 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.


Print Page | Close Window