VISUALTOTALS
Returned Type
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:
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).