Wednesday, February 25, 2009

Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

Step 6 - Report Creation and Deployment in SharePoint 2007 with Business Intelligence Development Studio (BIDS)

Introduction

This post is the last of a group showing how to use SQL Server 2008 Reporting Services with SharePoint 2007 aka in SharePoint integrated mode. It is a tutorial that shows how to create and deploy a SQL Server 2008 Report from Business Intelligence Development Studio (BIDS) and how to consume this Report.

There are three ways of consuming it:
  • You can consume it from the report Server Web Site
  • You can consume it from a SharePoint 2007 site using two ways.
    • using RSViewerPage.aspx
    • using SQL Server Reporting Services Report Viewer Web Part
In this tutorial, I will show how to create a simple tabular report, deploy it to a SharePoint site, and consume it using the three previously mentioned ways. I have precisely detailed operations performed in the SharePoint environment in order to allow SQL Server and Reporting Services Specialists that does not know SharePoint very well to be initiated to the product.

The previous steps are:
2- Prerequisites
3 - Tutorial Overview

This tutorial is compatible both, with Windows SharePoint Services 3.0 and MOSS 2007, but in MOSS 2007 you have much more offered functionalities like data connection library.

As often in most of my tutorials, the goal is to give user the quicker way of experimenting a product or a products combination, and thus, I will use BIDS Report Wizard to create a report as quickly and simply as possible.

4 - Tutorial

4.1 Step 1 - Create Report Document Library in a SharePoint Site
1 - Open the SharePoint Site we have created in the second post of these posts group



2 - Click "Site Actions", then click Create



3 - On "Create" page in "Libraries" column, click "Document Library"



4 - Type "Reports" as a name for the Document Library, and select "None" for Document Template, click "Create"



In the Quick Launch Menu of your SharePoint Site you notice a new link corresponding to the Document Library you have just created.



4.2 Step 2 - Create Report with the Wizard of Business Intelligence development Studio (BIDS)
1 - Open Visual Studio 2008.

In Start Menu, point All Programs, point Microsoft Visual Studio 2008, click Microsoft Visual Studio 2008.



2 - Choose a new project
In File Menu, point "New", Click "Project".



3 - Select "Report Server Project Wizard", click "OK"



4 - Pass Report Wizard Welcome dialog
click "Next"



5 - Edit Data Source
In Report Wizard "Select Data Source" dialog click "Edit"



6 - Choose and test Data Source
In "Server Name" drop down list, choose your server.
In "select or enter a database name" drop down list, select "AdventureWorksLT2008"
Click "Test Connection"
Click "OK" on the "Test results" message box.



7 - Close the "Connection Properties" windows.
Notice that the "Connection string" field is filled,
Click "Next"



8 - Open Query Builder On "Design the Query" page, click "Query Builder" button.



9 - Add a Data base Table.
In the Query Designer window, click the "Add Table" icon.



On the "Add Table" Pop Up select "Product (SalesLT)".



10 - Select the table columns
In the "Product (SalesLT)" table control, select the following columns:
  • Name
  • productNumber
  • Color
  • StandardCost
  • ListPrice
  • Size
  • Weight
Click OK



11 - Close Query Builder
Notice that the Transact SQL request has been generated and appears in the "Design the Query" dialog.
click Next



12 - Select the Report Type In "Select the Report Type" dialog, select "Tabular", click "Next"



13 - Pass "Design the Table" dialog
Click "Next".



13 - Choose the Table Style
In "Choose the Table Style" dialog select "Forest", click "Next".



14 - Choose the Deployment Location In "Choose the Deployment Location" dialog,
  • for "Report Server" field, type the URL of your SharePoint Site where you plan to deploy the report
  • for "Deployment Folder" field, type the URL of the "Reports" document Library that we have previously created in the SharePoint site
Click Next





14 - Define the Report name
In "Completing the Wizard" dialog, type the report name (I let the default name).



15 - Click "Finish" to close the wizard, and go to the main window of BIDS.



