As described in SAS Note 40029, semi-additive measures can generate unexpected results when the VisualTotals function is also used. In order to ensure that your data does not display these unexpected values, a new option has been created for MDX expressions.

The option, VisualTotals_Behavior, can be included on any calculated measure and will cause the value of the calculated measure to be altered when the member is crossed with a VisualTotals function. The option currently supports only one behavior, which is to return a missing value for the VisualTotals line. The syntax for the option is:

VisualTotals_Behavior=(BLANK, {dimension_name | hierarchy_name}* )

IIF([TIME].currentmember.level.ordinal > 0,
   AGGREGATE(YTD() ,[Measures].[quantitySum] ) ,
   (CLOSINGPERIOD([TIME].[year], [TIME].defaultmember),[Measures].[quantitySum] )), 
VisualTotals_BEHAVIOR=(BLANK,{[Time].[YMD], [Geography]})

This syntax indicates that the measure created as a result of this expression will display a missing value whenever the YMD hierarchy of the Time dimension or the Geography dimension is included in the Rows or Columns of an MDX query. The measure will be missing only when it crosses the VisualTotals result of the given dimensions.

The VisualTotals_Behavior property can be defined only as a global member using the DEFINE statement in the OLAP Procedure, or as a global (CREATE GLOBAL), session (CREATE) or query (WITH) scope member when writing your own MDX queries. These facilities enable you to provide custom properties as part of your expression.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.21_M3 is available at: