ptg
581
Managing Shared Schedules
36
FIGURE 36.11
Manage Report History menu.
You must be a site administrator to manage shared schedules on a SharePoint site.
To create shared schedules, click Site Actions at the upper right on the site, pick Site
Settings, Modify All Site Settings, and then click Manage Shared Schedules under the
Reporting Services section (see Figure 36.12).
Click Add Schedule to create a shared schedule. You can specify a descriptive name,
frequency, and a start and end date (see Figure 36.13).
Shared schedules rely on the SQL Server Agent service. Once the schedule is created, reports
and subscriptions associated with the schedule are triggered to run in a timely fashion. The
FIGURE 36.12
Manage shared schedules.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Download at WoweBook.com
ptg
582
CHAPTER 36 Managing Reports in SharePoint
FIGURE 36.13
Create shared schedules.
FIGURE 36.14
Pause and resume shared schedules.
shared schedule can be paused or resumed (see Figure 36.14). If it is paused, reports execu-
tions associated with the schedule will not get triggered until the schedule is resumed.
Managing Subscriptions
There is a Manage Subscriptions menu option when you select a report from the
SharePoint document library. Subscriptions enable you to receive reports through a variety

not available in Standard, Web, or Express editions.
Besides report and delivery extension information, a DDS includes information such as
the subscriber data source and query to use for generating the dynamic subscription infor-
mation at runtime.
FIGURE 36.15
Manage subscriptions.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
584
CHAPTER 36 Managing Reports in SharePoint
FIGURE 36.16
Standard subscription in SharePoint integrated mode.
Figures 36.17 through 36.20 show the various steps in the SharePoint UI for setting up a
DDS from the Create a Data-Driven Subscription option.
Step 1 is to define the data source and query that will provide the dynamic list of recipi-
ents, delivery settings, and report parameter values for each recipient.
Step 2 is to specify the parameter values to use with the report subscription. It can be the
report default, a static value, or a dynamic value returned from the query specified in step 1.
Step 3 is to specify the delivery options to use with the report subscription, including loca-
tion and rendering format.
Step 4 is to specify the schedule for delivering the subscription. Delivery time can be based
on a shared schedule, customized schedule, or whenever a report snapshot is created.
Once a data driven subscription is created, when you click it to view details in SharePoint,
there is a nice summary page that can be used to view and edit the various parts of the
subscription, such as data definition, parameters, delivery options, and scheduling info
(see Figure 36.21).
SharePoint Document Management
One of the key advantages of SSRS integration with SharePoint is that you can leverage
popular SharePoint document management features such as versioning, content approval,

Excel file is updated.
No wonder that IT departments want to deploy SSRS in SharePoint integrated mode. Users
are already trained in uploading, sharing, and securing documents via SharePoint, and
they can now treat reports just like any other document and take advantage of SharePoint
document management features.
If you are new to SharePoint and want to learn more, you can find a helpful white paper
on SharePoint document management at http://go.microsoft.com/fwlink/?LinkID=92638.
Summary
Report management is done in the SharePoint UI via the menu options on reports in the
SharePoint document library. These include the management of report properties, permis-
sions, data sources, parameters, processing options, history, schedules, and subscriptions.
DDS support has been enabled in SharePoint integrated mode with the Reporting Services
2008 release.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
This page intentionally left blank
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
CHAPTER
37
Ad Hoc Reporting in
SharePoint
IN THIS CHAPTER
. Report Builder Reports in
SharePoint
. Data Sources in SharePoint
. Report Models in SharePoint
. Report Builder 2.0

From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
590
CHAPTER 37 Ad Hoc Reporting in SharePoint
FIGURE 37.1
Add SSRS content types to a SharePoint document library.
FIGURE 37.2
New menu options for report items in a SharePoint document library.
Do not get confused by a default New Report menu item that shows up in SharePoint
Reports Center. It is a SharePoint-specific concept, and it does not refer to SSRS
reports. Instead, use the New Report Builder Report menu option for your ad hoc
reporting needs.
Report Builder Reports in SharePoint
When the New Report Builder Report menu option is selected, it will start the download
of a click-once Report Builder application from the Report Server in SharePoint Integrated
mode (see Figure 37.3).
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
591
Report Builder Reports in SharePoint
FIGURE 37.3
Click-once installation of Report Builder from Report Server.
37
With SSRS 2008, Report Server has a property setting called
ReportBuilderLaunchURL
.
Based on the value of this property set by the administrator, the Report Builder 1.0 or
Report Builder 2.0 application may get downloaded for the end user. Report Builder 1.0

