This exercise includes the Max function, a common function for calculations. The Max function returns the maximum of values found in the tuples of a set. Its syntax is as follows:
Max (set, numeric_value)
To create a calculated member:
On the row axis set, specify the children of Product. For example:
SELECT
{}
ON COLUMNS,
{[Product].children}
ON ROWS
FROM Sample.Basic
At the beginning of the query, add a placeholder for the calculated member specification. For example:
WITH MEMBER [].[]
AS ''
SELECT
{}
ON COLUMNS,
{[Product].children}
ON ROWS
FROM Sample.Basic
To associate the calculated member with the Measures dimension and name it Max Qtr2 Sales, add this information to the calculated member specification. For example:
WITH MEMBER [Measures].[Max Qtr2 Sales] AS '' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
After the AS keyword and inside the single quotation marks, define the logic for the calculated member named Max Qtr2 Sales.
Use the Max function with the set to evaluate (Qtr2) as the first argument, and the measure to evaluate (Sales) as the second argument. For example:
WITH MEMBER [Measures].[Max Qtr2 Sales] AS ' Max ( {[Year].[Qtr2]}, [Measures].[Sales] )' SELECT {} ON COLUMNS, {[Product].children} ON ROWS FROM Sample.Basic
The calculated member Max Qtr2 Sales is defined in the WITH section. To use it in a query, include it on one of the axes in the SELECT portion of the query. Select the predefined calculated member on the columns axis. For example:
WITH MEMBER [Measures].[Max Qtr2 Sales]
AS '
Max (
{[Year].[Qtr2]},
[Measures].[Sales]
)'
SELECT
{[Measures].[Max Qtr2 Sales]}
ON COLUMNS,
{[Product].children}
ON ROWS
FROM Sample.Basic
Paste the query into the MaxL Shell and run it, as described in Exercise: Running Your First Query.
Results of the query are shown in Table 117: