Chapter 9: Reporting Services Enhancements

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

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:

=Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value,
Fields!StProvName.Value, "Dataset1")

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:

=Join(MultiLookup(Split(Fields!StateProvinceCode.Value, ","), Fields!StProv.Value,
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:

LookupSet(Fields!SalespersonCode.Value,Fields!SalesperonCode.Value,
Fields!CustomerName.Value,"Dataset2").Length

Aggregation

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 detail rows.

FIGURE 9-4 Aggregation of an aggregation

Here is the expression for the value displayed in the Monthly Average row:

=Avg(Sum(Fields!SalesAmount.Value,"EnglishMonthName"))

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)

Page Numbering

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 " +
CStr(Globals!OverallTotalPages) +")"

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:

=Variables!MyVariable.SetValue(Now())

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.

Layout Control

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.

Pagination Properties

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 box.

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:

=iif(Globals!RenderFormat.Name="EXCEL",False,True)

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 Excel.

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 sheet.

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.

Data Synchronization

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

Data Visualization

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.

Data Bars

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

Sparklines

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

Indicators

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.

Maps

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

Reusability

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.

Shared Datasets

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

Cache Refresh

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

Report Parts

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 publishing.

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:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<service xmlns:atom="http://www.w3.org/2005/Atom"
xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app">
  <workspace>
    <atom:title>Reseller Sales</atom:title>
    <collection
    href="http://yourserver/ReportServer?%2fExploring+Features%2fReseller+Sales
&rs%3aCommand=Render&rs%3aFormat=ATOM&rc%3aDataFeed=xAx0x0">
      <atom:title>Tablix1</atom:title>
    </collection>
  </workspace>
</service>

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 Gallery.

Edit Sessions

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 been eliminated.

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 release of SQL Server, which now supports AJAX (Asynchronous JavaScript and 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 computer.

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 http://msdn.microsoft.com/en-us/library/cc281309.aspx.

RDL Sandboxing

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.

SharePoint Integration

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 news.

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 or 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 troubleshooting process.