All things Data Related....

On this site I will be posting entries related to Data platform and analytics that I learn and believe could be valuable learning to others who want insight from their data

Incorporating Reporting Services Reports in Word and PowerPoint

Incorporating Reporting Services Reports in Word and PowerPoint

  • Comments 6
  • Likes

My colleague Alan Eldridge, BI Technology Specialist at Microsoft, put together a great post on incorporating Reporting Services reports in Word and PowerPoint. With his permission I am making it available to you.

This post talks about incorporating the report into Office/PowerPoint as opposed to exporting to these formats. The report information is positioned at the selected location in the document, and for it to stay as refreshable content.

1) Incorporating in Microsoft Word

I thought this might be possible, using Fields in Word, and with some poking about I managed to get it to work.

Firstly, open your Word document and insert a Field object:

image
Make the field type "Include Text" and paste in the URL to your SSRS report.

image
The format of the URL looks like this (again, assuming you are using SSRS in integrated mode with SharePoint):

http://YourSharePointServer/ReportServer?http://YourSharePointServer/Reports/Test.rdl&rs:Command=render&rc:Toolbar=False

More information on the URL format can be found here:

http://techpunch.wordpress.com/2008/09/17/sql-server-reporting-services-url-parameters-in-sharepoint-integrated-mode/

You can also use a "LINK" field to embed the report output - this gives a little more flexibility in how the content is to be interpreted.

To refresh the object, simply right click on the report content:

image
Enjoy!

2) Incorporating in PowerPoint

Unfortunately there isn't any native way to do the same thing, as PPT doesn't have the same concept of the Field object that we can use as a container for the SSRS output. We can embed the report output as an image into the document by inserting an image and pasting in the URL to the report as shown below:

http://yoursharepointserver/ReportServer?http://YourSharePointServer/Reports/Test.rdl&rs:Command=render&rc:Toolbar=False&rs:Format=IMAGE

However, this doesn't retain the link information so the image is not refreshable.

Enter a very useful PPT addin called LiveWeb. It allows you to embed a web frame onto a PPT slide and have it refresh each time you run the slide show. So by providing the appropriate URL to the report viewer we can have updating SSRS output directly into our PPT. Woot!

image

A couple of caveats:

  • It seems the addin only works with the 32-bit edition of Office. I was getting error messages when I tried to use it with my host machine 64-bit installation.
  • It's an addin so it's a separate file that would need to be deployed to each client machine that need to run these slide shows.
  • The content of the web page is only shown when the slideshow is running - in edit mode there is a placeholder image as shown below.

image
UPDATE:

At the suggestion of Shashank (Me Smile), I did some further fiddling to see if we could get a similar result by embedding a Word document with an embedded IncludeText field into the PowerPoint slide. And as it turns out, it works!

Firstly, open Word and link in your required SSRS output as documented in point 1 above. Then select the part of the Word document that includes the field and chose "Copy". Hint: to accurately select the field, right-click on it and choose "Toggle Field Codes".

image
Then go to PowerPoint and on the slide where you want the content, insert an Object and select a Microsoft Word Document - this will place a Word frame on the slide:

image

You can then paste the selected text from the Word document into this frame and the Field object will still allow you to refresh the SSRS content:

image

 

Thank you once again Alan for allowing me to post what I believe a lot of BI users will find handy.

Comments
  • You Have forgotten to mention system requirements, aka

    Client

    -WIN 7/Win Vista

    - Word 2010, PP 2010 only ?

    Server :

    - sharepoint 2010 only

    - sql server 2008 R2 only

    Thanks

  • Thank you ST. We had tested this with the following configuration:

    Client - Windows 7, Word and PowerPoint 2010

    Server - SharePoint 2010 and SQL Server 2008 R2

    However based on the features I am leveraging this should work with the following:

    Client - Windows XP/Vista/7, and Office 2007 and later

    Server - SharePoint 2007 or SQL Server 2005 reporting services and later

  • An Example of Sharepoint Integrated mode report (Seems that there is a change on sharepoint URL construction) - Report for this example is stored at Powerpivot gallery and the name of the report is TailSponSSRS.....

    http://sql2012rc0/_vti_bin/ReportServer/PowerPivot%20Gallery/TailSpinSSRS.rdl?http%3a%2f%2fsql2012rc0%2fPowerPivot+Gallery%2fTailSpinSSRS.rdl&rs:Command=Render&rc:Toolbar=false

    Reference :- msdn.microsoft.com/.../ms154042.aspx

  • Nice article

  • Hi Shashank,

    I am attempting the above with embedding SSRS reports into a Word document however the Field Properties in Word only accepting a URL of 255 characters. The URLs I have are much longer (due to page widths, margins, outputformat, etc), do you know if there is an option to increase the default length of URL that Word will accept?

    Thank you

  • AWESOME. Other options for doing this are a pain. Good stuff.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment