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.
Syntax
Generate ( set1, set2 [, [ALL]] )
Parameters
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])
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 |