On the ROWS axis, show the Actual Sales and percent change from last year’s sales for the product lines for which actual is less than 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 America.
The MDX statement that retrieves this dataset is
WITH MEMBER
Measures.[% Change from Last Year Sales] AS Sales /
(Sales, Year.PREVMEMBER)
SELECT GENERATE(
GENERATE({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 called % 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). Within the context of the rest of the expression, this new member is evaluated for each value in the n-dimensional manifold selected by the dataset to yield the required result.