A set that contains members that are not shared by both specified parameters.
Remarks
The - (Except) operator is functionally equivalent to the Except function.
Examples
The following example demonstrates the use of this operator:
// This query shows the quantity of orders for all product categories // with the exception of Components.
SELECT
[Measures].[Order Quantity] ON COLUMNS,
[Product].[Product Categories].[All].Children
- [Product].[Product Categories].[Components] ON ROWS FROM
[Adventure Works]
- (Negative)
Performs a unary operation that returns the negative value of a numeric expression.
Syntax
- Numeric_Expression
Parameters
Parameter Description
Numeric_Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
A negative value that has the data type of the specified parameter.
Examples
The following example demonstrates the use of this operator.
-- This member creates a negative version of the -- Reseller Freight Cost.
WITH MEMBER
Measures.[Resell Cost as Negative] AS -Measures.[Reseller Freight Cost] SELECT
[Date].[Calendar Month of Year].Children ON COLUMNS, [Product].[Product Categories].Children ON ROWS FROM
[Adventure Works] WHERE
See Also
MDX Operator Reference (MDX)
- (Subtract)
Performs an arithmetic operation that subtracts one number from another number.
Syntax
Numeric_Expression - Numeric_Expression
Parameters
Parameter Description
Numeric_Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
A value with the data type of the parameter that has the higher precedence.
Remarks
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If one expression evaluates to a null value, the operator returns the result of the non-null expression.
Examples
The following example demonstrates the use of this operator.
-- This member returns the increase or decrease -- in gross profit margin over a month.
WITH MEMBER [Measures].[GPM Delta] AS (
(Measures.[Gross Profit Margin]) -
), FORMAT_STRING = 'Percent' SELECT DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Month]) ON 0, [Product].[Category].[Category].Members ON 1 FROM [Adventure Works] WHERE ([Measures].[GPM Delta])
See Also
MDX Operator Reference (MDX)* (Crossjoin)
Performs a set operation that returns the cross product of two sets.
Syntax
Set_Expression * Set_Expression
Parameter
Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.
Return Value
A set that contains the cross product of both specified parameters.
Remarks
The * (Crossjoin) operator is functionally equivalent to the Crossjoin function.
Examples
-- This query returns the gross profit margin for product types -- and reseller types crossjoined by year.
SELECT [Date].[Calendar].[Calendar Year].Members * [Reseller].[Reseller Type].Children ON 0, [Product].[Category].[Category].Members ON 1 FROM [Adventure Works] WHERE
([Measures].[Gross Profit Margin])
See Also
MDX Operator Reference (MDX)
* (Multiply)
Performs an arithmetic operation that multiplies two numbers.
Syntax
Numeric_Expression * Numeric_Expression
Parameters
Parameter Description
Numeric_Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
Remarks
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If one expression evaluates to a null value, the operator returns a null value.
See Also
MDX Operator Reference (MDX)
/ (Divide)
Performs an arithmetic operation that divides one number by another number.
Syntax
Dividend / Divisor
Parameters
Parameter Description
Dividend A valid Multidimensional Expressions
(MDX) expression that returns a numeric value.
Divisor A valid MDX expression that returns a
numeric value.
Return Value
A value with the data type of the parameter that has the higher precedence.
Remarks
The actual value returned by the / (Divide) operator represents the quotient of the first expression divided by the second expression.
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If Divisor evaluates to a null value, the operator raises an error. If both Divisor and Dividend evaluate to a null value, the operator returns a null value.
Examples
The following example demonstrates the use of this operator.
-- This query returns the freight cost per user, -- for products, averaged by month.
With Member [Measures].[Freight Per Customer] as [Measures].[Internet Freight Cost]
/
[Measures].[Customer Count]
SELECT
[Ship Date].[Calendar].[Calendar Year] Members ON 0, [Product].[Category].[Category].Members ON 1
FROM
[Adventure Works] WHERE
([Measures].[Freight Per Customer])
Dividing a non-zero or non-null value by zero or null will return the value Infinity, which is displayed in query results as the value "1.#INF". In most cases, you should check for division by zero to avoid this situation. The following example shows you how:
//Returns 1.#INF when Internet Sales Amount is zero or null Member [Measures].[Reseller to Internet Ratio] AS
[Measures].[Reseller Sales Amount] /
[Measures].[Internet Sales Amount]
//Traps the division by zero scenario and returns null instead of 1.#INF Member [Measures].[Reseller to Internet Ratio With Error Handling] AS IIF([Measures].[Internet Sales Amount]=0, NULL,
[Measures].[Reseller Sales Amount] /
[Measures].[Internet Sales Amount]) SELECT
[Product].[Category].[Category].Members ON 1 FROM [Adventure Works] WHERE([Date].[Calendar].[Calendar Year].&[2001])
See Also
IIf (MDX) MDX Operator Reference (MDX)^ (Power)
Performs an arithmetic operation that raises one number by another number.
Syntax
Numeric_Expression ^ Numeric_Expression
Parameters
Parameter Description
Numeric_Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
A value with the data type of the parameter that has the higher precedence.
Remarks
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If one expression evaluates to a null value, the operator returns a null value.
See Also
/*...*/ (Comment)
Indicates comment text that is provided by the user.
Syntax
/* Comment_Text */
Parameters
Parameter Description
Comment_Text The string that contains the text of the comment.
Remarks
The server does not evaluate the text between the comment characters, /* and */. Comments can be inserted on a separate line or within a Multidimensional Expressions (MDX) statement. Multiple-line comments must be indicated by /* and */.
There is no maximum length for comments. Comments can be nested; for example, /* Test /*Comment*/ Text*/ is an example of a nested comment.
Examples
The following example demonstrates the use of this operator.
/* This member returns the gross profit margin for product types and reseller types crossjoined by year. */
SELECT
[Date].[Calendar].[Calendar Year].Members * [Reseller].[Reseller Type].Children ON 0, [Product].[Category].[Category].Members ON 1 FROM /* Select from the Adventure Works cube. */ [Adventure Works]
WHERE
See Also
// (Comment) (MDX) -- (Comment) (MDX)
MDX Operator Reference (MDX)
// (Comment)
Indicates user-provided text.
Syntax
// Comment_Text
Parameters
Parameter Description
Comment_Text The string that contains the text of the comment.
Remarks
Comments can be inserted on a separate line, nested at the end of a Multidimensional
Expressions (MDX) script line, or nested within an MDX statement. The server does not evaluate the comment.
Use // for single-line comments only. Comments inserted with // are delimited by the newline character.
There is no maximum length for comments.
Examples
The following example demonstrates the use of this operator.
// This member returns the gross profit margin for product types // and reseller types crossjoined by year.
SELECT
[Date].[Calendar].[Calendar Year].Members * [Reseller].[Reseller Type].Children ON 0, [Product].[Category].[Category].Members ON 1
FROM // Select from the Adventure Works cube. [Adventure Works]
WHERE
[Measures].[Gross Profit Margin]
See Also
/*...*/ (Comment) (MDX) -- (Comment) (MDX)
MDX Operator Reference (MDX)
: (Range)
Performs a set operation that returns a naturally ordered set, with the two specified members as endpoints, and all members between the two specified members included as members of the set.
Syntax
Member_Expression : Member_Expression
Parameters
Parameter Description
Member_Expression A valid Multidimensional Expressions (MDX) expression that returns a member.
Return Value
A set that contains the specified members and all members between the specified members.
Remarks
Both parameters must specify members within the same level and hierarchy of a given
dimension. If both parameters specify the same member, the : (Range) operator returns a set that contains just the specified member. If the first parameter is Null, then the set contains all members from the beginning of the level of the member specified in the second parameter, up
members from the member specified in the first parameter, up to and including the last member on the same level.
This set operator has no functional equivalent in MDX.
Examples
The following example demonstrates the use of this operator.
-- This query returns the freight cost per user
-- for products, averaged by month, for the first quarter. With Member [Measures].[Freight Per Customer] as
(
[Measures].[Internet Freight Cost] /
[Measures].[Customer Count] )
SELECT
{[Ship Date].[Calendar].[Month].&[2004]&[1] : [Ship Date].[Calendar].[Month].&[2004]&[3]} ON 0,
[Product].[Category].[Category].Members ON 1 FROM
[Adventure Works] WHERE
([Measures].[Freight Per Customer])
See Also
MDX Operator Reference (MDX)
+ (Add)
Performs an arithmetic operation that adds two numbers.
Syntax
Parameters
Parameter Description
Numeric Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
A value with the data type of the parameter that has the higher precedence.
Remarks
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If one expression evaluates to a null value, the operator returns the result of the other expression.
See Also
MDX Operator Reference (MDX)
+ (Positive)
Performs a unary operation that returns the positive value of a numeric expression.
Syntax
+ Numeric_Expression
Parameters
Parameter Description
Numeric_Expression A valid Multidimensional Expressions (MDX) expression that returns a numeric value.
Return Value
A positive value that has the data type of the specified parameter.
See Also
MDX Operator Reference (MDX)
+ (String Concatenation)
Performs a string operation that concatenates two or more character strings, tuples, or a combination of strings and tuples.
Syntax
String_Expression + String_Expression
Parameters
Parameter Description
String_Expression A valid Multidimensional Expressions (MDX) expression that returns a string value.
Return Value
A value with the data type of the parameter that has the higher precedence.
Remarks
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. If one expression evaluates to a null value, the operator returns the result of the other expression.
See Also
MDX Operator Reference (MDX)
+ (Union)
Syntax
Set_Expression + Set_Expression
Parameters
Parameter Description
Set_Expression A valid Multidimensional Expressions (MDX) expression that returns a set.
Return Value
A set that contains the members of both specified sets.
Remarks
The + (Union) operator is functionally equivalent to the Union (MDX) function.
Examples
The following example demonstrates the use of this operator.
-- This member returns the gross profit margin for each year for North American countries.
SELECT
[Date].[Calendar].[Calendar Year].Members ON 0,
{[Sales Territory].[Sales Territory].[Country].[United States]} + {[Sales Territory].[Sales Territory].[Country].[Canada]} ON 1 FROM
[Adventure Works] WHERE
([Measures].[Gross Profit Margin])
See Also
< (Less Than)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is less than the value of another MDX expression.
Syntax
MDX_Expression < MDX_Expression
Parameters
Parameter Description
MDX_Expression A valid MDX expression.
Return Value
A Boolean value based on the following conditions:
• true if both parameters are non-null, and the first parameter has a value that is lower than the value of the second parameter.
• false if both parameters are non-null, and the first parameter has a value that is either equal to or greater than the value of the second parameter.
• null if either or both parameters evaluate to a null value.
Examples
The following example demonstrates the use of this operator.
-- This query returns the gross profit margin (GPM) -- for clothing sales where the GPM is less than 30%. With Member [Measures].[LowGPM] as
IIF(
[Measures].[Gross Profit Margin] < .3, [Measures].[Gross Profit Margin], null)
SELECT NON EMPTY
[Sales Territory].[Sales Territory Country].Members ON 0, [Product].[Category].[Clothing] ON 1
FROM [Adventure Works] WHERE ([Measures].[LowGPM])
See Also
MDX Operator Reference (MDX)<= (Less Than or Equal To)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is less than or equal to the value of another MDX expression.
Syntax
MDX_Expression <= MDX_Expression
Parameters
Parameter Description
MDX_Expression A valid MDX expression.
Return Value
A Boolean value based on the following conditions:
• true if both parameters are non-null, and the first parameter has a value that is either less than or equal to the value of the second parameter.
• false if both parameters are non-null, and the first parameter has a value that greater than the value of the second parameter.
• null if either or both parameters evaluate to a null value.
Examples
The following example demonstrates the use of this operator.
-- This query returns the gross profit margin (GPM)
IIF(
[Measures].[Gross Profit Margin] <= .5, [Measures].[Gross Profit Margin],
null) SELECT
NON EMPTY [Sales Territory].[Sales Territory Country].[Australia] ON 0, NON EMPTY [Product].[Category].Members ON 1
FROM [Adventure Works] WHERE ([Measures].[LowGPM])
See Also
MDX Operator Reference (MDX)<> (Not Equal To)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is not equal to the value of another MDX expression.
Syntax
MDX_Expression <> MDX_Expression
Parameters
Parameter Description
MDX_Expression A valid MDX expression.
Return Value
A Boolean value based on the following conditions:
• true if both parameters are non-null, and the first parameter is not equal to the second parameter.
• false if both parameters are non-null, and the first parameter is equal to the second parameter.
• null if either or both parameters evaluate to a null value.
See Also
MDX Operator Reference (MDX)
= (Equal To)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is equal to the value of another MDX expression.
To compare objects, use the IS (MDX) operator. For example, use the IS operator when you are checking if the current member on a query axis is a specific member.
Syntax
MDX_Expression = MDX_Expression
Parameters
Parameter Description
MDX_Expression A valid MDX expression.
Return Value
A Boolean value based on the following conditions:
• true if the value of the first parameter is equal to the value of the second parameter. • false if the value of the first parameter is not equal to the value of the second parameter. • true if both parameters are null, or one parameter is null and the other parameter is 0.
Examples
The following query shows examples of these conditions:
With
--Returns true
Member [Measures].bool1 as 1=1 --Returns false
--Returns true
Member [Measures].bool3 as null=null --Returns true
Member [Measures].bool4 as 0=null --Returns false
Member [Measures].bool5 as 1=null Select
{[Measures].bool1,[Measures].bool2,[Measures].bool3,[Measures].bool4,[Measure s].bool5}
On 0
From [Adventure Works]
See Also
MDX Operator Reference (MDX)
> (Greater Than)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is greater than the value of another MDX expression.
Syntax
MDX_Expression > MDX_Expression
Parameters
Parameter Description
MDX_Expression A valid MDX expression.
Return Value
A Boolean value based on the following conditions:
• true if both parameters are non-null, and the first parameter has a value that is greater than the value of the second parameter.
• false if both parameters are non-null, and the first parameter has a value that is either equal to or lower than the value of the second parameter.
Examples
The following example query demonstrates the use of this operator.
-- This query returns the gross profit margin (GPM) -- for Australia where the GPM is more than 50%. With Member [Measures].[HighGPM] as
IIF(
[Measures].[Gross Profit Margin] > .5, [Measures].[Gross Profit Margin], null)
SELECT
NON EMPTY [Sales Territory].[Sales Territory Country].[Australia] ON 0, NON EMPTY [Product].[Category].Members ON 1
FROM [Adventure Works] WHERE ([Measures].[HighGPM])
See Also
MDX Operator Reference (MDX)>= (Greater Than or Equal To)
Performs a comparison operation that determines whether the value of one Multidimensional Expressions (MDX) expression is greater than or equal to the value of another MDX expression.
Syntax
MDX_Expression >= MDX_Expression
Parameters
Parameter Description
Return Value
A Boolean value based on the following conditions:
• true if the first parameter has a value that is either greater than or equal to the value of the second parameter.
• false if the first parameter has a value that is lower than the value of the second parameter. • true if both parameters are null or if one parameter is null and the other parameter is 0.
Examples
The following example demonstrates the use of this operator.
-- This query returns the gross profit margin (GPM)
-- for Australia where the GPM is greater than or equal to 50%. With Member [Measures].[HighGPM] as
IIF(
[Measures].[Gross Profit Margin] >= .5, [Measures].[Gross Profit Margin],
null) SELECT
NON EMPTY [Sales Territory].[Sales Territory Country].[Australia] ON 0, NON EMPTY [Product].[Category].Members ON 1
FROM [Adventure Works] WHERE ([Measures].[HighGPM])
See Also
MDX Operator Reference (MDX)AND
Performs a logical conjunction on two numeric expressions.
Syntax
Parameters
Parameter Description
Expression1 A valid Multidimensional Expressions
(MDX) expression that returns a numeric value.
Expression2 A valid MDX expression that returns a
numeric value.
Return Value
A Boolean value that returns true if both parameters evaluate to true; otherwise, false.
Remarks
The AND operator treats both expressions as Boolean values (zero, 0, as false; otherwise, true) before the operator performs the logical conjunction. The following table illustrates how the AND operator performs the logical conjunction.
Expression1 Expression2 Return Value
true true true
true false false
false true false
false false false