Solved

Crystal Report to detect a change in field

Posted on 2011-02-24
25
445 Views
Last Modified: 2012-06-21
I have a Crystal report sorting by group and within each group I have a specific field "Type" which is a string.

For normal operations generated by the report this field will always show the same result "TAGM" but from time to time I have a violation which will be reported under "WGDC" for the same field.

I am looking for a way/formula that will indicate within each grouping when there is a change.

Your assistance would be greatly appreciated.


John-S Pretorius.
0
Comment
Question by:John-S Pretorius
  • 16
  • 9
25 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34974093
You could use conditional formatting to highlight the record when the value is NOT TAGM

Right click the TYPE field
Click FORMAT FIELD
Click the BORDER tab
Click the formula button the the right of BACKGROUND
If CurrentFieldValue <> "TAGM" then
    crTeal

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34974803
I actually need to be able to create a filtered report at the end, that will only show the violations under each group and not short/show any of the other transactions that have no violations.

I was thinking of using a if statement triggering the change and then sorting only the violations or changes under the same group.

Thank you for your suggestion - any other ideas?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34974852
You can use the select expert to filter out and show only the non-TAGM records.
   {TypeField}  <> "TAGM"

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34975084
I may have not been completely clear about what I'm trying to achieve, I apologize.

Each user has 2 sets of access keys, tagm or wgdc and within a cycle which typically may be entry and exit are only allowed to use one type.

I am looking for just the violations when there is a change of card type usage and don't care about the rest of the valid 'same' type records
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34975218
So if they start with TAGM you want to see the WGDC records and vice versa.

Add 2 formulas
Name - HasTAGM
If {Type} = 'TAGM' then
    1
else
    0

Name - HasWGDC
If {Type} = 'WGDC' then
    1
else
    0


You could group by user
Use group selection as
Sum({@HasTAGM,{NameField}) > 0
AND
Sum({@HasWGDC,{NameField}) > 0


In the group header add a formula
WhilePrintingRecords;
Global StringVar FirstType;
FirstType := {TypeField};
""

Conditionally suppress the detail section with
WhilePrintingRecords;
Global StringVar FirstType;
FirstType = {TypeField}

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34975261
Just to confirm if they start the day with wgdc and it changes to the other and vice versa I want the report to group them.

This looks good so far and I will test it shortly.

Thanks for your patience, I will give you feedback in a short while.
0
 

Author Comment

by:John-S Pretorius
ID: 34975922
I am getting stuck adding a Group header, as I  already had a group sorting each user.

Where do I add the indicated 'Group header' - When I run the report without the last 2 instructions I have a clear page, none of my page header or anything.

Any ideas please.
0
 

Author Comment

by:John-S Pretorius
ID: 34976120
May I kindly request if you could look at the attatched file please - I'm not sure what I'm missing here. QCS-Monthly-Users-Activity.rpt
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 34976303
Look at this report

Your WGDC is really _WGDC

You were comparing to the wrong field

mlmcc
Q-26845990.rpt
0
 

Author Comment

by:John-S Pretorius
ID: 34976388
mlmcc you nailed it - that is really awesome, thank you this is certainly exactly what I was trying to do.

I will award you the points (not that al this about, surely) but I would like to keep it open for a little while longer as I may need to add a scenario where for instance if the first entrance was thru Device #52 (Broadway overhead tag - any one is alowed to start a transaction with this type TAGM and then follow thru with _WGDC as long as it stays at that)

Do you think it would be hard to add something like that ?

Thanks again, I hope we can complete the last request together.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34976552
Probably not too tough.

You can close and then we can still continue the discussion if you wish.

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34976564
You are the best, thank you so much -  I am going to have to follow you :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:John-S Pretorius
ID: 34976569
I have so much to learn but luckily there's people like mlmcc.
0
 

Author Comment

by:John-S Pretorius
ID: 34979463
Good day mlmcc, I am looking at adding another 'check' that needs to be allowed as a valid 'violation' : if the device.design = "broadway Overhead Tag" ignore that first read.

I am also looking into functionality on how to trigger a email/print  when such a violation occures. Currently I use easyview to auto generate a report every Hour and just email it out.

Is there a way to run the report and if it finfs a violation do something : email/print ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34980099
With the new report you should only get a report if there are violations.

Is the device.design = "broadway Overhead Tag" ever on a second or later record?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34980139
Unless they exit and come back in thru the same entrance. What if we just ignore that device completely ({device.design} = "Broadway overhead Tag" or it also goes by {device.number} = 52)

We are only concerned about this device - ignore it completely.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34980310
That was what I was thinking was to add that to the record select as
    {device.number} <> 52

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34980821
Worked perfectly...."What about triggering ?" or running a stored sql request and then emailing/printing when the violation occurs.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34981114
Crystal cannot "trigger" anything.

Are you running this from an application?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34981444
Yes I'm using easyview to batch generate a bunch of reports and email them out.
0
 

Author Comment

by:John-S Pretorius
ID: 34981972
The report is 100% complete now, thanks to you! You should be proud that I was able to get rid of all unwanted "numberlous" IDTag users that did not have anything asigned yet. I also changed the grouping from >0 to >2 which gives me a true violation report that Rocks.

My last Question if I may please : " How can I output at least some text if there is no violations or data to print" - currently it's just a blank page, and I would like to say maybe : "No Violationf for " {report.date} (Date)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34982524
You can add a field to the report header or page header that says what you want

You can conditionally suppress it with

Count({SomeField}) > 0

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 34982722
No its not, it just used for a outside building reader which is only used once.
0
 

Author Comment

by:John-S Pretorius
ID: 34982772
I learnt allot, thank you. when can we do the next challenge I am working on something a little more juicy defiantly worth 500 points
0
 

Author Comment

by:John-S Pretorius
ID: 35029761
Please help, I'm trying to add the following to the excisting formula :

I am trying to show if a grouped  userIDTag has a duplicate movement (entry) which will show as a 0 in the system - please see image.

I am thinking that :
({ContractParkersMovement.UserIDTag},{ContractParkersMovement.MovementType}) <> 
 next ({ContractParkersMovement.UserIDTag},{ContractParkersMovement.MovementType}

should do this but keep getting a error with the formula. Violation Image
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
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…

943 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

4 Experts available now in Live!

Get 1:1 Help Now