SSRS: Report Builder 3 Dataset Issues in SharePoint Integrated Mode

This blog post gives a brief overview of a dataset issue when attempting to work with a report which has been migrated from it's existing Reporting Server into a SharePoint Integrated Mode server.

The report works when you run it but when you open it in Report Builder 3 and attempt to edit the dataset you get an error.

1. Duplicating the Issue

Go to a report you have migrated from another document library;

Trigger the pop-up menu and select "Edit in Report Builder";

Expand and double-click a dataset in the menu on the left;

The error reads "The shared dataset cannot be loaded from the server". If you click on the Details button the text is;

The path of the item 'http:///Data Sets/FA Asset Budget Codes and Total Costs.rsd' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidItemPathException: The path of the item 'http:///Data Sets/FA Asset Budget Codes and Total Costs.rsd' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash.

You'll also notice that the "Current report server" box at the bottom left of Report Builder has re-connected to the "old" server the Report used to live on prior to being migrated. If this server doesn't exist then you'll see a connection error along the lines of "can't find the server".

2. What's Causing It

Go to a report you have migrated from another document library;

Hover over it and on the pop-up menu select "Send To" and then "Download A Copy". Open the downloaded file in a text editor of your choice;

The problem is in two keys, both under the key at the top of the file;

• SharedDataSetReference, and
• rd:ReportServerUrl

Despite the migration and re-pointing the Datasets in SharePoint the XML for the Report has not been updated and these two keys (for each dataset) are still pointing at the wrong server.

Ironically if you scroll down to the bottom of the file you'll notice that the rd:ReportServerUrl key for the report is actually set correctly - it's just the ones for the datasets that are incorrect.

3. Fixing The Problem

We have raised this issue with Microsoft and hopefully a solution will be provided soon (and I'll update this). Until that time the only possible fix is to manually download the file and update the affected two keys with the correct values from the server and then upload the file back on top of the original.

Hardly ideal (especially if you have a large number of files). It's possible that you could do a database update and "fix" the XML where it's stored in the Reporting Services DB but it's unlikely Microsoft would support it, and equally likely if you have a quality team who approves/ rejects your changes to a production system you can get them to agree to the risk.

4. Update 06-OCT-2011 ** Fix now available **

I have now written a fix which doesn't involve doing anything unsupported by Microsoft - it's available in this Knol here;

SSRS: Using TS&R To Fix Report Builder 3 Issue with SharePoint Integrated Mode

Enjoy!