NAMED SET/ CALCULATED FIELDS

Hi,

Can I use the following MDX in named set/ calculated fields:

with member [MEASURES].[thisistheratio] as

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%"

Select {[Measures].[Activity Count],

[MEASURES].[thisistheratio]} on columns,

{[Direction].[Direction].mEMBERS} on rows

FROM [CASEACTIVITYDETAIL]

how do I use it? Is there a particular syntax if you apply it inside named sets/calculated fields?

thanks a lot!

cherriesh wrote:

Hi,

Can I use the following MDX in named set/ calculated fields:

with member [MEASURES].[thisistheratio] as

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%"

Select {[Measures].[Activity Count],

[MEASURES].[thisistheratio]} on columns,

{[Direction].[Direction].mEMBERS} on rows

FROM [CASEACTIVITYDETAIL]

how do I use it? Is there a particular syntax if you apply it inside named sets/calculated fields?

thanks a lot!

What you have there is an MDX query, you cannot put the whole thing inside a calculated member/set. I am guessing that what you probably want to do is to create a calculated measure for this ratio. If this is the case you could do either of the following.

If you open up your cube and click on the calculations tab, about the 4th button along the top will be an "New Calculated Member" button. When you click on this it brings up a form to let you add a new calculated member and you would fill out the following properties:

Name: [thisistheratio]

Parent Hierarchy: MEASURES

Expression:

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count])

Format String: "0%"

Alternatively you could switch to the script view and just paste in:

CREATE MEMBER CURRENTCUBE.Measure.[thisistheratio]

AS

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%";