Introduction
Microsoft®
SQL Server™
2005 and the 2007 Microsoft Office System are key parts of the Microsoft
Business Intelligence (BI) offering. These technologies work together to put
tools in the hands of business users to enable them to make important decisions
about their businesses. To understand the key BI features built into the
2007 Microsoft Office System, see the 2007 Microsoft Office System Business Intelligence Integration
white paper.
One of the key BI integration points between SQL Server 2005
and the 2007 Microsoft Office system is the tight integration between SQL
Server 2005 Reporting Services and Microsoft Office SharePoint® Server 2007.
This white paper outlines the integration of SQL Server 2005 Reporting
Services with SharePoint Products and Technologies (Microsoft Office SharePoint
Server 2007 and Microsoft Windows® SharePoint® Services 3.0).
Microsoft Business
Intelligence
The Microsoft vision for Business Intelligence is to enable all employees in an organization to make better, faster,
and more relevant business decisions. This requires a robust set of
technologies that work together.
On the back end, the core BI platform is based on Microsoft SQL
Server 2005 and its related technologies, such as SQL Server
Integration Services (SSIS), SQL Server Analysis Services (SSAS), and
SQL Server Reporting Services (SSRS). On the front end, the
2007 Microsoft Office System, together with Microsoft Office Business Applications
provides a familiar suite of tools and applications with which to access the
back-end BI solution. By surfacing reporting and analytics through the tools
that users are already familiar with, users can be productive immediately. They
do not need go through lengthy training or learn new tools. This reduces the
time required to implement a BI solution to weeks or months, instead of months
or years, resulting in a faster return on the investment.
Terminology
Following is a brief introduction to the technologies and
acronyms used in this paper:
· WSS
– Microsoft Windows SharePoint Services 3.0. WSS 3.0 provides a
foundation for building Web-based business applications and is available as a
free download for basic team-based SharePoint sites. WSS 3.0 is also used
in Office SharePoint Server 2007, which provides additional collaboration
and portal capabilities (covered later in this paper). For more information
about WSS, see Microsoft Windows SharePoint Services 3.0 on
Office Online.
· MOSS
- Microsoft Office SharePoint Server 2007. MOSS includes collaboration
and workflow functionality. MOSS also enables forms and business processing,
personal sites, enterprise searching, and much more. For more information about
MOSS, see Microsoft Office SharePoint Server on Office
Online.
· SSRS
– SQL Server 2005 Reporting Services. SSRS is the reporting technology that
was first introduced in SQL Server 2000 as an add-in and is built into the
core product with the release of SQL Server 2005. SQL Server 2005
Service Pack 2 (SP2) further enhanced SSRS by tightly integrating
reporting into the SharePoint environment for a consistent experience to the
end user. For more information about SSRS, see SQL
Server 2005 Reporting Services.
The integration of Reporting Services
with SharePoint products and technologies is referred to as SharePoint integrated mode. Hosting Reporting Services
reports in the Reporting Services database and managing content through Report
Manager is referred to as Native mode. This
paper discusses SharePoint integrated mode.
Integration
Architecture
SQL Server 2005 Reporting Services integration with SharePoint
products and technologies was introduced with SQL Server 2005 Service
Pack 2 as an additional option for delivering
and managing Reporting Services content; the traditional Reporting Services
architecture with delivery and management though Report Manager is still
available. The delivery of reports through SharePoint technologies is an
alternative option for organizations that are looking for one central location
for accessing, managing, and sharing both structured and unstructured data for
improving business insight.
To enable tight integration between SharePoint Products and
Technologies and SQL Server Reporting Services, the Reporting Services
architecture is enhanced to enable Report Server users to store reports in
SharePoint document libraries and leverage the SharePoint security model. The
overall architecture is shown in Figure 1.
.gif)
Figure 1: Reporting Services / SharePoint Integration
Architecture
A number of key functional enhancements were made to SQL
Server 2005 SP2 to make Reporting Services work in SharePoint integrated
mode:
· Catalog
Synchronization – When a user works with a report in the SharePoint
environment, the report definition language (RDL) file is stored in the
SharePoint content database. SharePoint technologies communicate with the
Reporting Services Web service to perform various operations, including
rendering the report and exposing the report through the Reporting Services
API. Reporting Services ensures that it renders the latest published version of
the report by synchronizing the Report Server database content with the
appropriate SharePoint database content.
· Report
Security – A security extension in SP2 enables you to maintain report
security in MOSS or WSS for operations on the report server. This security
extension supports both Windows integrated and trusted account modes of
authentication between SharePoint Server and Report Server.
· Report
Viewer Web Part – The Report Viewer Web Part has been updated to display
rendered reports natively in a SharePoint Web application and to enhance the
overall functionality of using Reporting Services in a SharePoint environment.
The Report Viewer Web Part is discussed later in this paper in Dashboards and Web
Parts.
· Report
Management UI – User interface elements have been built for SharePoint
technologies to enable report management through the common SharePoint
interface. This includes multiple content types, Web pages, and
context-sensitive menus for reporting objects, such as reports, data sources,
and data models. Because of these report management UI enhancements, managing
reports in Report Manager is no longer supported when you use Reporting
Services in SharePoint integrated mode.
Report
Server
Report Server is still responsible for rendering reports,
synchronizing reports, securing reports, managing subscriptions, and virtually
all functionality required by Reporting Services. Report Server requires that
the following software be installed:
· SQL
Server 2005 Service Pack 2 – Service Pack 2 contains the enhancements
required to communicate with a SharePoint server and to manage integration with
SharePoint technologies.
· SharePoint
Object Model – The SharePoint object model must be installed on the
Reporting Services server. If you use the same server for SharePoint and
Reporting Services, the object model is already installed. If you use a
separate server for Reporting Services, you must install the object model on
the report server as well. Regardless of whether the SharePoint farm is a WSS
or MOSS farm, to install the object model, first install the WSS or MOSS Web
front end on the report server. After that, use the SharePoint Products and
Technologies Configuration Wizard to configure the object model to
join the existing WSS or MOSS farm. For details, see How to: Install the Windows SharePoint Services Object Model
on a Report Server Computer in SQL Server 2005 Books Online.
SharePoint
Server
Either MOSS or WSS is used as a common mechanism for viewing and
managing reports. They are also used as a common security mechanism for your
reports and other documents. SharePoint Server requires that the following
software be installed:
· Microsoft
SQL Server 2005 Reporting Services Add-in for SharePoint Technologies –
Also known as the Reporting Services Add-in, this
program is used to extend the functionality of SharePoint to implement the
following functionality:
· Context-sensitive
menus – Any SharePoint document library that is enabled for Reporting
Services shows the user a special menu that enables report, report model, and
report data source management. See also Report Manageability later in this white
paper.
· Report
Viewer Web Part – The Web Part communicates with the Report Server Web service
to render and display reports within the SharePoint environment in Full Page
view or from a SharePoint dashboard.
· Report
Management pages – Additional pages are installed on the SharePoint server
and are made available through the common SharePoint management environment to
support the tasks of managing reports. Management includes operations such as
uploading and deleting reports, viewing history, and managing subscriptions.
· Content
Types – Additional SharePoint content types are installed for creating
Report Data Sources, Report Builder Reports, and Report Models.
Note SharePoint technologies require
that all of the Web servers in your server farm have the same SharePoint
products and technologies installed. While SQL Server 2005 Reporting
Services with SP2 can integrate with either MOSS or WSS, installing WSS on
a Reporting Services computer and joining it to a MOSS farm (or vice versa) is
not supported.
Server
Configuration
To enable integration between MOSS or WSS and Reporting Services,
you must configure each server. Each instance must be configured even if both
run on the same server. The report server can be installed on the same server
as SharePoint, or it can be installed on a different server. Install MOSS or
WSS and Report Server on different servers when the workload needs to be spread
over multiple servers. The software requirements and configurations for each
server are described in the next two sections.
Report
Server
Configuration
After installing SQL Server 2005 Service Pack 2, use
the Reporting Services Configuration Tool to configure whether the Reporting
Services instance should run in SharePoint integrated mode or in Native mode.
To run in integrated mode, when the Report Server database is created, select
the Create the report server database in SharePoint Integrated mode
option. The report server database stores all Reporting Services-related
content.
After the database is configured for SharePoint integrated mode,
Report Manager, which is a user interface for managing reports when Reporting
Services is running in Native mode, is not supported. Report Manager is not
needed in SharePoint integrated mode because all report management is done from
within the SharePoint environment.
SharePoint Configuration
When you install the Reporting Services Add-in on the SharePoint
server, the new Reporting Services feature is automatically installed. This
feature and its configuration options in the SharePoint Central Administration
site are shown in Figure 2.
.jpg)
Figure 2: Configuring Reporting Services in Office SharePoint
Server 2007
To configure Reporting Services in WSS or MOSS, set each of the
following options (shown in Figure 2):
· Manage
integration settings – Used to configure the Report Server Web service URL
and authentication mode (either Windows or other trusted account).
· Grant
database access – Used to provision the service accounts for the Report
Server Web service and the Report Server Windows service for appropriate access
to the SharePoint configuration and content databases.
· Set
server defaults – Used to configure multiple Report Server system
properties.
Authentication
Modes
When you configure Reporting Services to work in SharePoint
integrated mode, you must configure SharePoint to securely connect to the
report server. You can select from two options:
· Windows
Integrated – The SharePoint user’s Windows identity is used to perform
operations on the Report Server. Integrated authentication works only when the
report server and the SharePoint server are the same server or when Kerberos
delegation is enabled between the servers.
· Trusted
Account – In this mode, the SharePoint application pool identity is used to
access the report server. SharePoint user information is passed along to the
report server as well, to ensure that operations are performed using the
SharePoint user’s context and permissions. Trusted account authentication is
required when forms or custom authentication is used over the Internet or in an
extranet scenario.
Forms
Authentication
If you want to expose your Reporting Services reports through
your SharePoint site to a group of people who do not have Active Directory® or domain
accounts on your network, you can use forms authentication.
Forms authentication is useful in scenarios such as extranets where your partners
need access to a SharePoint site, but you do not want to, or cannot, give them
access to your network.
When you use forms authentication, you configure your SharePoint
site to use an authentication mechanism called a membership
provider. The membership provider takes authentication information on a
Web form and validates it according to the functionality of the membership
provider. For example, the AspNetSqlMembershipProvider uses ASP.NET to
validate the input taken from the Web form against a SQL Server database. After
authentication, the user is permitted to use the SharePoint site. More
information about the steps to configure forms authentication can be found at Forms Authentication in ASP.NET 2.0.
Note Install the Reporting Services
hotfix 939942 before you configure your SharePoint
Web application to use forms authentication.
Firewalls
Viewing and managing reports in SharePoint is possible through a
firewall over the Internet, but comes with a set of challenges. Figure 3
illustrates this network topology. In this scenario, a browser makes a request
through the corporate firewall to MOSS or WSS across port 80 for http traffic
and port 443 for SSL traffic. SharePoint Server, in turn, requests the
Report Server Web service to render the selected report. This request is made
based on the configuration of MOSS or WSS.
.gif)
Figure 3: Using SharePoint across a Firewall
The scenario shown in Figure 3 is very common, but there is one
caveat: the URL specified for communicating to the Internet-facing SharePoint
server must be in the Default zone.
To enable a browser to work through a firewall when Reporting
Services is running in SharePoint integrated mode, follow these steps:
1. On
the Application Management tab of the SharePoint Central Administration
Web site, configure Reporting Services to use the URL of the report server.
2. On
the Operations tab of the SharePoint Central Administration Web site,
under Alternate access mappings, ensure that public URL to access the
Internet-facing SharePoint Web application is in the Default zone.
3. Test
to confirm that the SharePoint site correctly communicates with the report
server over the Internet.
Caution In SharePoint
integrated mode, you cannot configure the report server URL to work both over
the Internet and behind the firewall on your intranet at the same time.
However, this functionality is planned for an upcoming release of SQL Server
Reporting Services.
SharePoint
Server Farm
Using SharePoint products and technologies in a SharePoint Server
farm deployment enables you to spread the workload across multiple Web servers.
Each site in the SharePoint Server farm must be configured as follows:
· Each
server in the SharePoint Server farm that runs the Windows SharePoint Services
Web service must have the Reporting Services Add-in installed.
· Each
SharePoint site collection should have Reporting Services activated (see
Figure 2).
· Have
users access the load-balanced URL to the Web farm. Do not have them access the
URL for individual sites in the Web farm.
Note A SharePoint Server farm
is different from a Reporting Services scale-out configuration. Reporting
Services uses a single database instance to store and synchronize report
metadata from all report servers in the farm. There is only one report server
for a given SharePoint Server farm. This URL is specified on the Manage
Integration Settings page in the SharePoint Central Administration Web
site.
Report Publishing
After Reporting Services is configured for SharePoint
integration, publishing reports to a SharePoint server is very easy. Developers
can publish reports by using the SQL Server Business Intelligence Development
Studio (a Microsoft Visual Studio®-based development environment that is included with SQL
Server 2005). End users and business analysts can use Report Builder to
publish reports.
Using
SQL Server Business Intelligence Development Studio
Before you can publish a report or data source from
SQL Server Business Intelligence Development Studio, you must configure
the deployment properties for the project that contains the reports.
Figure 4 shows the properties for a Reporting Services project.
.jpg)
Figure 4: Properties for a Reporting Services Project
Here is an example to help you understand how to configure URLs
to work with a SharePoint document library. Assume that you have two SharePoint
document libraries, ReportsLibrary and Data Connections, located in a SharePoint
site named Reports on a server named localhost. You can select from these
deployment options:
· OverwriteDataSources
– If True, overwrites data sources located in the TargetDataSourceFolder
location.
· TargetDataSourceFolder
– Absolute URL to a SharePoint folder or document library that stores
report data sources. If it is not specified, the absolute URL entered in the TargetReportFolder
is used to store the report data source. The format for this URL is http<s>://<server>/<site>/<document
library>. An example is http://localhost/Reports/Data Connections.
· TargetReportFolder
– Absolute URL to a SharePoint folder or document library that stores
reports. The format for this URL is http<s>://<server>/<site>/<document
library>/<folder>. An example is
http://localhost/Reports/ReportsLibrary.
· TargetServerURL
–Absolute URL to the SharePoint site that contains the TargetReportFolder
or TargetDataSourceFolder. The format for this URL is http<s>://<server>/<site>.
An example is http://localhost/Reports.
Using
Report Builder
Report Builder is a Reporting Services client tool that enables
end users to create, modify, and share reports. To maintain security on the
data that is exposed to end users, and to translate the often complex
relational table structures into business-friendly terms, Report Builder uses a
report model, which is basically a semantic layer. A report model is used to
determine which tables and fields from an underlying data source are exposed to
Report Builder.
Report Builder is a click-once application that can be
automatically launched from a SharePoint document library via the browser in
SharePoint integrated mode. Figure 5 shows a new Report Builder report
that uses a report model.
.jpg)
Figure 5: Creating a report with Report Builder
After a report is created or edited, it can be previewed, and
then published to a SharePoint document library. Publishing a Report Builder
report is as simple as clicking the Save button and navigating to the
correct SharePoint document library.
Even though Report Builder is launched from SharePoint, it is
actually a Windows-based application that is installed on the user’s computer. Therefore,
it does not run under the same security context as the SharePoint Web session,
but runs by using the user’s actual logged-in Windows credentials. This means
that users are required to log on to SharePoint a second time if they are
using forms authentication.
Report
Management
When you are using Reporting Services in integrated mode, reports
are managed completely within the SharePoint environment. If you navigate to a
document library that contains reports, clicking a report brings up the context
menu shown in Figure 6.
.jpg)
Figure 6: Report Manageability Options for a Report
Properties
Click Edit Properties on the menu to change certain
properties of the selected report, report data source, or report model. Click View
Properties on the menu to view properties. You manage properties on
Reporting Services items the same way as you manage any other item in a
document library.
Permissions
Click Manage Permissions on the menu to access the
permissions for the selected document library or item. User permissions for
Reporting Services items (reports, report data sources, and report models) are
managed the same way as you manage permissions for any other document in a
document library. However, the permissions assigned to items in SharePoint have
special meaning to the Report Server Web service. The following table describes
this mapping:
|
Permission
|
Report Server Operation
|
|
Manage Lists
|
·
Create a folder in a SharePoint library during a publish operation
from an authoring tool.
·
Manage report history.
|
|
Add Items
|
·
Add reports, report models, shared data sources, and resources
(external image files) to SharePoint libraries.
·
Create shared data sources.
·
Generate report models from shared data sources.
·
Start Report Builder and create a new report or load a model into
Report Builder.
|
|
Edit Items
|
·
View past versions of a document, including report history snapshots.
·
Edit item properties for reports and other documents.
·
Set report processing options.
·
Set parameters on a report.
·
Edit data source properties.
·
Create report history snapshots.
·
Open a report model or a model-based report in Report Builder and save
changes to the file.
·
Assign clickthrough reports to entities in a model. Replace a report
definition, shared data source, report model, or resource with a newer
version (replace file, preserve metadata).
·
Manage dependent items that are referenced in a report or model.
·
Customize the Report Viewer Web Part relative to a specific report.
|
|
Delete Items
|
Delete reports, report models, shared
data sources, and other documents from a library.
|
|
View Items
|
Open a report, report model, and other
document and have it processed on the report server.
|
|
Open Items
|
·
View a list of shared data sources.
·
Download a copy of the source file for a report definition or report
model.
·
View clickthrough reports that use a report model as a data source.
|
|
View Versions
|
View past versions of a document and
report snapshots.
|
|
Delete Versions
|
Delete past versions of a document and
report snapshots.
|
|
Create Alerts
|
Create, change, and delete
subscriptions that use the Reporting Services delivery extensions to deliver
reports to target locations. Only the subscription owner and users who have Manage
Alerts permission can perform these actions.
|
Subscriptions
and Delivery
Subscriptions enable users to automatically receive reports
through a variety of delivery methods. While Reporting Services is designed
with an extensible delivery mechanism for custom delivery types, the default
delivery types are:
· E-mail
– Sends a report via e‑mail. Users can configure the To:, CC:, and BCC:,
subject, and priority of the e‑mail.
· Windows
File Share – Sends reports to a Windows file share. You can specify the
file name and security credentials required to write a file to the share.
· Null
Delivery Provider – Generates subscriptions that preload the report cache.
This type of subscription can help increase report performance.
· SharePoint
Document Library – Sends reports to a specified SharePoint document
library. The page where you configure this is shown in Figure 7.
.jpg)
Figure 7: Report Delivery to
SharePoint Document Library
To create a new subscription, click the Add Subscription
button. Select the delivery type for the subscription and configure the
properties for the selected type of subscription.
Note Before you can create a
subscription for a report, the data source that is associated with the report
must have the credentials that are stored in the report or be associated with a
shared data source that uses stored credentials.
Data
Sources
Report data sources indicate where the source of the data for a
report is located. Data sources specify the connection strings and security
credentials that are required to locate the data on your network. Reporting
Services supports two types of data sources:
· Shared
– Stores the data source definition (.RSDS) or report model (.SMDL) in a common
location so that it can be used by multiple reports. Depending on the number of
data sources you plan to have, you may want to create a SharePoint folder where
you store all your data sources.
· Custom
– Stores a one-time definition used by the selected report.
To manage a data source for a single report, click Manage Data
Sources on the menu. You can select a shared data source or create a new
custom data source.
To add a shared data source, navigate to the document library
that will contain the data source. Click the New button, and select Report
Data Source. This brings up a page where you can configure the shared data
source.
Note The Report Data Source
content type must be explicitly added in the document library settings before
it will appear in the New drop-down menu.
Parameters
Parameters are used to pass values to a report. Parameters are
defined when a report is created. To change parameter values or properties,
select the report and click Manage Parameters on the menu. You are
presented with a list of the parameters that are defined for the selected
report. You can change existing parameters at this screen, but you cannot add
or remove parameters. To add or remove parameters for a report, you must change
the report definition.
To manage an existing parameter, click the parameter name. This
brings up a screen that enables you to access the following properties of the
parameter:
· Parameter
Name – The name of the parameter as it is defined in the report. This
property cannot be changed at this screen. You can change it in the report
definition.
· Data
Type – The type of data expected by the parameter. This property cannot be
changed at this screen. You can change it in the report definition.
· Default
Value – Which value, if any, to use as the default. The values presented in
this screen are defined in the report definition. To change the domain of
values, edit the report definition.
· Display
– Controls what the user sees on the screen. Indicate how you would like the
parameter to prompt the user for a value, or select the Hidden or Internal
options.
Processing
Options
Processing options are used to instruct Reporting Services how to
run reports. Processing options are useful for creating historical views of
reports for audit purposes. Reports can also be processed and cached to enhance
performance. To set processing options, select the report, and then select Manage
Processing Options from the menu to configure the following options:
· Data
Refresh Options – Specify from where the selected report get its data.
Select from the following options:
· Use
live data – Always queries the data source. This ensures that report data
is up to date, but can negatively impact performance.
· Use
cached data – Always uses cached data to run the report, if the cache
exists. If it does not, live data is used. Reporting Services maintains a cache
based on the parameter values selected by the user.
· Use
snapshot data – Always uses data that was explicitly taken as a snapshot.
For example, a report can always run on last month’s data if a snapshot was
taken at that time.
· Processing
Time-out – Configure when Reporting Services should stop trying to execute
a report and time out. A time out helps to prevent excess server resources from
being used on a single report. The following time-out options are available:
· Use
site default setting – Uses the time-out setting that is configured in the Set
server defaults option in the Reporting Services application under the
SharePoint Central Administration site (see Figure 2).
· Do
not time out report processing – Runs a report indefinitely. Use this
option with caution, as it can cause poorly written data sources to consume too
many server resources.
· Limit
report processing (in seconds) – Runs a report for a specified number of
seconds, then times out.
· History
Snapshot Options – Select one or more options for how you would like
historical report snapshots to be taken and stored. Select from the following
options:
· Allow
report history snapshots to be created manually – Allows users to create
snapshots on an ad-hoc basis for the selected report.
· Store
all report data snapshots in report history – Stores snapshot data in
history.
· Create
report history snapshots on a schedule – Creates history snapshots at a
predefined interval.
· History
Snapshot Limits – Use to limit the number of snapshots for the selected
report. This helps to conserve disk space on the server. Choose from the
following options:
· Use
site default setting – Uses the snapshot limit setting that is configured
under the Set server defaults option in the SharePoint Reporting
Services application (see Figure 2).
· Do
not limit the number of snapshots – Enables unlimited snapshots. Use this
option with caution, because it can consume all available physical disk space.
· Limit
the number of snapshots to – Limits the number of snapshots to a specified
value.
History
Report history shows a list of all snapshots that have been taken
for the selected report. It also enables you to take an instant snapshot of the
report. The View Report History menu item presents a screen that shows
all snapshots taken for the selected report, including the date and time the
snapshot was taken.
Workflow/Approval
An additional advantage of running Reporting Services in
SharePoint integrated mode is that you can use SharePoint’s workflow and
approval mechanisms. As long as the document library that stores a report has
workflow configured, a report must be approved before it can be published and
made available to the public. To configure who can approve a report, see Permissions
earlier in this paper.
If workflow is enabled for a document library, an additional menu
item is displayed as Approve/reject. Click this menu item to approve or
reject the document. You can select from these options:
· Approved
– The document is public—it is available to anyone with view or read
permissions.
· Rejected
– The item is not made public.
· Pending
– The item is not available to someone with view or read permissions, but
is available to the person who created the document and to anyone with the Manage
Lists permission.
Dashboards
and Web Parts
Dashboards are a visual way to enable a user to get impressions
of the state of their business “at a glance.” Dashboards can be used for
displaying all types of data, including Key Performance Indicators (KPIs), scorecards,
scoreboards, and even reports. It is a common scenario to have a SharePoint
dashboard that serves as a starting place for users to generate business
reports. Reports can be segmented by business function, role, department, or
any other division that makes sense for your business. Building dashboards and
scorecards is largely handled by Microsoft Office PerformancePoint™ Server 2007
and is out of the scope of this paper. However, you can use Microsoft
SharePoint technologies to build dashboards using Web Parts.
Report
Viewer Web Part
The Report Viewer Web Part is added to the SharePoint Web Parts
gallery when the Reporting Services add-in is installed. The Report Viewer Web
Part is used to render reports from any document library that contains Reporting
Services reports in full page view.
You can embed Reporting Services functionality into any page or
dashboard on a SharePoint site. For example, you can easily render a report of
year-to-date sales on the home page of your SharePoint intranet site by using
Reporting Services.
To add a Report Viewer Web Part to your site, simply edit the
SharePoint page and add a new Web Part to a zone. Select SQL Server
Reporting Services Report Viewer, located in the Miscellaneous
section, as shown in Figure 8.
.jpg)
Figure 8: Adding the Report Viewer
Web Part
After the Report Viewer is added to the Web page, you can quickly
render a report. Open the tool pane and browse to the report in a SharePoint
document library. The selected report is rendered on the page, as shown in
Figure 9.
.jpg)
Figure 9: Viewing a Report in the
Report Viewer Web Part
Filter
Web Parts
Adding a Report Viewer to a Web page in a SharePoint site renders
a Reporting Services report. But what if you want to limit (or filter) the data
displayed in the Web Part, based on a selection in a different Web Part? There
are many filter Web Parts that enable this.
For example, suppose you have two Report Viewer Web Parts on a
page. Each displays a different view of company sales data. One displays a
matrix report and one displays a graph. It is cumbersome to filter the date of
each Web Part separately, so a Date Filter Web
Part could be very helpful in assigning the date to each report at the same
time.
To use a filter Web Part, after one or more Report Viewer Web
Parts are added to the Web page, place a Date Filter Web Part on the page by
using the same procedure described in Report Viewer Web Part earlier in this
document. Now just connect the Date Filter Web Part to the Report Viewer Web
Part(s). This is necessary so that the Report Viewer Web Part(s) know when a
date is entered into the Date Filter Web Part. To assign the value of the Date
Filter Web Part to the Report Viewer Web Part(s), simply modify the connections
of the Report Viewer Web Part(s), as shown in Figure 10.
.gif)
Figure 10: Specifying Report Viewer
Connections
Once the Date Filter Web Part is connected to the Report Viewer
Web Part, a date can be entered in the Date Filter Web Part and the report is
updated accordingly.
Conclusion
Microsoft Business Intelligence puts decision making in the hands
of every user within an organization. Combining familiar end-user tools with
powerful back-end technologies is the key to rapid design, development,
deployment, and acceptance of BI solutions.
The integration of Microsoft SharePoint product and technologies
(Windows SharePoint Services 3.0 and Office SharePoint Server 2007)
with SQL Server 2005 Reporting Services provides a single environment for
all information needed by a business user, available through a common Web-based
user interface.
With the BI platform in SQL Server 2005 and the
2007 Microsoft Office system, users have all the tools they need to start
making better business decisions.
About
the Author
Anthony T. Mann is the President of Mann
Publishing Group, which publishes business and technical books
under its Rational Press and Agility Press imprints. He is a veteran computer
book author, writing more than 15 titles and regularly produces technical
whitepapers and other content for Microsoft. He can be reached by e‑mail
at tmann@mannpublishing.com.
For more information:
http://www.microsoft.com/sql/
http://www.microsoft.com/BI