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
Quote:> I have a cube that tracks enquiries made against adverti*ts.
> I want to calculate the cost per enquiry.
> I am having real trouble generating a calculated member to accomplish
> this.
> I have a dimension - Advertisment, which has and "advert type"
> an "advert slogan" and an "advert code" level.
> 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/
by