?
Solved

Bring all records based on a value in Crystal Reports

Posted on 2009-04-03
9
Medium Priority
?
225 Views
Last Modified: 2012-05-06
Hello,

Please see the attached doc for the data that looks like.
I have more than one record for an "Incident Number" as you see in the doc.
I have a group based on Incident Number.
What I need is, If an "Incident Number" is affected by Assigned Group 2 =  "NPC" value then bring all the records related to that Incident Number. So basically I want to see all 4 records for the Incident Number - INC000000057860 and ignore the other records.
Please assist.

Thank you so much
0
Comment
Question by:angel7170
  • 4
  • 3
  • 2
9 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 24061706
Nothing attached...?
0
 
LVL 17

Expert Comment

by:MIKE
ID: 24061725
So have you tried using SELECT EXPERT....???

{Assigned Group 2} =  "NPC"
0
 

Author Comment

by:angel7170
ID: 24061799
Oops Sorry!
Here is the attachment

Also, I did tried using the SELECT EXPERT and it just brings the records that is equal to NPC but the not the other records related to that Incident Number.

Thank you


open-incidents-based-on-initial-.doc
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 101

Expert Comment

by:mlmcc
ID: 24062368
Will NPC always be on the first record or sould it be on any of the detail records?

mlmcc
0
 

Author Comment

by:angel7170
ID: 24062560
NPC will be the first record. Thank you
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24062930
Try this

Riight click the group header in the left margin
Click SECTION EXPERT
Click the formula button to the right of SUPPRESS
WhielPrintingRecords;
Global BooleanVar SuppressSection;
SuppressSection := {NPCFiled} <> 'NPC'
SuppressSection

Click SAVE & CLOSE

Click the DETAIL SECTION
Click the formula button to the right of SUPPRESS
WhielPrintingRecords;
Global BooleanVar SuppressSection;
SuppressSection

Unless you need the group footer suppress it

mlmcc
0
 

Author Comment

by:angel7170
ID: 24063664
Thank you so much.

This works!
But now I am asked to do some more.

To calculate the time difference, when an Incident Number is changed from  NPC to a different group like "2199" or some thing like.
I created two formulas to do it as below.

But I couldn't do a SUMMARY using this formula. I want to calculate the average time it took to change from one group to another.
Can you please help.
So far you guys have been a great help. Thank you again!




@TimeDifference
 
if {HPD_Search_Assignment_Logs.Incident_Number} = next({HPD_Search_Assignment_Logs.Incident_Number}) then
(Datediff("n",{HPD_Search_Assignment_Logs.Submit_Date2},next({HPD_Search_Assignment_Logs.Submit_Date2}))/60
 
 
@Time Value for NPC
 
if {HPD_Search_Assignment_Logs.Assigned_Group_2} = "NPC" and
 next({HPD_Search_Assignment_Logs.Assigned_Group_2}) like "*99*" then {@TimeDiffernce}

Open in new window

0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 24063754
You can't do a summary because summaries are done in the Reading Pass through the report and the NEXT function must run in the last pass through the report.

What you will have to do is do a manual total

@TimeDifference
if {HPD_Search_Assignment_Logs.Incident_Number} = next({HPD_Search_Assignment_Logs.Incident_Number}) then
    (Datediff("n",{HPD_Search_Assignment_Logs.Submit_Date2},next({HPD_Search_Assignment_Logs.Submit_Date2}))/60
else
    0
 
 
@Time Value for NPC
 WhilePrintingRecords;
Global NumberVar TotalTime;
Global NumberVar ChangeCount;
LocalNumberVar ThisTimeDiff;

if {HPD_Search_Assignment_Logs.Assigned_Group_2} = "NPC" and
 next({HPD_Search_Assignment_Logs.Assigned_Group_2}) like "*99*" then
(
    ChangeCount := ChangeCount + 1;
   ThisTimeDiff := {@TimeDiffernce};
   TotalTime := TotalTime + ThisTimeDiff;
   ThisTimeDiff
)

Add a formula to the report header
 WhilePrintingRecords;
Global NumberVar TotalTime;
Global NumberVar ChangeCount;

Add a formula to the report footer for the average
 WhilePrintingRecords;
Global NumberVar TotalTime;
Global NumberVar ChangeCount;
If ChangeCount = 0 then
   0
else
   TotalTime / ChangeCount

mlmcc
0
 

Author Comment

by:angel7170
ID: 24086637
This works perfect. That is exactly what I need. Thank you so much
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

579 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