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.
Retrieving an Attribute Using MDX
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Retrieving an Attribute Using MDX
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
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]
Robin
Robin Mackenzie
Re: Retrieving an Attribute Using MDX
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
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
Re: Retrieving an Attribute Using MDX
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
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Retrieving an Attribute Using MDX
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
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]
Code: Select all
oCellset.Axis(1).Positions(intPosition).Members(0).Properties(0).Value
Does that work for you ?
Robin
Robin Mackenzie
Re: Retrieving an Attribute Using MDX
Hi,
Thanks a lot. That works perfectly.
Cheers,
Robert
Thanks a lot. That works perfectly.
Cheers,
Robert