0

SSRS 2008 R2 - Report Builder 3.0 - Querying SQL Server 2008 R2

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 DATEDIFF() 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.

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.

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):

=SUM(IIF((Fields!MyData.Value <= 7),1,0),"MyDataSet")

=SUM(IIF(Cint(Fields!MyData.Value <= 7),1,0),"MyDataSet")

=COUNT(Fields!MyData.Value <= 7)

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 "<" and just set the value equal to 7 I get my expected 41 in both the first 2 conditional checks.

Why wont the "<=" work with the SUM - IIF?

Solution Found:

Turned out the NULL returns from the query were causing the bad counts in the sum. The final sum expression ended up being:

=SUM(IIF(IsNothing(Fields!MyData.Value),0,IIF(Fields!MyData.Value <= 7,1,0)),"MydataSet")

The 0 on true for the IsNothing check fixed my issue, shame I didn't check for NULL errors first. Thanks for helping :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
S.G.
  • 109
  • 14

1 Answers1

1

I don't think the COUNT will work correctly - SSRS doesn't evaluate Booleans to Integers correctly.

Your second SUM may have the same issue when converting the Boolean MyData <= 7 to an INT with CINT.

I don't see why this doesn't work as expected:

=SUM(IIF(Fields!MyData.Value <= 7, 1, 0), "MyDataSet")

You say when you remove the < the result is correct but I don't see how. That would only get the records where it was executed 7 days ago but not less.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • I found the issue, its the NULL's, can't believe I didn't check that first! I am trying to remove the NULL's from the counts now but getting bad return number still. For example: `code`=SUM(IIF(IsNothing(Fields!MyData.Value),IIF((Fields!MyData.Value <= 7),1,0),0),"MyDataSet")`code` Still getting the NULLs here though :) – S.G. May 10 '16 at 00:10
  • Okay my sum was bad in that last comment, I was adding on successful finds of null so I would get 1400. Changing it to only add 1 on fails of the first IF should have worked but now I get an error `code` =SUM(IIF(Fields!MyData.Value = "NULL",0,IIF(Fields!MyData.Value <= 7,1,0)),"MyDataSet") `code` I will get it sooner or later! – S.G. May 10 '16 at 00:36