InterSystems MDX Reference
%KPI


Returns a value from a KPI or plugin. This function is an InterSystems extension to MDX.
This function returns a
number.
%KPI(kpiName,propName,series)
%KPI(kpiName,propName,series,parmName1,parmValue1,parmName2,parmValue2)



series is the optional number or the quoted name of a series (row) in the KPI or plugin. The default is 1.

parmName1,
parmName2, and so on are optional quoted names of parameters of the KPI or plugin (in most cases, these are filters). Note that parameter names are casesensitive.
The order in which you list the filters does not affect the KPI.
You can specify up to 16 parameters and their values.

%KPI uses all provided parameter values and returns the value of the given
propName for the given
series. For KPIs and plugins, the caption for a value is the normalized and localized property name.
For MDXbased KPIs and plugins, 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:

"rows" specifies that the context of the current pivot row should be used

"columns" specifies that the context of the current pivot column should be used

"filters" specifies that the context of the filters of current pivot should be used

"all" specifies that all the preceding should be used (this is the default)
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 plugins) are executed synchronously by default but can be defined to execute asynchronously.
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
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 Freerange 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
The system provides several plugins for you to use with the %KPI function:
Gets the number of distinct values for the given level.
This plugin provides the property
DISTINCT. The plugin accepts the following parameters:
Gets the median value for a given measure, across all the lowestlevel records used in a cell.
This plugin provides the property
MEDIAN. The plugin accepts the following parameters:
Gets a percentile value for a given measure, across all the lowestlevel records.
This plugin provides the property
PERCENTILE. The plugin accepts the following parameters:
Note that these plugin classes are defined with
PLUGINTYPE as
"Aggregate", which means that the plugins cannot be directly used in the Analyzer or in widgets.