16 - Preview the report in BIDS.
In BIDS click the Preview tab.



Click the Design Tab
4.3 StStep 3 - Define Report properties to prepare deployment in Business Intelligence development Studio (BIDS)
1 - Define project properties
In BIDS Solution Explorer pane, right click Report Project1, click "properties".



In "Report Project Property Pages" dialog, in Deployment Section, fill the following fields that way:
  • TargetDataSourceFolder: Use the same URL that the one of the Report Document Library. In MOSS 2007 it would be different, because you can define special document libraries especially shaped for Data Sources.

  • TargetDataSourceFolder: It is the URL of the Report Document Library as defined with the Report Wizard.
  • TargetServerURL: It is the URL of the SharePoint Site where was created the "Reports" Document Library, as defined with the Report Wizard.



4.4 Step 4 - Build report and deploy report in SharePoint 2007 with Business Intelligence development Studio (BIDS)
1- Launch Build action
In BIDS Top Menu, click build, then click Build report Project1.



2- Check Build action
In BIDS Output pane, check the Build result.



3- Launch Deployment
In BIDS Top Menu, click build, then click Deploy report Project1.



4- Check Deployment
In BIDS Output pane, check the Deployment result.



4.5 Step 5 Check report deployment in SharePoint 2007 and consume it using RSViewerPage.aspx
1 - Open the SharePoint "Reports" document Library.
Go to your SharePoint site, on the home page in the left Quick Launch Menu click "Reports".



The "Reports" SharePoint Document Library welcome page is opening.
You notice that a file corresponding to the .rdl file deployed with BIDS is present:
Report1



2 - Consume Report1 using RSViewerPage.aspx
Double click the Report1 link or file icon.
The RSViewerPage.aspx is opening and the report is generated.



Do not close the page, but navigate backward to go back to the SharePoint site. You can do it with you browser, or using the bread crumb.
The RSViewerPage.aspx has unfortunately not clear navigation controls linked to the SharePoint site except the bread crumb but you can customize it to fix this lack.
4.6 Step 6 Consume report using SQL Server Reporting Services Report Viewer Web Part
1- Create a Web Part page
Click "Site Actions", then click Create



On "Create" page in "Web Pages" column, click "Document Library"



In "New Web Part Page" page, type the Name (TestReport), select any template, select "Shared Document" as the document library that will welcome your new web part page, then click "create".



The Web Part page is created and is opening in Edit Mode



2 - Add and Configure SQL Server Reporting Services Report Viewer Web Part



In the "TestReport" web part page opened in edit mode, click any "Add Web Part" button.




The Add Web Part Pop Up is opening, and notifies the zone where the Web Part will be added.
Scroll the Pop Up and locate SQL Server Reporting Services Report Viewer Web Part, select it and click "Add".



Notice that the Web Part is now embedded in you Web part page.
Click the "Click Here To open the tool pane" link.



In the tool pane, click the "..." browse button next to the "Report" input field.
The "Select an Item" Web Page Dialog is opening.







With the "Up" icon link navigate up to display the SharePoint site Document Libraries list, then click the "Reports" Document Library, select the "Report1" previously deployed report and click "OK" or double click the "Report1".







Notice that you can see the ".rdl" extension in the "Location (URL)" input field.
Now the "Report" input field of Web Part Tool Pane is filled with the Report URL.



Click "Apply" the report is being generated.







Click the "Exit Edit Mode" link to display the page in normal mode.



You can now see the second way of displaying a report in SharePoint, using SQL Server Reporting Services Report Viewer Web Part. You notice that in this way, you keep the possibility to navigate in the SharePoint site, while this is not possible with the RSViewerPage.aspx.



4.7 Step 7 - Consume report using ReportServer Web Site
We are now going to use Report Server Web Site to consume the same report we have deployed in SharePoint.

1 - Opening the Report Server Web Site
Open the Report Server Web Site, the URL of which we defined earlier with Reporting Services Configuration Manager in the third post of these posts group. ( Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1)
It should correspond to this pattern:
   
              http://yourmachinename/ReportServer




