Generate

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

Syntax

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

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.

Description

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])
East West South Central
Product Colas Root Beer Product Diet Drinks Cream Soda Product Root Beer Diet Drinks Product Diet Drinks Colas
Qtr1 5380 2747 562 7137 2025 2363 3077 1465 1146 9109 3291 2208
Qtr2 6499 3352 610 7515 1975 2739 3267 1540 1289 9826 3420 2473
Qtr3 6346 3740 372 7939 2100 2937 3515 1612 1310 10112 3478 2560
Qtr4 5936 2817 990 7270 1987 2692 3379 1498 1167 9215 3230 2249

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com