Syntax and Details
SELECT set_expression ON 0, set_expression ON 1 FROM cube_name
The ON clause is an optional axis specification. It has the following form:FeedbackOpens in a new window
set_expression ON axis_name_or_number
set_expression is an expression that evaluates to a set.
axis_name_or_number is COLUMNS (or 0, which is equivalent) or ROWS (or 1, which is equivalent).
You can specify zero, one, or two ON clauses. If you specify two ON clauses, you can specify them in either order.
In other implementations of MDX, if you specify ROWS, you must also specify COLUMNS. In InterSystems MDX, if you specify ROWS but do not specify COLUMNS, the system automatically generates an ON clause for COLUMNS. This clause uses the count measure.
cube_name is the name of the cube to use. The set expressions must make sense within that cube.
If you omit the ON clause, MDX returns the count of records in the cube. If this is a compound cube, this is the sum of the counts of all cubes combined in that compound cube.
For any given cell of the results, to determine the value to use, the system finds the intersection of the member used for the column and the member (if any used) for the row:
If one member is a measure and the other is not a measure, the system finds the value of that measure for that member. For example, if one member is the Ave Age measure, and the other member is the 34577 ZIP code, then the corresponding data cell contains the average age of patients whose home ZIP code is 34577.
If neither member is a measure, the system uses the default measure, which is usually %COUNT. For example, if one member is the gender F, and the other member is the 34577 ZIP code, then the corresponding data cell contains the count of all female patients whose home ZIP code is 34577.
The following simple example shows patient counts by ZIP code.
SELECT MEASURES.[%COUNT] ON 0, homed.zip.MEMBERS ON 1 FROM patients Patient Count 1 32006 2,272 2 32007 1,111 3 34577 3,399 4 36711 1,069 5 38928 2,149Copy code to clipboard
In the following example, the patients2 cube does not include the Home Zip level. Instead, this cube has a relationship called Home City that points to another cube, called cities. The query uses this relationship:
SELECT MEASURES.[%COUNT] ON 0, city.cityd.city.members ON 1 FROM patients2 Patient Count 1 Cedar Falls 1,097 2 Centerville 1,136 3 Cypress 1,124 4 Elm Heights 1,089 5 Juniper 1,133 6 Magnolia 1,063 7 Pine 1,124 8 Redwood 1,083 9 Spruce 1,151Copy code to clipboard