title: SSRS IF Statement with Less-than equal to returns bad counts
body: <p>SSRS 2008 R2 - Report Builder 3.0 - Querying SQL Server 2008 R2</p>
  
  <p>I have a report that is getting data from a database that has a "Last_Executed" column.  My data set is returning a query that has a column dedicated to a <code>DATEDIFF()</code> that returns the number of days since executed at run time. What I now need is a filter that keys on the last 7 days. </p>
  
  <p>You may be saying, "just make another data set and set your query to only return these" and I may just break down and do that but I can't help but think there is a better solution with the data I have.</p>
  
  <p>There is a single text box with a placeholder that I want to show this count.  I have tried (If the syntax for the fields is off it may be just my memory, the if check is what I am questioning):</p>
  
  <pre><code>=SUM(IIF((Fields!MyData.Value &lt;= 7),1,0),"MyDataSet")
  
  =SUM(IIF(Cint(Fields!MyData.Value &lt;= 7),1,0),"MyDataSet")
  
  =COUNT(Fields!MyData.Value &lt;= 7)
  </code></pre>
  
  <p>The scope had to be added outside the "IF" to allow for the reference to the data in question of course.  I have tried variations of the "cast" but none returned the proper counts.  The result set returns 1400 items and my test data should return 41 based on the date check.  I am getting 581, 581 and 1400 respectively and if I remove the "&lt;" and just set the value equal to 7 I get my expected 41 in both the first 2 conditional checks.</p>
  
  <p>Why wont the "&lt;=" work with the SUM - IIF?</p>
  
  <p>Solution Found:</p>
  
  <p>Turned out the NULL returns from the query were causing the bad counts in the sum. The final sum expression ended up being:</p>
  
  <pre><code>=SUM(IIF(IsNothing(Fields!MyData.Value),0,IIF(Fields!MyData.Value &lt;= 7,1,0)),"MydataSet")
  </code></pre>
  
  <p>The 0 on true for the <code>IsNothing</code> check fixed my issue, shame I didn't check for NULL errors first. Thanks for helping :)</p>