Wrox Home  
Professional SQL Server Reporting Services
by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney
May 2004, Paperback

Excerpt from Professional SQL Server Reporting Services

Report Items and Data Regions

Reports consist of items and regions that define the placement and format of data from a data source. In the Report Designer, you can place items or draw them onto the report body. If you have worked with Visual Basic or Access forms, you would be familiar with the practice of placing controls on forms. This is pretty much the same environment. When you add a new report to a report project in Visual Studio .NET, the designer is displayed in the Layout view. Much of the Visual Studio functionality is exposed using various utility windows. On the left side of the designer, you will find the Toolbox that contains all of the available report items as shown in Figure 1. The toolbox may be set to auto-hide using the pushpin icon in its toolbar.

Professional SQL Server Reporting Services - Figure 1
Figure 1

Textbox Report Item

The Textbox item can be used to display data from a data source, calculations or expressions or static data, much like a label control in a Windows forms project. When you drag fields from the Fields list onto the Report Designer, bound Textbox items are created. Common expressions can refer to a field in the report.

The following example in Figure 2 shows a Textbox used as a label and another Textbox bound to the LastName field of the report data source:

Professional SQL Server Reporting Services - Figure 2
Figure 2

Right-click the Textbox and select Properties from the pop-up menu to display the Textbox Properties dialog, as in Figure 3.

Professional SQL Server Reporting Services - Figure 3
Figure 3

Properties may also be viewed and set using the standard properties sheet located to the right of the designer. This window may be pinned out or will auto hide by default. As in Figure 4, this window contains quite a bit more detail than the custom properties window. However, the property information is not as conveniently organized. Right click to get to the most common properties, and use the property sheet when you need to set other properties.

Professional SQL Server Reporting Services - Figure 4
Figure 4

Line Report Item

Lines may be drawn in any direction and may be set to a variety of styles and colors, as displayed in Figure 5. The properties for a line are simple and mostly set using the properties window or designer toolbar.

Professional SQL Server Reporting Services - Figure 5
Figure 5

Some clever techniques are used to render lines. Reporting Services will typically try to render content using the most effective way possible. In Figure 5, when outputting standard HTML, the two black lines are rendered as table borders, the wide gray line is rendered as a DIV tag filled using a JavaScript function, and the diagonal broken line is rendered using Virtual Reality Modeling Language (VRML) commands.

VRML is a standard industry extension to HTML for displaying vector-based graphics in the web browser.

Rectangle Report Item

A rectangle item can have many different uses. A rectangle is simply used to visually separate a region of the report. It may be used to visually contain other items. If items such as text boxes, grids, and so on are placed into a rectangle, all these items can be moved together by simply moving the rectangle. A rectangle may also be used as a data container for data items and can be related to and repeated with a parent container.

Image Report Item

Images can be embedded into the report, linked to an external file, or obtained from a data source. Images can be of the BMP, GIF, JPG, JPE, PNG, or X-PNG type. Adding an image in the designer is pretty straightforward. A critical factor is that images are sized and cropped prior to being added to a report. You can resize the image in the Report Designer, but this will not result in a smaller file size. Use a graphics editing tool like the Office Picture Library, Microsoft PhotoDraw, Adobe PhotoShop, or Macromedia Fireworks to resize or crop the image and them save it to a new file.

Drag and drop an image item from the Toolbox onto the report. This will launch the Image Wizard dialog (see Figure 6). Select the method you want to use; the image can be from a table in the database or a file and may be linked or embedded into the report.

Professional SQL Server Reporting Services - Figure 6
Figure 6

Selecting the Project option will result in a linked image using a file found in the project. Selecting the Database option will allow you to extract an image stored in an Image or Binary type of column within a database, exposed through your dataset.

Keep the default selection Embedded and click Next to show the image selection page shown in Figure 7. Click New Image and find your image file.

Professional SQL Server Reporting Services - Figure 7
Figure 7

When you click Next, a summary is displayed with information about the image; see Figure 8.

