DeepSee MDX Reference
VISUALTOTALS
[Back] 
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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:
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).
See Also