Skip to main content

Defining KPIs with Filters and Listings

This page describes options for defining Business Intelligence KPIs that include filters and listings.

Before using this page, see Defining Basic KPIs. For more advanced KPIs, see Defining Advanced KPIs.

KPIs can also define actions. See Defining Custom Actions.

Also see Accessing the BI Samples.

Introduction to Filters

Typical dashboards include one or more filter controls, with which the user interacts with the widgets on the dashboard. Each filter control is typically displayed as a drop-down list. A filter can filter the data displayed by a widget, or it could affect the data source in some other different manner.

For a pivot table, any level in the cube or subject area can be used as filter. For a KPI, however, no filters are defined by default. To add filters to a KPI, you use the following system:

  • You define each filter; that is, you specify the filter names and the items in the filter lists.

    You can skip this step if you intend to use filters provided by a pivot table.

  • You specify the KPI query programmatically so that it uses the filters. To do this, you implement a callback method in the KPI so that it overrides any hardcoded query. Within this method, you have access to the selected filter values.

    In typical cases, you add a WHERE clause (or %FILTER, for an MDX query) that restricts the records used by the query. Then when the user refreshes the dashboard, the system reruns the query and displays the results.

    Because you construct the KPI query yourself, you can use filters in more general ways. That is, you are not required to use the filter selection to affect the WHERE (or %FILTER) clause of the query. For an extreme example, a filter could display the options 1, 2, and 3. Then the KPI could execute version 1, 2, or 3 of the query, which might be entirely different versions. For another example, a filter could display a list of measure names, and the KPI could include the selected measures in its query.

Creating Interoperable Filters

A dashboard can contain both KPIs and pivot tables, displayed in scorecards, pivot table widgets, or other widgets. Within a dashboard, a filter can be configured to affect multiple widgets.

It is possible to define filters that affect both KPIs and pivot tables.

Pivot tables send and receive filter values in a specific syntax, described in the following subsection. To create interoperable filters, you must consider this requirement; the second subsection describes two possible approaches.

Filter Syntax for Pivot Tables

The following table indicates the filter syntax used and expected by pivot tables. This syntax applies to pivot tables that are displayed in any kind of dashboard widget.

  Scenario Syntax Example
