Add SSRS report as dashboard to sharepoint

by Ewald Hofman 2. June 2009 06:58

It is possible to add a SSRS report to your Project Portal. In this way you can create a nice dashboard on you portal home page. This dashboard can consist of multiple reports, or can vary for the different roles in your team.

On the Site Actions menu, choose the Edit Page action

Click on Add a Web Part

Choose the Page Viewer Web Part, and click the Add button

Now a new web part is added to your page

Click on “open the tool pane”, and a nice tool windows pops up

Now you have to add the link to the report. To specify the link you can use: _layouts/tfsredirect.aspx?IsReport=1&ReportName=Remaining+Work

Where Remaining+Work is the name of the report you want to show.

To further customize your report, you can check out the Appearance, Layout and Advanced property groups

Advanced options

When you want to have more options when showing the report, you can add the full URL to the report and use the URL Access Parameters of Reporting Services (http://msdn.microsoft.com/en-us/library/ms152835.aspx).

The base link to the report is http://MyTfsServer/reportserver?/MyTeamProject/MyReport.

For example, when you have the following values

TFS Server = TfsServer

Team Project = Demo

Report = Work Item Hierarchy

That will make the following url: http://TfsServer/reportserver?/Demo/Work+Item+Hierarchy.

The reportserver may differ. You can find this to open the report site from the Team menu item in Visual Studio. When it says http://TfsServer/Reports_XXX, then the link will be http://TfsServer/reportserver_XXX?/Demo/Work+Item+Hierarchy.

Now you have access to the report, it is time to customize the report. First you have to indicate what should be done with the report. Default you want to render (show) the report. For this you have to add &rs:Command=Render to the URL. This will make http://TfsServer/reportserver?/Demo/Work+Item+Hierarchy&rs:Command=Render

When adding a report as dashboard to the portal, you want to hide the toolbar. To do this you have to add the &rc:Toolbar=false to the URL. This will make http://TfsServer/reportserver?/Demo/Work+Item+Hierarchy&rs:Command=Render&rc:Toolbar=false

Of course you want to tweak the default parameters also to only show the information you are interested in. To do that add &[Parameter name]=[Parameter value]. You can find the parameter name in the properties of the report itself.

So if you want to filter on the Area, then you have to specify AreaParam as parameter name. To filter on the Area \Demo\MyFirstArea, you will have to create the following URL: http://TfsServer/reportserver?/Demo/Work+Item+Hierarchy&rs:Command=Render&rc:Toolbar=false&AreaParam=\Demo\MyFirstArea

The Iteration path is a multiselect parameter to pass multiple values for this, just add twice the IterationParam to the URL. To add the filter on the iteration \Demo\Iteration0 and \Demo\Iteration1, use the following URL: http://TfsServer/reportserver?/Demo/Work+Item+Hierarchy&rs:Command=Render&rc:Toolbar=false&AreaParam=\Demo\MyFirstArea&IterationParam=\Demo\Iteration0&IterationParam=\Demo\Iteration1

[Added at 09-06-2009: update to Iteration and Area parameters for reports based on the OLAP cube like Remaining Work]

Many of the reports have an iteration and area parameter. To be able to add a value to these hierarchical parameter is somewhat tricky. In a blog post by Andrew Lynes you can find how you can solve this.

For reports that are based on the OLAP cube (such as the Remaining Work), the filtering is a bit different. It is out of scope of this post to describe how it works, but what you have to know is how you can add a filter based on iteration. The syntax is

IterationParam=[Iteration].[Parent_ID].%26[156]

The 156 is a node in the iteration hierarchy, which you can find in the Iteration table in the TfsWarehouse database. It is unfortunately not possible to have a nice looking URL to filter on Iteration. When you want to show the Remaining work with a filter on iteration, the URL is something like:
http://TfsServer/reportserver?/Demo/Remaining+Work&rs:Command=Render&rc:Toolbar=false&IterationParam=[Iteration].[Parent_ID].%26[156]

Tags:

Reporting

Comments

6/16/2009 2:01:52 AM #

trackback

Add SSRS report as dashboard to sharepoint

You've been kicked (a good thing) - Trackback from DotNetKicks.com

DotNetKicks.com |

8/12/2009 2:31:51 PM #

Greg Walker

I'm doing this exact thing, and I have report images that refuse to display properly in SharePoint.

Any one have any ideas or come across this before?

Thanks,
Greg

Greg Walker United States |

5/9/2011 2:03:26 AM #

Kuantan

I agree with your opinion.your article is excellent. I have been examinating out some of your stories and i can state pretty nice stuff. I look forward your next article. Its a great post.

Kuantan United Kingdom |

Comments are closed

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen


ClusterMap

Widget Statistics not found.

There is an error in XML document (0, 0).X

Recent comments

Comment RSS