Microsoft SQL Server 2005 : Report Definition and Design (part 3)

Query Builder

The Data subtab for a report definition manages datasets. You can edit existing datasets or add new ones using the Dataset drop-down list at the top left of the dialog box. Each dataset has a name, data source, and other information specific to the command type and selected data source type.

SQL relational data sources support text and stored procedure command types. The query string can be edited or pasted directly into the Dataset Query page. It is often easier to leave the query field blank and use the built-in query editors. There is a text-based editor (Figure 12) with a result window in the bottom pane. Click the Run button (exclamation point) on the toolbar to see the results of a query.

Figure 12. Text-based query designer

The Generic Query Designer button toggles from text to graphical mode for a relational dataset. This editor, shown in Figure 13, will be familiar to anyone who has worked with Microsoft database products over the years, including Microsoft Access and previous versions of SQL Server. The top pane has the table diagram. Use the Add Table button to add more tables. The second pane is the column grid. You can specify columns, aliases, sorting, and filter criteria. The third pane is the SQL statement. You can paste working statements into this area and have the table diagram and column panes fill automatically. There is no color coding or IntelliSense in the statement editor. This pane has a properties dialog box where you can modify the statement with options such as distinct values and top rows. Finally, the bottom pane displays the results in response to the Execute SQL command. Any of the four panes can be toggled on and off to improve readability.

Figure 13. Graphical query designer

Some queries will include optional parameters. The syntax for these depends on the data source type. SQL Server uses the @parameter syntax. It is easiest to enter these into the filter column of the grid pane. When the query is executed, a dialog box appears asking for the parameter values, as shown in Figure 14. Enter a known value or select from the drop-down list. Dataset parameters will appear in the report layout pane dialog boxes and the preview pane toolbar.

Figure 14. Query Parameters dialog box

The query editor also supports stored procedures. You can enter the name of the stored procedure directly into the text window. When you run the procedure, any required parameters appear in the prompt dialog box. Use the Generic Query Designer toolbar button to toggle into graphical mode. A drop-down list displays all the available stored procedures based on the target database name (catalog) specified in the data source.

Analysis Server MDX/DMX and XML datasets provide only the text editing pane. Consider using other query tools to perfect your query statements and then paste them into this query editor.

Report Layout

The Layout subtab of a report definition has a design surface that aids in visually creating report elements. The interface is typical of popular form creation programs such as Microsoft Visual Basic and Access.

Design Surface

New reports start with a blank canvas. Select report control objects from the toolbox, as shown in Figure 15, and drag them onto the design surface.

Figure 15. Report Designer toolbox

There are nine built-in controls, four of them representing data regions. Custom controls require custom report processing extensions. Third parties are busy developing add-ons to meet this need.

Each object has a unique set of properties that appear in the Properties window. Use the shortcut menu to select the Properties option. This opens a dialog box specific to that control type. It is often easier to complete this dialog box than to scroll through the Properties window. Simpler objects such as the text box have shortcut menu shortcuts for setting the value expression.

The Properties dialog boxes share General, Visibility, Navigation, Filter, Sorting, and Data Output tabs. The General tab has name, ToolTip, and dataset options, as well as page break and header/footer options. On the Visibility tab, you indicate whether the control should be rendered. This is interesting because you can build data-driven expressions that determine visibility. You can, for instance, display a graphic only if sales have exceeded some threshold. On the Navigation tab, you associate fields or expressions with document maps and bookmarks (as explained shortly). On the Sorting tab, you specify the order of detail rows. You can use filters to limit the number of rows displayed—this can be different from the underlying data source. You can combine filters and expressions to create some interesting effects. The Data Output tab is primarily for custom XML output.

The Table object in the Tabular.rdl sample adds a Group tab to the Properties dialog box. Each group has its own Page Break, Header/Footer, Visibility, Sorting, and Filtering settings. The Table control layout is shown in Figure 16.

Figure 16. Table control in the layout pane

Graphical icons represent the various groups and regions in a table. Click on the row or column to set properties specific to that area. Right-click the left table column to insert or delete rows or groups. You can see the rendered tabular report in the preview pane in Figure 2, shown earlier.

The Matrix control in the Matrix.rdl sample also has a Groups tab, but it has no Sorting tab because the data is summarized and there are no detail rows. The Groups tab has both row and column groups with a defined order. This helps you create drill-down and nesting effects. Note that Sorting is an option within each group. The Matrix layout is shown in Figure 17. (You can see the rendered matrix report in the preview pane in Figure 3, shown earlier.)

Figure 17. Matrix control in the layout pane

The Chart control in the Chart.rdl sample in the layout pane has property pages to set chart type, Data series, X and Y axes, Legend, 3D Effects, and Filters (Figure 18). Data series are similar to groups in the table and matrix controls. The General tab has style buttons for controlling border and fill colors. Series and labels all have font and style format options. If you prefer, you can use the Properties window and drill through the various categories. (You can see the rendered chart report in the preview pane in Figure 5, shown earlier.)

Figure 18. Chart control properties in the layout pane

The List control appears in the FreeForm.rdl sample. A list has no groups. Use the Edit Details Group button on the general Property tab to see Filtering and Sorting options for the underlying data. The List data region control is a container that holds other controls. Our example uses a few text boxes and a line to build a layout that repeats for each detail row.

FreeForm.rdl also contains a Subreport control. Subreports have an optional Parameters tab in the Properties dialog box where you can connect the parent report to a child using data-driven values in an expression. Select the required parameters from the drop-down list, and set the value to some expression. You can see a rendered Subreport and list in Figure 4, shown earlier.