Microsoft SQL Server 2000 Reporting Services

This chapter is from the book

Hitchhiker's Guide to SQL Server 2000 Reporting Services

Learn More Buy

This chapter is from the book

This chapter is from the book 

Hitchhiker's Guide to SQL Server 2000 Reporting Services

Learn More Buy

Writing Reports: The Report Designer

Reporting Services comes with a WYSIWYG Report Designer add-in to Visual Studio .NET 2003 (as shown in Figure 1.2) to aid in the construction of report definitions. The Report Designer provides a new set of Business Intelligence Project types containing two out-of-the-box templates: a Report Project wizard and a blank report project—we'll show you how to create your own Report Templates in Chapter 9. No, Visual Studio .NET doesn't have to be the Rolls Royce (or Cadillac) version to host the Reporting Services add-in. The inexpensive Visual Basic .NET 2003 Standard (or Academic) version is sufficient.

Figure 1.2 The Visual Studio .NET Report Designer Previewing a Report—Rendered in the UK

The Report Designer is as intuitive and easy to use as the Microsoft Access Report Designer, but it's far more powerful. The typical report-authoring approach is to create a Reporting Services DataSet (which, by the way, is not to be confused with an ADO.NET DataSet). This DataSet is connected to the source of the data through a Reporting Services Data Source, which is effectively a Connection string. Once the underlying data is provided for the report, the next step is to visually lay out the report controls, binding them to the underlying data, customize their properties, and preview the report. All of this can be done without having to involve the Reporting Services server.

Once you're ready, you can ask the Report Designer add-in to "deploy" either the report definition or the whole report project, which might contain several report definitions, to the designated IIS ^[7] server (where your Reporting Services virtual directories are hosted). This creates one or more managed reports on your server—one for each report in your project—and creates any Data Sources that are needed. At this point, users can execute any reports your report DBA has enabled to be visible. This chapter gives you an overview of this process, and Chapters 6 through 9 deal with the process in greater depth.

Define the Data Source

Usually, when creating a new report definition, you first wire up the report to its data source or sources. Data sources are typically SQL Server, Oracle, or other DBMS-style servers, but they can be anything exposed with a .NET Data Provider, or by ODBC or OLE DB. This means you can get report data from flat text files, spreadsheet files, Microsoft Access MDB files, or virtually any source of data. Sure, a report can extract information from several independent data sources, but if you need to JOIN data together from different data sources you'll need to do that through SQL Server's linked database facility.

Once you choose your data source, you need to create a report Data Source that connects to it and manages the credentials needed to access its data. Building a report's Data Source is very similar to creating an ADO.NET Connection string, and the Report Designer makes intuitive use of the very same Visual Studio .NET 2003 Data Link applet ^[8] dialog when creating report Data Sources. This applet assists in the validation and testing of the Connection string, driver choice, user credentials, and other driver-specific or provider-specific options. Sure, a report can have several Data Sources. A report's Data Source can also be created as a stand-alone object that can be shared among several reports (a "shared" Data Source), or alternatively it can be directly embedded and thus specific to a report. In Chapter 5, and again in Chapter 6, we'll discuss the security implications of embedded passwords in Data Sources and managed reports. Frankly, you won't see a single chapter that doesn't mention security in one context or another.

Create a DataSet

Typically, with a Data Source in place, you can create a query to populate a Reporting Services DataSet with data on which to base a report. So, as in ADO.NET, you need to create a SELECT query to return data from one or more tables (or views) or (better still) name a stored procedure and its parameters. One point to keep in mind: until the SQL Server 2005 version, the Reporting Services "DataSet" does not expose the same functionality as the ADO.NET DataSet. You can't "just" take an existing ADO.NET DataSet and pass it to a Reporting Services Report—at least not yet (unless you build a custom Data Processing extension, as we discuss in Chapter 13).

The Report Designer offers two ways to help build the SQL for your query: the default "generic" query designer, and the more familiar GUI-based Visual Design Tools' Query Builder. Figure 1.3 shows the generic query designer. It permits you to enter any ad hoc SQL query desired, including stored procedures—just change the command type on the toolbar over to stored procedure. To execute the SQL entered, click on the red ! icon. The generic designer might not be as easy to use as its GUI-based cousin, but it does not balk at complex queries as long as the syntax is understood by the target DBMS that the Data Source connects to.

