Search code examples
ssasmdxcubemeasuremultidimensional-cube

Get All Linked Members using LinkMember function

Please consider this structure:

Fact:

Sender           Receiver         Amount
----------------------------------------
Reseller1        Resseler2        100 
Reseller1        Resseler3        200
Reseller3        Resseler1        150
Reseller3        Resseler2        50
Reseller1        Resseler4        100
Reseller4        Resseler2        350
Reseller4        Resseler1        500
Reseller4        Resseler3        250

DimReseller:

ResellerCode            ResellerName
---------------------------------------
1                        Reseller1
2                        Reseller2
3                        Reseller3
4                        Reseller4

DimReseller is role-playing dimension and has 2 relation with fact table:

enter image description here

enter image description here

Now I wrote this MDX query to select How much money the resellers have paid each other:

WITH MEMBER SenderAmount AS
[Measures].[Amount], FORMAT_STRING = "Standard"


SELECT    {SenderAmount} ON 0,
NON EMPTY [Sender].[Hierarchy].MEMBERS ON 1
FROM [MyCube]

and I get this result:

enter image description here

Now I want to get how much money the resellers have received from each other and I wrote this query:

WITH 
MEMBER SenderAmount AS
[Measures].[Amount], FORMAT_STRING = "Standard"

MEMBER ReceiverAmount AS
  (LinkMember   
     ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
     ,[Measures].[Amount]), FORMAT_STRING = "Standard"


SELECT    {SenderAmount, ReceiverAmount} ON 0,
NON EMPTY [Sender].[Hierarchy].MEMBERS ON 1
FROM [MyCube]

and get this result:

enter image description here

I got null for receive amount. I test this MDX query:

select {LinkMember([Sender].[Hierarchy].[Reseller Code].&[1], [Receiver].[Hierarchy])} on 0
from [MyCube]

and I get 650 (the correct amount) for Reseller1 .Now when I change the query this way:

select {LinkMember([Sender].[Hierarchy].currentmember, [Receiver].[Hierarchy])} on 0,
[sender].[Hierarchy].members on 1
from [MyCube]

I get this result(Again for paid):

enter image description here

and when I change the query this way:

select {LinkMember([Sender].[Hierarchy].currentmember, [Receiver].[Hierarchy])} on 0,
[receiver].[Hierarchy].members on 1
from [MyCube]

I got this error:

The Hierarchy hierarchy already appears in the Axis0 axis.

How can I get this result?

Reseller              Received                   Paind                     
--------------------------------------------------------
Reseller1             650                        400
Reseller2             500                        (null)
Reseller3             450                        200
Reseller4             100                        1100

Sorry for too much explanation...

Thanks

Solution

  • Try the following change:

    MEMBER ReceiverAmount AS
  (LinkMember   
     ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
     ,[Measures].[Amount]
     ,[Sender].[Hierarchy].[All]), FORMAT_STRING = "Standard"