Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > MDX: How to filter dimension by member attribute
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 6th August 04:21
martinbx
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Hi,
I am just started to work with MDX. I have to dimesion 'Funds' . More
then one 'fund' from "Funds' dimension could have the same 'SubAsset Class'
property. I need to make a set with members from 'Funds' which have the same
'SubAsset Class' property. How should I do it ? I would appreciate any help.
  Reply With Quote


  sponsored links


2 6th August 04:21
milind
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Try this

SELECT {Some Measure} ON COLUMNS,
Filter({ Descendants ([Fund].[All Fund], [Fund].[All Fund].Level,
AFTER)},
([Fund].CurrentMember.Properties("SubAsset Class") = "25")) ON ROWS
FROM FundCube

You can define this in a calculated member if you want

Hope this help

Milind
  Reply With Quote
3 6th August 04:21
martinbx
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Thank you Milind, but my problem is that I do not know value of :SubAsset
Class" in advance, it dependes on CurrentMember property.
  Reply With Quote
4 6th August 05:08
jjfja
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


do you mean that you don't know the "25" value?

"it dependes on CurrentMember property." the currentmember of which
dimension?

do you want something like:
SELECT {Some Measure} ON COLUMNS,
Filter({ Descendants ([Fund].[All Fund], [Fund].[All Fund].Level,AFTER)},
([Fund].CurrentMember.Properties("SubAsset Class") = <filtering
dimension>.currentmember.property("SubAsset Class Target"))) ON ROWS
FROM FundCube

and the <filtering dimension> contains the list of possibile values to be
used has the target of the filter.
  Reply With Quote
5 6th August 05:09
martinbx
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Hi,
I am trying to do something like this

set [CurrentFund] as
'{[Fund].currentMember}'

member [Measures].[CurrentFund ID] as
' CLng([CurrentFund].Item(0).Item(0).Properties("SubAsset Class"))'

member [Measures].[FundSiblings]
' CStr
(
Filter
(
Descendants ([Fund].[All Fund], [Fund].[All Fund].Level,AFTER),
CLng([Fund].CurrentMember.Properties("SubAsset Class")=
[Measures].[CurrentFund ID]
).Count
)

but I made an error somewhere, sinceMDX can not find properties in my set,
but does not have any problem when it dealing with 'CurrentMember' not packed
in the set.
So I am looking for way to get sutset of [Fund] which has only 'funds' with
exactly the same 'subasset class' as 'CurrentMember'
  Reply With Quote
6 6th August 05:51
brian altmann
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Using the Foodmart dimension Store instead of Funds, and the property "Store
Manager" instead of "SubAsset Class"), is this what you mean? (see Johnson in
query results):

with
member measures.Manager as '[store].currentmember.properties("Store Manager")'
member measures.TotalManager as 'sum( generate ( {[store].currentmember}
as curstor , filter( [store name].members ,
curstor.current.properties("Store Manager") =
[Store].currentmember.properties("Store Manager"))) ,[unit sales] )'
select
{Manager, [unit sales] ,[TotalManager] } on columns,
[Store Name].members on rows
from sales

HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (http://www.huddle.com.ar)
http://www.geocities.com/bri****tmann/olap.html
  Reply With Quote
7 6th August 06:37
martinbx
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Thanks for advice Brian. Unfortunatly somebody rase "foodmart2000" database


'Count
(
Generate(
{[Portfolio Hierarchies].CurrentMember} as CurrMember,
Filter(
[Portfolio Hierarchies].Members,
[Portfolio Hierarchies].CurrentMember.Properties("Portfolio Id")=
CurrMember.Current.Properties("Portfolio Id")
)
)
)'

I got back an error: "Formula error - property name is not valid: "Portfolio
Id" - an error occuerred during attempt to get a cell value", but if I try
directly to output
[Portfolio Hierarchies].CurrentMember.Properties("Portfolio Id") , I get
back valid answer.
I would appreciate any suggestion on what is going on and how to
fix it.
"Brian Altmann" wrote:
  Reply With Quote
8 6th August 06:37
brian altmann
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


If your dimension name is [Portfolio Hierarchies] then you should replace it
by the Leaf level name as shown here:

A member property is only valid for the level at which it is defined, so you
have use [Dimension Level].members. If you [Dimension].members you get the
error you have reported.
HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (http://www.huddle.com.ar)
http://www.geocities.com/bri****tmann/olap.html
  Reply With Quote
9 6th August 06:38
martinbx
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


Thank you very much, it helped.
  Reply With Quote
10 7th August 06:06
newbie
External User
 
Posts: 1
Default MDX: How to filter dimension by member attribute


How would you do this with a calculated member?

I have the same scenario where I need all the descendants of the
ancestor of the current member that have the same member propery value
as that of the current member.

But I need to make it a calculated member so it shows in the cube.

Thanks...

*** Sent via Developersdex http://www.developersdex.com ***
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666