Figure 1.3 The Generic Query Designer

You toggle the designer of choice by clicking the icon on the toolbar, as shown in Figure 1.3. The problem with this icon is that its tool tip always says "generic query designer" instead of toggling as it should. We expect this problem to disappear quietly in a Service Pack, although it didn't appear to be fixed in Service Pack 1.

The GUI-based query designer—which you may already be familiar with if you've used Access, Visual Basic 6.0, or Visual Studio .NET—makes it a breeze to create simple table-based DataSets (see Figure 1.4). Once you venture beyond the bounds of the GUI-based designer's ability to parse your query, you'll have to go back to the generic designer or resort to using stored procedures or views.

Figure 1.4 The GUI-Based Data Tools Query Designer

You get an A if you noticed we said DataSets, plural. Yes, a report definition can use any number of separate DataSet objects—each obtaining data from the DBMS to which they are connected through their respective Data Sources. And yes, their underlying queries can have input and output parameters, which can be bound to lookups, can be fixed values, can be strongly typed, can have defaults, can have configurable prompt strings, can be included in expressions, and much, much more.

The cognoscenti ^[9] who read our last book, ADO.NET Examples and Best Practices for C# Programmers, might have thought that the DataSet we're talking about here is the classic ^[10] ADO.NET DataSet. However, the Reporting Services DataSet doesn't implement all the features you may have seen before in an ADO.NET DataSet; it implements only the streamlined subset of the features that the development team needed immediately. For the next version release of Reporting Services, we are confident that the DataSet element will be more fully implemented.

One of the features that isn't there at the moment is DataSet support for multiple resultsets. However, you can take several approaches if you want to work with multiple resultsets; for example, you might write a custom Data Processing extension or deal with this entirely on the server in a stored procedure by performing an ugly call out to a middleware component.

Visually Lay Out the Report Controls

The Visual Studio .NET Report Designer add-in provides nine report "controls" (called Report Items) on the Toolbox palette (see Figure 1.5). The spec and BOL usually refer to these as controls, so we will too. Just understand that these "items" are not the same as the controls you use with other Visual Studio projects, and so you can't, at present, add custom controls to the toolbox and use them within a report definition. We understand that extensibility is a feature that's not too far off.

Figure 1.5 The Report Designer Toolbox Report Controls

