Isolating the worst N members in a set
In the previous recipe, we showed you how to identify members with the highest result. In this recipe, we will do the opposite and return those with the lowest result.
Getting ready
Start SQL Server Management Studio and connect to your SSAS 2016 instance. Click on the New Query button and check that the target database is Adventure Works DW 2016.
In this example, we are going to use the Reseller
dimension. Here is the query we will start from:
WITH SET [Ordered Resellers] AS Order( [Reseller].[Reseller].[Reseller].MEMBERS, [Measures].[Reseller Sales Amount], BASC ) SELECT { [Measures].[Reseller Sales Amount] } ON 0, { [Ordered Resellers] } ON 1 FROM [Adventure Works]
Once executed, that query returns reseller ...
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.