brings up a SharePoint page for creating a shared report data source (see Figure 37.7).
Note that the SharePoint Data Connections document library is a logical place to save the
shared report data sources. However, you can save them in any document library where
reporting content types are enabled.
NOTE
It is also possible for reports in SharePoint integrated mode to use Office Data
Connection (ODC) files rather than report data sources in limited scenarios (such as
connecting to OLE DB or ODBC data sources). If you have existing ODC files that you
want to use with reports, be sure to first review the limitations by reading the Microsoft
SQL Server Books Online article titled “How to Use an Office Data Connection (.odd)
with Reports” at https://technet.microsoft.com/en-us/library/bb326418.aspx.
FIGURE 37.7
Create a new report data source from a SharePoint document library.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
594
CHAPTER 37 Ad Hoc Reporting in SharePoint
FIGURE 37.8
Create a new Report Builder model from a SharePoint document library.
Report Models in SharePoint
Report Builder 1.0 requires reports to be built on top of report models. An SMDL file repre-
sents a Report Builder model, and it is generally created via Model Designer, from where it
can be published to a SharePoint library. New Report Builder models can also be created
directly from the SharePoint document library. Figure 37.2 showed the Report Builder
Model option available on a SharePoint document. This brings up a SharePoint page for
creating a model (see Figure 37.8).
Creation of a Report Builder model requires a shared data source to be available on the
SharePoint site. The rules for the model generation are pretty much hidden from the user’s
perspective. After the model has been generated, there is an option to regenerate it if needed.

<location path=”_vti_bin/ReportBuilder/ReportBuilder.application”>
<system.web>
<authorization>
<allow users=”*” />
</authorization>
</system.web>
</location>
<location path=”_vti_bin/ReportBuilder/ReportBuilder.exe.manifest”>
<system.web>
<authorization>
<allow users=”*” />
</authorization>
</system.web>
</location>
<location path=”_vti_bin/ReportBuilder/ReportBuilder.chm.deploy”>
<system.web>
<authorization>
<allow users=”*” />
</authorization>
</system.web>
</location>
<location path=”_vti_bin/ReportBuilder/ReportBuilder.exe.deploy”>
<system.web>
<authorization>
<allow users=”*” />
</authorization>
</system.web>
</location>
2. For non-English sites, ensure that you also add the appropriate LCID and culture
entries to the

If users encounter an
HTTP 401
or
400
error when launching Report Builder with basic
authentication, you need to ensure that users select the Remember Password check box
when they see the User Credentials dialog box at login time. The root cause is that the
login via the User Credentials dialog box results in SharePoint creating a
WSS_KeepSessionAuthenticated
cookie to ensure this user remains authenticated for the
duration of the session. Report Builder is launched using click-once, which is not aware of
this
WSS_KeepSessionAuthenticated
cookie and results in this error from SharePoint
during launch of Report Builder.
Summary
Ad hoc reporting is supported in SharePoint via Report Builder, which enables SharePoint
users to create new reports or edit existing reports from the SharePoint document library.
Report Builder reports are built on top of data sources and report models.
The SSRS add-in for SharePoint adds new content types on the SharePoint site for reports,
report data sources, and Report Builder models. This enables creation and sharing of these
report items in the SharePoint document library. These report items can then be used via
Report Builder for ad hoc reporting.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
APPENDIX
A
References and
Additional Reading

2008 Forum
. Blogs
. White Papers
. SharePoint Integration Links
. Additional Books
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
598
APPENDIX A References and Additional Reading
. Report Server Catalog Best Practices
. Technet Webcast: Building SQL Server Reporting Services 2008 Large-Scale Solutions
(Level 400)
Microsoft SQL Server 2008 Samples
www.codeplex.com/MSFTRSProdSamples/Release/ProjectReleases.aspx?ReleaseId=18649
SQL Server Reporting Services 2008 Forum
If you get stuck, you can get your questions answered at http://social.msdn.microsoft.
com/Forums/en-US/sqlreportingservices/threads/.
Blogs
. SSRS Team blog
http://blogs.msdn.com/sqlrsteamblog/
. Report Engine, report design blog
http://blogs.msdn.com/robertbruckner
. SharePoint integration blog
http://blogs.msdn.com/prash
. Report Server, report management, security blogs
http://blogs.msdn.com/lukaszp
http://blogs.msdn.com/jgalla
http://blogs.msdn.com/jameswu/
. Data visualization blogs

. Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions
http://msdn.microsoft.com/en-us/library/cc655659(SQL.90).aspx
. Using SQL Server 2005 Reporting Services with Hyperion Essbase
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-
29f31282b04d/UsingSSRSandESSbase.doc
SharePoint Integration Links
. Learn about SharePoint integrated mode
http://msdn.microsoft.com/en-us/library/cc281021.aspx
. Configure Reporting Services integration with SharePoint
http://msdn.microsoft.com/en-us/library/bb326356.aspx
. White paper: SQL Server 2005 Integration with Microsoft SharePoint Products and
Technologies
http://technet.microsoft.com/en-us/library/bb969100.aspx
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
600
APPENDIX A References and Additional Reading
Additional Books
. Microsoft SQL Server 2008 Analysis Services Unleashed
By Irina Gorbach, Alexander Berger, Edward Melomed (Sams Publishing, 2008)
ISBN: 0672330016
www.informit.com/store/product.aspx?isbn=0672330016
. Pro SQL Server 2005 High Availability
By Allan Hirt (Apress, 2007)
ISBN: 159059780X
. Microsoft SQL Server 2008 Analysis Services Step by Step
By Scott Cameron (Microsoft Press, 2009)
ISBN: 0735626200
From the Library of STEPHEN EISEMAN