Calculated member wrong behaviour in Reporting Services

Hello,

I've created a SQL Server2005 AS Cube with a YTD calculated member:

Sum (YTD(),[Measures].[measure X])

This member (and others give the right results in the cube browser as well in an Excel pivot table). In a parameterized (year-month) report in Reporting services this does not work. It gives only the data of the given moth instead of the sum of all the months in the given year.

Can somebody help me with this issue?

Thanks!

Pieter

Hi Pieter,

What is the MDX query used in the report and what does your time dimension look like - I'm assuming that this problem occurs even with a fixed (no parameters) query?

|||

Thanx for your reply,

the YTD measures work correctly now both in the AS browser as in Reporting services, I redesigned the time dimension in AS. The only member whis does'nt work correctly in RS is a year total. this total works correct in the AS browser and also in RS without a parameter on time (month). It has to return the yeartotal regardless on what time is given in the parameter

The member looks as follows:

Measure Y:

sum(ancestors([Time dimension].[Hierarchy Year-Halfyear-Quarter-Month-Day].currentmember,[Time dimension].[Hierarchy Year-Halfyear-Quarter-Month-Day].[Year]),[Measures].[Measure X])

This is the MDX quert which is generated by RS:

SELECT NON EMPTY { [Measures].[Measure Y], [Measures].[Measure X] } ON COLUMNS FROM ( SELECT ( STRTOSET(@.Timedimensionhierarchyyearmonth, CONSTRAINED) ) ON COLUMNS FROM [ Cube]) WHERE ( IIF( STRTOSET(@.Timedimensionhierarchyyearmonth, CONSTRAINED).Count = 1, STRTOSET(@.Timedimensionhierarchyyearmonth, CONSTRAINED), [Time dimension].[Hierarchy year-month].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS