Reporting Services Components - SQL Server 2008

Most companies store a vast array of data that can form the basis of many critical decisions affecting the performance and direction of the business. However, up until the release of Reporting Services with SQL Server 2000, the creation of reports based on this data involved the use of often expensive third-party tools, which frequently used proprietary formats for the definition of a report and weren’t well integrated.

One of the goals of Reporting Services was to provide a single, standard platform for the design, creation, deployment, and management of all reports, and to promote interoperability between different reporting environments by adopting a standard XML-based language to define the reports, called Report Definition Language (RDL).

The underlying architecture for Reporting Services in SQL Server 2008 has fundamentally changed, removing the need for IIS and drastically increasing the scalability of the entire reporting environment. One of the limitations with the previous architecture was that there was no caching or lazy loading of very large reports. This gave the perception of poor performance and sometimes resulted in memory errors.

The main components of Reporting Services are as follows:

  • Report Server service: A Windows service that exposes web services and Report Manager as web-based applications. It also is responsible for processing every client request, either to render a report or to perform a management task.
  • Report Manager: A browser-based tool for viewing and rendering reports, creating report subscriptions, modifying report properties, configuring security, and a host of other tasks. Report Manager is no longer hosted within IIS; it is now part of the core Report Server service.
  • Report ServerWeb Services: A web-based programmatic interface that allows any client to initiate a report processing or maintenance task.
  • Report Server Background Processing: The component primarily responsible for the generation and delivery of scheduled reports. It also contains functionality that maintains the report server database.
  • Metadata catalog: Stores all of the information related to reports, such as report definitions, data sources, report parameters, cached reports, security settings, scheduling and delivery information, and report execution log information.
  • Report Builder: A graphical client accessed through Report Manager. This client is designed to facilitate simple, ad hoc reporting by end users and is not a full-featured report development environment.
  • Report Designer: A graphical client, embedded within BIDS and also available as a standalone
    application, that allows you to design and deploy reports in a “drag-and-drop” environment.

Figure provides a graphical representation of the Reporting Services components.

Reporting Services components

Reporting Services components

Report Server Service

Past releases of Reporting Services had aWindows service that provided functionality such as scheduling, maintenance, and report delivery. Reporting Services also had web applications that provided a management user interface (UI) and a web services-based API, hosted in IIS, separate from the Windows service. For many, this reliance on IIS was a concern, and the number of components within the architecture increased the risk of configuration mistakes.

With Reporting Services in SQL Server 2008, all services are combined into a single Windows service. This single Windows service hosts three application domains: Report Manager, Web Services, and Background Processes. This architecture ensures that there is no loss of functionality from previous versions. The following sections will provide details on some of the key pieces of the new service architecture.

All the other components interact with the Report Server service. For example, to deploy reports, Report Builder calls the methods exposed by web services. The Report Manager web application initiates management operations via the web service. Client or server applications could call the web service to automate Reporting Services functionality.

The Report Server service also supports URL addressability so that you can embed reports in your application using a web browser. By passing different parameters along with the URL, you can control different aspects of your reports. For example, the following URL retrieves a report called employees, whose RDL file is stored in the HR subfolder of the reportserver virtual root directory (which points to the web service) of a report server called SRS03, and instructs it to render the report (the rs:Command parameter) in PDF format (via the rs:Format parameter):

Native Support for HTTP.sys and ASP.NET

As mentioned earlier, IIS is no longer needed to host the ASP.NET applications of Reporting Services. Reporting Services now provides native support for ASP.NET and HTTP.sys, and therefore Report Manager and Web Services are hosted within the single Report Server service. To make this all work, the service includes an HTTP listener that listens for requests on a specific URL or URLs. The URL is registered, during installation time or through the Reporting Services Configuration Manager, with the HTTP.sys component of the underlying Windows operating system.

Improved Memory Control

It is now possible to configure memory utilization for the Report Server service. This is a very importantnew capability. In past releases, there was no way to limit Reporting Services so that it did not consume all available server memory. You can do this configuration by editing the underlying Reporting Services configuration file (unfortunately, the settings that control memory utilization cannot be set from within the Reporting Services Configuration Manager).

