calculated measure

I have a cube with a project dimension:

User Hierarchy

Project.ProjectHierarchy

Project.GrowthPlatform

Project.Stream

Project.ProjectName

Attribute

Project.GrowthPlatform

Project.Stream

Project.ProjectName

I would create a calculated measure that is

meas1 * (meas2 / meas3) only for th ProjectName level (the lowest)

Sum(children) for the other level

How can I test the level of a dimension ?

Cosimo

IIF(Project.ProjectHierarchy.CurrentMember.Level IS Project.ProjectHierarchy.ProjectName, ...)|||

Hi,

I would try to use the SCOPE function instead of the IIF. Sometning like this should work:

SCOPE([Project].[ProjectHierarchy].[ProjectName].members,[Project].[ProjectName].members);

-- Insert calculated measure here

END SCOPE;

HTH,

Eric

|||

Thanks for the suggestion.

I'm quite new of SSAS 2005 and had some experience in AS 2000.

In AS 2000 you can do [dimension].CurrentMember ... but in SSAS 2005 every attribute is a hierarchy so I need to do [dimension].[hierarchy].CurrentMember. But I need a solution for all the hierarchyes !

So I partially solved the problem for [PROJECT].[STREAM] using the he following formula:

IIF ([PROJECT].[STREAM].CURRENTMEMBER.LEVEL_NUMBER = 0

, SUM ( [PROJECT].[STREAM].CURRENTMEMBER.CHILDREN, [Measures].[NUM HEADCOUNT SUBCO] * [Measures].[NUM FTE NO SUBCO] / [Measures].[NUM HEADCOUNT NO SUBCO] )

,[Measures].[NUM HEADCOUNT SUBCO] * [Measures].[NUM FTE NO SUBCO] / [Measures].[NUM HEADCOUNT NO SUBCO]

)

But this does not function the other hierarchy.

There is a way that can function for all the hierarchy ...

What I really need is ussing the ratio only for the lowest level [PROJECT].[PROJECT NAME] and using the sum of the children for all the others.

Cosimo

|||

I solved the problem defining four different calcolated measures, even if it could be interesting having just one calculated measure that functions for all the hierarchies.

Cosimo