3

I have a report in SSRS 2014 (in SharePoint 2013 integrated mode, although I don't think that matters) that I have two copies of. One copy, when run, runs the following query, and returns data in the report as expected:

exec reports.sp_EvalQuestionsAndAnswers @EngagementCode=N'Engagement1',@LabIDs=NULL,@StartDate='2013-08-01 00:00:00',@EndDate='2014-12-18 00:00:00',@UTCoffset=-6

The second copy, when run, runs the following query, with slightly different parameters:

exec reports.sp_EvalQuestionsAndAnswers @EngagementCode=N'Engagement2',@LabIDs=NULL,@StartDate='2014-04-30 00:00:00',@EndDate='2014-12-19 00:00:00',@UTCoffset=-6

When running both of these in Management Studio, they both return hundreds of rows, as expected. But, while the first report returns data as expected, the second report shows its no rows returned message from its central tablix.

I know the queries are as I've shown them because I can see them in SQL Profiler.

I even deleted the second report, copied the first over, and changed the parameter values (that's the only change I can see, and in fact that's not a manual change - they come from some logic in the report itself based on where the report is published in SharePoint), and had the same result happen. So I know I didn't break the report in Report Builder, because I haven't touched it in Report Builder.

I don't believe neither SSRS shows no records in report but query returns results nor Reporting Services - Multi-value parameter query returning no results match my situation, because I don't have any multi-value parameters.

What else should I be looking at?

Community
  • 1
  • 1
MikeBaz - MSFT
  • 2,938
  • 4
  • 28
  • 57
  • I can see both are running in the same schema - is the database context the same? – Dave.Gugg Dec 19 '14 at 16:40
  • @Dave.Gugg I believe so - SQL Profiler shows the same database in use in both cases, with the same connecting info, and an appropriate run time for the call. – MikeBaz - MSFT Dec 19 '14 at 16:41
  • In the report that doesn't work, triplequadruple check to verify that you've connected the tablix to the correctly-named dataset. – Tab Alleman Dec 19 '14 at 16:45
  • @TabAlleman thanks for your answer. It's the exact same report in both cases, literally the same RDL (just two copies of it in two locations); I copied it over to make sure. – MikeBaz - MSFT Dec 19 '14 at 17:13
  • Is it a possible case of parameter sniffing in the sproc? Are other apps calling this stored procedure or is it just for this report? – Jt2ouan Dec 20 '14 at 16:21
  • @Jt2ouan the same sproc is used across multiple reports, and returns the correct answer when I make the same call I see in Profiler (copied and pasted from the event). – MikeBaz - MSFT Dec 21 '14 at 03:48
  • Have you checked the [ExecutionLog2 view](http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx) on the report server database for a RowCount? Also try checking the tablix itself for filters and unexpected grouping expressions. – stubaker Dec 23 '14 at 02:24
  • @stubaker I didn't know about the ExecutionLog2 view, but I can see the rowcount in Profiler. I think it may be something with the tablix, but I'm not sure yet. – MikeBaz - MSFT Dec 23 '14 at 17:59
  • SSRS is capricious at times. Can you make sure that your display area is big enough. Also, what happens when you try to export this blank report to csv? Is the behaviour the same when going to test the report? When opening the RS, can you run the query - filling in the parameters manually? – WickedFan Dec 23 '14 at 18:28
  • @WickedFan I haven't figured out how to get Report Builder to let me test, etc. against SharePoint integrated mode, unfortunately. I am pretty close to working out it's a combination of a data issue and a filtering issue in the report itself, but still trying to figure it out. – MikeBaz - MSFT Dec 23 '14 at 19:34
  • Make sure that the tablix is set to be displayed via its properties. Also, which sql server are you dealing with? Are you using IE? – WickedFan Dec 23 '14 at 23:26
  • You should be able to see a 'Design' and a 'Run' button top left – WickedFan Dec 24 '14 at 01:55

1 Answers1

3

After substantial investigation it turned out there were two things going on:

  1. A data quality issue between the two versions of the dataset existed where a calculated field in the sproc was not being calculated correctly in the second case due to missing data but was being calculated correctly in the first case. This took a very long time to work out, because the rows were being returned and at first/second/third glance looked valid.

  2. The report tablix had filtering that effectively filtered out the bogus records in the second dataset, leading to the "no rows" result.

MikeBaz - MSFT
  • 2,938
  • 4
  • 28
  • 57
  • 1
    I would rethink the design - splitting the filtering logic across the SP and SSRS code seems problematic (issues like what you describe) and is also inefficient (SP returns rows that SSRS then discards). In general the SSRS filters are pretty awful to work with and best avoided IMO. – Mike Honey Dec 29 '14 at 05:03
  • @MikeHoney I agree 100%. This came up as part of a redesign operation actually - testing the replacement version of the report. – MikeBaz - MSFT Dec 29 '14 at 17:01