The Report Server service will act in one of four ways based on the memory configuration settings and the memory pressure on the service. There is no default memory pressure setting. Instead, the service is constantly monitoring memory utilization and adjusting between the various memory pressure modes as required. These four modes of operation are as follows:

  • No Pressure: The Report Server service applications will be fairly liberal with the requests for memory. Each will request a set of memory before any requests are made, so that requests can be processed as quickly as possible.
  • Low: Existing requests continue to process and new requests are accepted. The Background Processing application group is given lower priority than the Web Services application group.
  • Medium: Existing requests continue to process and new requests are accepted if possible. Similar to low memory pressure, Background Processing is given the lowest priority. The Report Server service reduces the memory allocations from each of the three application groups (Report Manager, Web Services, and Background Processing), with Background Processing being reduced as much as possible.
  • High: Existing requests are slowed down to reduce the chance of memory errors, and no new requests are accepted. Memory allocations continue to be reduced from the three internal application groups. Memory is paged to disk. When new requests are denied, users will receive an HTTP 503 error until existing requests complete and free up resources. In very extreme cases, the service may actually restart an application group to make memory immediately available.

So how do we control the transition of the service between the low, medium, and high behaviors? Although we can’t control the actual behaviors, we can control the thresholds that the service uses to determine the level of memory pressure. Memory configuration settings are specified within the RSReportServer.config file, in the <Installation directory>Reporting ServicesReportServer directory. The following are some sample memory configuration settings:

<MemorySafetyMargin>80</MemorySafetyMargin>
<MaximumThreshold>190</MemoryThreshold>
<WorkingSetMaximum>4000000</WorkingSetMaximum>
<WorkingSetMinimum>2400000</WorkingSetMinimum>

Table describes the memory configuration elements in the configuration file.

Memory Configuration Elements

Memory Configuration Elements

Authentication

Reporting Services is no longer hosted within IIS and therefore needs to implement authentication and authentication extensions natively. AWindows Authentication extension is provided by default. This extension supports Kerberos, NTLM, and Basic authentication types. There is also a custom authentication type, which makes it possible to develop and configure a custom authentication extension that can authenticate against virtually any environment.

HTTP Logging

Reporting Services now provides its own HTTP logging so that records of requests can be monitored and verified by external HTTP aggregation tools. The format of the HTTP log is the same as the log file format provided by IIS.

HTTP logging is not enabled by default. To enable it, you need to make changes to the ReportServerService.exe.config file, which, for a default installation, is located at Program Files
Microsoft SQL ServerMSSQL.nReporting ServicesReportServerBin. The http:4 setting must be added to the Component element within the RStrace section of the configuration file, as in this example:

<RStrace>
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file" />
<add name="TraceFileMode" value="unique" />
<add name="HTTPLogFileName"
value="ReportServerService_HTTP_" />
<add name="HttpTraceSwitches" value="date,time,
activityid,sourceactivityid,clientip,
username,serverip,serverport,host,
method,uristem,uriquery,protocolstatus,
bytessent,bytesreceived,timetaken,
protocolversion,useragent,cookiereceived,
cookiesent,referrer" />
<add name="Components" value="all:3,http:4" />
</RStrace>

Table lists the fields that can be contained within the log.

HTTP Log Fields

HTTP Log Fields

Report Manager

The Report Manager web application allows you to browse, manage, and view your reports. Using the Report Manager interface, you can view the report hierarchy, select a single report to view, and then export that report to the multitude of formats that Reporting Services supports (such as HTML, Excel, TIFF, PDF, comma-delimited values, and XML). You can also manage your data sources, report parameters, execution properties, and subscriptions.

Three interesting pieces of functionality that Reporting Services provides and that you can manage through Report Manager are caching, report snapshots, and subscriptions. Caching allows you to increase the performance of your reporting solution. With caching enabled, a report can be added to the cache on its first execution and then retrieved from the cache, rather than the Reporting Services database, for subsequent requests.

