Creating a reporting portal with Reporting Services in SharePoint integrated mode

Reporting site

Reporting Services in native mode has a web site that is designed to allow users to browse and run reports. It allows users (with the correct permissions) to set up a directory structure and organize reports as they please. Running Reporting Services in SharePoint integrated mode means that SharePoint becomes the site that users use to see what reports are available and run them.

Users certainly could publish reports to any location in SharePoint, but it is usually desired to have some sort of a report repository: a location where users can go to view all the available reports for a given set of data. To do so, reports have to be published to SharePoint, document libraries need to be configured, and perhaps, web part pages need to be set up.

Document Libraries

Reports can be published to any document library. They do not have to be in a “report center”. They do not have to be in a library named “reports”. So create a document library (or identify an existing one).

Create and publish reports in VS with RS in SharePoint integrated mode

To publish while in integrated mode, go to the report project properties (on the project menu), and specify the SharePoint site to publish to:

Note: the folders should not end with a “/”. Also, the targetserverurl should be the site, the others should be paths to the libraries where you want the reports to be published.

For more info, see: http://blogs.msdn.com/bimusings/archive/2007/01/16/publishing-reporting-services-reports-to-moss.aspx

Again, reports can be published from VS to any library. In addition, the .rdl files can be uploaded to any library. However, if it is desired for users to be able to select a new report from the “new” dropdown menu, then the report server content types need to be associated with the library. Instructions for this are here: http://msdn.microsoft.com/en-us/library/bb326289.aspx

An additional note on the report builder report content type:

If you have multiple content types available in a library, selecting one of them from the “new” drop-down should create a new item of that content type. However, that doesn’t seem to work for the report builder report content type. Selecting a new report builder report does in fact create a new report, but the content type will be whatever the default is for the library. So, consider changing the default content type to “report builder report”, if possible. This can be done from settings à document library settings àcontent types à change new button order and default content type.

Additional Columns (metadata)

Reporting services in native mode allowed users to create folders and subfolders to organize reports. That can also be done in SharePoint. However, that is not the recommended model to use in SharePoint. Instead, the usual suggestion is to add columns to a library and use them to organize the items in the library.

Typical columns to add would be report category and report description. Category would typically be set up as a choice field, and would be used with filters and views. After the columns are created (settings à add column), a new view can be added that displays those columns instead of the default selections. In addition, the view could be set to either sort or group by the category:

Note: that if a column is marked as required, reports can still be published from either VS or Report Builder without specifying a value for the field. The next time a user modifies the properties of the report, it will prompt them to enter a value for the required field, but not until then.

If subreports are commonly used, then consider adding a choice to the category field for subreports. When users view the list of available reports, they generally don’t want to see the additional clutter of subreports that shouldn’t be run on their own. If an additional category is created, then the views can be modified to not show that category.

View available reports on a page (default.aspx)

Site actions à edit page à add web part. Then select the library that contains the reports. Once it has been added, you may want to edit the web part properties and change the default view so that it displays a specified set of reports.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s