Generate

Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.

Syntax

Set expression syntax:

Generate(Set1_Expression, Set2_Expression [, ALL])

String expression syntax:

Generate(Set1_Expression, String_Expression [, Delimiter_String])

Arguments

Set1_Expression	MDX expression that returns a set.
Set2_Expression	MDX expression that returns a set.
String_Expression	String expression that typically uses the name or the property name of the current member of each tuple in the specified set.
Delimiter_String	A valid delimiter is expressed as a string expression.
ALL	Optional parameter. If ALL is specified, the function retains duplicates in the resulting set.

Examples

Set expression example

The following example could be used as a calculated member in the Sprint dimension to retrieve the Last closed sprint from each board:

Aggregate(
  Generate(
    -- set expression1 - filters all boards
    [Sprint].[Board].Members,
    -- set expression2 - for each board, pulls in the last completed sprint
    Tail(
      Filter(
        [Sprint].CurrentMember.Children,
        [Sprint].CurrentMember.GetBoolean('Closed')
      ),
      -- 1st last closed sprint in each board
      1
    ).Item(0)
  )
)

See the example report Active (multiple) sprints story points burn-down in our demo account. The report uses a calculated member Last closed (completed) sprints as a selection option on Pages. The report has a selection of two other similar calculated members Closed Sprints 2nd last cycle and Closed Sprints 3rd last cycle. They represent closed sprints 2 cycles ago and 3 cycles ago from each board.

String expression examples

Here is an example to show a list of version releases based on release dates

Generate(
  -- set expression
  Filter( 
    [Fix Version].[Version].Members,
    DateInPeriod(
      [Fix Version].CurrentMember.get('Release date'),
      [Time].CurrentHierarchyMember
    )
    AND 
    (
      [Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]
    ) > 0
  ),
  -- get fix version name
  [Fix Version].CurrentMember.Name,
  -- delimiter
  ', '
)

See the example report Version releases in our demo account. The calculated measures Version release uses the formula above to show a list of versions with a release dates as a string for each period.

The default Issue property Issue Sub-task keys uses the function Generate to show all sub-task keys for any issue:

CASE WHEN NOT
  -- Sub-task keys property stores max 255 charters
  IsEmpty([Issue].CurrentHierarchyMember.Get('Sub-task keys'))
THEN
  -- generate all sub-task keys list from Sub-task hierarchy
  Generate(
    [Issue.Sub-task].[Parent].GetMemberByKey(
      [Issue].CurrentHierarchyMember.Key
    ).Children,
    [Issue.Sub-task].CurrentMember.GetString('KEY'),
    ','
  )
END