Databases Reference
In-Depth Information
The combination of the calculation subcube and the calculated cells condition is the
calculation scope
:
WITH CELL CALCULATION <
calculation name
> FOR '<
subcube definition
>'
AS '<
calculation formula
>' [<
conditions
>]
SELECT <
definition of the resulting multidimensional space
>
FROM <
source subspace
>
WHERE <
limiting the results to subspace
>
For example, we could have used the following query from our earlier example to display
sales in U.S. currency rather than the original Canadian dollars and Mexican pesos:
WITH CELL CALCULATION
CurrencyConversion FOR '(leaves(time),leaves(currency), measures.[store sales])'
AS measures.[store sales]*[Measures].[Rates]
SELECT measures.[store sales] ON COLUMNS,
[Store].[Stores].[Store Country].members ON ROWS
FROM [Warehouse and Sales]
Because you want to use calculated cells only in pretty exotic situations, we won't go into
further detail about calculated cells.
Named Sets
To simplify complex MDX expressions or to improve the performance of some queries,
you can extract the definition of some sets into a separate named set expression. Similar
to calculated member, you can define a named set in different scopes. The scope in which
you define a named set affects its lifetime:
.
A named set defined in an MDX script as part of a cube is available to all the queries
run against the cube.
.
A named set defined on a session is available to subsequent queries from the same
user during the same session.
.
A named set defined as part of a query is available only in the context of this cur-
rent query.
Use the
WITH
clause to define a named set as part of a query (as you do for a calculated
member):
WITH [DYNAMIC|STATIC] SET <
named set
> AS <
named set formula
>
SELECT <
definition of the resulting multidimensional space
>
FROM <
source subspace
>
WHERE <
limiting the results to subspace
>
For example, you can define the
TopCustomers
named set with the following code:
WITH SET TopCustomers AS
Search WWH ::
Custom Search