Defining a Listing for a KPI

Defining a Listing for a KPI

You can define a KPI so that it includes a listing option. In this case, if the KPI also includes filters, the listing definition must consider the filter selections.

To define the listing, you implement the %OnGetListingSQL() method in your KPI class. This method has the following signature:

ClassMethod %OnGetListingSQL(ByRef pFilters As %String,                               ByRef pSelection As %String) As %String  

This method returns the text of a listing query. The arguments are as follows:

• pFilters is a multidimensional array that contains the current filter values. This array has the following nodes:

  Node	Node Value
  pFilters(filter_name) where filter_name is the name of a filter defined in this KPI	Current value of this filter

  For details, see Defining KPI Filters.

• pSelection is a multidimensional array that contains the information about the current selection. This array has the following nodes:

  Node	Node Value
  pSelection("selectedRange")	Currently selected cells in the pivot as a string in the form "startRow,startCol,endRow,endCol" (1-based).
  pSelection("rowValues")	Comma-separated list of the values for the selected rows. In these values, any comma is presented as a backslash (\). If no property of the KPI is configured as the value, then this node contains the series name instead.
  pSelection("sortColumn")	Specifies the number of the column to use for sorting the listing. Use 0 for no sorting.
  pSelection("sortDir")	Specifies the sort direction, "ASC" or "DESC"

The method should return an SQL SELECT query. In this query, you can also use arrow syntax and SQL functions, as with other listings.

Or you can override the %OnGetListingResultSet() method. In this case, you must prepare and execute the result set.

Example

The following example is from HoleFoods.KPISQL:

ClassMethod %OnGetListingSQL(ByRef pFilters As %String, ByRef pSelection As %String) As %String
{
    Set tSQL = "SELECT TOP 1000 %ID,DateOfSale,Product FROM HoleFoods.SalesTransaction"

    // apply sorting, if asked for
    If (+$G(pSelection("sortColumn"))>0) {
        Set tSQL = tSQL _ " ORDER BY " _ pSelection("sortColumn") _ " " _ $G(pSelection("sortDir"))
    }

    Quit tSQL
}