{"id":78,"date":"2010-03-12T02:48:59","date_gmt":"2010-03-12T02:48:59","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=78"},"modified":"2013-01-24T01:52:11","modified_gmt":"2013-01-23T14:52:11","slug":"sql-server-reporting-services-ssrs-iif-statement-divide-by-zero-error","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2010\/03\/12\/sql-server-reporting-services-ssrs-iif-statement-divide-by-zero-error\/","title":{"rendered":"SQL Server Reporting Services (SSRS) &#8211; IIF statement divide by zero error"},"content":{"rendered":"<div id=\"ntsbl-1312987134\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors.<\/p>\n<p>The following example is trying to divide last weeks total hours worked <LWTotal) by the number of days someone worked (LWWorkingDays). The number of days could be zero, and would create a divide by zero error.\n&nbsp;<\/p>\n<p><b>The following errors:<\/b><br \/>\n[csharp]<br \/>\n=IIF(Sum(Fields!LWWorkingDays.Value) = 0,<br \/>\n  0,<br \/>\n  Sum(Fields!LWTotal.Value)\/Sum(Fields!LWWorkingDays.Value)<br \/>\n )<br \/>\n[\/csharp]<\/p>\n<p><b>The Problem<\/b><br \/>\nEven though the number of days = 0 and it should evaluate to the true condition of 0, SSRS still evaluates the false result, and the false result still throws a divide by zero error because it is evaluating:<br \/>\n  Sum(Fields!LWTotal.Value) \/ 0<\/p>\n<p>The solution:<br \/>\nWe need to try to make the false result not error when working days = 0.<br \/>\n<i>The false result<\/i><br \/>\n[csharp]<br \/>\nSum(Fields!LWTotal.Value) \/ Sum(Fields!LWWorkingDays.Value)<br \/>\n[\/csharp]<br \/>\nNeeds to change to the following:<br \/>\n[csharp]<br \/>\nSum(Fields!LWTotal.Value) \/<br \/>\nIIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value))<br \/>\n[\/csharp]<br \/>\nWhy does this work?<br \/>\nWhen working days = 0 the result is going to be 0. So we don&#8217;t really care what the false result is going to be we just don&#8217;t want it to error. So the new IIF statement on the denominator returns 1 when the working days are zero. <\/p>\n<p>This evaluates to:<br \/>\n[csharp]<br \/>\nSum(Fields!LWTotal.Value) \/ 1<br \/>\n[\/csharp]<br \/>\nand this does not error.<\/p>\n<p><b>Final Code Example<\/b><br \/>\n[csharp]<br \/>\n=IIF(Sum(Fields!LWWorkingDays.Value) = 0,<br \/>\n    0,<br \/>\n    Sum(Fields!LWTotal.Value) \/<br \/>\n    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) \/ 60<br \/>\n )<br \/>\n[\/csharp]<\/p>\n<p>In short by converting the zero to a 1, it stops the false result from erroring and allows the calculation to work correctly when it is evaluated.<\/p>\n<p>That is a work around for a problem that should never of existed, good one Microsoft..<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11,13],"tags":[],"class_list":["post-78","post","type-post","status-publish","format-standard","hentry","category-reporting-services","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}