We help IT Professionals succeed at work.

# Crystal select - can value of 2 records qualify

on
Medium Priority
382 Views
I want to know if it is possible for a set of conditions related to a row AND its next row to be the only data that qualifies for a report. I know I can suppress it but then I get a60 page report with data on 4 random pages.

I currenly have a formula that causes the 2 rows I want to have font & color changes but want to eliminate records that dont qualify.

IF {PHM_MAC_ERR_DTL.PTNAME} = NEXT({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='47') AND NEXT({PHM_MAC_ERR_DTL.ERROR_CODE})='2'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= NEXT({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - next({@dt}))*1440) <5      //minutes difference
THEN 10 else

IF {PHM_MAC_ERR_DTL.PTNAME} = previous({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='2') AND previous({PHM_MAC_ERR_DTL.ERROR_CODE})='47'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= previous({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - previous({@dt}))*1440) <5      //minutes difference
THEN 10
else 8
Comment
Watch Question

## View Solutions Only

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can suppress sections based on a formula that determines you don't want to see the rows.

Which rows don't qualify?

mlmcc

Commented:
But wont suppress still keep the blank spaces?

I want any rows where the code above is not true
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
It shouldn't

Use the above and have it return TRUE when you want it suppressed and FALSE when you want to show the records.

mlmcc
CERTIFIED EXPERT

Commented:
> But wont suppress still keep the blank spaces?

It depends on what those blank spaces are, and what you're suppressing.

If you're just using a formula to suppress some fields, then the sections will still be visible, unless the sections are completely blank and you use the "Suppress Blank Section" option in the Section Expert for that section.

If you use a formula to suppress a section (the entire section, not just a field in that section), then the section will not be visible on the report (no blank space) when the formula is true.

Also, if you're suppressing the detail section, but you also have a group on the report and the group header/footer is not suppressed, then you'll still see the group header/footer, even if the details are suppressed.  In that case, you would need to add a suppression formula to the group header/footer section too.

James

Commented:
mlmcc

Where should I put that statement? In the record select??
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
In the suppression formula

Right click the section in the left margin
Click SECTION EXPERT
Click the formula button to the right of SUPPRESS
Enter the formula

mlmcc
CERTIFIED EXPERT

Commented:
FYI, if you want to use tests similar to the ones that you posted, you'll have to use some kind of suppression (as mlmcc suggested).  You're using Next and Previous, and you can't use those in the record selection formula.

James

Commented:
mlmcc

This is 99% there , I will close this later today and award the points, I am just seeing a stray record slip through and am trying to work it out. I suspect they are either the only record in a group or the last and therefore my suppression not working
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If there is only 1 do you want it to display or not?

You could use this to suppress

Count({SomeFIeld},{GroupField}) = 1

mlmcc

Commented:
There should never be 1 record that meets the conditions as the only records that should qualify is when record 1 and record 2 meet certain conditions related to each other
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Did you try the count idea?

If Count({SomeField}) = 1 then
TRUE

mlmcc
CERTIFIED EXPERT

Commented:
In that last formula, you'd presumably need to include the group field in the Count.

James

Commented:
No I didn't i am unsure where / how to use it.... I have managed to suppress the first record but still cannot suppress the last.

If my logic is correct on the forst record I never want to check for a previous record and on the last record I never want to check for a next so I added some lines but still cannot suppress last record.

My formula is now

IF ONLASTRECORD AND {PHM_MAC_ERR_DTL.PTNAME} = previous({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='2') AND previous({PHM_MAC_ERR_DTL.ERROR_CODE})='47'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= previous({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - previous({@dt}))*1440) <=5      //minutes difference
THEN false ELSE
IF {PHM_MAC_ERR_DTL.PTNAME} = NEXT({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='47') AND NEXT({PHM_MAC_ERR_DTL.ERROR_CODE})='2'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= NEXT({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - next({@dt}))*1440) <=5      //minutes difference
THEN false else
IF ONFIRSTRECORD THEN TRUE ELSE
if onlastrecord then true else
IF {PHM_MAC_ERR_DTL.PTNAME} = previous({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='2') AND previous({PHM_MAC_ERR_DTL.ERROR_CODE})='47'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= previous({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - previous({@dt}))*1440) <=5      //minutes difference
THEN false else
FALSE

Commented:
Is there any debugger tool in Crystal that would allow me to walk through a formulas to determine where it is kicking out?
CERTIFIED EXPERT
Commented:
I'm not aware of a debugger tool.

IF ONLASTRECORD AND <Previous tests> THEN false
ELSE
IF <Next tests>

The first part will be false if this is not the last record, _or_ if any of the Previous tests are not true, so if the Previous tests are not true on the last record, you fall through to the Next tests, and I'm not sure what Next gives you on the last record.  There could be other problems with the formula too.

Try this:

IF (not OnFirstRecord) AND {PHM_MAC_ERR_DTL.PTNAME} = previous({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='2') AND previous({PHM_MAC_ERR_DTL.ERROR_CODE})='47'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= previous({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - previous({@dt}))*1440) <=5      //minutes difference
THEN
False
ELSE
IF (not OnLastRecord) and {PHM_MAC_ERR_DTL.PTNAME} = NEXT({PHM_MAC_ERR_DTL.PTNAME}) AND
({PHM_MAC_ERR_DTL.ERROR_CODE}='47') AND NEXT({PHM_MAC_ERR_DTL.ERROR_CODE})='2'
AND ({PHM_MAC_ERR_DTL.PAT_NUM}= NEXT({PHM_MAC_ERR_DTL.PAT_NUM})) AND
abs(({@dt} - next({@dt}))*1440) <=5      //minutes difference
THEN
false
else
True

I just added a (not OnFirstRecord) and (not OnLastRecord) test to the Previous and Next tests, respectively.

James

Commented:
A hearty THANK YOU to james & MLMCC... This darn issue has been costing me sleep but you've greatly helped me!
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I have seen a debugging tool but never used it.  I did a quick search and the Recrystallize team has one.

http://www.recrystallize.com/merchant/cortex/report-debugger.htm

I don't recall if this is the one I saw demoed or not.

mlmcc
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

James