These report controls can be dragged and dropped from the Toolbox palette onto the report definition layout surface. (Behind the scenes, the report's RDL definition is synchronized as the report design is visually manipulated and configured.)

The Layout tab is where you can most easily set the properties of your report's controls, just as you might do when designing a web or Windows form. Reporting Services permits you to place these controls virtually anywhere on the report. Be careful, though. Sizing the report can be tricky, as we'll discuss in Chapter 7.

Thankfully, there are enough report controls to build most types of report. For example, you'll find a Chart control that can build charts for every appetite, including line, column, area, and carbo-rich (bar, pie, and doughnut), along with the scatter, bubble, and stock types, including colorful 3D and explosive effects. Yes, these are the same chart types you've feasted on for years. We dedicate all of Chapter 8 to the Chart control.

You'll also find ways to lay out your report data using tabular layouts. For example, Reporting Services includes a Table control (shown in Figure 1.6) that lets you merge cells, create and sort multiple grouping rows, and enable report interaction with compelling drilldown into other areas of the same report and drill through onto other reports. For example, if you wanted the rendered report to be filtered on a particular postal code, you'll find it's easy to create a dropdown list or simple dialog to enter this parameter at runtime, just before the report is rendered. That's because, just like programs, these reports can be interactive. Yes, we also said "merge cells," and, what's more, it's possible to embed charts within tables, or tables within tables.

Figure 1.6 The Table Control in the Visual Studio .NET IDE

Another slick feature of the Visual Studio .NET 2003 Reporting Services Report Designer is that it supports dragging a Field into a cell in the Detail, Header, or Footer section of a Table control. When you do, the Designer does a lot of the binding legwork for you. For example, if you drag a Field into a cell in the Footer section, the Report Designer places the Field name in the Header cell and binds a sensible aggregate into the Footer cell; if it's not the aggregate you had in mind, you can always override it.

Oh, Fields! We forgot to mention that once a report has one or more DataSets, the Designer places their columns (AKA Fields) into a palette indexed by a DataSet combo box (as shown in Figure 1.7). This makes it easy to drag and drop selected Fields directly onto the report definition, at which time the Report Designer automatically wires up the bindings for you—just as with the Table control. Nope, there's nothing preventing you from the drudgery of manually binding controls to DataSet Fields if that's what helps you sleep better (or if you're being paid by the hour).

Figure 1.7 The Fields Palette Window

There's also another tabular data layout report control: the Matrix control (see Figure 1.8). And no, it's not supplied or licensed from the Wachowski Brothers, although it does provide a somewhat transcendental interactive user experience to intuitively drill into columns and rows. And no, it does not show data in long, scrolling columns of unrecognizable green characters, unless Mr. Smith gets involved. ^[11] Chapter 8 discusses the Matrix control.

Figure 1.8 The Matrix Control Rendered in a Report

We almost forgot to mention the most fundamental report controls from the Report Items toolbar because we simply took them for granted. As you know, no report would be complete without Line and Rectangle controls as well as data-bindable Textbox and Image controls.

In traditional report writers (such as Microsoft Access or Crystal Reports), you work with horizontal bands in the report that span the page and repeat for each row of data being shown. In contrast, Reporting Services uses a more Visual Basic forms-like approach. Here report controls can be hosted, or "parented," in data regions, which grow at runtime while other controls are automatically repositioned to make room. The advantage of this approach is that you can define multiple floating data regions, and not just above and below each other, as you do in banded reports. These data regions can appear virtually anywhere in the body of a report in any configuration (as shown in Figure 1.9). Think of the report definition as simply a container, or "form," for the parts of a report (the display controls) that sit between the report header and footer.

Figure 1.9 The Layout View with Two Side-by-Side Table Controls, an Image Control, and a Chart Control

As is to be expected, in Reporting Services, Report Designer can specify PageHeader and PageFooter sections in the report definition. These go at the top and bottom of report pages, and, naturally, a main Body section is positioned between the page header and page footer. Chapter 7 has a diagram (Figure 7.3) that shows how these areas are laid out. It's worth emphasizing that the body is not bindable to a DataSet, so the body can't repeat once for each record as the body does in Microsoft Access reports. Although this takes some folks by surprise, it's one of the most empowering features of Reporting Services.

We've mentioned the Table and Matrix tabular controls, and these certainly repeat for each record within themselves. However, not all reports call for tabular representations; many have a free-form repeating requirement, which Reporting Services provides via the List control. List controls can host other controls, and the List repeats once for each record in the DataSet to which it is bound.

In Reporting Services jargon, List, Table, and Matrix Report Items (controls) are data regions. What makes the tool so powerful is that these data regions can be laid out side by side (as shown in Figure 1.9), or even defined hierarchically within one another. The latter feature supports data that you want to be independently sorted, grouped, and filtered at the data region level.

You can lay out reports that need snaking columns (like a newspaper) by setting the Columns property on the report definition to the number of columns you want. Reports can also contain Subreport controls, which are basically reports within reports—and the nesting can get several layers deep before choking. There's also a wizard that takes the tough work out of translating and importing reports from Microsoft Access. A minor disappointment for us is that there isn't any out-of-the-box wizardry to translate, import, and then flush Crystal Reports out of our systems. We'll be happy to discuss converting your Crystal Reports (and Access Reports). See the website ^[12] for details.

Advanced Features: Programmability in the Report Designer

What a report looks like on the screen (how it's rendered) is controlled by a plethora of report definition and report control properties and the chosen Rendering extension. Although you can set many of these properties to permanent fixed values at design time, you can also set many of them to Visual Basic .NET expressions, which are evaluated when a managed report is prepared for rendering. It's also possible to make custom Visual Basic .NET code methods for use in any expression; to do this, you create Visual Basic .NET shared functions and paste them into the report's Code property. We show how to do that in Chapters 7 and 9.

This programmability makes it possible to do convoluted things, such as conditionally changing the text color in a Textbox control, but only when rendered by a specific user, only when the values are within certain ranges, or only when rendered on a Thursday afternoon between 3 and 4 PM by a certain user. This would be handy if you are a report programmer going into a review meeting with your boss at 3:30 and want to make an impact. Of course, the business logic possibilities are endless, and you'll probably have more serious requirements to implement!

Why use Visual Basic .NET and not C# (or both)? Well, behind the scenes a single .NET assembly is created for each managed report (using System.CodeDom). This assembly contains all the expressions and properties as property methods. It was a lot easier for Microsoft's developers to require that one and only one language assembly be compiled and used. Visual Basic .NET was chosen as being a slightly more user-friendly expression language, especially with paraprogrammers. By default, these expressions embedded in the report definition are given only Execute permission; this helps to prevent someone from creating a Trojan report by using an expression squirreled away that deletes files in the background, installs a virus, or bumps up your salary by 45 percent. By default, the Report Server does not permit access to the file system from custom code in expressions. Yes, a System Administrator might relax this, but this breach of the security dike would then apply to all expressions in all managed reports. We strongly advise against it, because there are other, more controlled ways to solve this problem.

What if you want to create some custom code that can be used by all reports? Well, the facility exists to create your own custom code class library assemblies in any Common Language Runtime (CLR) language, strongname them, reference them in the report definition's References Collection property, instantiate any specific classes, and subsequently use the class methods, or shared functions, in report expressions. The System Administrator must install each of these assemblies and grant them specific permissions. These can be as restrictive as None, denying any ability to run; can be Execute only, which relaxes a little more to permit the code to run in a restricted sandbox; can be completely unrestricted Full Trust; or any combination of specific permissions. All this is achieved through code access security (CAS). If a System Administrator granted sufficient privileges to an assembly, a Mission Impossible–style report could be created that, when rendered, would promptly fdisk the system in 30 seconds. But such a scenario is enabled only if the System Administrator has permitted it by breaching the security seals and leaving the door open. In Chapter 9 we'll show in detail how to work with expressions and custom assemblies and how to assign strongnames, and set code access security permissions.

We suggest using the principle of least privilege—that is, grant only the permissions needed to do the immediate tasks and no others. Full Trust permission should not be given arbitrarily, and this requires administrators to use fine-grained permissions. For example, if an assembly needs permission to open a certain file, it should be granted permissions only to that file and not to the whole hard drive, nor should it be fully trusted.

Document Maps

For large reports that extend over many pages, it's possible to create a navigable hierarchical tree view called a Document Map. Populating the nodes of this tree is as easy as putting a value into the Label property on any of the controls to be included in the Document Map (see Figure 1.10). This can be a fixed literal value, or, as just discussed, it can result from a calculated expression. If any labels have been set in any of the controls, the Document Map is deployed in a pane when the generated report is rendered in a browser. The Label property can also be bound to a DataSet Field. We'll show you this in action in Chapter 7.

Figure 1.10 A Document Map and Report as Rendered in a Browser

Previewing in the Designer

After you have visually laid out the report controls on the report definition, the Report Designer lets you render and debug the report locally using data collected from the Data Source(s). When using the Preview tab in the designer as shown in Figure 1.11, the Report Designer does not access the Reporting Services server—it uses its own renderer. After you deploy your reports, recheck how the Reporting Services Rendering extension displays the report—we've noticed some subtle (and some not so subtle) differences.

Figure 1.11 Previewing a Report in the Visual Studio .NET IDE

Previewing with the RSReportHost Utility

You can preview your report in the Report Designer Preview pane (as shown in Figure 1.11) by clicking the Preview tab in the Designer. This is great most of the time, but if you're developing with custom assemblies that you are also editing and redeploying, you won't want to use this technique. That's because the Visual Studio .NET process loads the custom assembly and doesn't unload it until you close down the Visual Studio .NET development environment. As a result, you won't be able to update the custom assembly you're working on until you've restarted Visual Studio. To address this issue, Microsoft provides the RSReportHost utility, which you call by setting the Solution Configuration to DebugLocal and selecting Debug on the project, or on a specific report. (Note that if you try this out with the Microsoft-supplied samples, there is not a "DebugLocal" Solution Configuration option, so you may have to create one—the crucial part is ensuring that the TargetServerURL property on the project remains blank. This ensures that when you try to debug, the RSReportHost starts instead. Figure 1.12 shows the Project properties page for the "Charting" project setup to use RSReportHost to preview the report design.

Figure 1.12 Setting the Report Project Properties for the "Charting" Report

When the RSReportHost utility starts, it loads itself and your custom assembly into a new process space, and it renders the report (as shown in Figure 1.13). When you close RSReportHost, that process space is unloaded, along with your custom assembly, so you'll then be able to work on any desired changes to your custom assembly.

Figure 1.13 Previewing in the RSReportHost Utility

Note that in rendered generated reports, currency formatting can adapt automatically to the current locale of the user browsing the report. That's the way that the Microsoft-supplied samples have been setup in the sample report definitions, but it is not the way we advise working. Guess where Figure 1.11 and Figure 1.13 were rendered? Unfortunately, these values are stored in the database using the currency datatype, not even as dollar amounts and not British pounds sterling. If you manage different types of currencies, we recommend that you explicitly embed any currency symbol directly into the Textbox control's Format property, possibly even based on the result of evaluating a report expression. If you are going to format a Textbox as "currency," placing a "c" in the Textbox's Format property (as Microsoft did in its samples) has the following effect: If the Language property is not set on that textbox, or it's set to "default," the report renderer defers to the Language property setting on the report definition. If the Language property is not set or left as "default," the language of the user's web browser is used as the language from which the currency symbol and formatting would be used. Clear? Leaving the rendering choice of currency symbol to the browser could be problematic, to say the least—especially if the rendered report is an invoice!

RDL: Report Definition Language

As we mentioned earlier, while you're designing a report, the Report Designer maintains the definition of the report in an "open" human-readable Report Definition Language (RDL) file (see Figure 1.14). RDL files are laid out in an XML format using an open published XSD schema. (Developers can indeed extend RDL by adding their own elements and attributes.) When a report definition is being edited visually in the Report Designer, the underlying RDL file is automatically kept synchronized. Sure, it's also possible to edit the RDL file directly, and those changes are immediately reflected in the Designer. Diehard gurus won't use the Report Designer; they'll use Visual Notepad, or even Edlin! ^[13]

Figure 1.14 A Sample of RDL Code in a Report Definition

The more serious corollary of an open RDL standard is that other report design tools can be built that extrude RDL or translate other proprietary reporting file formats to RDL. You'll then be able to verify the resulting RDL against the published XSD schema. You may even have ideas about enabling your own custom application to create or modify RDL files.

RDS: Report Data Source Files

Report Data Source (RDS) files provide a mechanism to share Data Sources on a Report Server scale. Like report Data Sources, shared Data Sources hold a Connection string and user credentials. These user credentials provide access to the underlying data—they're simply the User ID and Password or options that prompt the user for user name and password. These credentials determine whether the DBMS permits your report's DataSets to access the database and further grant or deny access to the underlying database tables, views, functions, or stored procedures. Shared Data Sources can be created in the Report Designer (in Visual Studio .NET), in the Report Manager, or programmatically through the SOAP interface (as we describe in Chapter 9¾). However, the Report Designer only permits a restricted subset of Report Data Source features to be configured. There is much greater capability (albeit a more awkward interface) when configuring deployed report-specific or shared Data Sources with the Report Manager.

There are a number of mechanisms you can use to specify, manipulate, and persist connection credentials. In Chapter 3 we show you how to create a shared Data Source using the Report Designer wizard, and in Chapter 4 we illustrate how to manipulate Data Sources in the Report Manager. Chapter 5 discusses serious security issues in a deployed production environment, and Chapters 6 and 7 discuss the use of Data Sources in the development environment. After reading these chapters, you'll see that each credential persistence approach has far-reaching consequences. To ensure that you don't leave the door open to Trojan reports or other security risks, you should get a good handle on this information before launching your first production environment.