Click the SharePoint Site URL



You will see the different Document Libraries of your SharePoint Site. (You should not see the "Data Connections" link if you have precisely followed these tutorial instructions).
Click the "Reports" Document Library link.



You will see the previously deployed "Report1.rdl" file.
Click that "Report1.rdl" link.



Your report has been generated for the third time, using the third way of displaying a report in SharePoint Integrated Mode.

29 comments:

Suman Hasnabad said...

Hi i have tried the same above procedure in my system which has report server and MOSS both in single system everything file but while i m deploying the report.dll it is giving the bellow error

An error occurred during client rendering.
The underlying connection was closed: An unexpected error occurred on a send.
Authentication failed because the remote party has closed the transport stream.

Please reply back if u find teh solution

Thanks...

Marc Charmois said...

Hi Suman,

I did not experiment this issue yet.
So it seems to occur in BIDS when you are deploying the report?

What are you using for the URL's?
your machine name, or localhost?
Try both...

Have you properly configured SQL 2008 for SharePoint that is to mean :

Use the SQL Server Network Configuration area to enable connection protocols as follows:
Named Pipes Enabled
TCP/IP Enabled

decribed in my previous post:
http://mosshowto.blogspot.com/2009/01/integrating-sql-server-2008-reporting.html

3.22 Step 22: Configure your new SQL Server installation

