Handling division by zero errors

Handling errors is a common task, especially the handling of division by zero type errors. This recipe offers a common practice to handle them.

Getting ready

Start a new query in SQL Server Management Studio and check that you're working on the Adventure Works database. Then write and execute this query:

WITH MEMBER [Date].[Calendar Year].[CY 2012 vs 2011 Bad] AS [Date].[Calendar Year].[Calendar Year].&[2012] / [Date].[Calendar Year].[Calendar Year].&[2011], FORMAT_STRING = 'Percent' SELECT { [Date].[Calendar Year].[Calendar Year].&[2012], [Date].[Calendar Year].[Calendar Year].&[2011], [Date].[Calendar Year].[CY 2012 vs 2011 Bad] } * [Measures].[Reseller Sales Amount] ON 0, { [Sales Territory].[Sales Territory].[Country].MEMBERS ...

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.