A report snapshot is a point-in-time representation of both the layout of a report and its data. The rendering of a large report (one that contains a lot of data) can consume valuable resources, and you may not want to perform this action every time a different user requests the report. Instead, you can create a snapshot of the report and allow users to access that. You can also use report snapshots to keep a history of your report and see how it changes over time.

You can also create subscriptions to reports. Subscriptions provide a means of delivering reports to a specified location—an e-mail account, a fileshare, or a custom delivery location you code—at a specified time (for example, to the e-mail account of a department manager on the last Friday of every month). Reporting Services has two types of subscriptions:

  • Standard subscriptions: These are statically set up for one or more users and can be created and managed by individual users.
  • Data-driven subscriptions: These are system-generated subscriptions. Subscriber lists can be dynamically generated from multiple data source locations.

Data-driven subscriptions are great in scenarios where you have a very large list of subscribers who may want personalized data and report formats, and where the subscriber list may change over time. For example, you may want to deliver personalized reports for a thousand salespeople based on their own sales data. Some may want the report in HTML, and others may want a PDF. Rather than creating and managing a thousand individual subscriptions for these salespeople, you can create one data-driven subscription that queries a database for the list of salespeople, their e-mail addresses, and the format in which they wish to get the report.

As part of the subscription, you can select the delivery mechanism. Reporting Services supports delivering reports via e-mail or posting to fileshares. You can extend the delivery system in Reporting Services to deliver reports to other locations via a set of extensions called delivery extensions.

Metadata Catalog

Reporting Services requires that you have a SQL Server database to store metadata information. The metadata catalog, created as part of the Reporting Services installation, stores information such as data sources, report snapshots that contain the layout and data for a report at a specific point in time, and report history and credential information for authentication, if you have Reporting Services store that information.

This metadata catalog can be on the same server as your Reporting Services server, but most people deploy it on a separate server for performance reasons and for high availability using clustering.

Report Designer in BIDS

BIDS provides an integrated development environment (IDE) for SQL Server BI development. Using the graphical Report Designer in BIDs, you can connect to your data, write your queries, and design and deploy your reports, all in a drag-and-drop environment. Figure shows Report Designer within the BIDS environment.

Report Designer in BIDS

Report Designer in BIDS
Report Designer has a number of controls that you can use to add a UI to your form. Report Designer supports controls such as text boxes, tables, matrices, images, lines, charts, gauges, rectangles, subreports, and lists. Along with the standard Properties window, you’ll find nicely redesigned Properties dialog boxes, which have been standardized throughout Report Designer, as shown in Figure.

New look for the Properties dialog boxes in Report Designer

New look for the Properties dialog boxes in Report Designer

Report Designer allows you to extend any of your reports using expressions, or even to call code in a custom .NET assembly that you have associated with the report. Figure shows a sample report included with Reporting Services. It demonstrates the use of an expression to dynamically set the title of a report. Expressions are based on Visual Basic .NET (VB .NET) and can be used in many different properties in Reporting Services.

A report that shows using controls and expressions in Report Designer in SQL Server 2008

A report that shows using controls and expressions in Report Designer in SQL Server 2008

You can write script code using VB .NET and embed it directly in your report, or you can write a custom .NET assembly and call it from your report. Using embedded script code is the simpler of the two techniques, and you get the highest level of portability, since the custom .NET code is saved with the report definition file. To add your custom VB .NET code, simply open the Report Properties window of your report from within Report Designer and paste the code into the Custom Code section in the Code tab. Methods in embedded code are available through a globally defined Code member that you can access in any expression by referring to the Code member and method name (in other words, Code.methodname).

Use of a .NET assembly adds to the complexity of deployment, because assemblies are deployed as a separate file and must be on every server where the report runs. However, the payback is flexibility. The assemblies can be written in any .NET language and, once installed, are available to all reports that run on that server.

If you look at the source for your report, you will see that the report definition is an XML file. This XML file uses RDL (see SQL Server Books Online for the complete report schema definition). You could edit your report’s XML by hand, but Report Designer is a much better interface to create your reports! Since RDL is a published format, third-party tools can create RDL, and Reporting Services will consume that RDL.

