Skip to main content

VISUALTOTALS (MDX)

Given a set of members in hierarchical order, returns that set with its visual totals. In the visual totals, the actual value for any higher-level member is replaced with the sum of the values for the children that are included in the query.

Returned Type

This function returns a set of members.

Syntax and Details

VISUALTOTALS(set_expression, optional_parent_name_pattern)

Where:

  • set_expression is an expression that evaluates to a set of members. This set can include members at different levels within the same dimension but the members should be in hierarchical order.

  • optional_parent_name_pattern is a string that includes an asterisk (*) in the place where the parent name is to be used. For example: "SUB *" or "* (SUBTOTAL)"

    If you omit this, no extra strings are added to the parent names.

Example

First, for reference, the following figure shows the hierarchy used in this example:

generated description: hierarchy example

Consider the following query, which does not use VISUALTOTALS:

WITH SET demo AS 'HIERARCHIZE({homed.32006,homed.34577,homed.CYPRESS,homed.PINE,homed.SPRUCE})' 
SELECT MEASURES.[%COUNT] ON 0, demo ON 1 FROM patients
                             Patient Count
1 32006                               2,272
2 Spruce                              1,117
3 34577                               3,399
4 Cypress                             1,150
5 Pine                                1,138

This query shows the patient count for each of the listed ZIP codes and cities. The patient count for each ZIP code is the total patient count for that ZIP code.

Now consider the following variation, which does use VISUALTOTALS:

WITH SET demo AS 'HIERARCHIZE({homed.32006,homed.34577,homed.CYPRESS,homed.PINE,homed.SPRUCE})' 
SELECT MEASURES.[%COUNT] ON 0, VISUALTOTALS(demo) ON 1 FROM patients
                             Patient Count
1 32006                               1,117
2 Spruce                              1,117
3 34577                               2,288
4 Cypress                             1,150
5 Pine                                1,138

In this case, the patient count for any higher-level members (the ZIP codes) reflects only the children that are included in the query. For example, the patient count for ZIP code 34577 is the sum of the patient counts for the cities of Pine and Cypress.

For another variation, consider the following query, which is like the preceding except that it also uses the second argument to VISUALTOTALS:

VISUALTOTALS */WITH SET demo AS 'HIERARCHIZE({homed.32006,homed.34577,homed.CYPRESS,homed.PINE,homed.SPRUCE})' 
SELECT MEASURES.[%COUNT] ON 0, VISUALTOTALS(demo,"* (included cities)") ON 1 FROM patients
                             Patient Count
1 32006 (included cities)             1,117
2 Spruce                              1,117
3 34577 (included cities)             2,288
4 Cypress                             1,150
5 Pine                                1,138

The values shown are the same as in the preceding query, but each ZIP code is shown with the trailing string (included cities).

See Also

FeedbackOpens in a new tab