Overview

Visual totals are system-generated calculated members that use the AGGREGATE function. If a calculated measure in the cube uses an AGGREGATE function (like SUM, COUNT, MIN, MAX, or AVG), then the VisualTotals function works as designed. If a calculated measure does not use an AGGREGATE function, then you might see unexpected results. For these cases, you can use the VisualTotals_Behavior calculated member option in order to suppress the total and subtotal values returned by the VisualTotals function. When this option is used, the VisualTotals function returns missing values.

Syntax for the VisualTotals_Behavior option

Here is the syntax for the VisualTotals_Behavior calculated member option:

VisualTotals_Behavior=(BLANK, {dimension_name | hierarchy_name} )

Where to add this option

You can add this option to MDX calculated measure definitions in SAS^® Information Map Studio, PROC OLAP, SAS^® Enterprise Guide^® OLAP Viewer, and SAS^® OLAP Cube Studio. However, the most reliable place to add the option is in the Expression Editor in SAS Information Map Studio. If you add the option using the other products, the option is removed under certain circumstances. Specifically, the option is removed when SAS Information Map Studio creates a wrapper calculated member.

Wrapper calculated members are created in the following circumstances:

• A name is changed in the information map, including the automatic conversion done to remove underscores and prop-case strings.
• A format is applied in the information map.
• The expression is edited in the information map.

Examples

In the following table, the Prior Yr VTB calculated member (highlighted in yellow) uses the VisualTotals_Behavior option. The Prior Yr calculated member (highlighted in red) uses the same definition as Prior Yr VTB, but it does not include the VisualTotals_Behavior option.

Here is the definition of the Prior Year calculated member without using the VisualTotals_Behavior option:

DEFINE MEMBER "[Prdcube].[Measures].[Prior Yr]" AS 'iif([Time].[Time].CurrentMember.Level.Name="YEAR"    ,
    ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Year],1))  ,
    iif([Time].[Time].CurrentMember.Level.Name="QUARTER",
    ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Quarter],4)) ,
      iif([Time].[Time].CurrentMember.Level.Name="MONTH",
      ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Month],12)),
      ([Time].[Time].CurrentMember,[Measures].[ActualSUM])
    ))), SOLVEORDER = -5';

Here is the definition of the Prior Year calculated measure with VisualTotals_Behavior option:

DEFINE MEMBER "[Prdcube].[Measures].[Prior Yr VTB]" AS 'iif([Time].[Time].CurrentMember.Level.Name="YEAR"    ,
    ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Year],1))  ,
    iif([Time].[Time].CurrentMember.Level.Name="QUARTER",
    ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Quarter],4)) ,
      iif([Time].[Time].CurrentMember.Level.Name="MONTH",
      ([Measures].[ActualSUM],ParallelPeriod([Time].[Time].[Month],12)),
      ([Time].[Time].CurrentMember,[Measures].[ActualSUM])
    ))), SOLVEORDER = -5, VISUALTOTALS_BEHAVIOR=(BLANK,{time})';

Operating System and Release Information