Data retrieval failed for the subreport
-
Two SSRS issues within a week.. I know, I know. Here's the latest: I'm running a report with 5 subreports. One of the subreports is failing with "Data retrieval failed for the subreport... Please check the log files for more information." Here's the kicker - it's not failing until page 18.
I've done/checked the following:
- Checked for null values being passed: Nope, no nulls
- Checked parameter type and data types: Both ints
- Tried deleting the .data files
- Deployed to Report Manager: Still failing there
- Rebuilt the Solution
- No additional filters on the subreport
- Checked the logs at Program Files\Microsoft SQL Server\<Instance>\Reporting Services\LogFiles: Didn't see any visible issueAny other thoughts on why this could be failing halfway through are much appreciated!
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller -
I'm not sure what would be causing this. Here's some approaches I've used to troubleshoot similar situations:
- I've had a few situations where re-deploying a report retained the previous parameters or parameter types on the report server, even though I'd changed the parameters in the redeploy(I'm a little foggy - this was a while back). I deleted and re-uploaded them to success. A better approach would be to copy both reports, modify the copy main report to point to the copy sub-report, and deploy both. Run the copy of the main report and see if the copied sub-report fails. If that works, then consider deleting and re-uploading the originals.
- Run a Profiler trace and see if the problem query execution returns different results than the previous executions of the query.
- Get the parameter values causing it to fail by adding a rectangle to the list/table holding the sub-report main report, putting the sub-report and some labels to capture the parameter values inside of that rectangle.
I'm surprised to learn that sub-query execution isn't logged to the ExecutionLog table in the ReportServer database. That makes troubleshooting this a little harder!
-
I was able to figure out what was causing this issue, and it was data related. In my dataset I was using NTILE(7) to assign column numbers and split the records for each state zips into 7 columns. I then set up a column grouping on the assigned column. From the screenshot below, you can see that CA had no problem. The issue was when I tried to group the NV data, where the assigned column number only went up to 5. I guess good old SSRS didn't know what to put in the remaining 2 columns. It was a little harder to find because the data retrieval error was thrown on all future pages, not the group that failed to meet 7.
I ended up filling in the gaps with CROSS APPLY and NULL zips for state groups that had less than 7 records, and the report is now working fine.
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply