Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: help with report forula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
j_ellemore
Newbie
Newbie


Joined: 31 May 2009
Location: United Kingdom
Online Status: Offline
Posts: 21
Quote j_ellemore Replybullet Topic: help with report forula
     Posted: 04 Dec 2009 at 3:37am
Hi,
I need to create a report for our Helpdesk which shows the number of incidents logged by each analyst, the number resolved by those Helpdesk anaysts and the percenage resolved (fixed first line).
 
I have set select expert so it only searches for incidents created by the Helpdesk ({Incident.CreatedBy} is one of xxx), and created a group and summary which shows how many each analyst has created.
 
I now need to calculate how many each of these analysts have resolved {Incident.resolved_by} and need to do a summary count and a percentage for each analyst.
 
I do not need to know about incidents resolved by the other 50 people in the department.
 
Can anyone advise the best way to do this?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Dec 2009 at 6:51am
can you post sample data with an explanation of how it is read, for example, does one ticket have multiple rows with multiple status changes or is it one row with a resolution field that indicates it is "resolved"?
And what do you mean by the incidents resolved by the other 50 people? How does that impact the data as you have it in the report?
IP IP Logged
j_ellemore
Newbie
Newbie


Joined: 31 May 2009
Location: United Kingdom
Online Status: Offline
Posts: 21
Quote j_ellemore Replybullet Posted: 04 Dec 2009 at 8:08am
Hi, there is one row for each ticket. Created by and resolved by will have one entry. Regarding "incidents resolved by the other 50 people" I only want to show the total incidents resolved by the creator.
 
Currently the report looks like this:
 
Incidents created by:   Total 10         Resolved By:     Total 10   %Res
James                             10               James                  5               50
                                                          Tom                     3
                                                          Sue                     2 ...etc
 
I want it to look something like this:
 
Incidents created by:   Total 10         Resolved By:     James       % Res
James                              10                                           5                50%
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Dec 2009 at 8:33am
Group on Incident created by
Total = count (recordfield,createdby)
Total resolved by use a Running Total
Field to Summarize=same field you used to do count
name=ResolvedByCount
Type=Count
Evaluate=Use a formula
table.Createdby=table.resolvedby and resolved=true
Reset=Group1 (your created by grouplevel)
Place on Group1 footer to see #
 
%resolved use a formula:
if count (recordfield,createdby)= 0 then 0
else {#ResolvedByCount} % count (recordfield,createdby)
Place on group1 footer to see
 


Edited by DBlank - 04 Dec 2009 at 8:33am
IP IP Logged
j_ellemore
Newbie
Newbie


Joined: 31 May 2009
Location: United Kingdom
Online Status: Offline
Posts: 21
Quote j_ellemore Replybullet Posted: 04 Dec 2009 at 8:52am
Hi, sorry this is a little unclear.....
Group on Incident created by - done
Total = count (recordfield,createdby) - hat do you mean bey recordfield, my summory for the total is incident.createdby and calculate summary is set to count.
Total resolved by use a Running Total - I have set-up a running total on Incident.resolved_by and set the type to count.
Field to Summarize=same field you used to do count - I have set up a summary to count incident.resolved_by
Tried setting up the formula and got an error, this is the formula I set-up:
 
{Incident.CreatedBy}={Incident.resolved_by} and resolved=true
Reset=GroupName ({Incident.CreatedBy})
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Dec 2009 at 10:44am
The Total is a Summary Function at the group level so you can do an insert Summary (sigma sign)
select the "createby" field
Calculated as a COUNT
Summary Location is group1 footer (createby group)
this will give you a summary field of
COUNT({incident.createdby},{incident.createdby})
 
Total Resolved by is a RT (Running Total) not a fromula field.
Right click on the Running Total Fields and select New:
Name it "ResolvedByCount"
Field to Summarize=same field you used to do count
Type=Count
Evaluate select the "Use a formula" option. Click on the formula button and add in the formula that meets 2 criteria (the createby is the same as the resolved by and it is a resolved incident)
e.g. {Incident.CreatedBy}={Incident.resolved_by} and table.resolved=true
Reset select the On change of Group and pick group1 (your created by grouplevel)
Place on Group1 footer. (RTS do not work in headers)
 
Your % will use these two fields together
RT % Count()
or
{#ResolvedByCount} % COUNT({incident.createdby},{incident.createdby})
Since it uses a RT it also will not work in a header.
 
Does that work?


Edited by DBlank - 04 Dec 2009 at 10:45am
IP IP Logged
j_ellemore
Newbie
Newbie


Joined: 31 May 2009
Location: United Kingdom
Online Status: Offline
Posts: 21
Quote j_ellemore Replybullet Posted: 07 Dec 2009 at 7:23am
Hi, thanks for your help. Would you mind clarifying this part:
 
{Incident.CreatedBy}={Incident.resolved_by} and table.resolved=true
 
I tried entering {Incident.CreatedBy}={Incident.resolved_by} and incident.resolved=true and this threw up an error?
 
Thanks
James
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Dec 2009 at 7:41am
You will need to tweak that part to give you a 2 part statement that determines if the field should be inclduded or excluded. I just guessed at what data defines this but you will need to adjust it so it is the correct interpretation of your data.
Part 1 is you want the person that created the ticket to be the same as teh person that resolved the ticket ... e.g. {Incident.CreatedBy}={Incident.resolved_by}
The second part may not even be needed. If the fact that there is a resolved by field entered and this measn it is a resolved ticket then it is moot and can be removed. If not then there is some data indicator that the data row is a 'resolved' ticket and you need to include that in the formula. However you know it is resolved by looking at a raw data row make that the second part of the formula. Maybe '(Not isnull(table.resolvedate))' ?
Basically, in a Running Total the Evaluate section determines which rows to read and which to skip. If you Use a formula to to this if the formula returns TRUE it reads the row if it returns FALSE it skips the row. So you want a formula to return TRUE for only rows that are resolved where the creator is the same as teh resolver. Skip everything else.
Does that clear it up?
IP IP Logged
j_ellemore
Newbie
Newbie


Joined: 31 May 2009
Location: United Kingdom
Online Status: Offline
Posts: 21
Quote j_ellemore Replybullet Posted: 07 Dec 2009 at 8:21am
Hi, yes there is a resolved by field so there should need to be a second part to the formual. I have enetered {Incident.CreatedBy}={Incident.resolved_by} into the running total but it just returned the same value as the count on incident created by. It's in the footer as requested?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Dec 2009 at 8:28am
You only have one group in your report which is grouped on CREATEDBY correct?
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.