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 using recursion

Recursion is sometimes the best way to iterate a collection. Why? Because iterations using set functions (including the GENERATE() function) require that we loop through the whole set. But what if that set is big and we only need to find something in it? Wouldn't it be great to be able to stop the process when we've found what we wanted? Recursion enables just that to stop when we're done.

In this recipe we're going to see how to calculate the average of an average using recursion.

Getting ready

To get started, start a new query in SSMS and check that you're working in the right database. Then write the following query:

SELECT { [Measures].[Order Count] } ON 0, NON EMPTY { Descendants( [Date].[Fiscal Weeks].[All ...

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