Calculated Dimension Member Problem

Hi,

I have a problem that I think I can solve by creating a calculated member in my time dimension, but I am struggling to get it to work.

My time dimension has a hierarchy of Year, Month, Week. The years go from 2002 to 2007, with an additional 'Pre 2002' year (having one month and one week). What I need to do is create an additional year which is the combination of 2002 and 'Pre 2002', which will then be called 'Pre 2003'. I have tried the following:

Code Snippet

CREATE MEMBER CURRENTCUBE.[Time].[Time].[All].[Pre 2003]

AS [Time].[Time].[Year].&[2002] + [Time].[Time].[Year].&[2001], -- (2001 is my name for the pre 2002 member)

VISIBLE = 1 ;

This builds ok, but when I browse the dimension, or use it in Excel 2003, the calculated member is nowhere to be seen.

Any ideas?

Dave

Your formula should be OK. An equivalent calc member for Adventure Works is the following.

create member currentcube.[Date].[Fiscal].[All Periods].[Pre 2004] as

[Date].[Fiscal].[Fiscal Year].&[2002] + [Date].[Fiscal].[Fiscal Year].&[2003];

In order to see your new calculated member you need to make sure you have deployed the changed calculation script to the server. When your said "this builds ok", it gave me an idea that you work in project mode (there is also possibility to work explicitly connected to the server). In the project mode the change you made is related only to the local files on your drive. When you use Build menu item it means that a validation has happened and a deployment script has been created but it does not mean the server already knows your changes. You need to use Deploy menu item in order Visual Studio to send your changes to the server. After that you should see your calculated member in the *new* browser window. If you already had it open you need to select Reconnect button for the change to be visible in the browser window.

In the server online mode (when you work directly with the server) you would just hit Save button and reconnect the browser.

Also, make sure you browse your Time hierarchy of Time dimension. A dimension can have more than one hierarchy. Similarly in Adventure Works i see the member above only when i browse Fiscal hierarchy of the Date dimension but Calendar hierarchy does not have the member.

|||

Andrew,

I am definitely deploying the changes to the server, as any other calculated measures appear correctly.

I have tried using the Adventure Work example you gave, and I have the same problem. In the dimension browser the new period is nowhere to be seen.

|||

Ok,

I can now see the calculated member. I was looking in the dimension browser instead of the cube browser!! Now I have another problem. I am connecting to the cube from Excel 2003, and when I drag in the hierarchy I cannot see the new member. In Excel 2007 there is a pivot table property "show calculated members from OLAP server" When I set this I can see the member fine. However, I need to use Excel 2003 for this project. I have found a pivot table property "ViewCalculatedMembers" which I have set to true using VBA. However, the member still does not appear.

Any ideas?