Result Data Newsletter - www.resultdata.com

Using Container Controls Inside Container Controls In A Reporting Services Report

by: Charles Tournear, Sr. Consultant, MCT, MCSE, MCSD, MCDBA, CRCP

SQL Server 2005 Reporting Services Reports supports three types of container controls: List, Matrix, and Table. For most reports we are use to using the wizard concept where we select a single container for the report depending on the type of report format we need. Use a table if a spreadsheet format is needed. Use a matrix if a cross-tab format is needed. Use a List control if a form layout format is needed.

There are some advantages to using one container type inside another, such as better control of the report layout. There are also some potential issues if the containers aren’t placed within each other correctly. In this article we are going to focus on just the List control and the Table control.

Let’s first look at the table control as the base of the report. The table control supports multiple grouping. We want multiple fields to appear together in a single cell, such as name, address, city, state, and zip. One method would be to use a complex expression that adds the fields together separated by char(13) + char(10) to put the fields on different lines.

Another method is to put a list box inside the cell and then place other controls such as images or textboxes within it.

In the example below the first table has had a grouping added to the group by invoice_id. In the first row, first cell a list box has been added which contains two textbox controls. This first row is the table header and if the table is the base control this section only appears at the beginning of the report. By putting the list control in the header, the list box is going to display all of the invoice addresses at the beginning of the report, followed by the grouping information. This is not what we want. We want the address to appear once for each invoice.

The second row is an example of a list box placed inside the group header. Since the group header only appears once for each group, the list box is automatically filtered for the group and display’s only the address for the current invoice_id.

So be careful where you place a list box if the table control is the base control.

If another list control or table control is placed inside the list box inside the table you will receive the following error when selecting preview.

A list control must have a grouping defined in order to put another container control within it.

For an invoice the reverse of the above would be a better scenario. Use a large list box as the base of the report and set the grouping property of the list box control by right clicking the control and selecting properties.

In the properties window on the General tab select the Data set name that the control is to use and select the Edit details group.

Set the Expression on the Grouping and Sorting General tab to the field that you want to group by.

Any control that you place inside the list box control will now be filtered by the grouping value, including table controls. We don’t have to configure the tables grouping properties unless we want to add a new grouping within the table. This allows you to have multiple table controls on the same invoice, along with additional text box controls all filtered for a specific invoice.

Now what if we put a list control in the header section of the table control in the list control? We will again have the entire list appear for the header. However if we set the grouping of the new list control to match the outer list control, it will be filtered again and only display information for the current invoice.

So consider your options for placing different container controls within each other to provide greater flexibility in your reports without having to create sub-reports and link them with parameters.

Go to Top | Return to Newsletter Contents