Report Designer Preview

In addition to Report Designer within BIDS, we have Report Builder, which is available via a Click Onceinstallation from within Report Manager. You’ve seen how Report Designer provides a very rich set offunctionality, and it is loved by application and database developers. Report Builder, on the other hand,provides fairly high-level functionality and uses a semantic data model, called the Report Model, to mask the complexities of report building from the user. This allows users the ability to build simple ad hoc reports when needed, as discussed in more detail in the “End-User Ad Hoc Query and Reporting”section later in this chapter.

Well, it turns out that some users are much smarter than we thought, and they are looking for something as powerful as Report Designer in BIDS to use within an environment that they are familiar with, such as Word, Excel, or PowerPoint. To address this need, the Microsoft Office team developed just such a tool, currently called Report Designer Preview.

Figure shows a fairly complex report being edited within the new Report Designer Preview application. You can see that Report Designer Preview provides a ribbon and bubble menu, which is consistent with the new Microsoft Office 2007 UI. Also notice the Data menu on the left, which provides access and allows the creation of built-in fields, parameters, images, data sources, and datasets.

A report in Report Designer Preview

A report in Report Designer Preview

Along the bottom of the screen are the row and column grouping panes, which allow for manipulation of value groupings within the new Tablix data regions. There is also a Properties window (not shown in Figure), which is very similar to the BIDS Properties window.

SQL Server Management Studio Integration

In SQL Server 2008, a number of Reporting Services features have been removed from SQL Server Management Studio. Management Studio is no longer used to manage folder hierarchies, report content, or user permissions. Those tasks are left to the Report Manager application or SharePoint Server, if running in SharePoint integrated mode.

Management Studio is used to manage jobs, shared schedules, roles, and system roles. Figure shows browsing report server jobs, security, and schedule objects from Management Studio.

Browsing Reporting Services objects from SQL Server Management Studio

Browsing Reporting Services objects from SQL Server Management Studio

Reporting Services Configuration Manager

Because of all the architectural changes for Reporting Services, it was only natural that the Reporting Services Configuration Manager would need some upgrading. The new Configuration Manager includes panes for defining the Report Manager and Web Services URLs, and an upgraded wizard for creating Reporting Services databases. The Configuration Manager can be used to configure the settings of both remote and local servers.

The following are details for some of the main areas within the Configuration Manager:

  • Web Services URL: This is the URL that is used by client applications for connecting to Report Server Web Services. This setting defaults to ReportServer.

Setting Report Server Web Services URL information

Setting Report Server Web Services URL information

  • Database: The Database pane allows you to set the Reporting Services database and credentials. This pane also provides access to the Database Configuration Wizard, as shown in Figure, which allows for the creation or specification of a new Reporting Services database.

Updated Database Configuration Wizard

Updated Database Configuration Wizard

  • Report Manager URL: This pane allows you to configure the URL to use when accessing the Report Manager application. By default, it is Reports, as shown in Figure, for a URL of httpservernamereports.

Setting the Report Manager URL

Setting the Report Manager URL

Reporting Services Security

The final piece of Reporting Services architecture that we will discuss is security. Reporting Services supports role-based security. The default roles in the server are as follows:

  • Browser: Users assigned to the Browser role may only view reports, folders, and resources. They may also manage their own subscriptions.
  • Content Manager: Administrators are assigned to the Content Manager role by default. This role allows a user to perform every task available for objects such as folders, reports, and data sources.
  • My Reports: When the My Reports feature is enabled, this is the role that is automatically assigned to a user. It creates individual report folders specific to each Windows user, and allows those users to create and manage their own reports.
  • Publisher: Typically, this role is used for report authors who work with Report Designer to create and deploy reports. It provides the privileges required to publish reports and data sources to the report server.
  • System User: This role allows a user to view the schedule information in a shared schedule and view other basic information about the report server.
  • System Administrator: This role can enable features and set defaults. You can set site wide security and define role definitions. This role can also manage jobs on the server.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics