Clever Geek Handbook

Simple SProc Call in Reporting Services Returns No Data, but Query Works

I have a report in SSRS 2014 (in SharePoint 2013 integrated mode, although I don't think it matters), I have two copies. One instance, on startup, executes the following query and returns the 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, on startup, 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 them in Studio Management, they return hundreds of rows, as expected. But while the first report returns data as expected, the second report shows its message no rows returned

from its central scoreboard.

I know the queries are the same as I showed them because I can see them in SQL Profiler.

I even deleted the second report, copied the first code and changed the parameter values ​​(which is the only change I see, and it is not really a manual change - they come from some logic in the report itself, based on where the report is published to SharePoint) and the same result happened. So I know that I didn't rip the report in Report Builder because I didn't touch it in Report Builder.

I don't believe SSRS does not display records in the report, but returns query results , and Reporting Services - multivalued parameter query that does not return results is appropriate for my situation because I do not have multivalued parameters.

What else should I watch?

+3


source to share


1 answer


After significant investigation, it emerged that two things had happened:



  • The data quality problem between the two versions of the dataset existed where the calculated field in the sproc was not calculated correctly in the second case due to lack of data, but in the first case it was calculated correctly. It took a very long time to work because the rows were coming back and the first / second / third look looked valid.

  • The report column had filtering that effectively filtered out the dummy records in the second dataset, resulting in a "no rows" result.

+3


source






More articles:


All Articles