Generate

The MDX Generate function for Essbase returns a set formed by evaluating a set expression using the following algorithm: For each tuple in set1, return set2. 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.

Syntax

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

Parameters

set1: The set to loop through.
set2: The set expression to evaluate for every tuple in set1.
ALL: If the optional ALL flag is used, duplicate tuples are retained.

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])

The above query returns the following result:

Table 4-73 Output Grid from MDX Example

(axis)	East			West			South			Central
(axis)	Product	Colas	Root Beer	Product	Diet Drinks	Cream Soda	Product	Root Beer	Diet Drinks	Product	Diet Drinks	Colas
Qtr1	20621	6292	5726	31674	8820	8043	12113	5354	4483	31412	10544	8074
Qtr2	224499	7230	5902	33572	9086	8982	12602	5535	4976	33056	10809	8701
Qtr3	22976	7770	5863	35130	9518	9616	13355	5690	4947	33754	10959	8894
Qtr4	21352	6448	6181	32555	8999	8750	12776	5429	4450	31458	10348	8139