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).
