Defining a New Member Example

OLE DB Programmer's Reference

On the ROWS axis, show the Actual Sales and percent change from last year's sales for the product lines for which (Sales,Actual) is less than (Sales,Budget). This should be sorted by Sales Last Year. On the COLUMNS axis, show the top two cities in the top two countries in the regions Europe and North America.

The dataset defined above is retrieved by the following MDX statement:

WITH MEMBER    Measures.[% Change from Last Year Sales] AS Sales /       (Sales, Year.PREVMEMBER) SELECT GENERATE(    GENERATE({North America, Europe},             TOPCOUNT(Geography.CURRENTMEMBER.CHILDREN, 2, Sales)),    TOPCOUNT(Geography.CURRENTMEMBER.CHILDREN, 2, Sales)) ON COLUMNS,    CROSSJOIN({Sales, [% Change from Last Year Sales]},              ORDER(FILTER([Product Lines].MEMBERS,              (Sales,Actual)<(Sales,Budget)),              (Sales, Year.PREVMEMBER))) ON ROWS FROM SalesCube WHERE ([1997], Actual)

The WITH MEMBER clause defines a new member named "% Change from Last Year Sales." This appears on the Measures dimension (as indicated by the qualifier [Measures]). This member is specified by the formula Sales/(Sales, Year.PREVMEMBER). To yield the required result, this new member is evaluated, within the context of the rest of the expression, for each value in the n-dimensional manifold selected by the dataset.

1998-2001 Microsoft Corporation. All rights reserved.



Microsoft Ole Db 2.0 Programmer's Reference and Data Access SDK
Microsoft OLE DB 2.0 Programmers Reference and Data Access SDK (Microsoft Professional Editions)
ISBN: 0735605904
EAN: 2147483647
Year: 1998
Pages: 1083

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net