docs.intersystems.com
Home  /  Application Development: Analytics Options  /  InterSystems MDX Reference  /  MDX Functions  /  %KPI


InterSystems MDX Reference
%KPI
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


Returns a value from a KPI or plug-in. This function is an InterSystems extension to MDX.
Returned Type
This function returns a number.
Syntax and Details
%KPI(kpiName,propName,series)
Or:
%KPI(kpiName,propName,series,parmName1,parmValue1,parmName2,parmValue2)
Where:
%KPI uses all provided parameter values and returns the value of the given propName for the given series. For KPIs and plug-ins, the caption for a value is the normalized and localized property name.
For MDX-based KPIs and plug-ins, you can use the special %CONTEXT parameter to cause the KPI to consider the context of query, which is otherwise ignored. For its value, specify a combination of the following flags:
Use a pipe character (|) to combine flags, for example: "rows|columns". The value "all" is equivalent to "rows|columns|filters". (The %MDX function also uses this parameter; see %MDX for examples.)
Important:
If you use the %CONTEXT parameter, remember to enclose it in quotes. Also, you must specify a value for this parameter unless you use it as the last parameter. For example, the following is valid:
%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Amount Sold","%CONTEXT")
The following is also valid:
%KPI("%DeepSee.Median","MEDIAN",1,"%CONTEXT","all","%measure","Amount Sold")
But the following is not correct and will not be interpreted as desired:
%KPI("%DeepSee.Median","MEDIAN",1,"%CONTEXT","%measure","Amount Sold")
KPIs (other than plug-ins) are executed synchronously by default but can be defined to execute asynchronously.
Example
The following example gets the value of the PatCount property for the first row of the DemoMDX KPI:
SELECT %KPI("demomdx","PatCount") ON 0 FROM patients
 
                             Patient Count
                                       115
The following example defines a calculated measure that uses the %DeepSee.Plugin.Median sample plug-in:
WITH MEMBER [MEASURES].[Median Amount Sold] AS 
'%KPI("%DeepSee.Median","MEDIAN",1,"%measure","Amount Sold","%CONTEXT")' 
SELECT NON EMPTY {[Measures].[Amount Sold],[MEASURES].[MEDIAN AMOUNT SOLD]} ON 0,
NON EMPTY [Product].[P1].[Product Name].Members ON 1 
FROM [HoleFoods]

                         Amount Sold   Median Amount Sold
 1 Bagels (dozen)               38.96                 2.95
 2 Bundt Cake                1,632.01                19.95
 3 Calamari (frozen            566.90                22.95
 4 Cheerios (box)              600.11                 3.95
 5 Donuts (dozen)              429.36                 2.95
 6 Free-range Donut          1,310.64                12.95
 7 Fruit Loops (box            772.83                 4.95
 8 Lifesavers (roll            248.96                 1.15
 9 Onion ring                  377.25                 4.95
10 Onion ring                   28.57                 5.95
11 Penne (box)                 176.72                 1.95
12 Pineapple Rings             512.00                 8.95
13 Pretzels (bag)               88.12                 3.95
14 Swiss Cheese (sl            445.10                 5.95
15 Tortellini (froz          1,000.89                 6.95
16 Unsalted Pretzel            316.70                 4.25
17 Ziti (box)                  979.43                 4.81
Available Plug-in Classes
The system provides several plug-ins for you to use with the %KPI function:
%DeepSee.Distinct
Gets the number of distinct values for the given level.
This plug-in provides the property DISTINCT. The plug-in accepts the following parameters:
Parameter Value
%cube Logical name of the cube.
%level MDX identifier for the level or relationship whose distinct values you want to count. For example, [DocD].[H1].[Doctor] or [RelatedCubes/Doctors].[DocD]
This plug-in is defined by the class %DeepSee.PlugIn.Distinct.
%DeepSee.Median
Gets the median value for a given measure, across all the lowest-level records used in a cell.
This plug-in provides the property MEDIAN. The plug-in accepts the following parameters:
Parameter Value
%cube Logical name of the cube.
%measure MDX identifier for measure whose values you want to use. For example: [MEASURES].[Measure Name]
This plug-in is defined by the class %DeepSee.PlugIn.Median.
%DeepSee.Percentile
Gets a percentile value for a given measure, across all the lowest-level records.
This plug-in provides the property PERCENTILE. The plug-in accepts the following parameters:
Parameter Value
%cube Logical name of the cube.
%measure MDX identifier for measure whose values you want to use. For example: [MEASURES].[Measure Name]
%percentile The percentile that you want to evaluate. The default is 50, so that the plug-in calculates the 50th percentile.
This plug-in is defined by the class %DeepSee.PlugIn.Percentile.
Note that these plug-in classes are defined with PLUGINTYPE as "Aggregate", which means that the plug-ins cannot be directly used in the Analyzer or in widgets.
See Also