Archive for category Reporting services

Working with Reporting Services in SharePoint 2010

As you probably knows SharePoint 2010 can be used to show off SQL server Reporting Services (SSRS) reports. By registering a SQL server running Reporting Services in SharePoint Native mode you can deploy reports to a sharepoint Document library and have either the ReportViewer applicationpage og the Report Viewer webpart show to report directly in your browser.

Both the Report viewer applicationpage and the webpart gets available when activating the Sitecollection feature “Report Server Integration Feature”

By default when clicking a deployed (or manually uploaded) report (*.rdl file) SharePoint will let you view it in the Report viewer page (_layouts/ReportServer/RSViewerPage.aspx)

The report viewer page takes the URL for the report as a parameter like so:

_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/SQLServerReports/ProductsSharedDataSource.rdl

You can furthermore add different parameters to the querystring to handle Report input parameters and different settings to control how the report is shown and rendered. More on that later

The Report viewer webpart can either takes the url for the deployed report or you can connect the webpart to a list view webpart that show the reports in a documentlibrary and thereby having the report viewer webpart show the report you select ind the List View webpart.

Setting up reporting Services
There are a lot of guides showing how to set up Reporting Services in ShaerPoint so I’ll only add some links here.

http://www.codeproject.com/Articles/88285/Integrating-Sharepoint-2010-and-SQL-Reporting-Serv

Building reports
When building reports for Reporting Services you should use Business Intelligence Development Studio (also known as BIDS) BIDS is part of the Visual Studio 2008 package and can be installed when installing the SQL Server 2008. Why not Visual Studio 2010 you think! Because there are no SQL Server 2010…

Building report are fairly easy and you don’t even have to known very much SQL to make it work. However when creating advanced reports you’ll most likely have to know some T-SQL to make your way.

The next few steps show you how to build a Report that shows all the products from the Northwind sample database. Can be downloaded here

  1. Open BIDS
  2. Create New project
  3. Select Report Server projet template (Fill in name etc.)

4.  Your Solution explorer should look like this

5. Right click Shared datasource and select “Add New Data Source”
6. Give it a name “Northwind datasource” and select Edit
7. Fill in servername, credentials, select database and test connection, press ok

8. We now have our datasource setup – Click ok
9. In Solution explorer right click Reports and select Add New Report

10. Verify that you have selected the newly created datasource “Northwind datasource” and click Next
11. Click Query builder OR type in your TSQL
12. In the Query Designer hit the “Add table” icon and select table: Products

13. Check columns: ProductID, ProductName, UnitPrice, UnitsInStock and click ok to close the Query Designer and then Next
14. Select the Tabular report type and click next

15. add all columns to details section and click Next
16. Select the Cooperate style and click Next
17. Name the report “Northwind products” and click Finish

18. You have now created your first report
19. You can easily pull or push the columns to make to report look nicer
20. And then try to preview

But that isn’t much fun unless you publish it to a Sharepoint Site so lets move on

Deploy the Report to a SharePoint site
The deployment of your report from BIDS its pretty easy but if you just right click your project and hits Deploy you’ll almost certainly fail

First of all you have to create a site to host your report

1. Create a plain teamsite
http://sharepoint/ReportingServices

2. Create a document library for your Reports
http://sharepoint/ReportingServices/Reports

3. Create a document library for your Shared Data Sources
http://sharepoint/ReportingServices/SharedDataSources

4. Now you need to do some configuring in your BIDS to make the Deployment work. Right click your project in Solution explorer and click Properties.
5. Fill in

– TargetDataSourcefolder: http://sharepoint/ReportingServices/SharedDataSources
– TargetReportFolder: http://sharepoint/ReportingServices/Reports
– TargetServerUrl: http://sharepoint/ReportingServices

The Properties page should look like this

6. Click ok
7. Right click your project in Solution Explorer and select Deploy

Your output should look like this

8. Check your SharePoint site and validate that your shared data source and your report are deployed
9. The datasource is deployed

10. And the report is deployed

11. Now click the “Northwind products” report and lets see it work

Url for the Report is: http://sharepoint/ReportingServices/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/ReportingServices/Reports/Northwind%20products.rdl

Passing parameters to the report
When loading the report we can pass different parameters in the querystring

More on that here: http://blogs.microsoft.co.il/blogs/bilive/archive/2011/04/26/passing-url-parameters-to-reports-in-sharepoint.aspx
and here: http://techpunch.wordpress.com/2008/09/17/sql-server-reporting-services-url-parameters-in-sharepoint-integrated-mode/

Leave a comment