Filter Name All MDX level identifiers [Region].[H1].[Country]
Filter Value User selects a single member MDX key for a member of that level, which is an expression of the following form: &[keyval] where keyval is the key value for the member. &[USA]
Filter Value User selects a range of members Expression of the following form: &[keyval1]:&[keyval2] &[2006]:&[2009]
Filter Value User selects multiple members Expression of the following form (for example): {&[keyval1],&[keyval2]} {&[2006],&[2007],&[2008]}
Filter Value User selects a single member and selects Exclude Expression of the following form: %NOT&[keyval1] %NOT &[2010]
Filter Value User selects multiple members and selects Exclude Expression of the following form (for example): %NOT{&[keyval1],&[keyval2} %NOT {&[2006],&[2007]}

Other than the optional %NOT string, the filter names and filter values are not case-sensitive.

Ways to Create Interoperable Filters

There are two general ways to create filters that can affect both pivot tables and KPIs:

  • You can create a KPI that defines and uses filters in the format required by pivot tables.

  • You can define a KPI that converts filter names and values from the pivot table format to the format needed for the KPI query.

These approaches can work with either MDX-based or SQL-based KPIs, but more work is needed to convert values when you use an SQL-based KPI.

For an MDX example, see the sample dashboard Demo Filter Interoperability. This dashboard demonstrates the former approach. This dashboard displays two pivot table widgets; the upper one displays a pivot table, and the lower one displays a KPI. For this dashboard, the Filters worklist on the left includes the following filters:

  • The Favorite Color filter is configured as part of the upper widget and is therefore defined by the pivot table that is displayed in that widget.

    Favorite Color is a level in the cube on which this pivot table is based.

  • The Doctor Group filter is configured as part of the lower widget and is therefore defined by the KPI that is displayed in that widget.

    This filter is defined programmatically within this KPI. This filter is defined to use values in pivot table format, as given in the previous section.

For both of these filters, the target of this filter is * (which refers to all widgets in this dashboard). As you can see by experimentation, both filters affect both of these widgets.

The KPI includes an additional filter (Yaxis), which controls the y-axis of the MDX query used by the KPI. This filter has no effect on the pivot table.

For details on how this works, see Additional MDX KPI Examples, later in this page.

Defining Filters in an MDX-Based KPI

To define filters in an MDX-based KPI, do the following:

  1. Specify the list of filters. Each filter can have a logical name (required) and a display name (the same as the logical name by default).

    One option is to hardcode the list of filters. To do so, add a set of <filter> elements to the <kpi> element. For example:

    <kpi name="sample KPI"...>
     <filter name="[aged].[h1].[age group]" displayName="Age Group"/>
     <filter name="[gend].[h1].[gender]" displayName="Gender"/>
     <filter name="[homed].[h1].[zip]" displayName="ZIP Code"/>
    ...
    

    In this example, the logical filter names are MDX level identifiers. It is convenient, but not required, to specify the logical names in this way.

    You could instead specify filter names at runtime. See Other Options for Defining Filter Names and Items, later in this page.

  2. If you add <filter> elements, specify the following options as wanted:

    • You can enable users to select only one element from a filter list (instead of multiple elements, which is the default). To do so, add multiSelect="false" to the <filter> element. For example:

       <filter name="[homed].[h1].[zip]" displayName="ZipCode" multiSelect="false" />
      

      When multiselect is disabled, the Exclude option is also disabled.

    • If the filter items are days, you can display a calendar control rather than the default drop-down list. To do so, add searchType="day" to the <filter> element. For example:

       <filter name="[birthd].[h1].[day]" displayName="Day"
      filterProperty="Day" searchType="day"/>
      
    • A filter can depend upon another filter. To indicate this, use the optional dependsOn attribute. For example:

      <filter name="[homed].[h1].[zip]" displayName="ZIP Code"/>
      <filter name="[homed].[h1].[city]" displayName="City" dependsOn="[homed].[h1].[zip]"/>
      
      
  3. Define the filter items for each filter. Each filter item can have a logical name (required) and a display name (the same as the logical name by default).

    It is useful if the logical names are the same as MDX member keys; otherwise, more work is needed to construct filter clauses, as described later in this page.

    One option is to implement the %OnGetFilterMembers() method. If the logical names of the filters are MDX level identifiers, you can use a version of the following simple implementation:

    ClassMethod %OnGetFilterMembers(pFilter As %String, Output pMembers As %List,pSearchKey As %String = "") As %Status
    {
        set status = $$$OK
    
        try {
            do ..%GetMembersForFilter("Patients.cube",pFilter,.pMembers,pSearchKey)
        }
        catch(ex) {
            set status = ex.AsStatus()
        }
    
        quit status
    }

    Simply replace Patients with the name of your cube. For example:

     do ..%GetMembersForFilter("YourCube.cube",pFilter,.pMembers,pSearchKey)
    

    For details on %OnGetFilterMembers(), see the first subsection. The example shown here uses the %GetMembersForFilter() method; for details, see the second subsection.

    You could instead hardcode the filter items; see Other Options for Defining Filter Names and Items, later in this page.

  4. Modify the query to use the value or values selected by the user. See Modifying an MDX Query to Use Filter Values, later in this page.

%OnGetFilterMembers() Details

In your KPI class, if you implement the %OnGetFilterMembers() method, use the following signature:

classmethod %OnGetFilterMembers(pFilter As %String,                                  Output pMembers As %List,                                  pSearchKey As %String = "") as %Status

Where:

  • pFilter is the logical name of a filter.

  • pMembers specifies the members in a $LISTBUILD list. This list contains both the logical names and the display names. For details, see the class reference for %DeepSee.KPIOpens in a new tab.

  • pSearchKey is the search key entered by the user.

Tip:

You can use this method in any KPI, regardless of the type of query it uses.

If the logical names of the filters are MDX level identifiers, you can use a simple implementation as shown previously. If not, it is necessary to do more work before getting the members. For examples, see Using Custom Logic to Build the List of Filter Items at Runtime, later in this page.

%GetMembersForFilter() Details

In your KPI class, you can use the %GetMembersForFilter() method, which has the following signature:

classmethod %GetMembersForFilter(pCube As %String,                                   pFilterSpec As %String,                                   Output pMembers,                                   pSearchKey As %String = "") as %Status

Where:

  • pCube is the logical name of a cube, with .cube appended to it.

  • pFilterSpec is a MDX level identifier (for example, "[DateOfSale].[Actual].[YearSold]").

  • pMembers, which is returned as an output parameter, is a list of members in the form required by %OnGetFilterMembers(). In this list, the filter items are member keys.

  • pSearchKey is the search key entered by the user.

This method is more useful for MDX-based KPIs than for SQL-based KPIs. For an SQL-based KPI, you would need to convert the filter values to a form suitable for use in your query.

Defining Filters in an SQL-Based KPI

To define filters in an SQL-based KPI:

  1. Specify the list of filters. Each filter can have a logical name (required) and a display name (the same as the logical name by default).

    One option is to specify the <filter> element of the <kpi> element; see the previous section.

    Or you could instead specify filter names at runtime. See Other Options for Defining Filter Names and Items, later in this page.

  2. Within each <filter> element, specify the following multiSelect, searchType, and dependsOn attributes as wanted. See the previous section.

  3. Define the filter items for each filter. Each filter item can have a logical name (required) and a display name (the same as the logical name by default).

    One option is to implement the %OnGetFilterMembers() method, described earlier in this page.

    You could instead hardcode the filter items; see Other Options for Defining Filter Names and Items, later in this page.

  4. Modify the query to use the value or values selected by the user. See Modifying an SQL Query to Use Filter Values, later in this page.

Other Options for Defining Filter Names and Items

This section describes other options for defining filter names and filter items. It discusses the following topics:

Specifying the Filter Names at Runtime

The easiest way to define the filter names is to hardcode them as described earlier in this page. Another option is to define them at runtime. To do so, override the %OnGetFilterList() method of your KPI class. This method has the following signature:

classmethod %OnGetFilterList(Output pFilters As %List,pDataSourceName As %String = "") As %Status

Where pFilters is an array with the following nodes:

  • pFilters — Specifies the number of filters.

  • pFilters(n) — Specifies the details for the nth filter. This is a $LISTBUILD list that consists of the following items:

    • A string that equals the logical name of the filter.

    • A string that equals the display name of the filter.

    • A string that equals the filter property; the default is the logical name of the filter.

    • 1 or 0 to indicate whether multiselect is enabled for this filter. Use 1 to enable multiselect or 0 to disable it.

pDataSourceName is for future use.

For example, the following %OnGetFilterList() adds a filter named New Filter:

ClassMethod %OnGetFilterList(Output pFilters As %List, pDataSourceName As %String = "") As %Status
{
    set newfilter=$LB("New Filter","New Filter Display Name",,0)
    set pFilters($I(pFilters))=newfilter

}

For another example, the following %OnGetFilterList() uses a utility method to define as filters all the levels in the Patients cube. The utility method %GetFiltersForDataSource() (in %DeepSee.Dashboard.UtilsOpens in a new tab) returns a list of filters in the format needed by %OnGetFilterList():

ClassMethod %OnGetFilterList(Output pFilters As %List, pDataSourceName As %String = "") As %Status
{
    set tSC = ##class(%DeepSee.Dashboard.Utils).%GetFiltersForDataSource("patients.cube",.tFilters)
    quit:$$$ISERR(tSC)

    set i = ""
    for {
        set i = $order(tFilters(i), 1, data)
        quit:i=""

        set pFilters($i(pFilters)) = $lb($lg(data,2), $lg(data,1),,1)
    }
    quit $$$OK
}

Hardcoding the List of Filter Items via the valueList Attribute

Another way to build the list of filter items is to specify the valueList attribute of the <filter> element. Use a comma-separated list of logical names for the filter items. The order of this list determines the order in which filter controls list the filter items.

For example:

<filter name="ZipCode" 
valueList="&amp;[36711],&amp;[34577],&amp;[38928],&amp;[32006],&amp;[32007]"
displayList="36711,34577,38928,32006,32007"
/>

This attribute takes precedence over the sql attribute.

If you specify this attribute, you can also specify the displayList attribute, as shown above. If specified, this must be a comma-separated list of display names. If you do not specify this attribute, the logical names are also used as the display names.

Retrieving the List of Filter Items via the sql Attribute

Another way to build the list of filter items is to specify the sql attribute of the <filter> element. If specified, this must be an SQL query. The query can return either one or two columns. The first column in the returned dataset must provide the logical names for the filter items. The second column, if included, provides the corresponding display names. If you do not include a second column, the logical names are also used as the display names.

For example:

<filter name="ZipCode1" sql="SELECT DISTINCT PostalCode FROM BI_Study.City"/>

If you specify the sql attribute, do not specify the displayList or valueList attributes (see the previous subsection).

Using Custom Logic to Build the List of Filter Items at Runtime

Another way to build the list of filter items is to implement %OnGetFilterMembers() and your own logic to create the list. For example:

ClassMethod %OnGetFilterMembers(pFilter As %String, Output pMembers As %List, pSearchKey As %String = "") As %Status
{
    Set status = $$$OK

    Try {
        If (pFilter = "AgeGroup") {
                set pFilterSpec="[AgeD].[h1].[Age Group]"
        } Elseif (pFilter="Gender") {
                set pFilterSpec="[GenD].[h1].[Gender]"
        } Elseif (pFilter="ZipCode") {
                set pFilterSpec="[HomeD].[h1].[ZIP]"
        }
        do ##class(%DeepSee.KPI).%GetMembersForFilter("Patients",pFilterSpec,.pMembers,pSearchKey)
    }
    Catch(ex) {
        Set status = ex.AsStatus()
    }

    Quit status
}

For another approach, you can query the dimension table that holds the level members. (For details on the dimension tables, see Details for the Fact and Dimension Tables.) The following shows an example:

ClassMethod %OnGetFilterMembers(pFilter As %String, Output pMembers As %List, pSearchKey As %String = "") As %Status
{
  set status = $$$OK

  try {
    if (pFilter = "AgeGroup") {
      //get values from level table
      set sql = "SELECT DISTINCT DxAgeGroup FROM BI_Model_PatientsCube.DxAgeGroup"
      set stmt = ##class(%SQL.Statement).%New()

      set status = stmt.%Prepare(sql)
      if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

      set rs = stmt.%Execute()
      if (rs.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rs.%SQLCODE, ": ", rs.%Message quit}

      while(rs.%Next()) {
        set display=rs.DxAgeGroup
        set actual="&["_display_"]"
        set pMembers($I(pMembers)) = $LB(display,actual)
      }
      if (rs.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rs.%SQLCODE, ": ", rs.%Message quit}
    }
  }
  catch(ex) {
    set status = ex.AsStatus()
  }

  quit status
}

Note that this example does not use the pSearchKey argument.

Modifying an MDX Query to Use Filter Values

For an MDX-based KPI, to modify the query to use filters, implement the %OnGetMDX() method. If you use the KPI wizard in Visual Studio CodeOpens in a new tab and you choose the source type as mdx, the generated KPI class includes the following stub definition for this method, which you can use as a starting point:

/// Return an MDX statement to execute.
Method %OnGetMDX(ByRef pMDX As %String) As %Status
{
    Quit $$$OK
}

The variable pMDX contains the MDX query specified in the mdx attribute of <kpi>. (If you did not specify that attribute, pMDX is null.)

When you implement this method, do the following:

  1. If there is no initial query, specify an initial value of the variable pMDX. This should be an MDX SELECT query.

    There is no initial query if you do not specify a query in the mdx attribute, as described in the Defining Basic KPIs.

  2. Examine each applicable filter and get its value.

    To do this, use the %filterValues property of your KPI instance, as described in the first subsection.

  3. Parse each filter value and convert it as needed to a form that can be used in an MDX %FILTER clause (if that is how you intend to use the filter). For a table that lists the suitable forms, see the second subsection.

  4. Modify the variable pMDX to use the filter values, as appropriate for your needs. In the most common case, append a %FILTER clause for each non-null filter, as follows:

    %FILTER mdx_expression
    

    Where mdx_expression is the expression for that filter, as created in the previous step.

    Or create an MDX tuple expression that combines the expressions and then add a single %FILTER clause like this:

    %FILTER tuple_expression
    

    In more exotic cases, you could use the filter values to rewrite the query string in other ways.

  5. Return $$$OK (or 1).

The last subsection provides an example.

Accessing Filter Values

When a user selects filter items, the system sets the %filterValues property of your KPI instance. This property is an object (specifically an instance of %ZEN.proxyObjectOpens in a new tab) that the system creates as follows:

  • It has one property for each filter in the KPI, with the same name (case-sensitive) as the filter.

    For example, if a KPI has filters named filter1, filter2, and filter3, then the %filterValues property of your KPI instance has properties named filter1, filter2, and filter3.

  • The properties of %filterValues contain values shown in the following table:

    User Selection in Filter Value of %filterValues.FilterName Notes
    None null  
    One item item  
    Multiple items {item1,item2} Not applicable if multiselect is off
    One item and the Exclude option %NOT item Not applicable if multiselect is off
    Multiple items and the Exclude option %NOT {item1,item2} Not applicable if multiselect is off
    Range item1:item2 Applies only to a filter defined in a pivot table, which can affect the KPI

Converting Filter Values to MDX Expressions for Use in %FILTER

The following table lists appropriate MDX expressions to use in %FILTER clauses, depending on user selections.

User Selection in Filter Appropriate MDX %FILTER Expression
None none (do not apply a %FILTER clause in this case)
One item [dimension].[hierarchy].[level].[member]
One item and the Exclude option [dimension].[hierarchy].[level].[member].%NOT
item1 and item2 {item1,item2,...} where each item has the form [dimension].[hierarchy].[level].[member]
Multiple items and the Exclude option EXCEPT([dimension].[hierarchy].[level].MEMBERS,{item,item,item,...}) where each item has the form [dimension].[hierarchy].[level].[member]
A range [dimension].[hierarchy].[level].[member]:[member]

For an example, see the method BuildString() in the sample class BI.Utils.MDXAutoFiltersKPI.

Note that if multiselect is disabled, EXCLUDE is also disabled, and there are fewer possible forms of filter values. In this case, query construction is comparatively simple.

Example

The following implementation of %OnGetMDX() applies to a KPI in which multiselect is disabled:

Method %OnGetMDX(ByRef pMDX As %String) As %Status
{
 if (..%filterValues."[aged].[h1].[age group]"'="") 
 {
    set pMDX = pMDX _ " %FILTER [aged].[h1].[age group]." _..%filterValues."[aged].[h1].[age group]"
 } 

 if (..%filterValues."[gend].[h1].[gender]"'="") 
 {
    set pMDX = pMDX _ " %FILTER [gend].[h1].[gender]." _..%filterValues."[gend].[h1].[gender]"
 } 

 if (..%filterValues."[homed].[h1].[zip]"'="") 
 {
    set pMDX = pMDX _ " %FILTER [homed].[h1].[zip]." _..%filterValues."[homed].[h1].[zip]"
 } 

 quit $$$OK
}

Because this method uses delimited property names, it can be confusing to read. Notes:

  • "[aged].[h1].[age group]" is a valid property name.

  • ..%filterValues."[aged].[h1].[age group]" is a reference to the "[aged].[h1].[age group]" property of the %filterValues property of the KPI instance.

Modifying an SQL Query to Use Filter Values

For an SQL-based KPI, to modify the query to use filters, implement the %OnGetSQL() method. If you use the KPI wizard in Visual Studio CodeOpens in a new tab and you choose the source type as sql, the generated KPI class includes the following stub definition for this method, which you can use as a starting point:

/// Return an SQL statement to execute.
Method %OnGetSQL(ByRef pSQL As %String) As %Status
{
    Quit $$$OK
}

The variable pSQL contains the SQL query specified in the sql attribute of <kpi>. If you did not specify that attribute, pSQL is null.

When you implement this method, do the following:

  1. If there is no initial query, specify an initial value of the variable pSQL. This should be an SQL SELECT query.

    There is no initial query if you do not specify a query in the sql attribute, as described in Defining Basic KPIs.

  2. Examine each applicable filter and get its value. The options are as follows:

    • Use the %GetSQLForFilter() method, which returns values in a convenient format for use in the WHERE clause of an SQL query. See the first subsection.

    • Use the %filterValues property of your KPI instance, as described in the previous section.

  3. Modify the variable pSQL to use the filter values, as appropriate for your needs. In the most common case, modify the query to include an SQL WHERE clause.

    In more exotic cases, you could use the filter values to rewrite the query string in other ways.

    The second and third subsections provide examples.

  4. Return $$$OK (or 1).

Note:

An SQL-based KPI cannot have more than 1000 rows; the system automatically limits the number of rows returned.

%GetSQLForFilter()

For an SQL-based KPI, you can use a method to access filter values in a format that is useful for including in your query:

method %GetSQLForFilter(sql_field_reference,filter_name) As %String

Examines the current filter selections and returns a string that you can use in the WHERE clause of an SQL query.

sql_field_expression is an SQL field name and can include arrow syntax. filter_name is the name of a filter defined in this KPI.

For example, consider the following method call:

..%GetSQLForFilter("City->Name","City")

The following table shows the values returned by this method call, in different scenarios.

Scenario Value Returned by Method
User selects PINE City->Name = 'PINE'
User selects a range, starting with MAGNOLIA and ending with PINE City->Name = ('MAGNOLIA':'PINE')
User selects MAGNOLIA and PINE City->Name IN ('MAGNOLIA','PINE')
User selects PINE and selects Exclude City->Name <> 'PINE'
User selects MAGNOLIA and PINE and selects Exclude City->Name NOT IN ('MAGNOLIA','PINE')

SQL KPI Example 1

The following example is from the sample class BI.Model.KPIs.DemoSQL. In this case, the filter adds GROUP BY and ORDER BY clauses to the SQL query.

Method %OnGetSQL(ByRef pSQL As %String) As %Status
{
    //this is the start of the SQL query for this KPI
    Set pSQL = "SELECT Count(*),AVG(Age) FROM BI_Study.Patient "
    
    Set where = ""
    //look at %filterValues to see if a filter has been applied to this KPI instance
    If $IsObject(..%filterValues) {
        If (..%filterValues.ZipCode'="") 
        {
            // Call utility method that returns filter data in convenient format
            Set sqlstring=..%GetSQLForFilter("HomeCity->PostalCode","ZipCode")
            Set where = "WHERE "_sqlstring
            
        } 
    }

    Set groupby="GROUP BY HomeCity "
    Set orderby="ORDER BY HomeCity "
    // assemble the SQL statement
    Set pSQL=pSQL_where_groupby_orderby
        Quit $$$OK
}

SQL KPI Example 2

The following example is from the sample class HoleFoods.KPISQL:

Method %OnGetSQL(ByRef pSQL As %String) As %Status
{
 If $IsObject(..%filterValues) {
   Set tWHERE = ""
   If (..%filterValues.City'="") {
      Set tWHERE = tWHERE _ $S(tWHERE="":"",1:" AND ") _ " Outlet->City = '" _ ..%filterValues.City _"'"
   }
   If (..%filterValues.Product'="") {
     Set tWHERE = tWHERE _ $S(tWHERE="":"",1:" AND ") _ " Product = '" _ ..%filterValues.Product _"'"
     }

   If (tWHERE'="") {
     // insert WHERE clase within query
     Set tSQL1 = $P(pSQL,"GROUP BY",1)
     Set tSQL2 = $P(pSQL,"GROUP BY",2)
     Set pSQL = tSQL1 _ " WHERE " _ tWHERE
     If (tSQL2 '= "") {
       Set pSQL = pSQL _ " GROUP BY" _ tSQL2
     }
    }
 }
 Quit $$$OK
}

In this case, the KPI defines the initial query within the sql attribute, as described in Defining Basic KPIs. The %OnGetSQL() method modifies that query.

Additional MDX KPI Examples

This section discusses some other MDX KPI examples.

DemoMDXAutoFilters KPI

The sample class BI.Model.KPIs.DemoMDXAutoFilters KPI is simple but uses a special superclass:

Class BI.Model.KPIs.DemoMDXAutoFilters Extends BI.Utils.MDXAutoFiltersKPI
{

Parameter CUBE = "PATIENTS";

Parameter DOMAIN = "PATIENTSAMPLE";

XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
{
<kpi name="DemoMDXAutoFilters" displayName="DemoMDXAutoFilters"  
sourceType="mdx"
mdx="SELECT {[Measures].[%COUNT],[Measures].[Avg Age],[Measures].[Avg Allergy Count]} ON 0,
NON EMPTY [DiagD].[H1].[Diagnoses].Members ON 1 FROM [Patients]">

<property name="Patient Count" displayName="Patient Count" columnNo="1" />
<property name="Avg Age" displayName="Avg Age" columnNo="2" />
<property name="Avg Allergy Count" displayName="Avg Allergy Count" columnNo="3" />

</kpi>
}

}

Notice that this class does not directly define any filters, does not directly define the filter members, and defines only a hardcoded MDX query. When you display the test page for this KPI, however, you can use all the levels of the Patients cube as filters, and the KPI appends a suitable %WHERE clause to the query. For example:

A KPI Test Page in Studio, showing that the class has 24 filters, including Age Group, Age Bucket, Age, and Allergies.

This KPI extends the sample BI.Utils.MDXAutoFiltersKPI, which defines the filters and rewrites the query.

Within this class:

  • %OnGetFilterList() retrieves all the levels defined in the cube, as given by the CUBE class parameter.

  • %OnGetFilterMembers() is implemented. For each level, it retrieves the level members, in the format required by pivot tables; see Filter Syntax for Pivot Tables, earlier in this page.

  • The instance method FilterBuilder() iterates through the cube-based filters, retrieves the current value of each, and then combines them into a string that is suitable for use as an MDX %FILTER clause.

  • %OnGetMDX() appends the %FILTER clause to the hardcoded query.

DemoInteroperability KPI

The sample class BI.Model.KPIs.DemoInteroperability KPI is a more complex version of the preceding example. Within this class, %OnGetFilterList() retrieves all the levels defined in the cube, as given by the CUBE class parameter. It then adds an additional filter called Yaxis:

ClassMethod %OnGetFilterList(ByRef pFilters As %List, pDataSourceName As %String = "") As %Status
{
    //call method in superclass so we can get filters of the associated cube
    set tSC=##super(.pFilters,pDataSourceName)
    quit:$$$ISERR(tSC) tSC

    //update pFilters array to include the custom filter
    set pFilters($i(pFilters)) = $lb("Yaxis","Yaxis",,0)
    
   quit $$$OK
}

%OnGetFilterMembers() is implemented. For the filter Yaxis, this method provides a set of members. For other filters, it retrieves the level members, in the format required by pivot tables; see Filter Syntax for Pivot Tables, earlier in this page. This method is as follows:

ClassMethod %OnGetFilterMembers(pFilter As %String, Output pMembers As %List, pSearchKey As %String = "", 
pDataSourceName As %String = "") As %Status
{
    set pMembers=""
    if (pFilter="Yaxis") {
        set pMembers($I(pMembers))=$LB("Home City","[homed].[h1].[city]")
        set pMembers($I(pMembers))=$LB("Favorite Color","[colord].[h1].[favorite color]")
        set pMembers($I(pMembers))=$LB("Profession","[profd].[h1].[profession]")
        set pMembers($I(pMembers))=$LB("Diagnoses","[diagd].[h1].[diagnoses]")
    } else {
        //call method in superclass so we can get filter members for the associated cube
         do ..%GetMembersForFilter(..#CUBE,pFilter,.pMembers)
 }
   quit $$$OK
}

Finally, %OnGetMDX() constructs the MDX query. The Yaxis filter determines which level is used for rows. Then the method appends the %FILTER clause to the query; the %FILTER clause uses any cube-based filters as in the previous example.

Method %OnGetMDX(ByRef pMDX As %String) As %Status
{
 set yaxis=", NON EMPTY [profd].[h1].[profession].MEMBERS ON 1"
 //check custom filter value
 if (..%filterValues."Yaxis"'="") {
    set yaxis=", NON EMPTY "_..%filterValues.Yaxis_".MEMBERS ON 1"
 }
 set pMDX="SELECT {MEASURES.[%COUNT],MEASURES.[avg age]} on 0"_yaxis_" FROM "_..#CUBE

 /// append a %FILTER clause to handle any other filter values
 Set pMDX = pMDX _ ..FilterBuilder() 
 Quit $$$OK
}

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
}
FeedbackOpens in a new tab