When a calculated measure uses a function that requires a dimension reference, the results of the calculation can be impacted by the VisualTotals function. This behavior is similar to the issue described in SAS Note 39955. The example below demonstrates this behavior and provides an explanation of the results.
WITH
MEMBER [Measures].[My Calculation] AS
AGGREGATE(YTD() ,[Measures].[ActualSum] )
SELECT
{[Measures].[ActualSum], [Measures].[My Calculation] } ON COLUMNS,
VISUALTOTALS(ADDPARENTS(
CROSSJOIN(
CROSSJOIN(
[Time].[All Time].[1993].children,
[GEOGRAPHY].[All GEOGRAPHY].[CANADA]
),
[PRODUCTS].[PRODTYPE].members
), POST), "Total *") ON ROWS
FROM
[SalesCube]
|
The calculated measure [Measures].[My Calculation] contains a YTD() calculation. You expect to see a NULL value for all Total rows, because, for the Total row, there is no current Time member, so the set created by the YTD function is empty. And that is indeed the case for all the rows with “Total 1”, “Total 2”, etc., all the way to “Total All TIME”. However, the subtotal values for Country and Product Type contain unexpected values for [Measures].[My Calculation].
The unexpected values occur because of the following:
When more than two dimensions are placed next to each other on an axis (rows, in this case), the subtotal for the innermost dimensions are crossed with an actual member of the outer dimensions. The actual member of the Time dimension exists in this case and is the set generated by YTD, which is not empty. That fact means the calculation will aggregate the totaled value over that set returned by YTD.
These actions are taken because of solve order (see SAS Note 40007). Solve order is defined as "A higher number indicates that the member is calculated using the values that result from the calculations that have a lower number."
In the case described, the calculated measure [Measures].[My Calculation] has the default solve_order=0. The visualtotal() has its default solve_order of −4096.
These solve order settings cause the VisualTotals function to process first. During the evaluation of the calculated measure [Measures].[My Calculation], the value of the contributing measure [Measures].[ActualSum] is retrieved first. The value is $29,004.00 in the cell for Quarter 1. That value is then filled into the remainder of the calculation: AGGREGATE(YTD() ,[Measures].[Actual_Sum] ) becomes AGGREGATE(YTD() , 29,004.00).
Because the Time member in this row is an actual member, the YTD function resolves to a set of 1 tuple: {[TIME].[All TIME].[1993].[1]}. The final evaluation of the calculated member is therefore 29,004.00.
The calculated member expression for Quarter 2 resolves in the same way to AGGREGATE(YTD() , 30,815.00 )
But, this time, the YTD function resolves to a set with two tuples: {[TIME].[All TIME].[1993].[1], [TIME].[All TIME].[1993].[2]}. The AGGREGATE function creates the result of $61,630.00, which is twice the original value, and not necessarily expected.
For the other quarters, the results are similar, a multiple of the original measure, depending on the size of the resulting set returned by the YTD function.
Operating System and Release Information
SAS System | SAS OLAP Server | 64-bit Enabled Solaris | 9.2 TS2M0 | |
64-bit Enabled AIX | 9.2 TS2M0 | |
64-bit Enabled HP-UX | 9.2 TS2M0 | |
Windows Vista for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | |
Microsoft® Windows® for x64 | 9.2 TS2M0 | |
Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | |
Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | |
z/OS | 9.2 TS2M0 | |
Windows Vista | 9.2 TS2M0 | |
Microsoft Windows XP Professional | 9.2 TS2M0 | |
Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | |
HP-UX IPF | 9.2 TS2M0 | |
Linux | 9.2 TS2M0 | |
Linux for x64 | 9.2 TS2M0 | |
OpenVMS on HP Integrity | 9.2 TS2M0 | |
Solaris for x64 | 9.2 TS2M0 | |
*
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.