O'Reilly logo

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition by Sherry Li, Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required