Needing formula

[ymrs]

I am doing a report on tickets stored in an SQL database. What needs to show is the entire history of the ticket when at any point, it takes more than 1 hour between actions.

I have a field in the details section that shows the DateTime of an action, it's format is "mm/dd/yyyy hh:mm:ss". I have to use this field to figure the time between actions. I have come close, but it only shows the the items in a particular ticket that meets the 1 hour criteria. I need to show all of the items in the ticket if just one of them meet that criteria.

I am in desparate need of assistance.

 

[Katy44]

Can you give us a bit more information on your tables and how they are linked? Does a ticket have many actions? And an action has a time associated? I think you will need to use an exists clause, but I'm not sure of the bast way to do this within Crystal

 

[ymrs]

I'll try to describe. The table that I pull this from is CL_PROBLEM_HISTORY, a history table. It contains the history entries for the tickets. It is linked by the ticket number to CL_CALL_LOGGING, which contains only the most recent entry into each ticket. When someone makes an entry into a ticket, it is inserted into CL_CALL_LOGGING. Any existing entries for that ticket number in CL_CALL_LOGGING is then moved to CL_PROBLEM_HISTORY. When this happens, a DateTime stamp is given to the entry. This is the ACTION_TIME. I am looking for an ACTION_TIME that is greater than 1 hour, but I need to view the entire history of that ticket also.

Here is a scenario - I log a ticket, insert the problem description and my actions, then send it to a Tier 2 group (let's call them Group1). Someone in the Group1 group opens the ticket with in 5 minutes, works with the user, then realizes that it needs to go to a different Tier 2 group (Group2). Nobody in Group2 does anything with the ticket for 90 minutes. Finally they take the ticket, work with the user, then they close the ticket because the issue is resolved.

In this scenario, there are going to be 5 entries for ACTION_TIME for this one ticket. The time that I forwarded it to Group1 (1), the time that someone in Group1 opened the ticket (2), the time that Group1 sent the ticket to Group2 (3), the time that Group2 opened the ticket (4), and the time that Group2 closed the ticket (5). Item number 4 took over 1 hour, so I want the entire ticket history to show up.

I hope this explains it without putting you to sleep.

 

[rosemaryl]

Dear ymrs,

Hmmm, does your call database allow customization? I have had the need to do the same thing, and what I did was create a calcualted field in the "history" view that took the time difference (datediff(s, (select statement for first time in seconds from the maximum "action time" that was less than the current Action Time), Action Time) when the count of the records in the History table was > 1 else 0 (the first record would have an elapsed time of Zero).

This gave me a column with a value in seconds, then it is a simple matter of either in the view or in a crystal SQL Expression of Summing the seconds. You would then be able to select records for your report where the sum of "seconds elapsed" was >= 3600 (the number of seconds in hour).

Now, if modifying the database isn't possible another option would be to Group the report by ticket #, place a sql expressions that does the calculations for you to give you an elapsed time column for each record in the detail section. Insert a Crystal Summary that sums the value, and then Do a Group Selection Criteria where the Summary field of the seconds elapsed >= 3600.

Does that help?

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[ymrs]

I can not modify the database, but I have tried something similar. The time was calculated in minutes instead of seconds, but did not show all of the history, only the history items that were greater than 60 minutes.

 

[rosemaryl]

Dear ymrs,

Did you do the Group Selection formula on the Group Summnary or the detail summary?

If you do the selection on the group summary? You must use the Report/Edit Selection Formula/Group option.

Here is an example: I have a report, grouped by the login id associated with the Call History. I created a summary that was a count of the tickets for that person. I want to select records, where the summary for the group was greater than 3. I entered the following in the Group Selection:

DistinctCount ({Incident.Incident #}, {Incident_Details.Login ID}) > 3

This displayed all records on the report where the GROUP value was greater than 3.

I assume that in addition to the Call Loggin and History table there is a main table for the Ticket. Why don't you join the Main Table for the Ticket to the History table, group on the Main Table Ticket number, create the calculation for each detail showing elapsed seconds (it is always better to work in seconds and then transform the display how you want).

Now, insert a summary of the total seconds in the Group footer and insert a Group Selection Criteria where that summary is >= 3600. That will work to select all the history records when the group summary is >= to one hour.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[ymrs]

That gives me records where the total action time is greater than 1 hour. I need it to look at the individual action times. If one of them is greater than 1 hour, they need to show all of action items.

 

[rosemaryl]

Dear Ymrs,

Okay, sorry for the misunderstanding.

Why don't you do this! Create a formula in the detail section that determines if the individual calculation is greater than 3600 seconds. Using whatever elapsed time formula you have.

The formula would be :

//Begin HourOrMoreCheck formula

If timedifferenceformula >= 3600 then 1 else 0

//end formula

Place that formula in the detail section. Click that formula and insert a summary(Sum). This will determine the the total number of ticket history records that elapsed an hour or more. Some groups will result in 0 but any one grouping that had a single ticket at >= 3600 would show at least a 1 as the total.

Now in the Group Selection formula do:

Sum({@HourOrMoreCheck},Table.GroupingField) >=1

that will return all the details for tickets where any one ticket had more >= 3600 secons or 1 hour.

Does that help,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[ymrs]

I can not create a summary on my formula. Here is how I got to where I am so far...

Here is my formula (@TimeDifference) for calculating the time difference:
{CL_PROBLEM_HISTORY.FORWARD_TIME} - previous({CL_PROBLEM_HISTORY.FORWARD_TIME})

This gives me a time in days. I then use this formula to convert it into minutes:
({@TimeDifference} * 24) * 60

After that, I convert it to HH:mm format by using this formula:
CStr(({@Convert_Time_Difference} \ 60),0) + ':' + CStr(({@Convert_Time_Difference} mod 60),'00')

I am unable to create a summary on any of these formulas, so they don't show up in my group selection choices.

 

[rosemaryl]

Dear ymrs,

Not having your formulas has an impact on advice.

You cannot sum string formulas, you cannot sum formulas that are determined while printing.

Since you are using previous field value, that makes this a while printing records formula, upon which you cannot perform a summary.

You cannot use a crystal running total, because again your formula is while printing.

You can use a manual running total (3 formula method) to display a summary, but again it cannot be used in a Group Selection formula as it must be evaluated later.

So the upshot, is that you cannot do this the way you want.

If it were me, I would go with a sql expression. However, I have no idea of how you are grouping your data, the other views or data in your report, nor the colums in your history table to help you write your expression.

In my opinion, the real issue here is that the database design is flawed and this value should be automatically calculated when a record is moved into your history table, thus negating the need to do it in a formula.

Regards,

ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[ymrs]

Although it isn't working in this report, I am using some of the ideas you've shared in other reports.

 

[tektipdjango]

You wrote :

I have come close, but it only shows the the items in a particular ticket that meets the 1 hour criteria. I need to show all of the items in the ticket if just one of them meet that criteria.

if you have the report with the right items, you can then insert the same report (only details) as a subreport in the section where are the selected item with link on item.
This sub report will give you all the detailed records for this specific item!

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

sorry the link must be on the ticket and not on item!

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​