Returns a set formed by evaluating a set expression. For each tuple in set1, return set2.
Syntax
Generate ( set1, set2 [, [ALL]] )
Parameter | Description |
---|---|
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. |
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])
(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 |