Problems with Shared Datasets and Subreports in SSRS
If you're using shared datasets to populate subreports in Reporting Services you might have encountered an error when you preview your reports. This short blog explains what the problem is and offers a few simple suggestions to work around the issue.

Posted by Andrew Gould on 23 April 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Problems with Shared Datasets and Subreports in SSRS

A couple of subscribers to our YouTube channel have recently been having issues with subreports in SQL Server Reporting Services.

The Problem

The error occurs when previewing a report within Business Intelligence Development Studio with an error message informing you that your subreport is unable to retrieve any data:

Error message

An example of the error message that you might see.

 

The Cause

After eliminating the most obvious causes of the problem (using the wrong data type for the parameter, and misspelling the parameter name), I discovered that there is a known issue with subreports which use a shared dataset in SSRS 2008 R2.

Microsoft have acknowledged the issue but as yet have not issued a fix for the problem in SSRS 2008 R2. The problem is resolved in later editions of the software however.

The Workaround

As Microsoft haven't actually fixed the problem there are several ways you could work around the issue:

  1. Upgrade to a later version of the software - not a very satisfactory answer!
  2. Avoid using shared datasets to populate your subreports.
  3. Ignore the problem at design time and deploy the report and subreport anyway. Strangely, the issue resolves itself once the report has been deployed!

The image below shows the report and subreport working successfully after they have been deployed:

Deployed report

Deploying the report solves the issue, although it does make it difficult to create a neat design.

 
This blog has 0 threads Add post