Print Page | Close Window

Selecting Entire Group if one Record Meets Select

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=3570
Printed Date: 06 May 2024 at 8:24am


Topic: Selecting Entire Group if one Record Meets Select
Posted By: Urgh
Subject: Selecting Entire Group if one Record Meets Select
Date Posted: 01 Jul 2008 at 9:46am
Hi Brian,
 
There has to be an easy way to do this without exporting the selections and linking them back in.  My data looks like this:
 
GROUP :   RO, MO, SO, TO ...
INS:  PROGR, AETN, BLSH, ...
ACCOUNT:  1234567, 2345678...
INCIDENT:  1,2,3,4,5...
CPT:  12345, 23456, ...
MOD:  15, 16, 17 ...
CHARGES:  $
PAYMENTS:  $
ADJUSTMENTS:  $
 
I am grouping on Group, Insurance, and Account&Incident&CPT&MOD (as a concatenated variable).  I created a variabe call "Balance" which is Charges - Payments - Adjustments.  I can filter out the detail records that have a balance of zero but I can't seem to get the group records to only include those with a zero balance without creating an external file and joining and linking back in from the external file.  My problem is there is goin to be soo much data that I won't be able to link it externally in excel and will ultimately have to create an access database which will be huge.  Is there another way?
 
Thank You,
MM aka Urgh!
 
 


Replies:
Posted By: hilfy
Date Posted: 02 Jul 2008 at 12:13pm

There are two possible ways of doing this.

1.  Use a command instead of tables.  A command is a SQL statement that will run in the database.  With the exception of parameters, it will follow the SQL syntax for your database instead of the Crystal formula syntax.  You could put an "exists" condition in your where clause to get only the records that you want.  Use this if your "Balance" calculation covers multiple records or if you need to look at multiple tables to get the balance.

2.  Add a second copy of the table to your report.  Crystal will tell you that the table already exists and ask if you want to rename it - click on 'Yes'.  You'll see a second copy of the table with '_1' at the end of the name. 
- Link from the original table to this one on whatever fields get you to the Group level for your report. 
- Set the select formula on the report to only select those records in the original table where the balance is 0. 
- On the Database menu, you may need to turn on 'Select Distinct Records'.
- Group on the fields from the original table. 
- Use the table with the '_1' alias for your details.
 
-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: Urgh
Date Posted: 03 Jul 2008 at 12:51pm
How do I put an exists on a group of items.  In this example it would be at the CPT level so that would be for the GROUP,INS,ACCOUNT,INCIDENT & CPT?  I'm new at this so I don't know what the syntax should be.


Print Page | Close Window