MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Iterating on a set 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 2016 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 ...