Generate

Returns a set formed by evaluating a set expression. For each tuple in set1, return set2.

Syntax

Generate ( set1, set2 [, [ALL]] )

Notes

The set expression set2 is evaluated in the context of each of the tuples from set1. The resulting sets are combined, in the same order as of the tuples in set1, to produce the output. Duplicates are not included by default.

Example

For each region of the market, return its top-selling 3 products. Display the sales data by quarter.

WITH SET [Top3BevsPerRegion] 
AS 
 'Generate ({[Market].children},
  Crossjoin 
   (
   {[Market].Currentmember}, 
    TopCount 
     (
      [Product].Members, 3, [Measures].[Sales]
     )
   )
 )' 
SELECT
 {[Top3BevsPerRegion]}
ON COLUMNS, 
 {[Year].children}
ON ROWS
FROM Sample.Basic
WHERE ([Scenario].[Actual], [Measures].[Sales])