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%";

Named Calculations for measures with formula

Hi,

The details of background are avaliable in this prev post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=550862&SiteID=1

After edward melomed's advice I tried to create a named calculation for the respective Fact table.

The problem is, let us say I have a two measures M1 and M2 in a cube. Now I want a named calculation say 'NM3' , that contains the formula M1 + M2. So I write the following SQL in the named calculation:

(select M1 + M2 from fact table)

although the query is right, while processing I get the error that the query returns multiple values. So now how do I specify the current row? or how do I specify something similar to a cursor?

I know that I can achieve this using calculated measures instead of named calculations, but then some formulae contain functions like decode, sum of metrics over a paticular dimension, average of metrics over a paticular dimension etc.

also, this entire process is automated using AMO. The details of the metrics and their formulae are got from an existing oracle data warehouse. (see the prev post for more details)

so how can this be achieved generally? and considering that it has to be automated using AMO?

Thanks and Regards

Hello. Why do you write (select M1 + M2 from fact table) in your named calculation?

I have tried m1 + m2 in the named calculation by using SalesAmount + TaxAmt from Adventure works.

You do not need select in the named calculation.

The cube process and the new measure, from the named calculation, seems to be correct in the browser.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thank you!

That was dumb of me. Actually I saw the microsoft help, where the example contained a select statement.

1. So, does the actual calculation take place at the database server (backend source database)?

2. and does that also mean that I can use functions like decode?

3. can I use AMO to do this programmatically?

Finally, what is the difference between Named Calculations, Named Sets and Named queries. (do we have anything else thats named.....?) apart from named calculation are created from DSV, named sets in the calculation tab.

Regards

|||

1. It is done in the DSV so I suspect that it is done in the cube only.

2. Decode? Is that PLSQL(Oracle)? If so then TSQL CASE is the same.

3. I do not know.

4. Named queries(see them as views) and named calculations(columns) are DSV-features. Named Sets are MDX and Cube related features. With them you build subsets of dimension members.

Regards

Thomas Ivarsson