Managing CA Business Intelligence Reports › Key Performance Indicators › SQL KPIs

SQL KPIs

SQL KPIs provide more flexibility than Stored Query KPIs. With SQL KPIs you can write your own queries to the CA Service Desk Manager database to produce the following types of metrics:

• Sum
• Count
• Max
• Duration

Note: Your SQL code must comply with SQL92 syntax.

The following considerations apply to SQL KPIs:

• To see the SQL code for these example queries, open the query definitions from the KPI List page.

  Note: For instructions, see the Online Help.

• The @ symbol is not supported in SQL KPIs. Instead, use syntax as shown in the following example and an attribute alias:

  SELECT * FROM cr WHERE assignee_last_name = "Smith"
  

  For this example, you could use a predefined attribute alias. The Attribute Alias Detail page appears as follows:

  " Object Name = cr

  " Alias Name = assignee_last_name

  " Status = Active

  " Alias Value = assignee.last_name

Examples: SQL Query KPI

• This predefined example produces the sum of the estimated costs of all pending change order workflow tasks:

  Est Cost Sum of Pending Change Workflow Tasks
  

• This predefined example produces a count of the active change orders that have been closed and reopened:

  Count of Reopened Change Orders