Processing Efficiencies

[smmedeiros]

I have been reading through many threads looking for ideas for processing efficiencies. I found an old thread that was similar to my issue and want to explore it in more detail. (thread767-1198959)

I have a database of millions of records that I’m reading through. Unlike the thread mentioned above, I do not want to show details.. Only summaries.. The information in my database is related to service desk calls.. So, I’m reading in ticket history.. The data is such as date, time, caller, assignment group, etc.. I have created a crosstab summary that will display #tickets, by month, by assignment group, and a few more flavors similar to this based on some formulas greated to break out the groups by divisions.

My problem is that this runs many hours. It was suggested to me in order to make my filter more efficient, I should place as much information in my ‘Report -> Select Expert” section.

My current select expert statement reads:
{@Status} like ["Closed", "Resolved"] and
{call_req.type} = "I" and
{@Open Date} in YearToDate

Status formula is:
if {call_req.status} = "ACK" then "Acknowledged"
else if{call_req.status} = "CL" then "Closed"
else if {call_req.status} = "OP" then "Open"
else if {call_req.status} = "PEND" then "Pending"
else if {call_req.status} = "PENDC" then "Pending Customer"
else if {call_req.status} = "PENDV" then "Pending Vendor"
else if {call_req.status} = "RE" then "Resolved"
else if {call_req.status} = "WIP" then "Work In Progress"

Open Date formula is:
//Convert Seconds into Days
numberVar SecondsConversion := {call_req.OPEN_DATE};
SecondsConversion := (SecondsConversion/3600);
SecondsConversion := (SecondsConversion + 19)/24;
//Convert Start Date to Crystal Start Date
numberVar DayConversion := DateDiff ("d", #12/30/1899#, #12/31/1969#);
//Compute Start Date
numberVar StartDate := SecondsConversion + DayConversion;
DateTimeVar ImplementationDate := DateTimevalue(StartDate);



Notice I have a custom formula for Status and Open Date

The suggestion provided to me is to not use the formula. This is forcing Crystal to read in ALL records, then to the evaluation. So, I’ve changed the selection as such:

{call_req.status} like ["CL", "RE"] and
{call_req.type} = "I" and
{call_req.open_date} ??????????


Call_req.open_date is a number.

If I show the results of this field without applying the formula is displays as “1,245,960,515” ( with formula 06/25/09 )


My DBA support person gave me an SQL statement that I don’t know how to interpret into crystal.. this was a sample specific to 1 day.

AND convert(varchar,Dateadd(ss, open_date,'01/01/1970 00:00:00.000'),101) = '06/24/2009'



My dilemma. How do I set the default database value for DATE to associate it to YTD at the top level without having to push it through a formula?


Thanks

 

[smmedeiros]

Closing this thread. Found a solution.