O'Reilly logo

MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook by 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 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 a for-each type of loop. This is done using the GENERATE() function. In this recipe we'll show you how to create a new set of members from the existing one.

Getting ready

Start a new query in SSMS and check that you're working on the right 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 4 years on columns and 6 countries on rows. This recipe shows how to get a set of best months, one for each year. ...

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