Problem Note 40550: VisualTotals with MDX functions using the .CURRENTMEMBER function can return unexpected results
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}* )
|
An example expression which makes use of this option is:
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
| SAS System | SAS OLAP Server | z/OS | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft® Windows® for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| Microsoft Windows XP Professional | 9.2 TS2M0 | 9.3 TS1M0 |
| Windows Vista | 9.2 TS2M0 | 9.3 TS1M0 |
| Windows Vista for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| 64-bit Enabled AIX | 9.2 TS2M0 | 9.3 TS1M0 |
| 64-bit Enabled HP-UX | 9.2 TS2M0 | 9.3 TS1M0 |
| 64-bit Enabled Solaris | 9.2 TS2M0 | 9.3 TS1M0 |
| HP-UX IPF | 9.2 TS2M0 | 9.3 TS1M0 |
| Linux | 9.2 TS2M0 | 9.3 TS1M0 |
| Linux for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| OpenVMS on HP Integrity | 9.2 TS2M0 | 9.3 TS1M0 |
| Solaris for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Query and Reporting ==> OLAP (Online Analytical Processing)
Third Party ==> Programming ==> MDX (Multiple Expression Language)
|
| Date Modified: | 2010-09-27 09:17:16 |
| Date Created: | 2010-08-09 16:35:24 |
