Modifying hard coded URL for sub-reports, embedded datasource reference etc. in SSRS reports

  • Article

I recently faced an issue while migrating our SSRS reports from one server farm to another with reports having an embedded data source, sub-report URL etc. in them. Needless to say but both the farms had different URLs:

For instance:

“Farm A” URL (Source) was something like: abc.example.com

And, “Farm B” URL (Destination) was similar to: xyz.domain.com 

The issue could have been easily dealt with had the number of reports been small. In such a case manually editing the reports could have been an easier fix.

In our situation we had several thousand or reports and manually editing all of those seemed to be a daunting task and might have been error prone too.

Hence, to automate the process, I tried using the native web service exposed by report server in custom Power-Shell / C# code but could not find a way to modify sub-report URL. However, we did found out how to re-point our reports to the new data source using a combination of report server web service and Power-Shell script.

I tried binging a lot but could not find a tool relevant for our purpose which could provide a single solution to fix my entire problem.

So, did my problem get fixed? If yes, then how?

These are the set of questions you might be having by now. Well, the answer is to think about the unconventional ways to fix the problem and that's what I did.

We created our own tool to fix the problem. Click here to download the tool.

Disclaimer:

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS / FILES CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS / FILES ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS / FILES, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.  

How does the tool look like? The tool looks like as shown in the screenshot below: 

 How to I use this tool? Please enlist the steps.

Well, it’s very easy to use to tool. Please find the usage steps below: 

  1. Download all the reports at a single location on your local machine. For example: C:\users\testuser\Desktop (Desktop of testuser)
  2. The reports can be saved all the root level of a folder or any structure within a folder.
  3. Click “Browse” button corresponding to “Select Report Folder” textbox to select the source folder location where you have saved the reports from steps above.
  4. Click “Browse” button corresponding to “Save Reports Location” textbox to select the target folder location to save the modified reports.
  5. Modify the contents of textbox labeled as “Search Text”, with the value you wish to find and replace in the reports.
  6. Modify the contents of textbox labeled as “Replace Text”, with the value you wish to replace “Search Text” within the reports.
  7. Select the checkbox “Do you wish to modify data source?”, in case you wish to modify embedded data source references within the reports.
  8. Click the “Run” button.
  9. The execution might take some time based on the number of reports you’re trying to modify. 

Output:

The output will be in the form of the new reports those will be created at the destination path specified in the step# 4.

I re-deployed the new reports to SharePoint 2010 and they seem to reflect the correct references.

Please Note:

I tested the tool to work with reports deployed to Report Server in SharePoint integrated mode. However, the tool should also work fine for reports deployed to Report Server in Native Mode.

 

Happy Coding!

/Vaibhav