Skip to main content

%OR (MDX)

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

%OR(set_expression)

Where:

  • set_expression is an expression that evaluates to a set of members or tuples. This expression must be enclosed in curly braces.

This function enables you to combine the given members or tuples into a single unit.

Example

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.soy,allerd.wheat}
 
 
1 Female                                 56
2 Male                                   59

This query construction, however, means that the system 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.soy,allerd.wheat})
 
 
1 Female                                 55
2 Male                                   57

Note the numbers are lower, because this query does not double-count any patients.

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
                                         71

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.soy,allerd.wheat}) ON 1 FROM patients 
 
ant bites+Others                        112

The %OR function provides several advantages:

  • The members of the set are treated as one unit.

  • The combination of members occurs in an earlier part of the processing for greater efficiency.

  • %OR returns a single member which can be combined (internally) with other filters to form simple tuple expressions.

Also see examples in Defining Calculated Members.

FeedbackOpens in a new tab