Solved

Crystal select - can value of 2 records qualify

Posted on 2012-03-13
18
262 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:hwassinger
  • 7
  • 6
  • 5
18 Comments
 
LVL 100

Expert Comment

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

Which rows don't qualify?

mlmcc
0
 

Author Comment

by:hwassinger
ID: 37717502
But wont suppress still keep the blank spaces?

I want any rows where the code above is not true
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37717509
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37718814
> 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
0
 

Author Comment

by:hwassinger
ID: 37725175
mlmcc

Where should I put that statement? In the record select??
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 37725188
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37727952
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
0
 

Author Comment

by:hwassinger
ID: 37728893
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37729216
If there is only 1 do you want it to display or not?

You could use this to suppress

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

mlmcc
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:hwassinger
ID: 37730109
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37730680
Did you try the count idea?

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

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 37731962
In that last formula, you'd presumably need to include the group field in the Count.

 James
0
 

Author Comment

by:hwassinger
ID: 37744857
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
0
 

Author Comment

by:hwassinger
ID: 37744868
Is there any debugger tool in Crystal that would allow me to walk through a formulas to determine where it is kicking out?
0
 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
ID: 37745840
I'm not aware of a debugger tool.

 Your formula starts with

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
0
 

Author Closing Comment

by:hwassinger
ID: 37747127
A hearty THANK YOU to james & MLMCC... This darn issue has been costing me sleep but you've greatly helped me!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37747391
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37747454
You're welcome.  Glad I could help.

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now