Link to home
Start Free TrialLog in
Avatar of hwassinger
hwassingerFlag for United States of America

asked on

Crystal select - can value of 2 records qualify

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
Avatar of Mike McCracken
Mike McCracken

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

Which rows don't qualify?

mlmcc
Avatar of hwassinger

ASKER

But wont suppress still keep the blank spaces?

I want any rows where the code above is not true
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
> 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
mlmcc

Where should I put that statement? In the record select??
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
If there is only 1 do you want it to display or not?

You could use this to suppress

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

mlmcc
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
Did you try the count idea?

If Count({SomeField}) = 1 then
    TRUE
ELSE -rest of your test

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

 James
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
Is there any debugger tool in Crystal that would allow me to walk through a formulas to determine where it is kicking out?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A hearty THANK YOU to james & MLMCC... This darn issue has been costing me sleep but you've greatly helped me!
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
You're welcome.  Glad I could help.

 James