Exercise: Creating a Calculated Member

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:

  1. Open qry_blank_2ax.txt.

  2. On the row axis set, specify the children of Product. For example:

    SELECT
      {}
    ON COLUMNS,
      {[Product].children}
    ON ROWS
    FROM Sample.Basic
  3. 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
  4. 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
  5. 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
    
  6. 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
    
  7. Save the query as gry_calc_member.txt.

  8. 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:

Table 117. Results: Creating a Calculated Member

 

Max Qtr2 Sales

100

27187

200

27401

300

25736

400

21355

Diet

26787