I am using an example from ‘SQL Server 2005 Books Online’, which explain how to retrieve User-Defined Member Properties.
Using the PROPERTIES Keyword to Retrieve User-Defined Member Properties:
DIMENSION PROPERTIES [Dimension.]Level.<Custom_Member_Property>
The PROPERTIES keyword appears after the set expression of the axis specification. For example, the following MDX query the PROPERTIES keyword retrieves the List Price and Dealer Price user-defined member properties and appears after the set expression that identifies the products sold in January:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS
DIMENSION PROPERTIES
Product.Product.[List Price],
Product.Product.[Dealer Price]ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
After running the above MDX query, I don’t see any [List Price] or [Dealer Price] and the result is exactly like running the following MDX query:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
How can I retrieve User-Defined Member Properties?
Thanks,
Yones
I found the problem, which is related to the way data is returned after execution of an MDX query. It is returned differently in Analysis Services 2000 and 2005. For example using an XMLReader, elements names are returned as follow:
AS 2000:
clXmlReader.Name:"List Price"
clXmlReader.value:"List Price value"
clXmlReader.Name:"Dealer Price"
clXmlReader.value:"Dealer Price value"
AS 2005:
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_Product_x005D_._x005B_Product_x005D_._x005B_ List Price _x005D_"
clXmlReader.value:"List Price value"
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Dealer Price _x005D_"
clXmlReader.value:"Dealer Price value"
No comments:
Post a Comment