how to add a dimension attribute to the results of mdx query

Question

The query below returns a grid of rows of accounts name, columns of months and amounts at the intersection.  We need to also return AccountType which is an attribute of the Account dimension.  Any suggestions?

with
set [rows] as descendants([Account].[Accounts].&[\NET INCOME], [Account].[Accounts].[Account Level 03],self_and_before)

set [columns] as {[DateCalendar].[Calendar].[Month Name].&[2011]&[1]:[DateCalendar].[Calendar].[Month Name].&[2011]&[12]}
select  [columns] on 0,  [rows] on 1  
from [MyCube] 
where  [Measures].[Amount] * {[Division].[Divisions].[Fred]} 

Answer 1

hello,

try the following:

WITH 
  MEMBER [Measures].[Account Type] AS 
    [Account].[Accounts].Properties("AccountType") 
  SET [rows] AS 
    Descendants
    (
      [Account].[Accounts].&[\NET INCOME]
     ,[Account].[Accounts].[Account Level 03]
     ,self_and_before
    ) 
  SET [columns] AS 
    (
      {
          [DateCalendar].[Calendar].[Month Name].&[2011]&[1]
        : 
          [DateCalendar].[Calendar].[Month Name].&[2011]&[12]
      }
     ,[Measures].[Account Type]
     ,[Measures].[Amount]
    ) 
SELECT 
  [columns] ON 0
 ,[rows] ON 1
FROM [MyCube]
WHERE 
  {[Division].[Divisions].[Fred]};

Philip,

Answer 2

thank you, 

but unfortunately I get an error "The Measures hierarchy is used more than once in the Crossjoin function.." on the SET [COLUMNS] line

Any other ideas?

thank you,  but unfortunately I get an error "The Measures hierarchy is used more than once in the Crossjoin function.
." on the SET [COLUMNS] line

Answer 3

Hi Richard,

try using the PROPERTIES keyword to get the attribute you need.

Something along the lines of

set [rows] as

 descendants([Account].[Accounts].&[\NET INCOME], [Account].[Accounts].[Account Level 03],self_and_before)

 DIMENSION PROPERTIES [Account].[Accounts].[AccountType]

Regards,

Mario

Answer 4

hi,

oops !

WITH 
  MEMBER [Measures].[Account Type] AS 
    [Account].[Accounts].Properties("AccountType") 
  SET [rows] AS 
    Descendants
    (
      [Account].[Accounts].&[\NET INCOME]
     ,[Account].[Accounts].[Account Level 03]
     ,self_and_before
    ) 
  SET [columns] AS 
    (
      {
          [DateCalendar].[Calendar].[Month Name].&[2011]&[1]
        : 
          [DateCalendar].[Calendar].[Month Name].&[2011]&[12]
      }
     ,{
        [Measures].[Account Type]
       ,[Measures].[Amount]
      }
    ) 
SELECT 
  [columns] ON 0
 ,[rows] ON 1
FROM [MyCube]
WHERE 
  {[Division].[Divisions].[Fred]};

Philip,

Answer 5

Philip,

That works!  Thank you very much.

I don't know if this done but the result is 2 columns for every period, an AccountType and an Amount for every column,  which means that the same AccountType value is repeated for every column in each row.

Any more ideas?

Answer 6

Mario,

Thank you for your help.  Something must be missing (,:{}....) as it underlined in red as soon as I enter the DIMENSION text.   I tried a couple of changes but I am fairly new to MDX and I wasn't successful with any.

Any suggestions?

Answer 7

Hi,

try the following:

WITH 
  SET [rows] AS 
    (
      Descendants
      (
        [Account].[Accounts].&[\NET INCOME]
       ,[Account].[Accounts].[Account Level 03]
       ,self_and_before
      )
     ,[Account].[AccountType].[AccountType]
    ) 
  SET [columns] AS 
    (
      {
          [DateCalendar].[Calendar].[Month Name].&[2011]&[1]
        : 
          [DateCalendar].[Calendar].[Month Name].&[2011]&[12]
      }
     ,[Measures].[Amount]
    ) 
SELECT 
  [columns] ON 0
 ,[rows] ON 1
FROM [MyCube]
WHERE 
  {[Division].[Divisions].[Fred]};

Depending on how the [Account].[AccountType].[AccountType] attribute is defined on your dimension, you might replace it by the correct reference.

Philip,

Answer 8

mdx_teghn,

thanks for the input, but it returns 0 rows.

Answer 9

Hi,

verify the reference of the following attribute :[AccountType]

maybe it is somehting like;

[Account].[Accounts].[AccountType]

if it still does not work:

Go to a mdx editor and drag the required hierarchy of [AccountType] to obtain the correct reference

In BIDS go to (account dimension/AcountType attribute) and verify that the "AttributeHierarchyEnabled" property of the AcountType attribute is set to True.  and try to tune other properties pertaining the hierachy like "AttributeHierarchyVisible" :

Philip,

Answer 10

mdx_teghn,

I checked those settings and I don't think that I changed anything (maybe I had an original code error) but the code now returns the wrong amounts.   I am checking on what the amounts could be(and will get back with the exciting answer).

Any other ideas?