---
title: "Generate (MDX) | Microsoft Docs"
ms.date: 06/04/2018
ms.prod: sql
ms.technology: analysis-services
ms.custom: mdx
ms.topic: reference
ms.author: owend
ms.reviewer: owend
author: minewiskan
---
# 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)](../mdx/settostr-mdx.md) function returns.  
  
## See Also  
 [MDX Function Reference (MDX)](../mdx/mdx-function-reference-mdx.md)