Solved

Monitor group if there is 2 exact same values directly after each other

Posted on 2011-03-10
34
218 Views
Last Modified: 2012-05-11
I am grouping in Crystal reports and have a field that I am trying to monitor for 2 duplicated values should they follow directly after another at any time. Please see example below :

IDTAG#14567 {Groupname}
Date                       Device   Tagtype  MovementType  Firstname
01/01/11 08:15am  Entry        AVI             0                   AnnaAVI1
01/01/11 08:25am  Entry        AVI             0                   AnnaAVI2
01/01/11 11:35am  Exit           AVI             4                   AnnaAVI2
01/01/11 12:05pm  Entry        AVI              0                  AnnaAVI1
01/01/11 15:24pm  Exit           AVI             4                   AnnaAVI1
01/01/11 16:00pm  Exit           AVI             4                   AnnaAVI2

I need a Boolean 'True' value output only if {MovementType} has 2 value's of '0' followed in direct succession, it doesn't matter when it happens within the specific group.
0
Comment
Question by:John-S Pretorius
  • 16
  • 11
  • 7
34 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 total points
Comment Utility
Try this

In the detail section
WhilePrintingRecords;
Global NumberVar ZerosFound;
If Not OnFirstRecord  and {Movement} = 0  then
    if Previous({GroupField}) = {GroupField} AND Previous({Movement}) = 0 then
        "MONITOR"
    else
        ""
Else
    ""

mlmcc
0
 

Author Comment

by:John-S Pretorius
Comment Utility
No result, just a empty field.

I am also attatching the .rpt with some data, if you can help with this it would be great. This is with a similar issue we had but I am taking a new approach - if we can identify 2 * '0's directly after another it will cover all possible violations. ViolationJohnNEW.rpt
0
 

Author Comment

by:John-S Pretorius
Comment Utility
My bad - I just had to find a transaction that was actually in violation : 982978 is in violation and shows monitor. How do we just show the ones that is reflecting this value ?
0
 

Author Comment

by:John-S Pretorius
Comment Utility
Do you think supressing and a sub report will be able to take care of this ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
There is no easy way to show just the monitor ones.  The only way I can thin to do it would be in a subreport that is blank if it doesn't qualify.

mlmcc
0
 

Author Comment

by:John-S Pretorius
Comment Utility
can you help with the subreport design please? that is still very new to me.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try this report.

I added a subreport.  You will need to edit the subreport and add your database table to it.
It won't work as is because there is no database.

mlmcc
ViolationJohnNEWRev1.rpt
0
 

Author Comment

by:John-S Pretorius
Comment Utility
This is where I'm rather new, and having problems with the subreport, I have re-assigned the database but it seems that the sub-report also needs to be tied in with the database since when I refresh it (subreport) does not recognise the fields and I don't see them as available to be added.
0
 

Author Comment

by:John-S Pretorius
Comment Utility
OK, Nevermind - I figured that out. Not sure what it was suppose to do but now it seems to be suppressing all the data and all I see is 80 pages of blanks and the 2 'monitor' violations in those pages.

Is it possible to to show the data for those 2 violations ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you upload the new report.  I may have the suppress options set wrong.

mlmcc
0
 

Author Comment

by:John-S Pretorius
Comment Utility
Here we go :  ViolationJohnNEWRev1.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You don't have any data in the report.

Where is the subreport I added?

mlmcc
0
 

Author Comment

by:John-S Pretorius
Comment Utility
I just opened the report and the data is there, this is what happened when I ran the report, it is all suppressed and if you doubleclick on the header the subreport will open...?

Unless I implemented the subreport wrong.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I don't see the subreport when I look at the report in the designer.

mlmcc
0
 

Author Comment

by:John-S Pretorius
Comment Utility
How was I supposed to add the subreport into the main report, maybe I'm doing something wrong.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I added it into the swample I gave you.  You just needed to double click it in the designer and assign the data source.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
mlmcc had added a subreport to the report that he posted, but he couldn't add your table to it, since he doesn't have your db, so he created a subreport with no datasource and you were supposed to add your table to it.  I've tried to work around that.  Rather than try to create a new subreport, I just imported your report as a subreport, so the table was already included.  I started with mlmcc's version of the report, imported the report as a subreport, made changes to the new subreport and tried to duplicate what mlmcc was doing in his subreport, and then replaced his subreport (with no datasource), with my version.  It looks OK, but I can't test it.

 Since the main report is grouped on UserIdTag and the subreport is in the group header, I added a UserID parameter to the subreport and changed the record selection to only read the records where UserIdTag = the UserID parameter, and linked UserIdTag in the main report to the UserID parameter in the subreport.  IOW, the subreport should only read the records for the current UserIdTag.

 Also, hopefully this won't be a problem, but I created this report from mlmcc's version, and when I opened his report, I got an error saying that the saved data was being discarded because a formula wouldn't compile (so I had no saved data to try to test my version with).  This usually means that a formula is using some custom function that I don't have, but I didn't see anything like that, so I don't know why I got that error.  The point is that there could be something "off" about this version.  I really don't know.  FWIW, I got no error when I opened the first version of the report that you posted, so whatever is causing that error appears to be in something that mlmcc added, but I don't see anything in any of the formulas.

 James

