This chapter is taken from book "Introducing
Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for
If you thought Microsoft SQL Server 2008 Reporting Services introduced a lot of
great new features to the reporting platform, just wait until you discover
what's new in Reporting Services in SQL Server 2008 R2. The Reporting Services
development team at Microsoft has been working hard to incorporate a variety of
improvements into the product that should make your life as a report developer
or administrator much simpler.
New Data Sources
This release supports a few new data sources to expand your options for report
development. When you use the Data Source Properties dialog box to create a new
data source, you see Microsoft SharePoint List, Microsoft SQL Azure, and
Microsoft SQL Server Parallel Data Warehouse (covered in Chapter 6, "Scalable
Data Warehousing") as new options in the Type drop-down list. To build a dataset
with any of these sources, you can use a graphical query designer or type a
query string applicable to the data source provider type. You can also use SQL
Server PowerPivot for SharePoint as a data source, although this option is not
included in the list of data source providers. Instead, you use the SQL Server
Analysis Services provider and then provide the URL for the workbook that you
want to use as a data source. You can learn more about using a PowerPivot
workbook as a data source in Chapter 10, "Self-Service Analysis with PowerPivot."
Expression Language Improvements
There are several new functions added to the expression language, as well as new
capabilities for existing functions. These improvements allow you to combine
data from two different datasets in the same data region, create aggregated
values from aggregated values, define report layout behavior that depends on the
rendering format, and modify report variables during report execution.
Combining Data from More Than One Dataset
To display data from more than one source in a table (or in any data region, for
that matter), you must create a dataset that somehow combines the data because a
data region binds to one and only one dataset. You could create a query for the
dataset that joins the data if both sources are relational and accessible with
the same authentication. But what if the data comes from different relational
platforms? Or what if some of the data comes from SQL Server and other data
comes from a SharePoint list? And even if the sources are relational, what if
you can access only stored procedures and are unable to create a query to join
the sources? These are just a few examples of situations in which the new Lookup
functions in the Reporting Services expression language can help.
In general, the three new functions, Lookup, MultiLookup, and LookupSet, work
similarly by using a value from the dataset bound to the data region (the
source) and matching it to a value in a second dataset (the destination). The
difference between the functions reflects whether the input or output is a
single value or multiple values.
You use the Lookup function when there is a one-to-one relationship between the
source and destination. The Lookup function matches one source value to one
destination value at a time, as shown in Figure 9-1.
FIGURE 9-1 Lookup function results
In the example, the resulting report displays a table for the sales data
returned for Dataset2, but rather than displaying the StateProvinceCode field
from the same dataset, the Lookup function in the first column of the table
instructs Reporting Services to match each value in that field from Dataset2
with the StProv field in Dataset1 and then to display the corresponding
StProvName. The expression in the first column of the table is shown here:
The MultiLookup function also requires a one-to-one relationship between the
source and destination, but it accepts a set of source values as input.
Reporting Services matches each source value to a destination value one by one,
and then returns the matching values as an array. You can then use an expression
to transform the array into a comma-separated list, as shown in Figure 9-2.
FIGURE 9-2 MultiLookup function results
The MultiLookup function in the second column of the table requires an array of
values from the dataset bound to the table, which in this case is the
StateProvinceCode field in Dataset2. You must first use the Split function to
convert the comma-separated list of values in the StateProvinceCode field into
an array. Reporting Services operates on each element of the array, matching it
to the StProv field in Dataset1, and then combining the results into an array
that you can then transform into a comma-separated list by using the Join
function. Here is the expression in the Territory column:
Fields!StProvName.Value, "Dataset1 "), ", ")
When there is a one-to-many relationship between the source and destination
values, you use the LookupSet function. This function accepts a single value
from the source dataset as input and returns an array of matching values from
the destination dataset. You could then use the Join function to convert the
result into a delimited string, as in the example for the MultiLookup function,
or you could use other functions that operate on arrays, such as the Count
function, as shown in Figure 9-3.
FIGURE 9-3 LookupSet function results
The Customer Count column uses this expression:
The aggregate functions available in Reporting Services since its first release
with the SQL Server 2000 platform provided all the functionality most people
needed most of the time. However, if you needed to use the result of an
aggregate function as input for another aggregate function and weren't willing
or able to put the data into a SQL Server Analysis Services cube first, you had
no choice but to preprocess the results in the dataset query. In other words,
you were required to do the first level of aggregation in the dataset query, and
then you could perform the second level of aggregation by using an expression in
the report. Now, with SQL Server 2008 R2 Reporting Services, you can nest an
aggregate function inside another aggregate function. Put another way, you can
aggregate an aggregation. The example table in Figure 9-4 shows the calculation
of average monthly sales for a selected year. The dataset contains one row for
each product, which the report groups by year and by month while hiding the
FIGURE 9-4 Aggregation of an aggregation
Here is the expression for the value displayed in the Monthly Average row:
Conditional Rendering Expressions
The expression language in SQL Server 2008 R2 Reporting Services includes a new
global variable that allows you to set the values for "look-and-feel" properties
based on the rendering format used to produce the report. That is, any property
that controls appearance (such as Color) or behavior (such as Hidden) can use
members of the RenderFormat global variable in conditional expressions to change
the property values dynamically, depending on the rendering format.
Let's say that you want to simplify the report layout when a user exports a
report to Microsoft Excel. Sometimes other report items in the report can cause
a text box in a data region to render as a set of merged cells when you are
unable to get everything to align perfectly. The usual reason that users export
a report to Excel is to filter and sort the data, and they are not very
interested in the information contained in the other report items. Rather than
fussing with the report layout to get each report item positioned and aligned
just right, you can use an expression in the Hidden property to keep those
report items visible in every export format except Excel. Simply reference the
name of the extension as found in the RSReportServer.config file in an
expression like this:
=iif(RenderFormat.Name="EXCEL", True, False)
Another option is to use the RenderFormat global variable with the IsInteractive
member to set the conditions of a property. For example, let's say you have a
report that displays summarized sales but also allows the user to toggle a
report item to display the associated details. Rather than export all of the
details when the export format is not interactive, you can easily omit those
details from the rendered output by using the following expression in the Hidden
property of the row group containing the details:
=iif(RenderFormat.IsInteractive, False, True)
Speaking of global variables, you can use the new Globals!OverallPageNumber and
Globals!OverallTotalPages variables to display the current page number relative
to the entire report and the total page count, respectively. You can use these
global variables, which are also known as built-in fields, in page headers and
page footers only. As explained later in this chapter in the "Pagination
Properties" section, you can specify conditions under which to reset the page
number to 1 rather than incrementing its value by one. The variables
Globals!PageNumber and Globals!TotalPages are still available from earlier
versions. You can use them to display the page information for the current
section of a report. Figure 9-5 shows an example of a page footer when the four
global variables are used together.
The expression to produce this footer looks like this:
="Section Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)
" (Overall " + Cstr(Globals!OverallPageNumber) + " of " +
Read/Write Report Variable
Another enhancement to the expression language is the new support for setting
the value of a report variable. Just as in previous versions of Reporting
Services, you can use a report variable when you have a value with a dependency
on the execution time. Reporting Services stores the value at the time of report
execution and persists that value as the report continues to process. That way,
as a user pages through the report, the variable remains constant even if the
actual page rendering time varies from page to page.
By default, a report variable is Read Only, which was the only option for this
feature in the previous version of Reporting Services. In SQL Server 2008 R2,
you can now clear the Read- Only setting, as shown in Figure 9-6, when you want
to be able to change the value of the report variable during report execution.
FIGURE 9-6 Changing report variables
To write to your report variable, you use the SetValue method of the variable.
For example, assume that you have set up the report to insert a page break
between group instances, and you want to update the execution time when the
group changes. Add a report variable to the report, and then add a hidden text
box to the data region with the group used to generate a page break. Next, place
the following expression in the text box to force evaluation of the expression
for each group instance:
In the previous version of Reporting Services, the report variable type was a
value just like any text box on the report. In SQL Server 2008 R2, the report
variable can also be a .NET serializable type. You must initialize and populate
the report variable when the report session begins, then you can independently
add or change the values of the report variable on each page of the report
during your current session.
SQL Server 2008 R2 Reporting Services also includes several new report item
properties that you can use to control layout. By using these properties, you
can manage report pagination, fill in data gaps to align data groupings, and
rotate the orientation of text.
There are three new properties available to manage pagination: Disabled,
ResetPageNumber, and PageName. These properties appear in the Properties window
when you select a tablix, rectangle, or chart in the report body or a group item
in the Row Groups or Column Groups pane. The most common reason you set values
for these properties is to define different paging behaviors based on the
rendering format, now that the global variable RenderFormat is available.
For example, assume that you create a tablix that summarizes sales data by year,
and group the data with the CalendarYear field as the outermost row group. When
you click the CalendarYear group item in the Row Groups pane, you can access
several properties in the Properties window, as shown in Figure 9-7. Those
properties, however, are not available in the item's Group Properties dialog
FIGURE 9-7 Pagination properties
Assume also that you want to insert page breaks between each instance of
CalendarYear only when you export the report to Excel. After setting the
BreakLocation property to Between, you set the Disabled property to False when
the report renders as Excel by using the following expression:
Reporting Services keeps as many groups visible on one page as possible and adds
a soft page break to the report where needed to keep the height of the page
within the dimensions specified by the InteractiveSize property when the report
renders as HTML. However, when the report renders in any other format, each year
appears on a separate page, or on a separate sheet if the report renders in
Whether or not you decide to disable the page break, you can choose the
conditions to apply to reset the page number when the page break occurs by
assigning an expression to the ResetPageNumber property. To continue with the
current example, you can use a similar conditional expression for the
ResetPageNumber property to prevent the page number from resetting when the
report renders as HTML and only allow the reset to occur in all other formats.
Therefore, in HTML format, the page number of the report increments by one as
you page through it, but in other formats (excluding Excel), you see the page
number reset each time a new page is generated for a new year.
Last, consider how you can use the PageName property. As one example, instead of
using page numbers in an Excel workbook, you can assign a unique name to each
sheet in the workbook. You might, for example, use the group expression that
defines the page break as the PageName property. When the report renders as an
Excel workbook, Reporting Services uses the page break definition to separate
the CalendarYear groups into different sheets of the same workbook and uses the
PageName expression to assign the group instance's value to the applicable
As another example, you can assign an expression to the PageName property of a
rectangle, data region, group, or map. You can then reference the current value
of this property in the page header or footer by using Globals!PageName in the
expression. The value of Globals!PageName is first set to the value of the
InitialPageName report property when report processing begins and then resets as
each report item processes if you have assigned an expression to the report
item's PageName property.
One of the great features of Reporting Services is its ability to create groups
of groups by nesting one type of report item inside another type of report item.
In Figure 9-8, a list that groups by category and year contains a matrix that
groups by month. Notice that the months in each list group do not line up
properly because data does not exist for the first six months of the year for
the Accessories 2005 group. Each monthly group displays independently of other
monthly groups in the report.
FIGURE 9-8 Unsynchronized groups
A new property, DomainScope, is available in SQL Server 2008 R2 Reporting
Services to fix this problem. This property applies to a group and can be used
within the tablix data region, as shown in Figure 9-9, or in charts and other
data visualizations whenever you need to fill gaps in data across multiple
instances of the same grouping. You simply set the property value to the name of
the data region that contains the group. In this example, the MonthName group's
DomainScope property is set to Tablix1, which is the name assigned to the list.
Each instance of the list's group-category and year-renders an identical set of
values for MonthName.
FIGURE 9-9 Synchronized groups
Text Box Orientation
Each text box has a WritingMode property that by default displays text
horizontally. There is also an option to display text vertically to accommodate
languages that display in that format. Although you could use the vertical
layout for other languages, you probably would not be satisfied with the result
because it renders each character from top to bottom. An English word, for
example, would have the bottom of each letter facing left and the top of each
letter facing right. Instead, you can set this property to a new value,
Rotate270, which also renders the text in a vertical layout, but from bottom to
top, as shown in Figure 9-10. This feature is useful for tablix row headers when
you need to minimize the width of the tablix.
FIGURE 9-10 Text box orientation
Prior to SQL Server 2008 R2 Reporting Services, your only option for enhancing a
report with data visualization was to add a chart or gauge. Now your options
have been expanded to include data bars, sparklines, indicators, and maps.
A data bar is a special type of chart that you add to your report from the
Toolbox window. A data bar shows a single data point as a horizontal bar or as a
vertical column. Usually you embed a data bar inside of a tablix to provide a
small data visualization for each group or detail group that the tablix
contains. After adding the data bar to the tablix, you configure the value you
want to display, and you can fine-tune other properties as needed if you want to
achieve a certain look. By placing data bars in a tablix, you can compare each
group's value to the minimum and maximum values within the range of values
across all groups, as shown in Figure 9-11. In this example, Accessories 2005 is
the minimum sales amount, and Bikes 2007 is the maximum sales amount. The length
of each bar allows you to visually assess whether a group is closer to the
minimum or the maximum or some ratio in between, such as the Bikes 2008 group,
which is about half of the maximum sales.
FIGURE 9-11 Data bars
Like data bars, sparklines can be used to include a data visualization alongside
the detailed data. Whereas a data bar usually shows a single point, a sparkline
shows multiple data points over time, making it easier to spot trends.
You can choose from a variety of sparkline types such as columns, area charts,
pie charts, or range charts, but most often sparklines are represented by line
charts. As you can see in Figure 9-12, sparklines are pretty bare compared to a
chart. You do not see axis labels, tick marks, or a legend to help you interpret
what you see. Instead, a sparkline is intended to provide a sense of direction
by showing upward or downward trends and varying degrees of fluctuation over the
represented time period.
FIGURE 9-12 Sparklines
Another way to display data in a report is to use indicators. In previous
versions of Reporting Services, you could produce a scorecard of key performance
indicators by uploading your own images and then using expressions to determine
which image to display. Now you can choose indicators from built-in sets, as
shown in Figure 9-13, or you can customize these sets to change properties such
as the color or size of an indicator icon, or even by using your own icons.
FIGURE 9-13 Indicator types
After selecting a set of indicators, you associate the set with a value in your
dataset or with an expression, such as a comparison of a dataset value to a
goal. You then define the rules that determine which indicator properly
represents the status. For example, you might create an expression that compares
SalesAmount to a goal. You could then assign a green check mark if SalesAmount
is within 90 percent of the goal, a yellow exclamation point if it is within 50
percent of the goal, and a red X for everything else.
A map element is a special type of data visualization that combines geospatial
data with other types of data to be analyzed. You can use the built-in Map
Gallery as a background for your data, or you can use an ESRI shapefile. For
more advanced customization, you can use SQL Server spatial data types and
functions to create your own polygons to represent geographical areas, points on
a map, or a connected set of points representing a route. Each map can have one
or more map layers, each of which contains spatial data for drawing the map,
analytical data that will be projected onto the map as color-coded regions or
markers, and rules for assigning colors, marker size, and other visualization
properties to the analytical data. In addition, you can add Bing Maps tile
layers as a background for other layers in your map.
Although you can manually configure the properties for the map and each map
layer, the easiest way to get started is to drag a map from the Toolbox window
to the report body (if you are using Business Intelligence Development Studio)
or click the map in the ribbon (if you are using Report Builder 3.0). This
starts the Map Wizard, which walks you through the configuration process by
prompting you for the source of the spatial data defining the map itself and the
source of the analytical data to display on the map. You then decide how the
report should display this analytical data-by color-coding elements on the map
or by using a bubble to represent data values on the map at specified points.
Next, you define the relationship between the map's spatial data and the
analytical data by matching fields from each dataset. For example, the datasets
for the map shown in Figure 9-14 have matching fields for the two-letter state
codes. In the next step, you specify the field in your analytical data to
display on the map, and you configure the visualization rules to apply, such as
color ranges. In the figure, for example, the rule is to use darker colors to
indicate a higher population.
FIGURE 9-14 A map using colors to show population distribution
SQL Server 2008 R2 Reporting Services has several new features to support
reusability of components. Report developers with advanced skills can build
shared datasets and report parts that can be used by others. Then, for example,
a business user can quickly and easily pull together these preconstructed
components into a personalized report without knowing how to build a query or
design a matrix. To help the shared datasets run faster, you can configure a
cache refresh schedule to keep a copy of the shared dataset in cache. Last, the
ability to share report data as an Atom data feed extends the usefulness of data
beyond a single source report.
A shared dataset allows you to define a query once for reuse in many reports,
much as you can create a shared datasource to define a reusable connection
string. Having shared datasets available on the server also helps SQL Server
2008 R2 Report Builder 3.0 users develop reports more easily, because the
dataset queries are already available for users who lack the skills to develop
queries without help. The main requirement when creating a shared dataset is to
use a shared data source. In all other respects, the configuration of the shared
dataset is just like the traditional embedded dataset used in earlier versions
of Reporting Services. You define the query and then specify options, query
parameter values, calculated fields, and filters as needed. The resulting file
for the shared dataset has an .rsd extension and uploads to the report server
when you deploy the project. The project properties now include a field for
specifying the target folder for shared datasets on the report server.
NOTE You can continue to create embedded datasets for your reports as
needed, and you can convert an embedded dataset to a shared dataset at any time.
In Report Manager, you can check to see which reports use the shared dataset
when you need to evaluate the impact of a change to the shared dataset
definition. Simply navigate to the folder containing the shared dataset, click
the arrow to the right of the shared dataset name, and select View Dependent
Items, as shown in Figure 9-15.
FIGURE 9-15 The shared dataset menu
The ability to configure caching for reports has been available in every release
of Reporting Services. This feature is helpful in situations in which reports
take a long time to execute and the source data is not in a constant state of
change. By storing the report in cache, Reporting
Services can respond to a report request faster, and users are generally happier
with the reporting system. However, cache storage is not unlimited.
Periodically, the cache expires and the next person that requests the report has
to wait for the report execution process to complete. A workaround for this
scenario is to create a subscription that uses the NULL delivery provider to
populate the cache in advance of the first user's request.
In SQL Server 2008 R2 Reporting Services, a better solution is available. A new
feature called Cache Refresh allows you to establish a schedule to load reports
into cache. In addition, you can configure Cache Refresh to load shared datasets
into cache to extend the performance benefit to multiple reports. Caching shared
datasets is not only helpful for reports, but also for any dataset that you use
to populate the list of values for a parameter. To set up a schedule for the
Cache Refresh, you must configure stored credentials for the data source. Then
you configure the caching expiration options for the shared dataset and create a
new Cache Refresh Plan, as shown in Figure 9-16.
FIGURE 9-16 The Cache Refresh Plan window
After developing a report, you can choose which report items to publish to the
report server as individual components that can be used again later by other
report authors who have permissions to access the published report parts. Having
readily accessible report parts in a central location enables report authors to
build new reports more quickly. You can publish any of the following report
items as report parts: tables, matrices, rectangles, lists, images, charts,
gauges, maps, and parameters.
You can publish report parts both from Report Builder 3.0 and Report Designer in
Business Intelligence Development Studio. In Report Designer, the Report menu
contains the Publish Report Parts command. In the Publish Report Parts dialog
box, shown in Figure 9-17, you select the report items that you want to publish.
You can replace the report item name and provide a description before
FIGURE 9-17 The Publish Report Parts dialog box
When you first publish the report part, Reporting Services assigns it a unique
identifier that persists across all reports to which it will be added. Note the
option in the Publish Report Parts dialog box in Report Designer (shown in
Figure 9-15) to overwrite the report part on the report server every time you
deploy the report. In Report Builder, you have a different option that allows
you to choose whether to publish the report item as a new copy of the report. If
you later modify the report part and publish the revised version, Reporting
Services can use the report part's unique identifier to recognize it in another
report when another report developer opens that report for editing. At that
time, the report author receives a notification of the revision and can decide
whether to accept the change.
Although you can publish report parts in Report Designer and Report Builder 3.0,
you can only use Report Builder 3.0 to find and use those report parts. More
information about Report Builder 3.0 can be found later in this chapter in the
"Report Builder 3.0" section.
Atom Data Feed
SQL Server 2008 R2 Reporting Services includes a new rendering extension to
support exporting report data to an Atom service document. An Atom service
document can be used by any application that consumes data feeds, such as SQL
Server PowerPivot for Excel. You can use this feature for situations in which
the client tools that users have available cannot access data directly or when
the query structures are too complex for users to build on their own. Although
you could use other techniques for delivering data feed to users, Reporting
Services provides the flexibility to use a common security mechanism for reports
and data feeds, to schedule delivery of data feeds, and to store report
snapshots on a periodic basis. The Atom service document contains at least one
data feed per data region in the report if a report author has not disabled this
feature. Depending on the structure of the data, a matrix that contains adjacent
groups, a list, or a chart might produce multiple data feeds. Each data feed has
a URL that you use to retrieve the content.
To export a report to the Atom data feed, you click the last button on the
toolbar in the Report Viewer, as shown in Figure 9-18.
FIGURE 9-18 Atom Data Feed
The Atom service document is an XML document containing a connection to each
data feed that is defined as a URL, as shown in the following XML code:
version="1.0" encoding="utf-8" standalone="yes"
Report Builder 3.0
Report Builder 1.0 was the first release of a report development tool targeted
for business users. That version restricted the users to queries based on a
report model and supported limited report layout capabilities. Report Builder
2.0 was released with SQL Server 2008 and gave the user expanded capabilities
for importing queries from other report definition files or for writing a query
on any data source supported by Reporting Services. In addition, Report Builder
2.0 included support for all layout options of Report Definition Language (RDL).
Report Builder 3.0 is the third iteration of this tool. It supports the new
capabilities of SQL Server 2008 R2 RDL including maps, sparklines, and data
bars. In addition, Report Builder 3.0 supports two improvements intended to
speed up the report development process-edit sessions and the Report Part
Report Builder 3.0 operates as an edit session on the report server if you
perform your development work while connected to the server. The main benefit of
the edit session is to speed up the preview process and render reports faster.
The report server saves cached datasets for the edit session. These datasets are
reused when you preview the report and have made report changes that affect the
layout only. If you know that the data has changed in the meantime, you can use
the Refresh button to retrieve current data for the report. The cache remains
available on the server for two hours and resets whenever you preview the
report. After the two hours have passed, the report server deletes the cache. An
administrator can change this default period to retain the cache for longer
periods if necessary.
The edit session also makes it easier to work with server objects during report
development. One benefit is the ability to use relative references in
expressions. Relative references allow you to specify the path to subreports,
images, and other reports that you might configure as targets for the Jump To
action relative to the current report's location on the report server. Another
benefit is the ability to test connections and confirm that authentication
credentials work before publishing the report to the report server.
The Report Part Gallery
Report Builder 3.0 includes a new window, the Report Part Gallery, that you can
enable from the View tab on the ribbon. At the top of this window is a search
box in which you can type a string value, as shown in Figure 9-19, and search
for report parts published to the report server where the name or the
description of the report part contains the search string. You can also search
by additional criteria, such as the name of the creator or the date created. To
use the report part, simply drag the item from the list onto the report body.
The ability to find and use report parts is available only within Report Builder
3.0. You can use Report Designer to create and publish report parts, but not to
reuse them in other reports.
FIGURE 9-19 The Report Part Gallery
Report Access and Management
In this latest release of Reporting Services, you can benefit from a few
enhancements that improve access to reports and to management operations in
Report Manager, in addition to an additional feature that supports sandboxing of
the report server environment.
Report Manager Improvements
When you open Report Manager for the first time, you will immediately notice the
improved look and feel. The color scheme and layout of this Web application had
not changed since the product's first release, until now. When you open a report
for viewing, you notice that more screen space is allocated to the Report
Viewer, as shown in Figure 9-20. All of the space at the top of the screen has
FIGURE 9-20 Report Viewer
Notice also that the Report Viewer does not include a link to open the
report properties. Rather than requiring you to open a report first and then
navigate to the properties pages, Report Manager gives you direct access to the
report properties from a menu on the report listing page, as shown in Figure
9-21. Another direct access improvement to Report Manager is the ability to test
the connection for a data source on its properties page.
FIGURE 9-21 The report menu
Report Viewer Improvements
The display of reports is also improved in the Report Viewer available in this
XML). If you are familiar with earlier versions of Reporting Services, you can
see the improvement that AJAX provides by changing parameters or by using
drilldown. The Report Viewer no longer requires a refresh of the entire screen,
nor does it reposition the current view to the top of the report, which results
in a much smoother viewing experience.
Improved Browser Support
Reporting Services no longer supports just one Web browser, as it did when it
was first released. In SQL Server 2008 R2, you can continue to use Windows
Internet Explorer 6, 7, or 8, which is recommended for access to all Report
Viewer features. You can also use Firefox, Netscape, or Safari. However, these
browsers do not support the document map, text search within a report, zoom, or
fixed table headers. Furthermore, Safari 3.0 does not support the Calendar
control for date parameters or the client-side print control and does not
correctly display image files that the report server retrieves from a remote
If you choose to use a Web browser other than Internet Explorer, you should
understand the authentication support that the alternative browsers provide.
Internet Explorer is the only browser that supports all authentication methods
that you can use with Reporting Services- Negotiated, Kerberos, NTLM, and Basic.
Firefox supports Negotiated, NTLM, and Basic, but not Kerberos authentication.
Safari supports only Basic authentication.
NOTE Basic authentication is not enabled by default in Reporting
Services. You must modify the RSReportServer.config file by following the
instructions in SQL Server Books Online in the topic "How to: Configure Basic
Authentication in Reporting Services" at
When you grant external users access to a report server, the security risks
multiply enormously, and additional steps must be taken to mitigate those risks.
Reporting Services now supports configuration changes through the use of the RDL
Sandboxing feature on the report server to isolate access to resources on the
server as an important part of a threat mitigation strategy. Resource isolation
is a common requirement for hosted services that have multiple tenants on the
same server. Essentially, the configuration changes allow you to restrict the
external resources that can be accessed by the server, such as images, XLST
files, maps, and data sources. You can also restrict the types and functions
used in expressions by namespace and by member, and check reports as they are
deployed to ensure that the restricted types are not in use. You can also
restrict the text length and the size of an expression's return value when a
report executes. With sandboxing, reports cannot include custom code in their
code blocks, nor can reports include SQL Server 2005 custom report items or
references to named parameters in expressions. The trace log will capture any
activity related to sandboxing and should be monitored frequently for evidence
of potential threats.
SQL Server 2008 R2 Reporting Services continues to improve integration with
SharePoint. In this release, you find better options for configuring SharePoint
2010 for use with Reporting Services, working with scripts to automate
administrative tasks, using SharePoint lists as data sources, and integrating
Reporting Services log events with the SharePoint Unified Logging Service.
Improved Installation and Configuration
The first improvement affects the initial installation of Reporting Services in
SharePoint integrated mode. Earlier versions of Reporting Services and
SharePoint require you to obtain the Microsoft SQL Server Reporting Services
Add-in for SharePoint as a separate download for installation. Although the
add-in remains available as a separate download, the prerequisite installation
options for SharePoint 2010 include the ability to download the add-in and
install it automatically with the other prerequisites.
After you have all components installed and configured on both the report server
and the SharePoint server, you need to use SharePoint 2010 Central
Administration to configure the General Application settings for Reporting
Services. As part of this process, you can choose to apply settings to all site
collections or to specific sites, which is a much more streamlined approach to
enabling Reporting Services integration than was possible in earlier versions.
Another important improvement is the addition of support for alternate access
mappings with Reporting Services. Alternate access mappings allow users from
multiple zones, such as the Internet and an intranet, to access the same report
items by using different URLs. You can configure up to five different URLs to
access a single Web application that provides access to Reporting Services
content, with each URL using a different authentication provider. This
functionality is important when you want to use Windows authentication for
intranet users and Forms authentication for Internet users.
RS Utility Scripting
Report server administrators frequently use the rs.exe utility to perform
repetitive administrative tasks, such as bulk deployment of reports to the
server and bulk configuration of report properties. Lack of support for this
utility in integrated mode had been a significant problem for many
administrators, so having this capability added to integrated mode is great
SharePoint Lists as Data Sources
Increasing numbers of companies use SharePoint lists to store information that
needs to be shared with a broader audience or in a standard report format.
Although there are some creative ways you could employ to get that data into
Reporting Services, custom code was always part of the solution. SQL Server 2008
R2 Reporting Services has a new data extension provider that allows you to
access SharePoint 2007 or SharePoint 2010 lists. After you
create the data source using the Microsoft SharePoint List connection type and
provide credentials for authentication, you must supply a connection string to
the site or subsite in the form of a URL that references the site or subsite.
That is, use a connection string such as http://MySharePointWeb/MySharePointSite
http://MySharePointWeb/MySharePointSite /Subsite. A query designer is
available with this connection provider, as shown in Figure 9-22, allowing you
to select fields from the list to include in your report.
FIGURE 9-22 SharePoint list Query Designer
SharePoint Unified Logging Service
In SharePoint integrated mode, you now have the option to view log information
by using the SharePoint Unified Logging Service. After you enable diagnostic
logging, the log files capture information about activities related to Reporting
Services in Central Administration, calls from client applications to the report
server, calls made by the processing and rendering engines in local mode, calls
to Reporting Services Web pages or the Report Viewer Web Part, and all other
calls related to Reporting Services within SharePoint. Having all SharePoint-related
activity, including the report server, in one location should help the