Skip to main content
Previous section   Next section

Adding Summaries

This chapter describes how to add summaries (such as averages and totalsxt) to your MDX queries. It discusses the following topics:

Introduction to Summary Functions

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.

For example:

SELECT MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT]) ON 0 FROM demomdx

                                       MAX
                                        828
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

In this case, DeepSee uses %COUNT.

Adding a Summary Line

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
Copy code to clipboard

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.

For another example:

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
Copy code to clipboard

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
Copy code to clipboard

Another way to add a summary line is to define a summary member that combines the displayed members. See “Adding a Summary Member,” in the chapter “Creating and Using Calculated Measures and Members.”