Useful calculated fields

Below is a list of calculated fields that will work out-of-the-box, listed by module:

Incident Management:

Due Date Status (to allow colour coded quickviews):

(CASE WHEN GETDATE() > "RECOMMENDEDFIX_DATE:" AND GETDATE() < "DUE_DATE:" THEN 'Warning' WHEN GETDATE() > "DUE_DATE:" THEN 'Critical' ELSE 'Normal' END)

Based on the following assumptions:

1. Normal: Ticket has not exceeded recommended fix duration.
2. Warning: Ticket has exceeded recommended fix duration but not actual fix duration.
3. Critical: Ticket has exceeded actual fix duration.

Total cost of attached purchase requests:

(SELECT SUM("Total Cost") FROM "_SMDBA_"."Purchase Requests" WHERE "_SMDBA_"."Purchase Requests"."HD#" = BASE."SEQUENCE")

Total work done (based on a summation of all history durations for a ticket plus all history durations for any attached work orders:

(ISNULL((SELECT SUM("TIME SPENT") FROM "_SMDBA_"."_WORKDET_" JOIN "_SMDBA_"."_WORKORD_" ON "_SMDBA_"."_WORKORD_"."SEQUENCE" = "_SMDBA_"."_WORKDET_"."TTNUM" WHERE "_SMDBA_"."_WORKORD_"."HDNUM" = "BASE"."SEQUENCE"), 0) + (SELECT SUM("TIME SPENT") FROM "_SMDBA_"."_TELDETAI_" WHERE "PROBLEM #" = "BASE"."SEQUENCE")))

Problem Management:

No of incidents with the same subject as the problem:

(SELECT COUNT(*) FROM "_SMDBA_"."_TELMASTE_" WHERE "SUBJECT" = "BASE"."SUBJECT")

Initiator of the problem:

(SELECT "Login ID" FROM "_SMDBA_"."Problem Management Details" WHERE "Seq.ProbMgmt" = "BASE"."SEQUENCE" AND "Action ID" = 'PM_OPEN')

Change Management:

No of assessments:

(SELECT COUNT(*) FROM "_SMDBA_"."_CHANGEASMT_" WHERE "CHANGE" = "BASE"."SEQUENCE")

No of awaiting assessments (based on the assumption that the assessor with be the same as the last user when the assessment has been updated):

(SELECT COUNT(*) FROM "_SMDBA_"."Change Assessment" WHERE "Seq.Change" = "BASE"."SEQUENCE" AND "LastUser" <> "Assessor Login ID")

No of approvals:

(SELECT COUNT(*) FROM "_SMDBA_"."_CHANGEAPPR_" WHERE "CHANGE" = "BASE"."SEQUENCE")

No of awaiting approvals (based on the assumption that the decision will be null if the decision has not been made):

(SELECT COUNT(*) FROM "_SMDBA_"."_CHANGEAPPR_" WHERE "CHANGE" = "BASE"."SEQUENCE" AND DECISION IS NULL)

No of work orders:

(SELECT COUNT(*) FROM "_SMDBA_"."_WORKORD_" WHERE "SEQ_CHANGE" = "BASE"."SEQUENCE")

No of open work orders (based on the state of the work order):

(SELECT COUNT(*) FROM "_SMDBA_"."_WORKORD_" WHERE "SEQ_CHANGE" = "BASE"."SEQUENCE" AND STATUS = 'O')

Total time spent on work orders linked to this change:

(SELECT SUM("TIME SPENT") FROM "_SMDBA_"."_WORKDET_" JOIN "_SMDBA_"."_WORKORD_" ON "_SMDBA_"."_WORKORD_"."SEQUENCE" = "_SMDBA_"."_WORKDET_"."TTNUM" WHERE "_SMDBA_"."_WORKORD_"."SEQ_CHANGE:" = "BASE"."SEQUENCE")

Inventory Catalog:

No of incidents relating to inventory items of that catalog:

(SELECT COUNT(*) FROM "_SMDBA_"."Incident" WHERE "Part #" = "BASE"."PART #")

Work Order Management

Total work done:

(SELECT SUM("TIME SPENT") FROM "_SMDBA_"."_WORKDET_" WHERE "_SMDBA_"."_WORKDET_"."TTNUM" = "BASE"."SEQUENCE")

Client Management:

Show the normal email address as opposed to SDE formatted email address:

(SELECT Substring("BASE"."_EMAILID_", 7, CharIndex('}', "BASE"."_EMAILID_")-7))

I hope some of these may be of help.