Monday, June 7, 2010

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II

If you need to create an extremely user-friendly environment for Reporting Services 2008 R2, SharePoint 2010 Integrated Mode is the best solution. Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part 1 explains the initial configuration up to SharePoint integration. This post discusses creating a web site for Reporting Services with relevant libraries and content types, finally publishing reporting from BIDS.

Let’s start creating a Web Application. Go to SharePoint Central Administration and click Manage Web Applications under Application Management. Once Application Management is open, click on New to create a new web application.
newwebapp

Some of the attributed in New Web Application Form as as follows;

  • Authentication
    Select Classic Mode Authentication
  • IIS Web Site settings
    Select Create a new IIS web site option. I have named it as Report Server – 10005 and set the port as 10005.
  • Security Configuration
    Leave default values
  • Public URL
    In my case, URL is http://DP03:10005
  • Application Pool
    Make sure Create new application pool is selected. I have named it as ReportServer – 10005.
  • Database Name and Authentication
    Set the SQL Server name for Database Server. I have named the database as WSS_Content_ReportServer_10005.

webapp

Click OK to save setting. Once the process is completed, Application Created windows is appeared. Click OK to continue. New web application will be listed in Web Application window.
webapp2 
Next step is creating a site collection. Go back to Central Administration and click create site collections under Application Management. This opens Create Site Collection window. Let’s ceate one;

  • Make sure that correct web application is selected (In my case, it is http://DP03:10005/.
  • Add Title as Report Server.
  • Let’s use the root for the Web Site Address. Do not select sites from the drop-down. Leave it blank.
  • Select the Template as Team Site.
  • Set both Primary Site Collection Administrator and Secondary Site Collection Administrator. Since I have everything in one machine, I set both as DP03\Administrator;.
  • Click OK to create the site.
    sitecoll

Once done, a message will be appeared saying Top-Level Site Successfully Created. Click on the link to go the site (In my case, link is http://dp03:10005). This is what I see;
site

Site needs libraries for holding reports and connections. Let’s create two libraries for the site.

  • Click on Libraries link in left pane. It lists all document libraries available.
  • Click the button Create for creating a new library.
  • Select Document Library and Set the name as Reports.
    lib1
  • Click Create for creating the library. Once the library is open, click on Library Settings for adding content types.
     lib2
  • When Library Settings is open, library related things such as General Settings, Columns, and Views are shown. By default, it does not show Content Type. In order to enable content type, click Advanced Settings under General Settings. Once the window is open, select Yes for Allow management of content types.
    adv
  • Click OK to save settings. Note that now the Content Type is shown under Library Settings. Click Add from existing site content types link under Content Type. This opens Add Content Types page.
  • Select Report Server Content Type from Content Types From drop-down. Then move Report Builder Report from left listbox to right listbox.
    Note that if you are not seeing “Report Server Content Types”, that indicates that Add-In has not been properly installed”.
    contenttypes
  • Click OK to save settings. Report Builder Report content type is now appeared under Content Types of Reports library.
    contenttypes2
  • We have to create another library for data connection. Follow same steps for creating the library. Name it as Data Connections. Then enable Allow management of content types as did for Reports library. Then add Report Data Source content type for it.
    contenttypes3

Now SharePoint 2010 is ready for holding reports from Reporting Services 2008 R2. Let’s try to publish some reports to this. Open Business Intelligence Management Studio and create a Reporting Services project. If you have AdventureWorks 2008 Sample Report Project, you can use it for testing. Once the project is open, get the properties of the project. Set following properties;

prp

Now deploy reports and see. Reports will be published to SharePoint site we created. Once published, open the home page of the site (http://dp03:10005) and click on Reports link in left pane. It lists all the reports published.
reports

Click one of reports and see whether it works fine. Here is the output of Company Sales 2008 report.
reports2

All done. Reporting Services 2008 R2 is integrated with SharePoint 2010. I will be exploring more on this, specially SharePoint related things. Once they are explored, I will publish them.

Update: Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

15 comments:

Buddhika Malwana said...

Hi,
Very useful posts(both part 1 and 2). I'm also going to setup the SQL Server Report Server (SQL Server R2) with SharePoint(MOSS 2010) integrated mode very soon in one of our client side development environment. Hope this will save my tail :)

Artem said...

Hi,

Thanks a lot! Very clear and useful! Keep on making some interesting articles. For instance I'm going to set up a dashboard in SP2010 with SSRS.

Cheers!

Letchu said...

I agree with the rest of the guys. Excellent post. It helped me setup a demo machine quickly without going through too much of materials. I like your geospatial reporting posts too. Keep up the great work Dinesh.

HanoYs said...

Dear...
Thanks alot again...

I'm stuk with this error
Error 1 The path of the

item '/http://epm2010/PWA/ProjectBICenter/Reports' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash.


and off course the report server is in a SharePoint Integrated Mood

Any Help...
ASAP...

Bhaskardeb said...

I am also having the same error. as soon as it will see a ':', it is adding '/' by default.

any help will be highly appreciated.
ASAP.....

Dinesh Priyankara said...

Hi HanoYs, Bhaskardeb,

Sorry for the delayed reply. Can you tell me the exact point of the error? Are you getting it when you configure at SharePoint or with BIDS, or browsing?

Thanks

pduffy said...

Hello,

When deploying from BIDS on the report server to Sharepoint, a Reporting Services Login box appears and deployment cannot be completed. It does not matter what User name and Password are used. Do you know what configuration is missing?

Thanks

Buddhika Malwana said...

Hi pduff,

This kind of thing may occure when the Report deploying user (Windows user you loged in) does not have the permission on the Report folder in the Sharepoint site. Try to give full controll to the report folder (Where you have specified in the Report Project properties). or else add the user to Report site's Site Collection Admins list and try.

Thank you
Buddhika

P said...
This comment has been removed by the author.
graham said...

Thanks Dinesh, this saved me some hours. Good Work

Raghu said...

Good Day Dinesh,
You did splendid job, i really appreciate you for your detailed explanation.
I tried as you explained,the deployment process has finished successfully, but when i check the reports on Sharepoint server it doesn't showing any reports,i don't know where did i made a mistake, kindly let me know, you would be much appreciated.
Thank you.

Dinesh Priyankara said...

Hi Raghu,

Thanks for the comment.

What do you see when you go to Reports library? Did it success when you deploy from BIDS?

Rajiv said...

Hi Dinesh,

Thanks for this post.

I have deployed the report as per the steps provided but while running the report from sharepoint i am getting an error message as
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
ORA-12638: Credential retrieval failed

But the report is running fine from BIMS.

Thanks
Yamini

Murali said...

Thank you very much Dinesh

gonzo said...

very useful.
I still don't get how to export a report into a sharepoint library though (without exporting onto the local machine and then saving up to the library - which is clunky)