(that was surface area settings configuration in SQL Server 2005 http://technet.microsoft.com/en-us/library/cc263187.aspx)

Good luck...

Marc

Suman Hasnabad said...

Thanks Marc for your response i will check what you specified...

Anonymous said...

Hello,
First of all this article is a great Help!

I am working with two different servers. In One server, I installed the SharePoint (MOSS 2007) and other server has Report server + Report Catalog DB (SharePoint Integrated Mode).

Both servers are running under the Domain account with Administrative permission.

How I should configure?

Thanks,
Mike.

Marc Charmois said...

Hi Mike,

Thank you, I am happy if these posts may help...

I have now published something about that topic, a complete tutorial with screenshots as usual.

But roughly operations are the following:

-Regarding Report Server configuration you have to deploy SharePoint on a computer and make the computer be a Web Front End of the SharePoint Farm. Then create a Database in SharePoint Integrated Mode with Report Server Configuration Manager.
Last, always using Report Server Configuration Manager, configure the Report Server Web Service URL

-For SharePoint configuration on the other computer, in Central Administration "Manage Integration Setting" Page you MUST specify the report Server Web Service URL.
Then, for the "Authentication Mode" Section select "Trusted Account" that is the right mode for the configuration in multiple servers with NTLM.
Last, grant to the Report Server Service Account the permission to access SharePoint Config Database in the SharePoint Central Administration "Grant Database Access" Page.

-After having done that, don't forget to configure the datasources of your reports using the properties of your .rdl.

You will find all the detailled instructions in my post

SQL Server Reporting Services 2008 in SharePoint Integrated Mode - Distributed Server Deployment with Three computers

Good luck!

Marc

seamus33 said...

Hi Marc,

Good article, however I am getting a problem after I have successfully deployed the reports. When i try and run the report through the sharepoint report viewer webpart i get the following error.
An error occurred during client rendering.
The remote server returned an error: (500) Internal Server Error.


I've looked into this and there is a suggestion it is a timeout issue with a mismatch in the settings between sharepoint and RS.

When I run a report with a selection list of parameters the error message comes up but the drop down list is populated with the data pulled from the database, so I'm puzzled as I seem to be able to retrieve data no problem but when it comes to render the report in SP it won't produce. If I run the report within BIDS it runs no problem.

Help!

Marc Charmois said...

Hi Seamus,

I personally did not experiment this type of error, but I can try to produce it and find what is involved.
You described very well this error, but can you tell me more about your configuration? Do you use a machine or several machines, do you use one service account or several?
Did you checked the log files of Report Server?
Can you tell me how you found the suggestion it is a timeout issue with a mismatch in the settings between sharepoint and RS.

Thank you.
Marc

seamus33 said...

Hi Marc,

I'm running single server and I picked up the timeout issue from the following post
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/65f5db23-1a6a-47fc-8bf6-b90b9ce1fff0/

The loga are holding nothing with stands out as an error to me at the moment

Marc Charmois said...

Hi Seamus,

Are you really sure there is nothing in the Reporting Services log files although you had error 500 from Report Server ?
it is very weird...

Can you replay Reporting Services Configuration in SharePoint
Manage Integration Settings and see if it works properly?

Can you display this Report with the Report Server Web service ?

If it works, the issue seems to be linked to the report itself or to SharePoint Web Part.
Is the report big, does it take long time to be generated?
Did you tried with RSViewerPage.aspx?

Have you tried with a very simple report with few rows and without any parameter?

Good courage and give me news,

Marc

seamus33 said...

Hi Marc,

I have a full log file to send if you wish, obviously I won't post it here.

AS to your post, everything seems correct and i can run teh reports successfuly with the reportviewer.aspx, ie stepping through to the directory and doubleclicking the .rdl file

I can't find anything tagged as error in the log but it may mean more to the experienced eye.

Thanks for your continued help.

Marc Charmois said...

Hi seamus,

You are welcome.

you can mail me at marccharmois AT
hotmail dot com.
I will check, but I do not warant to find something more since there is nothing like an error.
Can you check also SharePoint log?

Marc

jit said...

hi. how can i create custom reports?

for example i will have a form and according to user input i will create a custom report. Do you have any idea to do this?

Marc Charmois said...

Hi Jit,

well,
a report in SharePoint Integrated Mode is somewhere a .rdl file stored in a SharePoint document library.
And a .rdl file is an xml file where all the report properties are specified.
So you can maybe build an application in SharePoint that is able to generate this xml and push it with the .rdl extension in your reports document libray.
But inside the xml there is maybe references to your report server environment and you maybe have also to use the report server Web Service while generating your .rdl file in order it can be recognized as a report and can be generated properly in SharePoint.

The task you are planning to do is very interesting, but I think it can lead to a big amount of time regarding research and development.

I have absolutely no time left until next week to open a .rdl file and to check if its xml can be automatically generated within a SharePoint application, so I just suggest you this track but do not warrant at all it is possible to obtain a valid report doing that way.

However this blog readers and I would be very interested if you could tell you suceeded in making a POC working.

Give me news on that topic, and if you still have questions about it next week we will try to find the answers.

Marc

Murat Girgin said...

Hi Marc,

I usually follow your blog, but this time I've a problem that doesn't include an answer; could you please check it from msdn link?

I hope you can solve it. Thanks.
http://social.msdn.microsoft.com/Forums/en-US/sharepointbi/thread/f2766e74-16de-4ca0-910a-7d5bf8627086

Marc Charmois said...

Hi Murat,

Regarding your issue,

Did you checked the log files of Report Server?

can you go to the IIS error log file and check it?
(c:\WINDOWS\system32\LogFiles\HTTPERR)
We will maybe able to get more information.

I know a case where someone had exactly the same issue and he was using SSL for Report Server. But we did not look further...


Hope that helps.

Marc

Unknown said...

Hi Marc,
Gr8 post and helped me a lot.
I followed your steps and was able to get reports in reportviewer webpart successfully :).
I had one doubt,in my reportViewerWebPart i want to give relative url of the report,so that when i deploy it to other machine links work fine.
Also i have some hyperlinks on the reports by clicking on which i want to navigate back to one of my sharepoint pages but those links do not work fine as they hit the report server.
It would be great if you could extend some help in these regard.
Thanks..

Anand Thakare said...

Go to

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config


Change >>

Add Key="SecureConnectionLevel" Value="2"

To
Add Key="SecureConnectionLevel" Value="0"

Unknown said...

