SQL Server 2005 : Report Definition and Design (part 2) - Report Designer

Report Designer

Report Designer has a rich set of features that will satisfy report professionals and programmers. Reports can be simple or complex, highly structured or free form, static or interactive. You have complete control over layout, formatting, formulas, and pagination.

Business Intelligence Development Studio

Report Designer is installed as part of Business Intelligence Development Studio (BI Development Studio), which comes standard with SQL Server 2005. BI Development Studio is really a slimmed-down version of Visual Studio. If you install Visual Studio 2005 on the same machine as BI Development Studio, you can access the reporting tools from the menu of either product.

Reports are stored inside project templates. When you create a new project in Visual Studio or BI Development Studio, select the Business Intelligence Project category in the New Project dialog box, as shown in Figure 7. Three of the six installed templates are used for reporting purposes. The Report Server Project opens an empty project template. The Report Server Project Wizard guides you through the creation of a single report while creating the necessary project files behind the scenes. The Report Model Project is used for reporting models .

Figure 7. New Report Projects in BI Development Studio or Visual Studio

Report projects contain two folders. The Shared Data Sources folder holds the database connection information for the project. The Report folder contains reports (Figure 8).

Figure 8. Solution Explorer with Report and Model Projects

You cannot create nested folders for categorization. Instead, you must create separate report projects and have each project deploy the completed reports to a specific folder on the report server. This creates the desired categorization and keeps the reports segmented enough that multiple developers can work simultaneously. You can set the deployment target folder in the Reports Properties Pages dialog box (Figure 9). You can set the target server, data source folder, and report folder. There are separate properties for each Configuration target. You can change the target server or folders based on the target environment.

Figure 9. Report Properties Pages dialog box with configuration options

Shared data sources can be specific to the working environment—for example, development, test, and production. As reports are promoted from one environment to the next, these named data sources can be pointed to entirely different database resources. You can create new data sources or add existing ones already saved to disk. They are stored in RDS files that have a simple XML format. Data sources have a name, connection properties, and credentials, as shown in Figure 10 and Figure 11. Credentials options include integrated, user specified, prompted, or none. Server stored credentials are encrypted using key technology.

Figure 10. Shared Data Source dialog box with SQL Server connection type

Figure 11. Data source connection properties for SQL Server with credential details

Be careful when you deploy project data sources. An overwrite option on the Report Properties pages indicates whether to replace the data source. This defaults to false because the credentials used by a developer might differ from those used in testing or production. The first time a report project deploys, the data source is copied to the report server. After that, the administrator can change the server-based credentials to satisfy the user population. Subsequent deployment of report versions do not overwrite this data source unless you change the default.

Reports are added to the Report folder. You can make new report items using a wizard or a blank report. You can add existing report items stored on disk as an RDL file. You can also import reports from Microsoft Access. In this case, most data source, query, and report layout information is translated into RDL and stored in the report folder.

Report names must be unique within a project. As long as each project deploys to a unique folder on the report server, it is possible to have the same report name several places on a server. Keep this in mind when you define naming standards for your reporting infrastructure.

Once a new report is created or an existing report loaded, the report displays in a tab in BI Development Studio or Visual Studio. The left side of the screen has a series of tabbed tools. One is called Datasets, and it contains the datasets and fields associated with the report. The second is the toolbox, which contains the data region and graphic controls used in report layout. On the right side of the screen is the typical Solution Explorer with a hierarchical view of report files and the Properties window for inspecting report and control details.

The center panel has three subtabs called Data, Layout, and Preview. The Data view is used to create and manage datasets. The Layout view is a design surface used to create and style report elements. The Preview tab has complete rendering engine capability and can display reports that work exactly like the report server. This is made possible by using the same .NET DLL on the server that is used in development. The viewer includes a toolbar with pagination controls, export capability, print/preview, and zooming.

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. The sample Chap21 solution includes a Reports project with six sample reports.

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 21-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 21-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 21-4, shown earlier.

Expression Editor

The Expression Editor can create formulas for data display and formatting options. Most of the properties available for report objects have an <Expression...> option in the drop-down list. The Edit Expression dialog box has a color-coded syntax editor with Undo and Paste buttons. It also supports IntelliSense, so functions display parameter options as you type.

The Treeview control on the left has a list of syntax categories. Select one of these to fill the center list of syntax options. The description on the right panel explains how to use each feature. Double-click the item in the center panel to have it pasted into the expression. This approach is very useful for dataset fields and report parameters. The Constants category is specific to the property being edited and will show numbers, colors, or other appropriate values.

The example expression in Figure 19 sets the text color based on the value of two other fields.

Figure 19. Expression Editor with a simple formatting statement

Code and Custom Assemblies

The Expression Editor can reference .NET code that is stored with the report definition or in a custom assembly installed on the developer machine and report server. Use the Report Properties dialog box to find the Code and References tabs.

The Code page presents a simple text editor where you can add multiple instance-based methods written in Visual Basic. There is no color coding or IntelliSense. Consider writing the methods in a regular Visual Basic project and pasting the code into this window. To access the code in an expression, use the Code class as follows:

=Code.MyConvert(Fields!Price.Value)

You can write custom assemblies in any .NET language that uses static or instance methods. The calling syntax is different with a static member:

=MyNameSpace.MyClass.MyStaticMethod(Fields!Price.Value)

Make sure the assembly is loaded to the Reporting Services bin folder or the global assembly cache (GAC). Note that custom assemblies are loaded once for each development session and changes do not appear until Visual Studio is restarted.

Interactivity

The Reporting Service delivers static and interactive reports. Much of the interactivity comes from the use of query and report parameters. These values are known only by the end user running the report. Parameters can also be supplied from a table when you run subscriptions .

Parameters and Filters

Most parameters are based on data source query input values. The exact syntax needed to create a parameter depends on the data source type. In SQL Server, the parameters start with the @ symbol.

The Chap21 solution has a report sample called Parameter.rdl that includes several query parameters. In this case, three datasets are created. The Category query lists all possible categories. The SubCategory dataset has a Filter expression that asks for the CategoryID. When you run this query on the Data tab, you are prompted for a CategoryID value. The third dataset is Product, which also has a filter asking for a SubCategoryID.

When this report is run on the Preview tab, a drop-down list is created for each parameter. You must specify a category before the dependent SubCategory list is enabled and filled. These parameters dynamically build on each other through expressions (as shown in Figure 20).

Figure 20. Dynamic parameters selected in the preview pane

On the Layout tab, use the Report menu or right-click on the design surface to show the Report Parameters menu option (Figure 21).

Figure 21. Report parameters generated automatically from query parameters

Notice that each dataset with a parameter creates a matching Report parameter. For each parameter, you can specify the name, data type, and prompt. If you need to associate data values with a parameter, these can be hardcoded or derived from a query. Default values can also be defined in this dialog box. The extra datasets in this report are used to fill the report parameter from a query. The order of the parameters is important because, in this example, a CategoryID is required before a SubCategory can be selected.

This is just one example of adding interactivity to a report. Parameters have many purposes in advanced report building. They can appear in titles, headers, expressions, and other calculations. Reports with parameters can be linked to each other to create sophisticated drill-down reporting.

Document Maps

Another interesting interactive feature is the document map, which you can see on the left side of the rendered DocumentMap.rdl report sample (Figure 22).

Figure 22. Document Map with a selected link in the preview pane

Each control has a Navigation tab in the property pages. Setting the Document Map entry to a field or expression causes the map to be displayed. Set the document map to a different grouping level to get the nested look in the sample. Document maps render in HTML, XLS (as a cover worksheet), and PDFs (as a document map).