ViolationJohnNEWRev1-James.rpt
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
That was an interesting idea.  

I went baclk and took the original report and inserted it as a subreport.  WHen I tried to preview it, the subreport requested the database so it didn't work.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 125 total points
Comment Utility
The main idea wasn't so that you or I could run it (since I had no saved data, there was no chance that it was going to work for me anyway).  I was just trying to provide a more "complete" solution, where John didn't have to add the table to the subreport (since he appeared to be having some trouble with that), not to mention adding the link to UserIdTag in the subreport, so that it would only read the records for the current UserIdTag (which I don't think you had mentioned).

 James
0
 

Author Comment

by:John-S Pretorius
Comment Utility
I think this can work.....

James, Your approach does capture the violations correctly but I have to go thru page by page for the subreport to run. or save report with data (17 pages takes about 2 minutes to complete - and I can work this as I am uploading a run report every 10 minutes to Crystal.com for the customer)

Is there a way to Automatically just group the these violations without the group header information and include all pages.

See report with data and also image.

I appreciate the efforts, this is what I am looking for - just need some further magic. Saving looks strange Report detaials ViolationJohnNEWRev1-James.rpt
0
 

Author Comment

by:John-S Pretorius
Comment Utility
I was also trying to actually include this image : I'm not sure why I get it each time I save the report. I originally started using Crystal reports 2008 SP1 and recently upgraded to SP3 with the Hotfixes 3 package installed Saving the file
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
If you save it, it should get rid of the message.  It is just a warning that the file format has changed.  It apparently thinks the report was created with CR8.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
One way to force it to build the full report is to use the N of M as the page numbering.  

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
For the record, this is mlmcc's approach, not mine.  I was just trying to fill in some blanks.

 To get CR to produce all of the pages, try mlmcc's suggestion about using "Page N of M" on the report.  He knows more about that than I do.  If you don't want to see the page numbers, I imagine that you could suppress the field and it would still have the same effect.

 I was writing some other comments when I realized something.

 I think there's a simple problem with how mlmcc set things up when he added the subreport.  The subreport and the @DispGroupName formula are in GH1.  The formula displays the GroupName variable, and the subreport sets that variable.  I'm assuming that the intent is for the subreport to be run and set the variable, and _then_ the formula to display the value in that variable.  The problem is that I don't know which will be done first, the subreport or the formula, and in this case, I think CR is evaluating the formula first.

 If I'm right, you need to create a new group header section below GH1 (so you have GH1a and GH1b) and move @DispGroupName to GH1b.  I can't test that because when I move @DispGroupName, CR wants to connect to the datasource, which I don't have.  If you do that, you'll probably also need/want to change how GH1a (the section with the subreport) is suppressed.  I covered that in one of my other comments (below).

 The rest of this is stuff that I wrote before I noticed the subreport/formula issue above.  If I'm right about that, some of this may not be relevant anymore.


 > Is there a way to Automatically just group the these violations
 > without the group header information and include all pages.

 I'm not sure what you're asking there, but I did notice that when I opened the new version of the report (with the saved data), there were a bunch of blank GH1 sections before the first detail, and you don't seem to have those GH1 sections in your screenshots.  If those are a problem, they're there because, while the subreport is not producing any visible output, it _is_ producing sections, so CR doesn't see it as being "blank", so the subreport is not suppressed, so the GH1 section is not suppressed.

 If you want to get rid of those blank GH1 sections, just go into the subreport and suppress the Report Header and Detail sections.  The formulas will still be evaluated.  For the Detail section, you'll need to remove the current suppression formula (you can comment the lines if you like, instead of deleting them), and just check "Suppress".

 If you actually want those blank GH1 sections, then nevermind all that.  :-)

 However, if you really do want those blank GH1 sections, I'm not sure if the suppression of the Detail section in the subreport is working the way that you want.  The @Has2 formula in the Detail section sets the SuppressGroup variable.  The suppression formula for the Detail section suppresses the section if SuppressGroup is true.  The question is, which is evaluated first, the formula in the Detail section (@Has2) or the suppression formula for the section?  I'm really not sure, but if they're not evaluated in the right order, you may not get the desired results.  If that's a problem, I think we can fix it.


 FWIW, if you continue to have issues with getting the subreports to run or how long the report takes (because of the subreports), etc., there's another approach that you could try.  Instead of running the subreport once for each group (UserIdTag), you could theoretically put a subreport in the Report Header that would read all of the records (instead of just the records for a single UserIdTag) and use an array to save the UserIdTag's with the values that you're looking for.  Then you'd check that array in the main report.  That would be a bit more complicated to set up (because of the use of an array), but it would theoretically be more efficient (and perhaps faster) than running the subreport once for each UserIdTag, and the subreport would presumably be run right at the beginning of the report, so you wouldn't have the issue that you have with the current report, where you're trying to get the subreport for each UserIdTag to run by paging through the report (or whatever).

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I think you are right James as to the order but crystal is strange sometimes.  That is the issue with not having data or a database to work against.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Well, there was saved data with the last version of the report that he posted (under the two screenshots).  But it was a while before I realized that the formula was probably supposed to be showing the value from the subreport, and didn't seem to be.

 While looking at the report again just now, I ran into something odd.  What I tried before was creating a GH1b section and dragging and dropping (moving) the @DispGroupName formula into GH1b, but when I try that, CR wants to connect to the datasource.  However, I just found that if I copy the formula and paste it into the new section (so that it's still in GH1a too), CR uses the saved data.  I don't know why CR wants to access the datasource if I move the formula, but that's not the odd thing that I was talking about.

 The copy of the formula in GH1b is coming up blank too.  If I put a copy of the formula in the Detail section too, the value shows up there.  I was sure that if the subreport in GH1a was setting the variable (and it is), the value would show up if the @DispGroupName formula was in GH1b underneath that, but it's not.  Was I wrong?


 Also, I'm wondering if the subreport should reset the GroupName variable each time.  It resets SuppressGroup, but not GroupName.  Even if it's not necessary, I think it might be a good idea.

 James
0
 

Author Comment

by:John-S Pretorius
Comment Utility
James and mlmcc, I appreciate all the extensive feedback and appologize for not replying sooner but currently find myself pulled back to the previous report I posted getting revenue from 'count, split and extract' - ID: 26867189

This is now priority as I have a new scope that involves deeper thinking, James you had quite some good feedback on this report and I feel that maybe my best appoach would be to clean the report up (try to based on your comments) and then rebuild based on the new scope.

I would like to hold of on this question for a short while until I get thru my new challenge, I would like to include or continue where the last discussion ended - do I start a a new thread or should we continue giving feedback from where you left off.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Since you mentioned having to go back to a previous report/discussion, I'm having some trouble figuring out whether you're referring to the old report/discussion or this one in some places.  When you say, "I would like to include or continue where the last discussion ended", are you referring to this question, or are you talking about going back to the old question?

 If it's this question, and it's not going to be too long (like a matter of days), I think you could just leave it and get back to it later.

 If it's the old question, then it would probably be best to start a new question, since that one is older, and has (I think) already been closed.  FWIW, there should be an "Ask a related question" link/button somewhere that you could use to start a new question that included a link to the old one for reference, although I'm not seeing it at the moment (don't know why).

 James
0
 

Author Comment

by:John-S Pretorius
Comment Utility
Thank you James, I will start a new question and maybe refer to the older version.

Johnsp1234
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Ah.  I think the "Ask a related question" link may only show up once the question has been closed.  I didn't realize that.

 James
0
 

Author Comment

by:John-S Pretorius
Comment Utility
Well I have 3 locations, which covers 2 of the 3 open questions I have (this specific one above, being one) - I have to maybe let linger for a couple of days until I can get direction on the following link (as refered to earlier) : http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26881696.html and plan a scope to produce a new design for a report request. - Exciting stuff, I am rather new to Crystal but have been learning so much and find it very addictive, I appreciate your assistance.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You said "the following link (as refered to earlier)", but the question that you linked to was not the "count, split and extract" question that you mentioned a few messages back, but maybe you were referring to something else.

 I assume that you will post a new message in one of those old questions at some point, or use the "ask a related question" link to start a new question.  If you don't already know, when you use that link, the people that had participated in the old question will get an email notification about the new one.

 James
0
 

Author Comment

by:John-S Pretorius
Comment Utility
Great that is how I will proceed, as the new question has some additions to the count, split and extract question.
0
 

Author Closing Comment

by:John-S Pretorius
Comment Utility
I am going to work with what the guys put together and maybe post a follow up question later in time.
0

Featured Post

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.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
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…

762 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

8 Experts available now in Live!

Get 1:1 Help Now