Database Reference
In-Depth Information
In the above example, we created a calculated member in the Measures
dimension. It is also possible to create a calculated member in a dimension,
as shown in the following example:
WITH MEMBER Product.Categories.[All].[Meat & Fish] AS
Product.Categories.[Meat/Poultry] + Product.Categories.[Seafood]
SELECT
{
Measures.[Unit Price], Measures.Quantity, Measures.Discount,
Measures.[Sales Amount]
ON COLUMNS,
Category.ALLMEMBERS ON ROWS
FROM Sales
}
The query above creates a calculated member equal to the sum of the
Meat/Poultry and Seafood categories. This member is a child of the All
member of the hierarchy Categories of the Product dimension. It will thus
belong to the Category level of the Categories hierarchy. The following table
shows the result of the query:
Unit Price Quantity Discount Sales Amount
···
···
···
···
···
Meat/Poultry
$50.25
3,897
7.48%
$139,428.18
Produce
$41.24
2,710
5.09%
$90,216.14
Seafood
$27.64
7,070
8.25%
$122,307.02
Meat & Fish
$77.89
10,967
15.73%
$261,735.20
In the following query, we define a named set Nordic Countries composed
of the countries Denmark, Finland, Norway, and Sweden:
WITH SET [Nordic Countries] AS
{
Customer.Country.Denmark, Customer.Country.Finland,
Customer.Country.Norway, Customer.Country.Sweden
}
SELECT Measures.MEMBERS ON COLUMNS,
[Nordic Countries] ON ROWS
FROM Sales
The result of the query is as follows:
Unit Price Quantity Discount Sales Amount
Freight
Sales Count
Denmark
$70.28
1,156
17.94%
$32,428.94
$1,377.75
45
Finland
$54.41
848
9.09%
$17,530.05
$827.45
51
Norway
$97.95
152
0.00%
$5,321.15
$257.45
15
Sweden
$68.73
2,149
19.57%
$51,292.64
$3,032.12
94
In the above example, the named set is defined by enumerating its
members, and thus, it is a static name set even if defined in the scope of
a session or a cube, since its result must not be reevaluated upon updates
of the cube. On the contrary, a dynamic named set is evaluated any time
there are changes to the scope. As an example of a dynamic named set, the
following query displays several measures for the top five selling products:
 
Search WWH ::




Custom Search