Professional SQL Server Reporting Services - Figure 8
Figure 8

If your picture data is stored in the database and the Database option is selected, the database field page is displayed in the wizard. This gives you the option to derive an image file type from the image, as in Figure 9.

Professional SQL Server Reporting Services - Figure 9
Figure 9

Generally, the JPEG format is most flexible. If the image uses transparency, use either the GIF or X-PNG formats. The GIF and JPEG formats are most widely used on the Internet and are supported by all web browsers.

Subreport Item

A subreport is a container for another report. The subreport can contain practically any other report with its own, independent data source. It can optionally have its data linked to a record in the main report, often referred to as a master/detail report. Subreports are an important element in complex report designs. Figure 10 shows a simple report containing a master record and related detail records in the subreport.

Professional SQL Server Reporting Services - Figure 10
Figure 10

The design details of the subreport are not visible in the designer. This report is designed separately and then inserted into the main report as a subreport item.

Chart Report Item

The chart functionality in Reporting Services is really a simplified version of Dundas Chart that Microsoft has licensed from Dundas Software. It's a very capable and easy-to-use charting solution with a variety of available chart types.

Probably the most common and most recognizable chart type is the column graph. This example in Figure 11 shows store sales data grouped by year, with the total product sales grouped by product category.

Professional SQL Server Reporting Services - Figure 11
Figure 11

If any of the sample sales data in the AdventureWorks2000 database is accurate, I'm glad that I don't own a bike shop. Let's use the same data to take a peek at some other report types.

Bar charts and column charts are pretty much the same. You can tilt your head to the side to get the same view as the other. In addition to the standard, single-bar view, the stacked view provides a consolidated look at a series of values by using fewer bars or columns. Each bar is like a mini pie chart where each value in the bar's range is in proportion to the others.

Figure 12 shows a standard stacked column chart. A series of related values are stacked in the column to show the aggregate sum of values and their proportional values.

Professional SQL Server Reporting Services - Figure 12
Figure 12

A variation, the 100% stacked bar or chart (not shown), displays each bar with the same height or length as others, regardless of the total values. This type of chart is useful for comparing values within the bar's range but not for comparing the aggregates represented by each bar.

Area and line charts are useful for analyzing trends and helping the viewer to follow data points through a series. Figure 13 shows a simple area chart with three points of data for each series.

Professional SQL Server Reporting Services - Figure 13
Figure 13

To view the proportional components of an aggregate sum, this type of chart comes in two pastry types: Pie and Doughnut. Values are presented visually as a percentage of the total for all values in a series. Pie and Doughnut chart views may be either Simple or Exploded. Figure 14 is an example of an exploded Doughnut (sounds messy). This presentation may help to visually separate values, especially the smaller slices. This chart looks more like Pac-Man undergoing a root canal than retail sales figures, but these types of charts can be useful for placing values into comparative perspective.

Professional SQL Server Reporting Services - Figure 14
Figure 14

The data source for a chart can either be pre-aggregated in the underlying query using GROUPBY and SUM functions, or the chart can perform the aggregation for you. If you have a large volume of data, aggregating the values in the database, using a view or stored procedure, will be much more efficient.

Drill-Down and Drill-Through Reports

Although related, these are two different features. A drill-down report, as in Figure 15, contains related groups or sections of information. Each section can be expanded or collapsed to show or hide pertinent information. In the following report, product categories only are displayed when the report opens. Using the expand icon next to a category, the category group (in this case, Clothing) is expanded to reveal a group of related subcategories. Expanding a subcategory (such as Bib-Short) reveals individual products within the subcategory.

Professional SQL Server Reporting Services - Figure 15
Figure 15

A drill-through report may or may not include some drill-down functionality. Items shown in the report may represent sections or more detailed information that may be viewed in a separate report. These key items are displayed as a hyperlink and when a user clicks the link, a separate detailed report is displayed for the item selected, as in Figure 16:

Professional SQL Server Reporting Services - Figure 16
Figure 16