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

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.