Returns for each member of { [America], [Europe] } its firstChild.
Query
Generate( {[Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[Europe]},
{[Geography].[Geo].currentMember.firstChild}
)
Result
{
[Geography].[Geo].[Country].[Canada],
[Geography].[Geo].[Country].[France]
}
Assertion : MDX Equals
{[Geography].[Geo].[Continent].[America].firstChild, [Geography].[Geo].[Continent].[Europe].firstChild}
A classical example, for each continent we get the top (Country,Licence) tuple.
Note the usage of .currentMember and named set (s1).
Query
Generate( [Geography].[Geo].[Continent].members as s1,
TopCount( s1.CurrentMember.children * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] ) )
Result
{
( [Geography].[Geo].[Country].[United States], [Product].[Prod].[Licence].[Corporate] ),
( [Geography].[Geo].[Country].[Switzerland], [Product].[Prod].[Licence].[Corporate] )
}
Assertion : MDX Equals
Generate( [Geography].[Geo].[Continent].members,
TopCount( [Geography].[Geo].CurrentMember.children * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] ) )
Assertion : MDX Equals
Union(
TopCount( [Geography].[Geo].[Continent].[America].children * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] ),
TopCount( [Geography].[Geo].[Continent].[Europe].children * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] )
)
The ALL flag allows for retaining duplicates members/tuples.
Our example is keeping two instances of [America].
Query
Generate( { [Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[America] },{[Geography].[Geo].CurrentMember}, ALL )
Result
{
[Geography].[Geo].[Continent].[America],
[Geography].[Geo].[Continent].[America]
}
Assertion : MDX Equals
{[Geography].[Geo].[Continent].[America],[Geography].[Geo].[Continent].[America]}
Assertion : MDX Equals
Union({[Geography].[Geo].[Continent].[America]},{[Geography].[Geo].[Continent].[America]},ALL)
An example of the generate function using tuples instead of members.
Note the usage of .currentMember returning a tuple and the usage of a named set (s1).
Query
Generate( { {[Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[Europe] } * [Time].[Calendar].[2010] } as s1,
TopCount( s1.CurrentMember * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] ) )
Result
{
( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010], [Product].[Prod].[Licence].[Corporate] ),
( [Geography].[Geo].[Continent].[Europe], [Time].[Calendar].[Year].[2010], [Product].[Prod].[Licence].[Corporate] )
}
Assertion : MDX Equals
Union(
TopCount( {[Geography].[Geo].[Continent].[America]} * [Time].[Calendar].[2010] * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] ),
TopCount( {[Geography].[Geo].[Continent].[Europe]} * [Time].[Calendar].[2010] * [Product].[Prod].[Licence].Members, 1, [Measures].[Amount] )
)
Generates concatenates also string expressions using the delimiter defined by the third expression.
In our example, the set {[America],[Europe]} is converted into "America-Europe".
Query
WITH
MEMBER [Measures].[Names] AS Generate( { [Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[Europe] } AS countries, countries.CurrentMember.Name, "-" )
SELECT
[Measures].[Names] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[Names] AS "America-Europe"
SELECT
[Measures].[Names] ON 0
FROM
[Sales]
Generates concatenates also string expressions (without delimiter).
In our example, the set {[America],[Europe]} is converted into "AmericaEurope".
Query
WITH
MEMBER [Measures].[Names] AS Generate( { [Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[Europe] } AS countries, countries.CurrentMember.Name )
SELECT
[Measures].[Names] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[Names] AS "AmericaEurope"
SELECT
[Measures].[Names] ON 0
FROM
[Sales]
Within the generate iteration auto-exists between France and Spain is applied.
Query
SELECT
Iif( Generate( { [Geography].[Economy].[Spain] }, Iif(( [Geography].[Geo].[France], [Measures].[Amount] ) = 4 , 'exist', 'no' )) = 'exist',
[Time].[Calendar].[2011],
[Time].[Calendar].[2010]
) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
SELECT [Time].[Calendar].[2010] ON 0 FROM [Sales]
