Advanced DeepSee Modeling Guide Defining KPIs with Filters and Listings [Home] [Back] [Next]

• Introduction to KPI filters
• How to make filters interoperable between KPIs and pivot tables
• How to define filters in an MDX-based KPI
• How to define filters in an SQL-based KPI
• Additional options for defining filter names and items
• How to modify an MDX query to use filter values
• How to modify an SQL query to use filter values
• Additional MDX KPI examples
• How to define a listing for a KPI

• 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, DeepSee 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.

• 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.

• 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.

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 chapter.
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 chapter.
   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 chapter.
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 chapter.

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

• 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.KPI.
• pSearchKey is the search key entered by the user.

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

• pCube is the logical name of a DeepSee 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.

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 chapter.
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 chapter.
   You could instead hardcode the filter items; see “Other Options for Defining Filter Names and Items,” later in this chapter.
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 chapter.

• How to specify filter names at runtime
• How to hardcode the list of filter items in the valueList attribute
• How to specify the list of filter items via the sql attribute
• How to use custom logic to create the list of the filter items at runtime

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

• 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.

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

}

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
}

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

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

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
}

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 DeepSee_Model_PatientsCube.DxAgeGroup"
            Set st = ##class(%SQL.Statement).%New()

            Set status = st.%Prepare(sql)
            If $$$ISERR(status) {Do $system.Status.DisplayError(status) Quit}

            Set rs = st.%Execute()
            While(rs.%Next(.status)) {
                If $$$ISERR(status) {Do $system.Status.DisplayError(status) Quit}
                Set display=rs.DxAgeGroup
                Set actual="&["_display_"]"
                Set pMembers($I(pMembers)) = $LB(display,actual)
            }
        If $$$ISERR(status) {Do $system.Status.DisplayError(status) Quit}
        }
    }
    Catch(ex) {
        Set status = ex.AsStatus()
    }

    Quit status
}

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

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 previous chapter.
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).

• 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

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
}

• "[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.

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

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 the previous chapter.
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).

method %GetSQLForFilter(sql_field_reference,filter_name) As %String

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

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 DeepSee_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
}

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
}

Class DeepSee.Model.KPIs.DemoMDXAutoFilters Extends DeepSee.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>
}

}

• %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 chapter.
• 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.

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
}

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
}

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
}

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

• 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"

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
}