TM1 Forum

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.

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:
Hope that helps,
Robin

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

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

Hi Robert,

Yes, it is DIMENSION PROPERTIES before ON ROWS.

This works for me in 9.1SP3U2: and then the following VBA: returns the name attribute of the customer.

Does that work for you ?

Robin

Hi,

Thanks a lot. That works perfectly.

Cheers,

Robert