Projects

This page is dedicated for the random work that is given to me by my managers.

1.11.10

I have completed the report. Over these several weeks. I have learned quite a lot in formulating and researching on report creation using Crystal. Hopefully this little guide may help anyone who was once having problems like me.

Edit: 1.14.10

Average open and closed tickets will not be part of this report.

1.7.10

Okay, I've been having a few issues on how averages are setup in Crystal Reports. So far, I've created a Formula Field (AVGO & Ticket).

The code for Ticket:

datediff("d",{Incident.CreatedDateTime},{Incident.ResolvedDateTime})

I used datediff instead of a regular subtraction due to calculating days, weeks or months. This allows me to find multiple days instead of a single date.

Then I've incorporated this formula into another formula (AVGO):

sum({@Ticket})/count({Incident.IncidentNumber}) ;

This calculates the sum of all days per tickets.

1.6.10

I will be taking a break from this report as I need to focus on more important matters.

1.4.10

Report Update:

• Add Average age of open tickets (by OwnerTeam, monthly)
  • This can be done by getting the date between resolveddatetime - createddatetime
• Add Average age cycle time of closed tickets (by OwnerTeam, monthly)
• Abandonment Rate of all tickets within the past week.

12.31.09

Okay, so I have not completed the 4 points as I stated for yesterday. I had a major issue in the report that needed to be taken care of. Now that it has been resolved, I can continue working on these:

• Add Overall Opened Tickets (daily) to summary (Completed)
• Add Overall Closed Tickets (daily) to summary (Completed)
• Change within 48 hours to Greater than 48 hours (Completed)
• Change past 14 days to Greater than 14 days (Completed)
• Revise GrandTotal Created By.(Completed)

To revise this, I created a running total and under Field to summarize I used {Incident.Createddatetime}. Put this as count. By using the formula, I am now able to get the proper Grand Total count for these SA only.

{Incident.CreatedBy} = ["cc\coopera6", "cc\donalda1", "cc\knochs", "cc\litchnt", "cc\monachl", "cc\moorej10",

"cc\parkerd4","cc\quickm2", "cc\sagulag", "cc\TICKHIC","cc\zieglek"}

Add Overall Opened Tickets (daily)

I created a running total called Daily_opened_all and put the Field to summarize to {Incident.Createddatetime}. Also, I put this as count. I also created another formula as shown below.

Forgot to mention to also add {Incident.LastStatus} in ["Active", "Logged", "Waiting"] to the formula as this is only looking for opened tickets. This goes with the same process for Add Overall Closed Tickets (daily), but change the status to "Closed" and "Resolved".

12.30.09

Today, I will be finishing up:

