Iterating on a set in order to create a new one

There are situations when we don't want to eliminate certain members from a set, but instead execute for each type of loop. This is done using the GENERATE() function. The GENERATE() function applies a set to each member of another set, and then joins the resulting sets by union. In this recipe we'll show you how to create a new set of members from the existing one.

Getting ready

Let's start a new query in SSMS against the Adventure Works DW 2012 database. Then write the following query:

SELECT
   NON EMPTY
   { [Date].[Calendar].[Calendar Year].MEMBERS *
     [Measures].[Sales Amount] } ON 0,
   NON EMPTY
   { [Sales Territory].[Sales Territory Country].MEMBERS }
   ON 1
FROM
   [Adventure Works]

The query returns four years on columns and six countries plus the top level [All Sales Territories] on rows. The result is shown as follows:

	CY 2005	CY 2006	CY 2007	CY 2008
	Sales Amount	Sales Amount	Sales Amount	Sales Amount
All Sales Territories...