(Many, many thanks to Heidi Steen of the super-duper UE team at Microsoft for providing the material below. Hell, she even created screen shots!)
Now that lots of folks are happily playing around with SSRS/MOSS integration, it’s important to understand exactly how you publish the things to MOSS via Business Intelligence Studio. You still use the same dialogs you’re used to, but plug different values into them. Here are some nice, step-by-step instructions:
1. In SQL Server Business Intelligence Development Studio,
open an existing or new Report Server project.
2. From the Project menu, click Properties. The <project>
Property Pages dialog box opens.
3. Choose the Configuration you use to publish to a SharePoint site.
For example, Active(Debug) or Production.
4. In the Deployment section, for the optional TargetDataSourceFolder,
type a fully qualified URL to a library or library folder. For example,
If left blank, this defaults to the TargetReportFolder value.
5. For TargetReportFolder, type a fully qualified URL to a
library or library folder. For example,
6. For TargetServerURL, type a URL to a SharePoint
top-level site or sub-site. If you do not specify
a site, the default top-level site is used. For
7. Click OK.
8. In Solution Explorer, right-click on the report
you want to publish, and click Deploy. The report
is published to the location specified in
TargetReportFolder. Deployment errors appear in the Output window.
NOTE: In order to upload data sources and reports to MOSS, the user in question must have “Contribute” or “Full Control” permissions.
Below, I’ve attached some screenshots which demonstrate publishing to a library in a custom and default MOSS site (again, thanks to Heidi!)
Although the blog entry provides all the details for such activity but there are some pre-requisites to do that.
- SQL Server 2005 Reporting Services – hosts Reporting Services reports and communicates with Office SharePoint Server 2007 using a Web Services interface. The Reporting Services server also manages subscriptions and report caching. This server can be separate from the Office SharePoint Server 2007, but if it is, you must also do a basic install of Office SharePoint Server 2007 on the Reporting Services server to ensure that the Windows SharePoint Services (WSS) 3.0 object model is installed.
- SQL Server 2005 Service Pack 2 – enables Office SharePoint Server 2007 and Reporting Services integration on the report server.
- Office SharePoint Server 2007 –Serves as the central access point for all reports and business data. Alternately, you can use Windows SharePoint Services 3.0.
- Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies – This feature includes the Report Viewer Web Part and the new report management user interface.
After installing the required components to integrate Reporting Services with Office SharePoint Server 2007, both the report server and SharePoint server need to be configured. The following sections outline the configurations necessary to achieve integration.
After installing SQL Server 2005 Service Pack 2 on the Reporting Services server, a new configuration option becomes available, called SharePoint Integration, as shown in Figure 2.
Figure 2: Configuring Report Server SharePoint Integration.
After installing the Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies feature of the Feature Pack for SQL Server 2005 Service Pack 2, a new application becomes available under the Application Management tab. This new application is shown in Figure 3.
Figure 3: Configuring the Reporting Services Application in Office SharePoint Server 2007.
To configure Reporting Services, you must set each of the options shown in Figure 4, as follows:
- Manage integration settings – To configure the Report Server Web Service URL and Authentication Mode (either Windows or other trusted account).
- Grant database access – To configure the name of the report server database server instance name.
- Set server defaults – To configure report server timeouts and other options.
For more information Read