Overview:  Discussion of how to alter the RDL code of a Reporting Services (SSRS) report stored in SharePoint (Integrated Mode), so the report can be executed within BIDS.

A Little Background

Recently I was tasked with updating an SSRS report.  So I did a Get Latest in our Team Foundation Server environment & proceeded to open up the SSRS project in BIDS.  I open the report & run it before making any changes.  Huh.  Wait.  Something looks different.  So, I launch my browser & go run the live report, which is stored in SharePoint 2010 running in SSRS Integrated Mode.  Something IS different between the reports!  How can this possibly be?!?

Well, I will not name any names to protect the innocent, but one of the developers had been in a rush to get the report updated so he used Report Builder to change the live report directly in SharePoint.  I proceeded to do much “tsk, tsk, tsking” and looking over my glasses at the poor fella.  So, at this point, my first step is to update BIDS with the latest report code.  We have our BIDS environment integrated with TFS for source control.

Data Sources in BIDS versus SharePoint Integrated Mode

Before we try to sync up the RDL files, let’s do a quick refresher of how data source file extensions are just a bit different when we’re dealing with SharePoint integrated mode:

• BIDS or Report Manager (Native Mode):  RDS file format
• SharePoint Integrated Mode:  RSDS file format

BIDS takes care of this conversion for us when we publish from BIDS to SharePoint.  However, the difference in file formats makes it so we cannot just download the RDL and use it in BIDS – i.e., going backwards requires us to make one modification to the RDL code before the report will execute properly in BIDS.

Steps to Reverse Engineer the Report For Use in BIDS

Step 1.  Download the file.

• Within SharePoint, click the drop-down arrow > Send To > Download a Copy.  Save the RDL file wherever you like.

Step 2.  Copy contents of the file.

• Open the RDL file you just downloaded in your favorite text editor, such as Notepad.  Select all text and copy it onto your clipboard.

Step 3.  (Optional)  Reproduce the error.

Just for grins, let’s try to run the RDL in BIDS, without modifying it, so we can see the error that BIDS returns in this situation.

• Find the outdated version of the report within your BIDS project.  (Note: in my situation, the original RDL did already exist in TFS; it just wasn’t current.  If you need to create a new report, that's fine; the rest of the steps are still the same.)
• Within BIDS, right-click your report in Solution Explorer & choose View Code.  Select all of the XML code & delete it.  Replace it with the RDL code from your clipboard (i.e., the version from SharePoint).
• Now, try to preview the report in BIDS.  You should see an error to the effect of:

      An error occurred during local report processing. Could not find a part of the path … Path\DataSourceName.rsds.rds.

Hmmm, based on the error message we can see that SSRS is looking for the rsds file.  Well, that makes sense since we downloaded it from SharePoint.  The good news is we can fix that very easily!

Step 4.  Update the RDL file.

• Within BIDS, right-click your report in Solution Explorer & choose View Code. 
• Do a Ctrl-F to find the <DataSources> XML tag.  Find <DataSourceReference> & change the “http://url.rsds” to be the name of your data source as you have it set up in BIDS.  Here’s an example:

Before (SharePoint Integrated Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>http://reports.sqlchick.com/Reports/MelissaDW.rsds</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>a1eb2cb6-a837-475c-a924-ff4b4a195c04</rd:DataSourceID>

     </DataSource>

</DataSources>

After (BIDS & Native Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>MelissaDW</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>967ece07-fbff-4580-9968-97ebdd74b6bf</rd:DataSourceID>

     </DataSource>

</DataSources>

• When you preview the report in BIDS now, it should execute properly.  If it doesn’t, then find another existing report in BIDS that uses the same data source.  Copy & paste the whole contents from <DataSource> to </DataSource> and you should be good to go!
• Check-in to TFS, or whatever source control system you are using.

Lastly, it goes without saying, but any time you are modifying the RDL code directly – be super careful!