Hello,

I have a seemingly simple MDX statement that VISUALTOTALS fails on.

I want to calculate a 'Last Year' measure using PARALLELPERIOD.
I can create the measure OK and display it ON COLUMNS okay too.
The problem comes if I wrap VISUALTOTALS around it to form the "all" member
- it always give me a null for last year + all.
Sample MDX is shown below.
In the result set the column headed "All" + "CCMS Last Year" is always NULL
despite the fact that the individual last year members have values.
The column headed "All" + "CCMS This Year" has the correct value in it!

---------------------------------------
WITH
MEMBER [measures].[CCMS This Year] AS ([Measures].[Column Cms]),
NON_EMPTY_BEHAVIOR=[Measures].[Column Cms]
MEMBER [measures].[CCMS Last Year] AS ([measures].[Column Cms],
PARALLELPERIOD([Date].[Year_Quarter_Month_Week_Hierarchy].[Year],1,[Date].[Year_Quarter_Month_Week_Hierarchy].currentmember))
, FORMAT="#,0.00", NON_EMPTY_BEHAVIOR=[Measures].[Column Cms]

SELECT CROSSJOIN (
VISUALTOTALS({[Date].[Year_Quarter_Month_Week_Hierarchy].[All],
[Date].[Year_Quarter_Month_Week_Hierarchy].[Month].&[2006-01-01T00:00:00]:[Date].[Year_Quarter_Month_Week_Hierarchy].[Month].&[2006-02-01T00:00:00]}),
{[measures].[CCMS This Year], [measures].[CCMS Last Year]}
) ON COLUMNS
,NON EMPTY [Publication_Filter].[Filter Group].members ON ROWS
FROM b2b_cube
WHERE (<various slicer clauses>)

----------------------
Please help if you can
Thanks in advance
Mgale1

There's a couple of issues, from what I can tell:

- The column headed "All" + "CCMS Last Year" may return NULL even
without VisualTotals(), because
PARALLELPERIOD([Date].[Year_Quarter_Month_Week_Hierarchy].[Year],
1,[Date].[Year_Quarter_Month_Week_Hierarchy].[All])
will return a null member.

- With VisualTotals() applied to the [Date] dimension, it intersects the
[CCMS Last Year] calculation. While it sounds like you may want
VisualTotals() to apply to the results of [CCMS Last Year], thereby
summing [CCMS Last Year] over the selected months, calculated measures
can't be aggregated.

An approach which could work is to create [CCMS Last Year]as a cube
measure, by translating the [Column Cms] fact table field back 1 year in
time in a DSV Named Query. But this would be a somewhat static and
inflexible solution.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***