DeepSee MDX Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

Enables you to combine multiple members into a single member, for efficiency and to avoid double-counting. This function is an InterSystems extension to MDX.
Returned Type
This function returns a member. The name of the member is the name of the member, followed by +Others.
Syntax and Details
This function enables you to combine the given members or tuples into a single unit.
Often it is necessary for the WHERE clause to contain a set of multiple members. For example:
SELECT gend.MEMBERS ON 1 FROM patients WHERE {allerd.[ant bites],,allerd.wheat}
1 Female                                 56
2 Male                                   59
This query construction, however, means that DeepSee evaluates the query results multiple times (once for each item in the WHERE clause) and then combines them. This can be undesirably slow and can double-count items. (In this example, a given patient can be counted as many as three times, once for each allergy in the WHERE clause.)
With the %OR function, you can rewrite the query as follows:
SELECT gend.MEMBERS ON 1 FROM patients WHERE %OR({allerd.[ant bites],,allerd.wheat})
1 Female                                 55
2 Male                                   57
Note the numbers are lower, because this query does not double-count any patients.
As of release 2014.1, you can use %OR with a set that contains members of different levels (or even that contains tuples). For example:
SELECT NON EMPTY [Measures].[%COUNT] ON 0 FROM [Patients] 
WHERE %OR({[AgeD].[H1].[Age Bucket].&[80+],[DiagD].[H1].[Diagnoses].&[CHD]})
                             Patient Count
If you use this function on the column or row axis, you can see that it returns a member:
SELECT %OR({allerd.[ant bites],,allerd.wheat}) ON 1 FROM patients 
ant bites+Others                        112
The %OR function provides several advantages:
Also see examples in Defining Calculated Members in Defining DeepSee Models.