Exercise: Using the CrossJoin Function

The CrossJoin function returns the cross product of two sets from different dimensions. Its syntax is as follows:

CrossJoin(set,set)

The CrossJoin function takes two sets from different dimensions as input and creates a set that is a cross product of the two input sets, useful for creating symmetric reports.

  To use the CrossJoin function:

  1. Open qry_blank_2ax.txt.

  2. Replace the braces {} from the columns axis with CrossJoin().

    SELECT
      CrossJoin () 
    ON COLUMNS, 
      {}
    ON ROWS
    FROM Sample.Basic
  3. Add two comma-separated pairs of braces as placeholders for the two set arguments you will provide to the CrossJoin function:

    SELECT
      CrossJoin ({}, {})
    ON COLUMNS,
      {}
    ON ROWS
    FROM Sample.Basic
  4. In the first set, specify the Product member [100-10]. In the second set, specify the Market members [East], [West], [South], and [Central].

    SELECT
      CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
    ON COLUMNS,
      {}
    ON ROWS
    FROM Sample.Basic
  5. On the row axis, use CrossJoin to cross a set of Measures members with a set containing Qtr1:

    SELECT
      CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
    ON COLUMNS,
      CrossJoin ( 
        {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]} 
      ) 
    ON ROWS
    FROM Sample.Basic
  6. Save the query as qry_crossjoin_func.txt.

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

When using CrossJoin, the order of arguments affects the order of tuples in the output.

Results of the query are shown in Table 113:

Table 113. Results: Using the CrossJoin Function

  

100-10

100-10

100-10

100-10

  

East

West

South

Central

Sales

Qtr1

5731

3493

2296

3425

COGS

Qtr1

1783

1428

1010

1460

Margin %

Qtr1

66.803

59.118

56.01

57.372

Profit %

Qtr1

45.82

29.974

32.448

24.613