Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Application Development / InterSystems MDX Reference / MDX Functions / VISUALTOTALS
Previous section   

VISUALTOTALS

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:
images/d2rmdx_hierarchy_example.png
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

Previous section