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:
Figure provides a graphical representation of the 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:
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>
Table describes the memory configuration elements in the configuration file.
Memory Configuration Elements
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.
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:
Table lists the fields that can be contained within the log.
HTTP Log Fields
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:
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.
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 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
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
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
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
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:
Setting Report Server Web Services URL information
Updated Database Configuration Wizard
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:
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.