I want to use the SUM shown below but do not want to hard code the dimension
name. Is there a function I can use that will generically replace
<<Dimension>> with the current dimension name ?:

SUM(Descendants(<<Dimension>>.CurrentMember,,Leave s),[Measures].[AssetUnderManagement])

Depends on what you mean by "current dimension". A common technique,
when referring to dimensions of MDX query axes, is to use something
similar to "Axis(1).Item(0).Item(0).Dimension" (may need to use StrToSet
along with this).


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi, I tried your suggestion but the syntax was not accepted, probably because
I put your suggestion in the wrong place:

My situation is that I have a dimension called Branch_to_Client_Drilldn,
which has 4 members. One of the members has a property called Net Worth. I
have created a calculated member called AssetsUnderManagement, which uses the
MDX shown below. Unfortunately, when I view the cube using dimensions where
the AssetsUnderManagement member does not seem appropriate the calculation
gives spurious results. To get round this I thought I could change the
dimension Branch_to_Client_Drilldn after Descendants from hard coding to
something more generic:

IIF ( Isleaf(Branch_to_Client_Drilldn.CurrentMember),
StrToValue(Branch_to_Client_Drilldn.CurrentMember. Properties("Net Worth")),
SUM(Descendants(Branch_to_Client_Drilldn.CurrentMe mber,,Leaves),[Measures].[AssetUnderManagement]))


"Deepak Puri" wrote:

Is the [AssetsUnderManagement] calculated member a measure?

Also, can you give examples of the data you are dealing with - it's not
clear why only 1 of 4 members has the "NetWorth" property. What is the
dimension hierarchy?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes, the [AssetsUnderMeasurement] calculated member is a measure.

The Dimension hierarchy is:

Branch_Name
The FACT table is Trades containing trades transacted for our clients. The
DIMENSIONS are:

Client - which joins to Trade on a one-to-many (trades) basis
Branch_Exec - containing Branches and Investment Advisors (Exec_Name)
Product - List of Products traded
Country - Country of Residence of Client
Time - Year, Quarter, Month, Day and Date of Trade

A sample of the data could be:

1st column is TradeRef
2nd column is Client FullName
3rd column is Branch_Name
4th column is EXEC_Name
5th column is Revenue
6th column is NetWorth (AssetsUnderManagement)


Col1 Col2 Col3 Col4 Col5 Col6
----- ---------------- ----------------- ----------------- -------- --------
1 John Smith London Bob White 100 100000
2 John Smith London Bob White 200 100000
3 Mr Chin Hong Kong Mr Yin 500 400000
4 Mr Chin Hong Kong Mr Yin 700 400000
5 Bill Brown London David Green 800 200000

In the case of records 1 & 2 above I want to avoid the AssetsUnderManagement
measure displaying as 200000 rather than 100000. It seems to work okay for
the dimension with 4 members shown above but when I use other dimensions it
looks like the AssetsUnderManagement measure displays a sum of the NetWorth
for all clients for everything.

Only one member has the NetWorth property because I thought it was more
appropriate to attach to FullName (of client). Apologies if this is not
correct but I am new to Analysis Services/MDX!!





"Deepak Puri" wrote:

Based on the fact table columns, it appears that the dimension is either
built from the fact table, or joins to the fact table on multiple
columns, neither of which is ideal. What does the dimension table in
question look like - maybe I'm missing something?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The fact table is:

Trades
====
[DetailRecord01Id] [int] NULL ,
[trade_id] [nchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[time_id] [int] NULL ,
[accountnumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch_Exec_Id] [int] NULL ,
[ProductCode] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Revenue] [float] NULL ,
[PershingCharge] [real] NULL ,
[RecordCount] [int] NULL

The Dimension table holding the NetWorth is:
Client:
====
[accountnumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (112) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryOfResidency] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetWorth] [float] NULL ,
[NetworthBand] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageband] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ONSHORE_OFFSHORE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Ray

"Deepak Puri" wrote:

The dimension table doesn't have Branch_ or Exec_Name, but these
appeared in the dimension hierarchy that you had described. Is this a
snowflake dimension, else where are those levels of the dimension
hierarchy defined? Summing the [NetWorth] on just a Client table-based
hierarchy should be more straightforward.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!