Using Sets of Tuples as Axes of a Query

You can use sets of tuples as axes of a query. The following example shows the simplest case, a set that consists of one tuple:

SELECT MEASURES.[%COUNT] ON 0, (homed.juniper,allerd.wheat,aged.[20 to 29]) ON 1 FROM demomdx

                                    %COUNT
Juniper->wheat->20 to 29                  1

The following example shows a set of tuples used as a query axis:

WITH SET myset AS 
'{(homed.[cedar falls],allerd.soy,colord.red),(homed.magnolia,allerd.soy,colord.green),
(homed.34577,allerd.eggs,colord.green)}' 
SELECT MEASURES.[%COUNT] ON 0, myset ON 1 FROM demomdx
 
                                    %COUNT
1 Cedar Falls->soy->Red                   *
2 Magnolia->soy->Green                    1
3 34577->eggs->Green                      *

For another example, the following is a valid query in InterSystems MDX:

WITH SET myset AS 
'{(homed.[cedar falls],allerd.soy,colord.green),(colord.red,allerd.soy,homed.pine,gend.male)}' 
SELECT MEASURES.[%COUNT] ON 0, myset ON 1 FROM demomdx
 
                                    %COUNT
1 Cedar Falls->soy->Green                 *
2 Red->soy->Pine->Male                    *

Finally, the following example uses tuples that refer multiple times to a single dimension:

SELECT MEASURES.[%COUNT] ON 0, 
{(allerd.soy,allerd.wheat),(homed.juniper,homed.centerville)} ON 1 FROM demomdx
 
                                    %COUNT
1 soy->wheat                              4
2 Juniper->Centerville                    *

There are 4 patients who are allergic to both soy and wheat.

There are no patients with two home cities.

Introduction to Cubes

Example Tuple Expressions