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

For example, Generate({USA, FRANCE}, {SEATTLE, BOSTON}, ALL) returns the set

{SEATTLE, BOSTON, SEATTLE, BOSTON}. However, if ALL was not specified, then the set returned is {SEATTLE, BOSTON}.

String

The following example returns the string "19971998": Generate( {Time.[1997], Time.[1998]}, Time.CurrentMember.Name)

The following example returns the string "1997 and 1998": Generate( {Time.[1997], Time.[1998]}, Time.CurrentMember.Name, " and ")

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Updated syntax and arguments to improve clarity. Added updated examples.