Your articles on installing Reporting server in an intgrated mode is very good. I have this issue i am not able to resolve:
I have the following Servers on the farm:
• WFE – WFE01 and WFDE02, installed SQL Server 2008 Reporting Service Add-in for SharePoint in the application mode.
• App server and Reporting Server –AppServer01. This is a part of the SharePoint farm. It has the CA site hosted as well.
• Database Server - DB01
I created a site, http://testingSite. Made the necessary DNS to point to WFE01 and WFE02, created the AAM for it in SharePoint and load balanced it. The site works great and so does the load balancer. When I access the site as http://WFE01 or http://Appserver01, the site shows the report from my desktop. When I use http://testingSite the report, it gives me the standard error saying An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. But when I log into the WFE or App server and visit http://testingSite it works fine.
Any idea why that would be happening.

Paul said...

Hi,

I am using MOSS 2007 and SSRS 2008. When I try to deploy the report it asks for a username and password. I have looked all over the place and havent solved my problem yet. Any ideas?

Marc Charmois said...

Hi Paul,

I did not use reporting services for a long, but, first of all, if you provide the authentication dialog with proper login and password, does it work?
Second, is the machine where is installed your Visual Studio and the report server and SharePoint server on the same network using NTLM ?
If yes, are you logged on on the visual studio machine with an account that has contribution permissions for the document library where you want deploy the report in SharePoint?
This is first elements to check.

Tell me.

Marc

Grant said...

Nice one thank you very much for this article. I followed from step 1 through step 6. I overlooked the Collation Configuration so had to reinstall SQL but otherwise, very well written and extrenmely helpful.

Thanks again!

Rajesh said...

Hi Marc,

It works for me. Marc you done great job, lot of time saves for me. thanks a lot

lanry smith said...

This is such a briefly description of share point 2007. I am happy to see this blog, I found many solutions of my questions.

Network monitoring software

Stephane said...

Hello,

I have follow your from 1 to 6.
All is ok, until I try to deploy a report.
The deployment si Ok, but when i try to see result in my share point library, the report is opening then after a short time I have the error message :
"Erreur c'est produite lors du rendu client. Version non prise en charge détectée : 34.62.1836345404 version attendue : 10.3."
My config
Windows 2003 SP2 (x86)
WSS3.0 SP2
.net 3.5
SQL2008R2

Marc Charmois said...

Hi Stephane,

some other people seem to have the same problem as you...

But unfortunately, it also seems there is no obvious solution for that.

I post the MSDN forum thread that can give you more clues to fix your ptoblem or that can give you a definitive solution if someone find it.

http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/44992b65-a404-4798-b5bd-8f5a3e930daa

It is all that I can do for the moment. Sorry...

Good luck.

Marc

Stephane said...

Thank a lot Marc,
I notice something and I share htem here may help people to work around this trouble.
-SQL2008R2 have only a X64 rsSharePoint.msi not a rsSharepoint.msi x86
My windows 2003 is a x86 not a x64. so i can not install rsSharepoint X64. then i decide to install rssharepoint x86 from SQL2008 not from SQL2008R2.
When I run ReportServices in a native mode it work fine.
So there are 2 way : Upgrade my win2k3 from x86 to x64 then update my SQL2008R2 in x64 and then install rsSharePoint in x64 (I think i will install from scratch.)
or
intergrate the native mode from reportservice in my WSS3 web site.
Tahnk a lot for the job you done here.

Marc Charmois said...

Thanks to you !

You found the solution and you have posted it.
Thanks to make the general knowledge on the products growing and be available.

You should also post this on the MSDN Forum Thread, if not I'll do it later.

Cheers !!!

Marc

Dominique said...

Hello,
Thans for the excellent post which is really helpful.
Everything went fine until the Deployment in Visual Studio where I am getting an unautorized http 401!!!
I am dbo on the Db... I have access to all reports through the URLs...
BTW where are the Virtual Directory for the ReportServer? On the SharePoint Server or on the Reporting Server?

Thanks,
Dom

Anonymous said...

Very helpful guide. A lot more detailed and walkthrough then MS provides!