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