Retrieving User-Defined Member Properties using PROPERTIES keyword

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"

Retrieving Last 'N' records from a Table in the Database

Hello House,

Please, I need the SQL statement/keyword used to Retrieve the Last 'N' records from a table in the Database; just as we have TOP 'N' for the First N-records in a table.

For example, ("Select TOP 'N' * From Particulars Order by Phone DESC", conn)

where N is the number of records to be retrieved.

I use both SQL Server 2005 and Microsoft Access.

Thanks.

Quote:

Originally Posted by Temidayo

Hello House,

Please, I need the SQL statement/keyword used to Retrieve the Last 'N' records from a table in the Database; just as we have TOP 'N' for the First N-records in a table.

For example, ("Select TOP 'N' * From Particulars Order by Phone DESC", conn)

where N is the number of records to be retrieved.

I use both SQL Server 2005 and Microsoft Access.

Thanks.

create an identity column on your table...select your table, ORDER BY (that identity column) DESC...the first record will be the last record inserted on your table