Print Page | Close Window

zentechinc

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=12620
Printed Date: 09 May 2024 at 1:24pm


Topic: zentechinc
Posted By: zentechinc
Subject: zentechinc
Date Posted: 15 Mar 2011 at 12:57pm
Okay, so i am trying to compare two different fields related by a customer number but differentiated by another field. The layout looks something like this:

cust_no, consump, mxu_id
0000001,      13,     LF
0000002,      10,     LF
0000003,     117,     HF
0000004,      43,     LF
0000004,      60,     HF
0000005,      91,     LF
0000005,      70,     HF

Ideally, i would like the report to return only those values where consumption (consump) is identified as being High Flow (HF) AND where the high flow consumption is greater than the Low Flow Consumption (LF). In short, it should only return customer number 0000004.

Is there any way to do this?

I have a selection query that works great for listing anything with HF and LF since mxu_id can contain quite a few other things.


Replies:
Posted By: lockwelle
Date Posted: 16 Mar 2011 at 3:33am
well, I would try grouping by cust_no, and sort by mxu_id.  Then I would suppress the group/details if the count for the group was 1, or if Next(consump) > consump.
 
at the very least, it's a start

Posted By: zentechinc
Date Posted: 16 Mar 2011 at 6:11am
Good advice. Im not in the office today but i will implement it tomorrow.

The problem with the Next Consump > consump is that the High Flow does not always come after Low Flow so i cannot rely on that to consistently output my desired data.

Posted By: DBlank
Date Posted: 16 Mar 2011 at 6:32am
to deal with ordering issues i would try a using a couple of formulas, grouping and then the select expert with a group condition
formula1 "LF_formula"
if mxu_id = 'LF' then consump else 0
formula 2 "HF_formula"
if mxu_id = 'HF' then consump else 0
group on cust_no
use the select expert in group selection as
distinctcount(mxu_id,cust_id)=2
and
maximum(@HF_formula,cust_id)>maximum(@LF_formula,cust_id)

Posted By: zentechinc
Date Posted: 18 Mar 2011 at 9:21am
...
DBlank, I love you.
Nah, JK. This is the first time ive had a chance to look at the issue in the past few days but DBlank's post looks promising. Thank you.


Print Page | Close Window