Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 19, 2003

Using Sets in MDX Queries - Page 4

By William Pearson

Specifying Members in a Set

As we see from the fruits of our labor so far, we can assign sets to axes with relatively little programming effort. This is particularly true when we can rely upon many of the default member settings to "fill in the blanks," with regard to the requirement to supply "addresses" for the values we seek to retrieve, in terms of all dimensions in the cube. However, to approach the content of a report that leverages multi-dimensional concepts to produce denser information in a useful form, we must go further than this. We must be able to specify individual members, for purposes of extracting precise information from the wealth of cube data, as well as to be capable of combining multiple dimensions on a single axis. MDX rises to the occasion, and supports our need for precise control of the presentation quite well.

Let's explore meeting these further requirements, with the following steps:

35. Start a new query using the New Query button.

36. Type the following into the Query pane:


{[Measures].[Warehouse Cost]} ON COLUMNS

FROM Warehouse

We enclose the set we want to retrieve in braces ({}). The braces inform the application that the enclosed member(s) represent a set.

The results we obtain should be a single "grand total" for Warehouse Cost, and should duplicate those shown in Illustration 7.

Illustration 7: 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. Our enclosing the measure in braces defines its status as a set, as we have noted, which is a requirement for anything that is placed on an axis within that status.

Now let's say that our business requirement changes: we are asked to display not only the Warehouse Cost value, but two additional values, Warehouse Sales and Units Shipped. We will deliver these tuples with the following enhancement to our query.

37. Add the two additional measures, separated by commas and spaces, to the query as follows:


{[Measures].[Warehouse Cost],[Measures].[Warehouse Sales],
[Measures].[Units Shipped]} ON COLUMNS

FROM Warehouse

38. Click the Run Query button.

The results appear as shown in Illustration 8.

Illustration 8: Three Tuples Yield Three Measure Columns

Our set now consists of three tuples. We have thus presented a multiple member set, identifying those members specifically in our query.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM