Working with Sets in
Our Queries
We can
generate queries that are far more sophisticated by adding sets of
members to one or both of the axes. Let's explore this, with the following
steps:
10. Select Query
-> New from the top menu to set up a new
Query pane.
11.
Type in the
following query once again, for starters:
SELECT
FROM Warehouse
NOTE: I often intentionally add
generous space between rows, and between components of the coding, in my lessons.
This is to make the coding easier to follow.
12.
Placing the
cursor behind word SELECT, type in one space.
13.
Expand (by clicking on the "+"
sign to its left) the Set folder in the Syntax Examples list on
the right side of the Metadata pane.
14.
Select the Members-Level
function by double-clicking.
The
function appears, amid our initial query, in the Query pane, as shown in
Illustration 3.
Illustration 3: Building
the Query: Inserting a Set Function
15.
Click the <<Level>>
token once to highlight it.
16.
Expand the Store
dimension in the Metadata tree to expose its member levels.
17.
Double-click
the Store State level, within the expanded
Stores dimension.
18.
Type another
space just behind the .Members function of the top row.
19.
Type the words
ON ROWS.
Now we
will arrange the query a bit, as we build it further.
20.
Click to the left
of [Store], just after SELECT.
21.
Press the ENTER
key.
22.
Place the
cursor behind the SELECT statement.
23.
Press the ENTER
key again, to create a blank row under SELECT.
24.
Expand the Set
folder in the Syntax Examples list, once again, if necessary.
25.
Double-click the
Children function.
The
function populates the newly created row.
At
this stage, the statement should duplicate the following:
SELECT
<<Member>>.Children
[Store].[Store State].Members ON ROWS
FROM Warehouse
26.
Select the <<Member>>
token with a single-click.
27.
Double-click
the Store Type dimension in the Metadata tree to select it into
the <<Member>> token.
Our
statement should now appear as below:
SELECT
[Store Type].Children
[Store].[Store State].Members ON ROWS
FROM Warehouse
28.
Type in a
space, then the words ON COLUMNS after Children in the above
statement.
29.
Add a comma (",")
immediately after the word COLUMNS.
Our
statement now appears as follows:
SELECT
[Store Type].Children ON COLUMNS,
[Store].[Store State].Members ON ROWS
FROM Warehouse
The ON
COLUMNS addition specifies that the second set be added to the column
axis. Anytime that we create multiple axes, as we are doing within this
step of our example, we are required to designate the respective axis with
which each such string is associated. Order in the query is not as important
as the fact that the designation itself is made. (A single axis need not be
defined, as it defaults to Column axis, similarly to the case we observed in
the first query in this lesson).
30.
Select Query
--> Run from the main menu.
We obtain
the results shown in Illustration 4 below.
Illustration 4: The
Results of the Two-Axis Query
31.
Save the query
as MXAS06-2.MDX.
In
addition to using other members' functions (explore these in the Set
folder of the Syntax Examples list as you find time), we can make a
simple alteration to our query statement to cause it to return the entire
membership of the Store dimension.
32.
Remove "[Store State]." from the query (including
the "." delimiter that follows the right bracket) from the query we have designed.
Our
modified statement appears in the Query pane as shown in
Illustration 5. (I have created a composite picture, removing many of the
empty rows, to conserve space.)
Illustration 5: The
Modified Query
33.
Click the Run
Query button to execute the query.
We obtain
the results partially shown in Illustration 6 (scrolling to the
appropriate rows):
Illustration 6: The
Store Dimension's Entire Population is Retrieved (Composite View)
34.
Save the query as MXAS06-3.MDX.
» 
Comment and Contribute