• Add Overall Opened Tickets (daily) to summary
• Add Overall Closed Tickets (daily to summary
• Change within 48 hours to Greater than 48 hours
• Change past 14 days to Greater than 14 days

Add Overall Opened Tickets (daily)

This is a sum of all tickets opened between Main Campus System Analysts (SA) and Regional SA for the day. Current code:

if {Incident.CreatedDateTime} = currentdatetime then

if {Incident.CreatedBy} = ["cc\coopera6", "cc\donalda1", "cc\knochs", "cc\litchnt", "cc\monachl", "cc\moorej10",

"cc\parkerd4","cc\quickm2", "cc\sagulag", "cc\TICKHIC","cc\zieglek","cc\baconm","clarks1","busha","reamek",

"hegazyy","abukhar"] then

if {Incident.LastStatus} in ["Active", "Logged", "Waiting"] then 1 else 0

However, this is giving me a total count for the week for only Main Campus SA.

Update:

I've finished creating Add Overall Opened Tickets.

How to do this: I created a formula field calling it all_opened and used the following code:

if {Incident.CreatedBy} in ["cc\coopera6", "cc\donalda1", "cc\knochs", "cc\litchnt", "cc\monachl", "cc\moorej10",

"cc\parkerd4","cc\quickm2", "cc\sagulag", "cc\TICKHIC","cc\zieglek","cc\baconm","clarks1","busha","reamek",

"hegazyy","abukhar"] then

if {Incident.LastStatus} in ["Active", "Logged", "Waiting"] then 1 else 0

Then I created a running formula field and entered the formula

{Incident.CreatedDateTime} = today

This now sums all tickets for today.

DBlank has once again helped me with my issue. I removed the select expert --> record "CreatedBy" and created a section expert instead. Now this will only show me the Main Campus SA. I can now show the true count of the overall tickets created.

12.29.09

Report update:

• Report is now categorized as follows: CreatedBy, OwnerTeam (Completed)
• Removed Status as a category. This has been placed as a column now.(Completed)
• Added Priority as a column (Completed)
• Added a two subtotals to the report. (Completed)
  • First sub total counts phone, non-phone, and owner team grand total (under OwnerTeam category)
  • Second sub total counts phone, non-phone and created by grand total (under CreatedBy category)
• Adding a total count for tickets per CreatedBy. (Completed)
  • Created By - Open
  • Created By - Logged
  • Created By - Waiting
  • Created By - Resolved
  • Created By - Closed
• Add Average age of open tickets (by OwnerTeam, monthly)
  • This can be done by getting the date between resolveddatetime - createddatetime
• Add Average age cycle time of closed tickets (by OwnerTeam, monthly)
• Abandonment Rate of all tickets within the past week.
• Change within 48 hours to Greater than 48 hours
• Change past 14 days to Greater than 14 days
• Changed Print out from Portrait to Landscape (Completed)
• Removed Category from columns (Completed)
• Add RIS - Report, RAD Desktop, RAD Server in the OwnerTeam Category

12.28.09

Today I will be trying to complete the 'phone per day' count. The overall count will not be part of this report anymore.

Update:

Woot! I figured it out!

Here's what I did:

I created a formula field named Phone and created an If/else statement.

if {Incident.Source}= "Phone" then 1 else 0

From the formula, I created a summary and chose the field: Phone and the summary as Sum.

This now counts the total number of phones per day.

So after figuring out the 'phone per day' count, I was able to duplicate it for 'non-phone per day' count. I changed the code from = to <>

if {Incident.Source}<> "Phone" then 1 else 0

I also created another Summary from the formula field called Other.

12.23.09

Still trying to figure out the overall tickets and phone per day. I am now asking the experts at http://www.crystalreportsbook.com (at least for the overall tickets).

I thought that since the 48 hours and 14 days would be the same, that overall would have a similar effect. I tried using the code below.

{Incident.CreatedDateTime} in dateadd('ww',-52,currentdatetime) to currentdatetime

However, the end results would give me 0. So I tried using the following code:

{Incident.CreatedDateTime} in YeartoDate

Again, the end results gave me 0. Dblank from the forums wanted me to try out:

{Incident.CreatedDateTime} in datetime(year(currentdate),1,1,0,0,0) to currentdatetime

Unfortunately, this is giving me the same results as the previous two lines of code.

12.22.09

Found this neat table that discusses the dateadd function.

This can be found at: http://www.thevbprogrammer.com/Ch04/04-11-DateAdd.htm

12.21.09

Today I will be working on opened tickets overall.

12.18.09

Currently I am working on opened tickets (active, logged, waiting) for the past 48 hours. Currently my code is this:

if {Incident.LastStatus} in ["Active", "Waiting", "Logged"] then

if minute({Incident.CreatedDateTime}) < 2

880 then

count ({Incident.LastStatus})

if {Incident.LastStatus} in ["Active", "Waiting", "Logged"] then

if ({Incident.CreatedDateTime} < currentdate and minute({Incident.CreatedDateTime}) >= 2880 ) then

count ({Incident.LastStatus})

However, I believe there is a better way to do this. I just need to now figure out what this better way is. :)

I have posted my dilemma to http://www.crystalreportsbook.com on their forum. Hopefully, someone would be able to answer me.

Update: DBlank from Crystalreportsbook.com was able to help me out with my issue.

12.17.09

I need to create a sum of total tickets opened (active, logged, waiting) that is

1. > 14 days
2. > 48 hours
3. overall

Secondly, I need to create a sum of the total tickets that are a Phone source.

Okay, So I created an SQL Expression Field and called it SourceCount. This allowed me to setup a create an Insert Summary, however, I am unable to get the correct # of phones. Code for SourceCount:

(select count (distinct source) as "Phone"

from incident)

Instead now I created a Formula Field. I wasn't sure if the SQL field was correct, but I was unable to obtain my data. Here is the current code for the Formula Field:

if {Incident.CreatedDateTime} = {?Start Date} and {Incident.Source}= "Phone" then

count({Incident.Source})

12.16.09

I've gotten another problem. I was unable to figure out how to count the number of times a word appears in the table. I've played around the coding and have come up with this so far:

if {Incident.Source} = "Phone" then

whileprintingrecords;

numbervar cnt;

I've encountered a solution to the Sat2Fri. I created a Start Date parameter field and set it as static (not dynamic). I also changed "Allow Range Values" to true. This now allows me, not only, to have the dates I need, but also allows a custom date settings.

12.14.09

I've been boggling my head with a way to create something similar to LastToDateFromSun function. Other than the limited book material and the same type of information over and over again on the web, I've decided that to create a little post on how to create functions that does not fit with the other functions.

This first function I will be trying to create is Sat2Fri. Due to our schedule for work and on-call, our work week starts Saturday and ends on Friday.

© 2008-2013 Yahia Hegazy