Need Selection Formula Help

[cathey04]

We are using HP Openview Service Desk running on HPUX / Oracle 9. I am trying to get the number of priority severity 1 service outages calls that are still open and new (either closed or new) in the last 24 hours. The way the formula is written, takes about 35 to 60 minutes to run, which is way too slow. Here is the formula

{V_SERVICE_EVENT.IMPACT} = "Service Outage (Unavailable to all users)" and
({V_SERVICECALL.STATUS} in ["In Progress", "Routed", "Pending", "Dispatched"] OR
({V_SERVICE_EVENT.CREATED} >= (CurrentDateTime - 1) and {V_SERVICE_EVENT.CREATED} < CurrentDateTime ))

 

[BobSuruncle]

What version of Crystal are you using?

Depending upon the version, what you're specifying for dates might not get passed into the WHERE clause in the SQL statement.

On the DATABASE menu, look near the bottom of the list for "Show SQL Query".

Does your date selection show up in the WHERE clause?


Bob Suruncle

 

[cathey04]

It is Crystal 10, and here is the query

SELECT "V_SERVICE_EVENT"."REF__", "V_SERVICE_EVENT"."IMPACT", "V_SERVICE_EVENT"."DETAILED_DESCRIPTION", "V_SERVICE_EVENT"."WORKGROUP_NAME", "V_SERVICE_EVENT"."CREATED", "V_SERVICE_EVENT"."SYMPTOM_DESCRIPTION", "V_SERVICE_EVENT"."PRIORITY", "V_SERVICECALL"."STATUS", "V_SERVICECALL"."SOLUTION", "V_SERVICECALL"."SERVICECALLDATE10", "V_SERVICECALL"."SERVICECALLDATE3", "V_SERVICECALL"."CLOSURECODE", "V_SERVICECALL"."CONFIGURATIONITEM_SEARCHCODE"
FROM "SERVDESK"."V_SERVICECALL" "V_SERVICECALL" INNER JOIN "SERVDESK"."V_SERVICE_EVENT" "V_SERVICE_EVENT" ON ("V_SERVICECALL"."OBJECT_ID"="V_SERVICE_EVENT"."OBJECT_ID") AND ("V_SERVICECALL"."ID"="V_SERVICE_EVENT"."REF__")
WHERE "V_SERVICE_EVENT"."IMPACT"='Service Outage (Unavailable to all users)' AND (("V_SERVICECALL"."STATUS"='Dispatched' OR "V_SERVICECALL"."STATUS"='In Progress' OR "V_SERVICECALL"."STATUS"='Pending' OR "V_SERVICECALL"."STATUS"='Routed') OR ("V_SERVICE_EVENT"."CREATED">={ts '2006-02-13 17:18:10'} AND "V_SERVICE_EVENT"."CREATED"<{ts '2006-02-14 17:18:10'}))
ORDER BY "V_SERVICE_EVENT"."IMPACT"

 

[BobSuruncle]

Well, it looks like everything you're looking to filter on is making it to the WHERE clause.
Now it's not a Crystal problem.
Are the fields you're filtering on indexed in the database?
If not, see if you can get your DBA to create indexes.

Try running the query from SQL plus and see if it performs just as badly there.


Bob Suruncle

 

[cathey04]

What I ended up doing was to put another selection parameter in

Service_Event.Ref# > 620,000

and it worked. Thanks for the feeback and help, too. I will give it a try without the Ref# and see what happens.