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.
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... |