Thursday, November 01, 2007

YTD and Time Dimension

I kept getting the following error when trying to do YTD calculations using MDX on a client cube:

VALUE #Error Query By default, a year level was expected. No such level was found in the cube.
FORMATTED_VALUE #Error By default, a year level was expected. No such level was found in the cube.

Here is the MDX to do a simple YTD:
SUM(YTD([Time].currentmember), [Measures].[Sales])

The MDX is good and works in every other cube including Adventure Works so I had to look deeper. I started changing keys thinking that the client did not set those up properly. I should have looked at the error message more closely because it alluded to what the problem was. It says, "No such level was found in the cube". Well, I know it's there because I can see it in the dimension structure. However, looking at the dimension properties, the Year attribute was set to Regular as the Type. Changing the Type to "Years" solved the issue. So that makes sense, the MDX parser was looking for a Year level but could not find it.

Sometimes just figuring out what the error says helps a lot :)

No comments: