Exercise: Using the MemberRange Function

The MemberRange function returns a range of members inclusive of and between two specified members of the same generation. Its syntax is as follows:

MemberRange (member1, member2, [,layertype])

where the first argument you provide is the member that begins the range, and the second argument is the member that ends the range. The layertype argument is optional. See the Oracle Essbase Technical Reference.

Note:

An alternate syntax for MemberRange is to use a colon between the two members, instead of using the function name: member1 : member2.

  To use the MemberRange function:

  1. Open qry_blank.txt.

  2. Delete the braces {}, which are unnecessary when you are using a function to return the set.

  3. Use the colon operator to select a member range of Qtr1 through Qtr4:

    SELECT 
      [Qtr1]:[Qtr4]
    ON COLUMNS 
    FROM Sample.Basic
  4. Paste the query into the MaxL Shell and run it, as described in Exercise: Running Your First Query.

    Qtr1, Qtr2, Qtr3, and Qtr4 are returned.

  5. Use the MemberRange function to select the same member range, Qtr1 through Qtr4.

    SELECT
      MemberRange([Qtr1],[Qtr4])
    ON COLUMNS
    FROM Sample.Basic
  6. Save the query as gry_member_range_func.txt.

  7. Paste the query into the MaxL Shell and run it, as described in Exercise: Running Your First Query.

Results: The same results should be returned when running the queries listed in step 3 and step 5.