Database Reference
In-Depth Information
Supplier.Geography.Supplier.MEMBERS, ST Distance(
Customer.Geography.CURRENTMEMBER.Properties( ' CustomerGeo ' ),
Supplier.Geography.CURRENTMEMBER.Properties( ' SupplierGeo ' ))
< 200 AND Measures.SalesAmount > 0))ONROWS
FROM Sales
This query uses the GENERATE and the FILTER functions to obtain for
each customer the suppliers that are at less than 200km from the customer
and such that they are related through at least one order, that is, their
SalesAmount measure is greater than 0.
Query 11.7. Distance between the customer and supplier for customers
that have orders delivered by suppliers of the same country.
WITH MEMBER Measures.CustomerCountry AS
Customer.Geography.CURRENTMEMBER.PARENT.
PARENT.PARENT.NAME
MEMBER Measures.SupplierCountry AS
Supplier.Geography.CURRENTMEMBER.PARENT.
PARENT.PARENT.NAME
MEMBER Measures.Distance AS
ST Distance(Customer.Geography.CURRENTMEMBER.
Properties( ' CustomerGeo ' ), Supplier.Geography.
CURRENTMEMBER.Properties( ' SupplierGeo ' ))
SELECT { Measures.Distance } ON COLUMNS,
GENERATE( Customer.Geography.Customer.MEMBERS,
FILTER( Customer.Geography.CURRENTMEMBER *
Supplier.Geography.Supplier.MEMBERS,
SupplierCountry = CustomerCountry AND
[Sales Amount] > 0) ) ON ROWS
FROM Sales
In the above query, we use the GENERATE function to obtain for each
customer the suppliers located in the same country. For this, we use the
FILTER function to keep only couples of customer and supplier located in the
same country and such that the customer has an order in which the supplier is
involved. Note that the expression Customer.Geography.Customer.MEMBERS ,
although somehow awkward, first points to the name of the dimension
( Customer ); then, for this dimension, it looks for the Geography hierarchy
and goes up again to look for the Customer , actually the first level in the
hierarchy.
Query 11.8. Number of customers from European countries with an area
larger than 50,000km 2 .
WITH MEMBER Measures.CountryArea AS
ST Area(Customer.Geography.CURRENTMEMBER.
Properties( ' CountryGeo ' ))
MEMBER Measures.CustomerCount AS
COUNT(EXISTING Customer.Geography.Customer)
 
Search WWH ::




Custom Search