hwassinger
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.PTNA ME}) AND
({PHM_MAC_ERR_DTL.ERROR_CO DE}='47') AND NEXT({PHM_MAC_ERR_DTL.ERRO R_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_CO DE}='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
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.PTNA
({PHM_MAC_ERR_DTL.ERROR_CO
AND ({PHM_MAC_ERR_DTL.PAT_NUM}
abs(({@dt} - next({@dt}))*1440) <5 //minutes difference
THEN 10 else
IF {PHM_MAC_ERR_DTL.PTNAME} = previous({PHM_MAC_ERR_DTL.
({PHM_MAC_ERR_DTL.ERROR_CO
AND ({PHM_MAC_ERR_DTL.PAT_NUM}
abs(({@dt} - previous({@dt}))*1440) <5 //minutes difference
THEN 10
else 8
ASKER
But wont suppress still keep the blank spaces?
I want any rows where the code above is not true
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
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
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
ASKER
mlmcc
Where should I put that statement? In the record select??
Where should I put that statement? In the record select??
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
James
ASKER
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
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},{GroupFi eld}) = 1
mlmcc
You could use this to suppress
Count({SomeFIeld},{GroupFi
mlmcc
ASKER
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
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
James
ASKER
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_CO DE}='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.PTNA ME}) AND
({PHM_MAC_ERR_DTL.ERROR_CO DE}='47') AND NEXT({PHM_MAC_ERR_DTL.ERRO R_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_CO DE}='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
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.
({PHM_MAC_ERR_DTL.ERROR_CO
AND ({PHM_MAC_ERR_DTL.PAT_NUM}
abs(({@dt} - previous({@dt}))*1440) <=5 //minutes difference
THEN false ELSE
IF {PHM_MAC_ERR_DTL.PTNAME} = NEXT({PHM_MAC_ERR_DTL.PTNA
({PHM_MAC_ERR_DTL.ERROR_CO
AND ({PHM_MAC_ERR_DTL.PAT_NUM}
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.
({PHM_MAC_ERR_DTL.ERROR_CO
AND ({PHM_MAC_ERR_DTL.PAT_NUM}
abs(({@dt} - previous({@dt}))*1440) <=5 //minutes difference
THEN false else
FALSE
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
James
Which rows don't qualify?
mlmcc