SHAREPOINT INTEGRATION
The new features fall into two broad categories: those that apply to a report server configured for SharePoint integration mode ("Server-related enhancements") and those related to the SQL Server 2008 R2 Reporting Services Add-in for SharePoint applications. Some features apply to both categories.
SERVER-RELATED ENHANCEMENTS
Support for Multiple SharePoint Zones
In SQL Server 2008 Reporting Services, we have new options, via the alternate access mapping functionality in the SharePoint environment, to access report server items (in previous releases, report server items were available from only the default SharePoint zone). The latest release allows us to access these items from one or more of the following SharePoint zones:
• Default
• Intranet
• Extranet
• Internet
• Custom
This can be an advantage where SharePoint environment can be accessed by users from several zones. We could, for example, employ alternate access mapping to provide access to the same report server items from our primary SharePoint site for information consumers from the intranet and Internet zones.
Support for SharePoint User Token
Using a new server interface (called IRSSetUserToken), which débuts in SQL Server 2008 Reporting Services, we can use the SharePoint user token to connect to a SharePoint site, via the SharePoint Object Model, and then retrieve data from the site by using SharePoint credentials.
Claims Based Authentication
In addition to supporting the existing SharePoint User Tokens, SQL Server 2008 R2 Reporting Services in SharePoint integrated mode supports Claims Based Authentication.
Scripting with the rs Utility
Servers configured in SharePoint integrated mode are now supported using the rs utility, which can be employed to automate deployment and administration tasks.
ADD-IN FOR SHAREPOINT - RELATED ENHANCEMENTS AND ADDITIONS
When you use the SQL Server 2008 R2 Reporting Services Add-in for SharePoint applications, you’ll notice some improvements. In order to get the most benefit, Microsoft recommends that we use the SQL Server 2008 R2 version of the add-in with a SQL Server 2008 R2 report server.
Integration Configuration in Fewer Steps
The new SQL Server 2008 R2 Reporting Services Add-in for SharePoint applications makes it easier to integrate a SharePoint server with a Reporting Services report server. In the current version, fewer steps are required to configure report server integration within SharePoint Central Administration.
SUPPORT FOR SHAREPOINT UNIVERSAL LOGGING SERVICE
Reporting Services now provides several categories which can be configured to be a part of the SharePoint diagnostic logging. (SharePoint Foundation 2010 and SharePoint Server 2010 offer diagnostic logging to monitor activity on a SharePoint server.) The new categories are part of the 'SQL Server Reporting Services' log file area, and will log several categories of information, including the following:
• General: Logs events that involve access to the following items:
• Reporting Services Web pages
• Report Viewer HTTP handler
• Report access (.rdl files)
• Data sources (.rsds files)
• URLs on the SharePoint site (.smdl files)
• Database: Logs events that involve database access.
• Office Server General: Logs logon failures.
• Topology: Logs current user information.
• Web Parts: Logs events that involve access to the Report Viewer web part.
SQL Server 2008 R2 also makes available new categories for SharePoint diagnostic logging specifically for Reporting Services. The categories that log information with a product area of 'SQL Server Reporting Services' include the following:
• HTTP Redirector: Logs calls made by client applications to the report server.
• Configuration Pages: Logs calls from Reporting Services pages in SharePoint central administration
• UI Pages: Logs calls made by non-configuration-related pages within the SharePoint user interface.
• Soap Client Proxy: Logs calls made by the Reporting Services web pages or Report Viewer web part, to the report server.
• Local Mode Rendering: Logs call made by the Reporting Services rendering engine while in local mode.
• Local Mode Processing: Logs calls made by the Reporting Services processing engine while in local mode.
GETTING DATA FROM A SHAREPOINT LIST
A new Microsoft SharePoint List and query experience allows us to easily bring SharePoint List information into a report when we designate a SharePoint List as a data source for reports.
LOCAL MODE
Reports from Microsoft Access 2010 and the new Reporting Services SharePoint list data extension can run locally from the SharePoint document library (no connection to a SQL Server Reporting Services report server is required). In the new Local Mode we can use the Report Viewer to directly render reports from SharePoint (when the data extension supports local mode reporting).
Local mode supports rendering reports that have an embedded data source or a shared data source from an .rsds file. However, the management of the report or its associated data source is deliberately not supported in local mode, but only in connected mode.
HTTP REQUESTS
In the latest version of Reporting Services, an HTTP redirector has been put in place to listen for client requests (such as those from Report Builder) to the SharePoint web front end. The HTTP redirector will re-direct such requests to the Report Server.
LINKS DIRECTLY USE DOCUMENT LIBRARIES.
In SQL Server 2008 R2 Reporting Services, on a SharePoint server integrated with Reporting Services, report subscriptions and drill-through links work directly with the linked resources in the document library itself.
INCREASED LANGUAGE AND CHARACTER SUPPORT
The SQL Server 2008 R2 Reporting Services related interface within SharePoint now supports 37 languages, complex text layout with Thai, and right-to-left character sets with Hebrew and Arabic, and. In addition to document library menus and user interface controls, support includes the Report Viewer web part.
COLLABORATION AND REUSE
SQL Server 2008 R2 Reporting Services provides new features that support enhanced collaboration and reuse of components within the reporting environment. We can classify these new features within two groups: Report Parts and Shared Datasets.
Report Parts
Report Parts allow us to employ the various strengths and roles of team members. For example, a given team member can reuse, in his or her various reports, report parts that have been created and saved by another member, say, working within a developmental capacity. Previous versions of SQL Server Reporting Services did not offer a ready means of generating report parts (such as tables, charts and parameters) that might be used pervasively throughout our business intelligence solutions. SQL Server 2008 R2 Reporting Services supports our selection of individual report parts from a given report for uploading to a library on the report server, from which we can subsequently browse and select them for inclusion in prospective reports.
The items that we can publish as Report Parts include the following:
• Charts
• Gauges
• Images and embedded images
• Lists (via the Tablix data region)
• Maps
• Matrices (via the Tablix data region)
• Parameters
• Rectangles
• Tables (via the Tablix data region)
Report Parts are stored either on a report server, or within a SharePoint site that is integrated with a report server. Report Parts can be reused in multiple reports, and they can be updated on the server.
We can reuse report parts in many reports, where each report part we add to a given report uses a unique ID to maintain a relationship to the instance of the “parent” report part on the respective server or site. Report parts that display data (for example, a matrix, table, or chart) can now be based upon a shared dataset (see the section that follows); otherwise, when a report part is published, the dataset upon which it depends is saved as an embedded dataset. Moreover, embedded datasets can be based upon embedded data sources, but credentials are not stored in embedded data sources.
Choices for the type of dataset underlying a given Report Part can have security implications: if a Report Part depends upon an embedded dataset that uses an embedded data source, for example, the credentials for the embedded data source will need to be provided by anyone reusing this Report Part. (To avoid this, we would base our embedded and shared datasets upon shared data sources with stored credentials.)
SHARED DATASETS
The second new type of Report Server item that we see in SQL Server 2008 R2 Reporting Services is Shared Datasets. Shared Datasets can retrieve data from shared data sources that connect to external data sources. Shared Datasets use only shared data sources (embedded data sources are not allowed). A Shared Dataset can be based on a report model, or upon any data source for a supported Reporting Services data extension.
Many of us have no doubt experienced the need to use identical datasets within reports we designed that are similar in presentation, or that use similar or identical information. (This is particularly common when the datasets act as data sources for report parameters that are common to multiple reports). Before SQL Server 2008 R2 Reporting Services, we were limited to copying such a dataset’s definition from the Report Definition Language (RDL) of one report to that of another report to allow the latter to “share” the same dataset (the Business Intelligence Development Studio did not afford us the ability to copy and paste datasets between reports). The only other alternative was to create an identical dataset from scratch within the new report.
A Shared Dataset offers numerous benefits; at the top of the list is the fact that it provides a way to share a query among many reports. This practice helps report authors and others to build reports upon consistent sets of data. The dataset query can include dataset parameters. Moreover, we can configure a Shared Dataset to cache query results for specific parameter combinations. We can perform such a cache upon first use or through the specification of a schedule. Shared Dataset caching can be used together with report data feeds and report caching to help manage access to a data source, as well as to optimize our use of system resources.
We can “share” a dataset within the Report Designer simply by selecting Convert to Shared Dataset on the context menu that appears when we right-click a pre-existing dataset within a report. Once we have converted a standard dataset to a shared dataset, we can select that dataset from among a list of any other shared datasets, within the reports of the current Report Server project, simply by ensuring the selection of the radio button labeled Use a shared dataset within the Dataset Properties dialog that appears when we go to add a new dataset in the Report Data pane, Datasets folder.
When added to a report, a Shared Dataset (like a report part) maintains a relationship to the definition of the “parent” dataset on the report server. Unlike Report Parts, when the definition is changed, we do not have to accept updates. All reports that have the relationship always use the Shared Dataset definition on the report server.
DATA SOURCES
SQL Server 2008 R2 Reporting Services offers three new data sources types: Microsoft SQL Azure, Microsoft SQL Server Parallel Data Warehouse, and Microsoft SharePoint List. Each of these data sources types, together with a description of its nature and operation
DATA VISUALIZATION
SQL Server 2008 R2 Reporting Services provides three new ways to visualize data in reports: Maps, Sparklines and Data Bars, and Indicators. Let’s take a look at each of these ways, together with a description of its nature and operation, in the sections below.
MAPS
The new Map Wizard and Map Layer Wizard in Report Designer allow us to add maps and map layers to our reports, an example of which we can see in Illustration 3, to help visualize data against a geographic background. Once map elements are related with report data, we can control color, size, width, or marker type on any given layer, as well as add labels, legends, titles, a color scale, and a distance scale to help assist users in interpreting the map display. Moreover, we can add interactive features, such as tooltips and drill through links, or provide parameters that enable a user to interactively control the visibility of each layer, and much more.
SPARKLINES AND DATA BARS
Sparklines and Data Bars have the basic chart elements of categories, series, and values, but they have no legend, axis lines, labels, or tick marks. Both are simple charts used to convey much information in a little space (and often in-line with text), and they can be easily used within matrices and tables.
Sparklines and Data Bars are most effective when many of them are presented together, for rapid visual comparison (making it easy to identify outliers). Each Sparkline often represents multiple data points over time. Because Sparklines display aggregated data, they must go in a cell that is associated with a group (and are not added to a detail group in a table). By contrast, Data Bars can represent multiple data points, but typically illustrate only one (with each Data Bar typically presenting a single series).
INDICATORS
Indicators are available in Report Builder 3.0 and Report Designer, and are icon-like, minimal gauges that convey the state of a single data value at a glance. They are typically used in matrices or tables to present data in rows or columns.
Indicators are often used to reflect:
• Trends – with directional images such as arrows;
• Ratings – using incremental icons such as stars;
• States (of being) – with traffic lights or check marks.
Once we drag the indicator item from the toolbox to a report we are crafting in Report Designer, we are prompted to make a selection among various options
REPORT LAYOUT AND RENDERING
SQL Server 2008 R2 Reporting Services continues to add features designed to support more rendering options. Moreover, we can now use our reports as the source of data feeds and as exports to Microsoft Excel.
NAMING EXCEL WORKSHEET TABS
We can now generate the names of worksheet tabs when we export reports to Excel, using the properties of reports and page breaks in Tandem. We can provide an initial page name of a report that can be exported as the default name of worksheet tabs, or use page breaks and page names to provide different names for each worksheet tab.
RENDERING REPORTS TO DATA FEEDS
Via enhancements in SQL Server 2008 R2 Reporting Services, including the new Atom rendering extension, we can export a production report as a data feed or create a report whose primary purpose is provide data, in the form of data feeds, to applications. The option to use reports as a data feed gives us an additional way to provide data to applications. We might, for example, prefer to hide the complexity of a data source and make it simpler to use the data: We might, alternatively do this when targeted data is not easy to access through client data providers.) Another benefit would be the availability of familiar Reporting Services features, such as Report Manager, security, report snapshots and scheduling, to manage the reports that now provide data feeds. Whatever the business need, the capability to render reports directly to data feeds means reusability of existing reports, as well as a means of making best use of all the effort of creating and securing the underlying datasets.
REPORT PAGINATION AND TEXT ROTATION
SQL Server 2008 R2 Reporting Services introduces enhancements to page breaks in several key components, including:
• Tablix data regions (table, matrix, and list)
• Groups
• Rectangles
When we delimit the pages of a report using page breaks, we can now assign different reset page numbering and page names to those pages. Furthermore, we can leverage expressions to establish the dynamic update of page names and page numbers at runtime, or the conditional (in addition to full) disablement of page breaks.
An example of the settings involved in creating a page break, based upon a group (Product Category) within a sample report
We can now rotate standalone text boxes up to 270 degrees in:
• Report headers
• Report footers
• Report body
• Cells of tables
• Cells of tables
This rotation, and the display of text written vertically and bottom to top that it supports, can help us to better use “available real estate” to create more readable reports, fit more data on printed reports, and create reports with more graphical appeal.
AGGREGATES, EXPRESSIONS, AND FUNCTIONS
Aggregates, expressions and functions gain power in SQL Server 2008 R2Reporting Services. This includes:
• Enhanced expression support – New globals are introduced, including:
o OverallPageNumber and OverallTotalPages - Support for page numbering for the entire rendered report.
o PageName- Support for naming pages.
o RenderFormat - Support for information that is specified for the renderer.
A new read-only property (set by default) for report variables is introduced. This property provides a way to persist data across report rendering, page changes, and some types of user interaction. The value of the variable is set when the report is reprocessed, but is maintained in the current session. (This is the equivalent of setting the Writable property for a report variable in RDL.)
• Support for calculating aggregates of aggregates – we can now calculate aggregates of aggregates, and use this capability, as an example, to more precisely align horizontal and vertical axes for charts and scales for gauges (when nesting within a table, etc.).
• Lookup Functions – Expressions in data regions can now include references to functions which can retrieve names and values from a dataset that is not bound to the data region.
REPORT AUTHORING TOOLS
In SQL Server 2008 R2 Reporting Services both report authoring tools, Report Builder and Report Designer, been improved to make report design and creation easier and more efficient.
Enhanced Preview Experience in Report Builder 3.0
The introduction of edit sessions enables the reuse of cached datasets when previewing reports in Report Builder 3.0, resulting in quicker rendering. We can now reuse cached datasets when previewing reports, thanks to the introduction of edit sessions in Report Builder 3.0. This means not only quicker rendering, but, because edit sessions are bound to a report, we can use references to subreports, as well as relative references, in reports.
Easier Report Design and Editing
Report Builder 3.0 provides a user-interface for changing credentials when it is unable to connect to the data source. We can use the Enter Data Source Credentials dialog box to change the credentials used by Report Builder 3.0 at design time, to connect to the data source as the current Windows user, or provide a user name and password. (This is to compensate for the fact that run-time credentials, specified in the data source properties of a report, might not work for design time tasks such as creating queries and previewing reports, depending upon the setup of the local environment).
New Capabilities in the Graphical Query Designer
The new query designer provides a graphical user interface to create custom relationships between related and unrelated tables. The query designer also makes it easy to include aggregates and grouping that summarize data in queries. The graphical query designer can now be used to create queries that retrieve report data from Microsoft SQL Server, Microsoft SQL Azure, and Microsoft SQL Server Parallel Data Warehouse databases, as we noted earlier.
Similarly, the new graphical query designer for the Microsoft SharePoint List data source type simplifies the design and creation of queries that retrieve report data from SharePoint lists. The query designer lists the fields in each SharePoint list item on a SharePoint site for easy inclusion into a query; the designer also helps us to intuitively define filters within our queries to limit the amount of data returned.
CACHE REFRESH PLANS
SQL Server 2008 R2 Reporting Services introduces cache refresh plans, enabling us to cache shared dataset query results or reports from a schedule, or upon first use. (Previous versions allowed us to control caching at the report level only, resulting in the simultaneous caching of all datasets.) We can schedule cache refreshes through an item-specific schedule or a shared schedule, managing and scheduling report caching separately from report subscriptions. This means that we can refresh certain report components independently, and with varying frequencies, from others. Cache refresh plans for commonly used parameter combinations, as one illustration, can help improve data retrieval and report viewing response times.
REPORT MANAGER ENHANCEMENTS
Report Manager has been considerably updated in SQL Server 2008 R2 Reporting Services, to make design and creation easier. More noticeable changes in Report Manager include an enhanced layout, which provides easier navigation to manage report properties and report server items, as well as an updated color scheme. A new drop-down menu is accessible for each report or report server item in a folder, whereby we can access the various configuration options for the report or item we choose.
The Report Manager has improvements in navigation and workflow for viewing and managing reports and report server items, provided by the new drop-down menu to access various configuration options for each report or report server item in a folder. It eliminates of the need to render a report before accessing and configuring report properties when in default view. It has an updated Report Viewer toolbar, which includes some updates to the toolbar controls, as well as the ability (discussed earlier) to export report data to an Atom service document and data feeds. It also provides more “real estate” for Report Viewer when rendering reports.
BUSINESS INTELLIGENCE DEVELOPMENT STUDIO (BIDS)
The Business Intelligence Development Studio as installed with SQL Server 2008 R2 Reporting Services supports design, creation and maintenance of both SQL Server 2008 and SQL Server 2008 R2 reports, and of Report Server projects in the SQL Server 2008 R2 version of Business Intelligence Development Studio. This means that we can open, preview, save, and deploy either version of reports or Report Server projects.
We can set Report Server project properties to specify the version of the Report Server to which we wish to deploy reports, as well as to dictate how warnings and errors we might experience, when either upgrading a report from SQL Server 2008 to SQL Server 2008 R2, or reverting a report from SQL Server 2008 R2 to SQL Server 2008, are handled.
NEW REPORT DEFINITION LANGUAGE SCHEMA AND NEW WEB SERVICE ENDPOINT
The Report Definition Language (RDL) schema introduced in SQL Server 2008 R2 includes a new
In order to support management operations of the report server in both native mode and SharePoint integrated mode, SQL Server 2008 R2 includes a new Web Service management endpoint, named ReportingService2010,that merges the functionalities of both the ReportingService2005 (which is used for managing objects on a report server that is configured for native mode) and the ReportingService2006 (used for managing objects on a report server that is configured for SharePoint integrated mode) endpoints. Features that are introduced in SQL Server 2008 R2, such as shared datasets and cache refresh (which we have discussed earlier), are included within the new endpoint.
