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
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
When using the summary functions, you might find it convenient to use named sets, as described in 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
Another way to add a summary line is to define a summary member that combines the displayed members. See Adding a Summary Member, in Creating and Using Calculated Measures and Members.