Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Specifying Members in a Set

We can assign sets to axes with relatively little programming effort, particularly when relying on many of the default member settings to supply info. To combine multiple dimensions on a single axis, however, we must specify individual members:

  1. Click the New Query button to start a new query.

  2. If necessary, click the Pivot Results button to place the axes in the original position.

  3. Type the following query in the Query pane:

    SELECT {[Store Cost]} ON COLUMNS FROM Sales

    The set we want to retrieve is enclosed in braces ({}). This is required to create a set without the benefit of a set function (which we used in the previous exercises). The braces tell the application that the enclosed members represent a set.

    The result of this query should be the single grand total for Store Cost shown at the bottom of the final PivotTable list in the previous exercise (see Figure 30).

    Figure 30Figure 30 The grand total of the Store Cost measure for the cube.

    The focus here is the selection of an individual measure for placement on the column axis. Enclosing the measure in braces defines its status as a set, which is a requirement for anything that's placed on an axis. But we also want to display two other positions in the PivotTable list, one each for Store Sales and Unit Sales.

  4. Add a comment (if desired) and the two additional measures, separated by commas and spaces, as follows:

    SELECT {[Store Cost], [Store Sales], [Unit Sales]} ON COLUMNS FROM Sales
  5. Click the Run Query button. Figure 31 shows the results.

    Figure 31Figure 31 Three tuples yield three measure columns.

Our multiple-member set consists of three tuples, with each tuple bearing a single measure as a member.

  • + Share This
  • 🔖 Save To Your Account