Using MDX with DeepSee
Adding Summaries


This chapter describes how to add summaries (such as averages
and totalsxt) to your MDX queries. It discusses the following topics:
MDX includes functions that summarize a given value, across
a given set. For each function, the arguments are a set and an optional
numeric expression (such as a reference to a measure). DeepSee evaluates
the expression for each member of the set and then returns a single
value. If no numeric expression is given, DeepSee instead evaluates
the measure used in the query (possibly %COUNT).
The functions are as follows:

SUM, which returns
the sum of the values.

AVG, which returns
the average value. This function ignores members for which the expression
is null.

MAX, which returns
the maximum value.

MIN, which returns
the minimum value.

MEDIAN, which returns
the value from the set that is closest to the median value.

STDDEV, which returns
the standard deviation of the values.

STDDEVP, which
returns the population standard deviation of the values.

VAR, which returns
the variance of the values.

VARP, which returns
the population variance of the values.
SELECT MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT]) ON 0 FROM demomdx
MAX
828
This query shows the maximum value of the
%COUNT measure for the members of the
Diagnoses level.
For another example, use the same function without specifying
its second argument. In this case, the query displays the
%COUNT measure as a column:
SELECT MEASURES.[%COUNT] ON 0, MAX(diagd.diagnoses.MEMBERS) ON 1 FROM demomdx
%COUNT
MAX 828
For another example, use the same function without specifying
any measure in the query at all:
SELECT MAX(diagd.diagnoses.MEMBERS) ON 0 FROM demomdx
MAX
828
In this case, DeepSee uses
%COUNT.
More typically, rather than displaying the summary value by
itself, you include it in a query that shows all the values of the
set. This process is analogous to adding a summary line (as a row
or column) in a spreadsheet.
The following example shows the
%COUNT measure
for each diagnosis, followed by the maximum value for this measure
across this set:
SELECT MEASURES.[%COUNT] ON 0,
{diagd.diagnoses.MEMBERS, MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT])} ON 1 FROM demomdx
%COUNT
1 None 828
2 asthma 90
3 CHD 37
4 diabetes 45
5 osteoporosis 22
6 MAX 828
Notice that the system first computes the
%COUNT measure for each member using the aggregation method defined for
that measure. In this case, the patients are counted. The
asthma member, for example, has a total
%COUNT value of 90. The MAX function then obtains the largest value for
this measure, across the set of diagnoses.
SELECT {gend.gender.MEMBERS, AVG(gend.gender.MEMBERS,MEASURES.[%COUNT])} ON 0,
MEASURES.[%COUNT] ON 1 FROM demomdx
Female Male AVG
%COUNT 488 512 500
When using the summary functions, you might find it convenient
to use
named sets, as described
in the chapter
“Working with Sets.” For example, the following query is equivalent to the preceding
one:
WITH SET genders AS 'gend.gender.MEMBERS'
SELECT {genders, AVG(genders,MEASURES.[%COUNT])} ON 0, MEASURES.[%COUNT] ON 1 FROM demomdx