Understanding Reporting Services and RDL


As I said, the core reference on SQL Server Reporting Services is our book on Reporting Services[2], but to summarize a 784-page book, Reporting Services is an ASP.NET service that manages a SQL Server database that maintains a database repository of reports formatted using the industry-standard[3] XML-based report definition language dubbed "RDL." An RDL report might contain references to the actual source of data, but this is not a firm requirement. It does include references to the data "columns" or fields to be displayed or used in expressions managed by the RDL report generator. SQL Server Reporting Services maintains these RDL definitions in a SQL Server database for on-demand retrieval and rendering. Based on parameters passed to the ASP.NET Reporting Services service, the report is connected with a data source, the data is retrieved, and the report is constructed and rendered given the format and expressions built into the report. RDL definitions can also incorporate values from input parameters used to focus the query, change the way a report is rendered, or deal with any number of other issues, including formatting, special rendering or printing instructions, and many others.

[2] Hitchhiker's Guide to SQL Server 2000 Reporting Services (Addison-Wesley, 2004; ISBN 0321268288).

[3] W3C standard RDL.

Figure 14.1 shows a broad overview of the SQL Server Reporting Services architecture. Basically, Reporting Services is an IIS-based executable that exposes its functionality via Simple Object Application Protocol (SOAP)[4]. The Visual Studio Report Designer is used to create the reports and deploy them to the SQL Server Reporting Services reports database catalog. The Reporting Services engine has total responsibility for the reportit manages the connection, executes the query (including prompting for and managing query and report parameters), generates the report based on the RDL report definition, and renders the report in one of a number of user-selectable formats. Reporting Services also includes mechanisms to protect reports and the data they expose, and permit reports to be automatically distributed on demand or on a predetermined schedule. The Reporting Services toolset includes the Report Manager, which is a browser-based tool used by DBAs and users alike to configure, tune, and access reports exposed in a hierarchical directory structure hosted on the IIS and SQL Server.

[4] www.w3.org/TR/soap/

Figure 14.1. SQL Server Reporting Services overview.


To give you an idea what I'm talking about when it comes to RDL-formatted XML, take a look at Figure 14.2it's an RDL file exposed in a browser as XML.

Figure 14.2. A typical report's RDL file displayed as XML.


The RDL in this specific report was generated using the Visual Studio Business Intelligence Development Studio (BIDS) Report Designerit contains the following elements that describe the report:

  • This RDL report definition addresses a data source whose actual connection details are kept elsewhere indexed by a GUID. This means that this report must be "hooked up" with a specific Data Source that contains the actual ConnectionString details when it's time to render the report. Yes, an RDL definition could point to a "private" or report-specific Data Source, and in this case, the RDL contains <ConnectionString> and <DataProvider> elements.

  • The RDL report definition describes the report size, shape, and margins, along with other formatting details, but does not specify the manner in which it should be rendered. That is, while the report is described in every detail, there is nothing in the RDL that says how it should be displayed in a browser, on a printer, or in an Adobe PDF file.

  • Some of the elements in the RDL file (like <ReportID>) are not part of the RDL specification. If you look closely, you'll see that ReportID is prepended with rd:, which means it's part of the rd namespace. If you look at the top of the document, you'll see that the rd namespace is that for the Report Designer. There are lots of undocumented parts of the RDL that you'll see as part of the rd namespace that are used by the Report Designer. It is possible to extend RDL to utilize your own namespaces as long as you properly reference them.

  • The report body section contains elements and attributes (XML-speak) that specify the layout of the report items such as TextBox, Table, Chart, and such. These are collapsed behind the +<Body> tag. The RDL in this section describes how each item is laid out and how they are to be populated with data or expressions.

  • The DataSets section contains the Field definitions for each dataset used in the report, along with a Query definition used by the data processing extension to retrieve data.

  • The DataSets section contains the SQL query that returns columns that map to the data fields to be mapped to specific report elements. Yes, field values can be expression-based. However, it's not possible to combine fields or create expressions that combine fields from different datasets in a report.

