Cubing — Generate function (MDX)

The Generate function applies a set expression or a string expression to each member of a specified set. The function either returns the union of the sets that result, or returns a string that is constructed by concatenating the strings that result.

Returns: A set or a string.

Syntax


>>-Generate--(--SetExpression1--,--+-SetExpression2--+--------+---------+--)-><
                                   |                 '-,--ALL-'         |      
                                   '-StringExpression--+--------------+-'      
                                                       '-,--Delimiter-'

Parameters

SetExpression1: Specifies the set, in the context of whose members the function evaluates the set or string expression.
SetExpression2: Specifies the expression to evaluate over each member of the first set.
ALL: Specifies that the function retain duplicates within the first set. If you do not specify ALL, the function excludes duplicate members of the first set from the result set.
StringExpression: Specifies the expression to evaluate over each member of the first set.
Delimiter: Specifies a string to be inserted between the strings that result from evaluating the string expression.

Description

The Generate function has two forms: the set form and the string form.

You can use the set form of the function to get a set that consists of the union of the sets that result from applying the second set expression to each member of the first set. Typically, SetExpression2 involves functions such as CurrentMember, so applying the expression to a member of the primary set consists of evaluating the set expression in the context of each member of SetExpression1.

You can use the string form of the function to construct a string by concatenating the strings that result from applying the string expression to each member of the set. If a delimiter is specified, the function inserts the delimiter between each result string.

Example 1

The following example uses the Generate function to retrieve the three stores that have the lowest sales amount for each year in the cube.

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} ON AXIS(0),

{Generate([Price Analysis].[Time].[All Time (Calendar)].Children, 

Crossjoin({[Price Analysis].[Time].CurrentMember}, 

BottomCount(Descendants([Price Analysis].[Store].[All Stores], -1, 

LEAVES), 3, 

([Price Analysis].[Measures].[Sales Amount], 

[Price Analysis].[Time].CurrentMember))))} 

ON AXIS(1)

FROM [Price Analysis]

Result

Time	Store	Sales Amount
2002	ValueTrend Store 554	59438.98
	ValueTrend Store 835	64257.57
	ValueTrend Store 782	71548.32
2003	ValueTrend Store 554	53137.31
	ValueTrend Store 835	58443.68
	ValueTrend Store 782	73695.94
2004	ValueTrend Store 835	68399.22
	ValueTrend Store 554	71944.2
	ValueTrend Store 782	83726.36

Example 2

The following example uses the function to retrieve the concatenation of the name of each child of the All Time (Calendar) member separated by a comma.

Query

WITH

MEMBER [Price Analysis].[Measures].[Children] 

AS Generate([Price Analysis].[Time].CurrentMember.Children, 

[Price Analysis].[Time].CurrentMember.Name, ", ")

SELECT {[Price Analysis].[Measures].[Children]} ON AXIS(0),

{[Price Analysis].[Time].[All Time (Calendar)]} ON AXIS(1)

FROM [Price Analysis]

Result

Time	Children
All Time (Calendar)	2002, 2003, 2004