## Creating a calculated member using dimension members

### Creating a calculated member using dimension members

I want to calculate the cost per enquiry.
I am having real trouble generating a calculated member to accomplish
this.
I realise that "Advert cost" needs to be somehow linked to enquiry and
have tried using "Advert cost" as a member property of "advert code"
and as a virtual dimension and incorporating it into the new calculated
member.
I can't seem to get any functions like sum, min or max to work
against "Advert cost" (even though it is a numeric field).  The only
thing I can get is a count.
Is there a restriction against using these types of functions with
members that are not members of the measures dimension?
Does anyone have any pointers?

Sent via Deja.com
http://www.veryComputer.com/

### Creating a calculated member using dimension members

I'm not sure exactly what you're looking for, but below is an MDX example of
creating a calculated measure that uses a member property to deliver an
aggregated value. The first step is to determine that the member property
can, indeed, be converted into a numeric value. If the member property is
null, for example, you'll end up with #ERR for aggregated values that
include such member properties. If you're sure that your member properties
are all populated, you can remove the second IIF check in the calculated
member below. Since some of the values in the Store SQFT member property are
null, however, it's needed for the example query.

Note, too, that this measure is recursive in nature. If it's on the last
level of the Store dimension, it returns the member property. For all other
levels, it uses itself as the measure for the SUM function.

WITH
MEMBER
[Measures].[Sq Ft]
AS
'IIF(
Store.CURRENTMEMBER.LEVEL.NAME = "Store Name",
IIF(
IsNumeric(Store.CURRENTMEMBER.PROPERTIES("Store SQFT")),
CDbl(Store.CURRENTMEMBER.PROPERTIES("Store SQFT")),
0
),
SUM(
DESCENDANTS(Store.CURRENTMEMBER, [Store Name]),
[Measures].[Sq Ft]
)
)'
SELECT
{[Measures].[Unit Sales],  [Measures].[Sq Ft]} ON COLUMNS,
Store.MEMBERS
ON ROWS
FROM
Sales

Hope this helps!

Dennis Kennedy
Formless Realm Consulting
www.formlessrealm.net

> I want to calculate the cost per enquiry.
> I am having real trouble generating a calculated member to accomplish
> this.
> I realise that "Advert cost" needs to be somehow linked to enquiry and
> have tried using "Advert cost" as a member property of "advert code"
> and as a virtual dimension and incorporating it into the new calculated
> member.
> I can't seem to get any functions like sum, min or max to work
> against "Advert cost" (even though it is a numeric field).  The only
> thing I can get is a count.
> Is there a restriction against using these types of functions with
> members that are not members of the measures dimension?
> Does anyone have any pointers?

> Sent via Deja.com
> http://www.veryComputer.com/

Simon, if you are doing financial app, I recommend creating real dimension
members with Custom Rollup Formula instead of calculated members.
This way you can control ordering by carefully chosing member keys and
asking Analysis Services to sort dimension by key.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky (moshap at microsoft dot com)
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================