Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 190

where the current member of the hierarchy will be used by default if not
specified. The result of the query is as follows:

Sales Amount Percentage Sales

Europe $683,523.76 59.69%

Austria $115,328.31 16.87%

Belgium $30,505.06 4.46%

Denmark $32,428.94 4.74%

Finland $17,530.05 2.56%

··· ··· ···

As can be seen, for example, sales in Austria represent 16.87% of European
sales, while European sales represent 59.69% of the overall sales. The problem
with the above calculated measure is that it works well for all members of
the Geography hierarchy, at any level, except for the All member, since it does
not have a parent. Therefore, we must add a conditional expression in the
definition of the measure as follows:

WITH MEMBER Measures.[Percentage Sales] AS
IIF((Measures.[Sales Amount],
Customer.Geography.CURRENTMEMBER.PARENT)=0, 1,
(Measures.[Sales Amount]) / (Measures.[Sales Amount],
Customer.Geography.CURRENTMEMBER.PARENT)),
FORMAT STRING = ' #0.00% '
SELECT . . .

The IIF function has three parameters: the first one is a Boolean condition,
the second one is the value returned if the condition is true, and the third
one is the value returned if the condition is false. Thus, since the All member
has no parent, the value of the measure sales amount for its parent will be
equal to 0, and in this case a value of 1 will be given for the percentage sales.
The GENERATE function iterates through the members of a set, using a
second set as a template for the resultant set. Suppose we want to display the
sales amount by category for all customers in Belgium and France. To avoid
enumerating in the query all customers for each country, the GENERATE
function can be used as follows:

SELECT Product.Category.MEMBERS ON COLUMNS,
GENERATE( { Customer.Belgium, Customer.France } ,
DESCENDANTS(Customer.Geography.CURRENTMEMBER,
[Company Name])) ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

The result of the query is given next.