Retrieving an Attribute Using MDX

Post Reply
robertk
Posts: 11
Joined: Wed Jul 02, 2008 6:08 am

Retrieving an Attribute Using MDX

Post by robertk »

Hi,

I am trying to retrieve a specific member property (the attribute Description) rather than the Caption property when generating a report using VBA and MDX.

Code:
AccountCode = cst.Axes(1).Positions(0).Members(h).Caption

Example:
AccountCode = 3005

The above returns an account code, however I want the attribute Description returned instead (3005 – Salaries and Wages)

I have tried many variants such as

Code:
AccountCode = cst.Axes(1).Positions(0).Members(h).CurrentMember.Properties (“Description”)

Or

AccountCode = cst.Axes(1).Positions(0).Members(h).Properties (“Description”)

With no luck.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Retrieving an Attribute Using MDX

Post by rmackenzie »

Hi,

It might differ between TM1 versions (as the general MDX interface sometimes does) but I've had the case where dimension attributes and aliases are not automatically populated into the Cellset. To access them via the Member Properties collection I've had to write the query using the DIMENSION PROPERTIES statement. For example:

Code: Select all

SELECT
{[Dim1].[Subset1]} ON ROWS,
{[Dim2].[Subset2]} ON COLUMNS
DIMENSION PROPERTIES
[Dim2].[Attribute1]
FROM [Cube]
Hope that helps,
Robin
Robin Mackenzie
robertk
Posts: 11
Joined: Wed Jul 02, 2008 6:08 am

Re: Retrieving an Attribute Using MDX

Post by robertk »

Hi,

Thanks for your help and quick reply.

I have tried your suggestion (see below) without any luck.

MDX EXPRESSION:

SELECT
{([Version].[Actual], [Debt_Measures].[Credit Limit])} ON COLUMNS,
NON EMPTY {[IntegraT_Facilities].[Total Australian Facilities].Children} ON ROWS
DIMENSION PROPERTIES
[IntegraT_Facilities].[Description]
FROM [IntegraT_DebtBook_Monthly]
WHERE ([IntegraT_Lender].[CBA],[IntegraT_Currency].[Total_Currency],[Fund Group].[TotalGroup],[DebtStatus].[Secured],[Years].[2008],[Months].[Jun])

Without the Dimension Properties section the query returns the debt facility values for each facility agreement for CBA in June 2008.

Example:

[IntegraT_Facility] Dimension
Column 1 Column2
CBA Deal 1 $100
CBA Deal 2 $100

However when I add the DIMENSION PROPERTIES section I get the error message "Run Time Error...MDX Syntax error on or near DIMENSION PROPERTIES...."

I am using Version 9.1 SP2 U2

Thanks
robertk
Posts: 11
Joined: Wed Jul 02, 2008 6:08 am

Re: Retrieving an Attribute Using MDX

Post by robertk »

Just an update.

I made the folllowing change which gets rid of the error but makes no difference to what is being retieved.

SELECT
{([Version].[Actual], [Debt_Measures].[Credit Limit])} ON COLUMNS,
NON EMPTY
{[IntegraT_Facilities].[Total Australian Facilities].Children}

DIMENSION PROPERTIES
[IntegraT_Facilities].[Description]
ON ROWS

FROM [IntegraT_DebtBook_Monthly]
WHERE ([IntegraT_Lender].[CBA],[IntegraT_Currency].[Total_Currency],[Fund Group].[TotalGroup],[DebtStatus].[Secured],[Years].[2008],[Months].[Jun])


Cheers,

Robert
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Retrieving an Attribute Using MDX

Post by rmackenzie »

Hi Robert,

Yes, it is DIMENSION PROPERTIES before ON ROWS.

This works for me in 9.1SP3U2:

Code: Select all

SELECT
{[Measures].[Quantity]} 
ON COLUMNS,
{[Customer].[N LEVEL]}
DIMENSION PROPERTIES
[Customer].[Name]
ON ROWS
FROM [Cube]
and then the following VBA:

Code: Select all

oCellset.Axis(1).Positions(intPosition).Members(0).Properties(0).Value
returns the name attribute of the customer.

Does that work for you ?

Robin
Robin Mackenzie
robertk
Posts: 11
Joined: Wed Jul 02, 2008 6:08 am

Re: Retrieving an Attribute Using MDX

Post by robertk »

Hi,

Thanks a lot. That works perfectly.

Cheers,

Robert
Post Reply