フィルタおよびリストを含む KPI の定義

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

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
}

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

}

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
}