The RDL shown previously was used to create the report shown in Figure 14.3[5].

[5] Well, yes, the RDL shown above is only a subset of the entire RDL report definition.

Figure 14.3. A simple sample summary report.


This report was built using the BIDS Report Designer in about 10 minutes. Okay, it might take someone unfamiliar with the Report Designer a bit longer, but the process is really pretty easy. As I said, this is not a chapter about the BIDS Report Designerit's about Visual Studio 2005 and how it implements this technology. However, whether or not you've used Reporting Services, you might (just might) miss some of the features supported by BIDS that are not supported in Visual Studio. The following list summarizes what you'll have to do withoutbasically, Reporting Services supports the following features not exposed in Visual Studio 2005:

  • The BIDS Report Designer includes a report wizard that walks you through the process of building reports or a new report project. The Report Project Wizard walks through the process of setting up a shared or report-specific Data Source and the initial report query.

  • The BIDS report wizard walks you through the process of creating a report from the initial query. It also leverages several (fixed) layout templates that can be used to customize the look of your reports. It can help get you started with either "table-based" or "Matrix" reports, build templated headings, and help build basic groups.

  • The BIDS Report Designer includes interactive Data Source and DataSet designers that support easy WYSIWYG creation or alteration of the queries used to populate reports and parameter pick lists. This interface includes both a generic query interface that can take virtually any query syntax, including stored procedure invocation, and a "GUI" query designer used to create simpler table-based queries.

  • The BIDS Report Designer supports several different techniques to implement and configure "code-behind" routines that can be invoked directly from the query designer or behind the scenes in the report properties.

  • The BIDS Report Designer includes an interactive report parameters page that permits developers to specify a variety of ways to configure how parameters are defined, set to default values, or populated from pick lists. This feature should not be under-estimated in its power and usability.

  • The BIDS Report Designer is extensible with third-party controls like the new add-in Chart control from Dundas[6]. Don't expect to be able to use these controls in the Visual Studio 2005 Report Designernot anytime soon.

    [6] Dundas is a well-respected third-party add-in vendorand it's pronounced with an Irish accent: "Duundis".

  • Once the report is deployed on the SQL Server report server, it can be called up using the Report Manager ASP page that permits developers, DBAs, and users to access reports, specify and fine-tune security personas, manage (and delete) shared data sources, define subscription schemes, and move reports between directories.

  • Access to the reports from Reporting Services can be channeled through an SSL connection to encrypt all traffic being sent to and from the user. This protects the confidentiality of the report. This approach also means that the user application does not have access to the RDL used to specify the Data Source, credentials, or queries used to generate the report.

  • Report access is governed by a Reporting Servicesspecific user/group/administrator hierarchy of rights that permits the DBA to ensure that only specific users or groups can perform administrative operations or access specific reports or report directories.

  • Reports can be programmed using the Report Manager (report "explorer") to prompt users for report and query parameters or simply run with default parameter settings. These parameters can be managed by the Report Manager (at least, to a great extent), where default values and parameters can be set or hidden on a user-by-user basis (based on the user's group). The report UI supports sophisticated cascading or multi-select parameter schemes.

  • Reports launched from SQL Server Reporting Services can be re-rendered to an XML, CSV (comma delimited), TIFF, or Excel file or a Web Archive format. Users can also choose to tune page setup specifications, hide/display report parameters, and interactively zoom the report view as desired. In the 2005 version, users can choose to route reports to a selected printer.

  • The report server can be programmed using the Report Manager to launch reports at scheduled times, cache reports so they don't have to be re-executed or re-rendered more than once, push reports to specific users, or permit reports to be pulled by users on demand.

Yes, I could go onthis isn't anywhere near the entire list of SQL Server Reporting Services features, but you get the idea. Just understand that Reporting Services is a more sophisticated report-management system designed for companies that have a significant report workload. As Peter has pointed out on many occasions, there is still a lot of work to do to make SQL Server Reporting Services an "enterprise-ready" reporting tool.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net