docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  InterSystems MDX Reference  /  MDX Functions

InterSystems MDX Reference
%OR
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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:
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:
Also see examples in “Defining Calculated Members” in Defining Models for InterSystems Business Intelligence.


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-18 06:45:48