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 books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
