Generate (MDX)
Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Syntax
Set expression syntax
Generate( Set_Expression1 , Set_Expression2 [ , ALL ] )
String expression syntax
Generate( Set_Expression1 , String_Expression [ ,Delimiter ] )
Arguments
Set_Expression1
A valid Multidimensional Expressions (MDX) expression that returns a set.
Set_Expression2
A valid Multidimensional Expressions (MDX) expression that returns a set.
String_Expression
A valid string expression that is typically the name of the current member (CurrentMember.Name) of each tuple in the specified set.
Delimiter
A valid delimiter expressed as a string expression.
Remarks
If a second set is specified, the Generate function returns a set generated by applying the tuples in the second set to each tuple in the first set, and then joining the resulting sets by union. If ALL is specified, the function retains duplicates in the resulting set.
If a string expression is specified, the Generate function returns a string generated by evaluating the specified string expression against each tuple in the first set, and then concatenating the results. Optionally, the string can be delimited, separating each result in the resulting concatenated string.
Examples
Set
In the following example, the query returns a set containing the Measure Internet Sales amount four times, because there are four members in the set [Date].[Calendar Year].[Calendar Year].MEMBERS:
SELECT
GENERATE( [Date].[Calendar Year].[Calendar Year].MEMBERS
, {[Measures].[Internet Sales Amount]}, ALL)
ON 0
FROM [Adventure Works]
Removing the ALL changes the query so that the Internet Sales Amount is returned once only:
SELECT
GENERATE( [Date].[Calendar Year].[Calendar Year].MEMBERS
, {[Measures].[Internet Sales Amount]})
ON 0
FROM [Adventure Works]
The most common practical use of Generate is to evaluate a complex set expression, such as TopCount, over a set of members. The following example query displays the top 10 Products for each Calendar Year on Rows:
SELECT
{[Measures].[Internet Sales Amount]}
ON 0,
GENERATE(
[Date].[Calendar Year].[Calendar Year].MEMBERS
, TOPCOUNT(
[Date].[Calendar Year].CURRENTMEMBER
*
[Product].[Product].[Product].MEMBERS
,10, [Measures].[Internet Sales Amount]))
ON 1
FROM [Adventure Works]
Note that a different top 10 is displayed for each year, and that the use of Generate is the only way to get this result. Simply crossjoining Calendar Years and the set of top 10 Products will display the top 10 Products for all time, repeated for each year, as shown in the following example:
SELECT
{[Measures].[Internet Sales Amount]}
ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS
*
TOPCOUNT(
[Product].[Product].[Product].MEMBERS
,10, [Measures].[Internet Sales Amount])
ON 1
FROM [Adventure Works]
String
The following example shows the use of Generate to return a string:
WITH
MEMBER MEASURES.GENERATESTRINGDEMO AS
GENERATE(
[Date].[Calendar Year].[Calendar Year].MEMBERS,
[Date].[Calendar Year].CURRENTMEMBER.NAME)
MEMBER MEASURES.GENERATEDELIMITEDSTRINGDEMO AS
GENERATE(
[Date].[Calendar Year].[Calendar Year].MEMBERS,
[Date].[Calendar Year].CURRENTMEMBER.NAME, " AND ")
SELECT
{MEASURES.GENERATESTRINGDEMO, MEASURES.GENERATEDELIMITEDSTRINGDEMO}
ON 0
FROM [Adventure Works]
Note
This form of the Generate function can be useful when debugging calculations, as it enables you to return a string displaying the names of all the members in a set. This might be easier to read than the strict MDX representation of a set that the SetToStr (MDX) function returns.