Talk with the Author
 Crystal Reports Forum : General Information : Talk with the Author
Message Icon Topic: Selection Criteria - Having Post Reply Post New Topic
Author Message
Urgh
Newbie
Newbie
Avatar

Joined: 24 Mar 2008
Location: United States
Online Status: Offline
Posts: 5
Quote Urgh Replybullet Topic: Selection Criteria - Having
    Posted: 24 Mar 2008 at 4:14pm

Hi All,  I need some help!!!

APPLIES_TO PRIMARY_ID TRAN_TYPE CHARGES PAYMENTS ADJ BILL_TYPE POSTING_DATE LINE_OF_BUSINESS
7758807 8892181 P 0.00 6.44 0.00 350  03/20/2007 MEDI-CAL
7758807 9096311 A 0.00 0.00 -7.73 1  05/15/2007 PRIVATE PAY
7758807 9181988 A 0.00 0.00 1.29 350  06/05/2007 MEDI-CAL
7758807 9548360 P 0.00 -7.73 0.00 351  08/31/2007 MEDI-CAL
7758807 9548684 A 0.00 0.00 7.73 1  09/04/2007 PRIVATE PAY
9510229 9510229 C 42.00 0.00 0.00 145  08/23/2007 BLUE SHIELD
9510229 9562334 P 0.00 2.03 0.00 145  09/06/2007 BLUE SHIELD
9510229 9562335 A 0.00 0.00 39.10 145  09/06/2007 BLUE SHIELD
9510229 9725413 P 0.00 0.87 0.00 1  10/24/2007 PRIVATE PAY
9874375 9874375 C 248.00 0.00 0.00 145  11/29/2007 BLUE SHIELD
9874375 9937925 P 0.00 63.62 0.00 145  12/14/2007 BLUE SHIELD
9874375 9937926 A 0.00 0.00 139.38 145  12/14/2007 BLUE SHIELD
 
I'm trying to do a cross tab report by Line of Business with Charges (C), Payments(P) and Adjustments(A).
 
However,  I only want them for a range of posting dates that have a tran type of C.  The Applies to and the Primary will equal each other when the transaction type is a charge (C).
 
Next I need to eliminate those items that C-P-A (for the applies to item) are not equal to 0.
 
I've tried to do this so many different ways and get real close but I can't get the total formulat to work. 
 
Any sugestions would be greatly appreciated.
 
-Urgh
 
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 25 Mar 2008 at 8:52am
Since you have tried many ways and are coming very close, it would be helpful to show us what you have right now. Otherwise, any attempts to help will probably be recreating what you've already attempted.
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
Urgh
Newbie
Newbie
Avatar

Joined: 24 Mar 2008
Location: United States
Online Status: Offline
Posts: 5
Quote Urgh Replybullet Posted: 25 Mar 2008 at 12:05pm
I've geen able to handle the line of business issue by creating a formula 
BT=:maximum ({Sheet1_.PROD_BILL_TYPE},Sheet1_.APPLIES_TO_ITEM}).
 
Then I created another variable:
LOB: using case:111 222    for each line of business.
 
I created a group by APPLIES_TO_ITEM with totals for Charges, Payments and Adjustments. Which gives me a nice clean summary and looks great in the cross tab.  The only problem is I onlyd want the groups that total zero.
 
By the way your Crystal Reports Encyclopedia book is awesome.  I must have sold a dozen or so.  It's always on my desk and is very loved with lots of pretty tabs all over it (for reference).
 
Thanks for your help!!!  -Urgh
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 25 Mar 2008 at 7:37pm
Glad you like the book (and I'm glad that people keep buying more copies!)

Re your question, have you tried using a record selection formula based on the group value? That way you can filter out the records where the group value isn't equal to zero. See page the section "Selecting Records for Grouping" starting on page 164 in my book.

-Brian

You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
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
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.016 seconds.