Thursday, December 22, 2011

Exporting Linked Reports to another SSRS instance? Yes YOU can!

Bumped into the issue where some Linked Reports had to be exported to another SSRS instance used for SCOM R2. But out of the box that won’t work. So it was time for some investigation and guess what? I found a great tool which enables one to export all catalog items from one SSRS instance to another, among them Linked Reports! But before I tell you more about that tool, let’s start at the beginning.

What’s a LINKED Report?
A Linked Report isn’t a Report at all but a reference to the report definition of an existing Report, plus any settings and properties that one defines for a Linked Report. (This information is to be found here as well).

Basically this explains why a Linked Report can’t be exported directly as a RDL file. Since it’s simply not a RDL file but some kind of shortcut with some predefined parameters. And out of the box, Linked Reports can’t be exported to another SSRS instance.

Along came Jasper Smith
Who is he? Good question! He’s a UK based SQL MVP and runs a blog of his own, all about… SQL! He made a great tool, Reporting Services Scripter (, which enables one to export all items from a SSRS catalog.

The tool came to be in 2005 and has been developed ever since. The last version is built in 2009 and works with SQL Server 2008 R2 as well. I tested it myself and everything goes fine!

Some issues to reckon with
Even though the tool explains it self and comes with a good help file, there are some issues to reckon with:

  1. When running the tool against a x64 based version of SQL server, the path for SET RS in the batch file has to be modified to SET RS="C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\RS.EXE". Otherwise the batch file won’t be able to find the RS.EXE executable.
  2. When you export Linked Reports, you also want to export the predefined parameters like targeted Groups and the lot. In order to export those as well, you have to modify the Options of the tool. Go to the third tab Report, copy these settings and click Apply. Now all predefined parameters will be exported as well.
  3. Many times customized Reports are saved to a separate folder and presented as such in the SCOM R2/OM12 Console, like this:

    When running the tool in order to export the Linked Reports, it’s to be advised to select that folder.
    The contents of that folder (the Linked Reports) will be exported as well. When the script is run on the new SSRS instance, the folder AND it’s contents are automatically created. Saves one a lot of time.

  4. Last but not least: Only import the Linked Reports to the new SSRS instance when the referred Report Definitions and Reports are in place as well as the predefined parameters like Groups and computers for instance. Otherwise one might bump into some unexpected results like a faulty Report.

All credits go to Jasper Smith for building AND sharing such a magnificent tool! Thank you Jasper!

Tool can be found here.

No comments: