Counting siblings

I have an "instrument" dimension [aka RIC], and each instrument has an "underlying" attribute (aka RIC 1). I'd like to list all instruments, and for each instrument, show the count of other instruments with the same underlying.

this is what I've got so far - sad to say it's not working - what should I be doing here?

with member [measures].[x] as count(filter([wm instrument].[RIC].[RIC],[wm instrument].[RIC 1].CurrentMember)

select

[measures].[x] on 0,

[wm Instrument].[RIC].[RIC].Members on 1

from [itdev1 hk]

Here's an example from Adventure Works which shows how to count the number of Products in the same Category as the current Product:

with member measures.prodsinsamecat as

count(

exists([Product].[Product].[Product].members,

exists([Product].[Category].[Category].members, [Product].[Product].currentmember)

)

)

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

What it does is first of all finds the member(s) on the Category attribute that exist with the currentmember on the Product attribute, then finds and counts the members on the Product attribute that exist with these Categories.

HTH,

Chris

|||

And improving a little over Chris's suggestion, given the fact that there is attribute relationship defined between Product and Category, a simpler and more efficient way to do it would be

with member measures.prodsinsamecat as

count(exists([Product].[Product].[Product].members, [Product].[Category].currentmember))

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

|||

thanks, that helps. any idea how i could order the output by measures.productsinsame cat? I tried this, no joy:

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members*[WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

|||

also, I actually want to show JUST the "products", rather than the crossjoin of products and categories.

however, if I take the category out the crossjoin operation, I get a list of all my "products", with the same number of prodsinsamecat

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

interestingly, if I take out the product out of the crossjoin operation, I get a list of all my "categories", with the correct number of prodsinsamecat along side each one

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

any insights gratefully received.