The Basic Functionality of Report Designer

Monday Jun 8th 2009 by Marcin Policht

Recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition have discussed its reporting capabilities. This article illustrates another approach to generating reports, relying on the Report Server Project template, which offers a considerably wider range of flexibility than its wizard-driven counterpart does.

In the recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have been discussing its reporting capabilities. So far, we have presented its most basic characteristics, stepped through its installation procedure, and demonstrated creation of a sample report using the Report Project Wizard incorporated into Business Intelligence Development Studio. In this article, we will illustrate another approach to generating reports, relying on the Report Server Project template, offering a considerably wider range of flexibility than its wizard-driven counterpart does.

Regardless of the choice of a template, the ability to produce reports relies on the Report Designer component of Reporting Services, which constitutes the only built-in mechanism providing this functionality in SQL Server 2005 Express Edition. (Full-fledged editions include an alternative solution in the form of the Report Builder, which facilitates ad-hoc reporting carried out by end users). Its implementation is fully integrated with Business Intelligence Development Studio and, consequently, all of its actions (including publishing a final product to the Report Server Web site) are performed within the same interface (consistent with other Microsoft Integrated Development Environment products). Report Designer yields a report definition (.rdl) file in an XML-based Report Definition Language format, that identifies the data source of a target report (based on underlying query), as well as its visual characteristics, such as layout or formatting.

In the previous article, we have described a wizard-driven process of creating a report based on the query described in the Sales and Marketing Scenario MSDN article, allowing you to view address data of individual customers recorded in AdventureWorks database. Now we will produce a similar outcome without resorting to the help of a wizard, in order to gain more familiarity with options available in the Business Intelligence Development Studio interface.

Start by creating a new project (via the New Project... menu) based on the Report Server Project template. This should reveal the Solution Explorer window with two subfolders labeled Shared Data Sources and Reports. From the context sensitive menu of the latter, select the Add New Item... entry. In the resulting Add New Item dialog box, choose Report template and type its name (e.g. Individual Customer Addresses.rdl). Clicking on Add will display an empty, centrally located area representing the newly created RDL file (along with a corresponding Datasets window), with three tabs labeled Data, Layout, and Preview. The next step involves exposing data necessary to generate our report.

The underlying query combines data extracted from Person.Contact, SalesIndividual, Sales.CustomerAddress, Person.Address, Person.CountryRegion, and Person.StateProvince tables of the AdventureWorks database (as described in the MSDN article we have referenced). In order to make it available, we have to define a corresponding dataset (consisting of a data source and a query identifying records that our report will be based on). This is done via the Dataset listbox on the Data tab of the report window within the Business Intelligence Development Studio. After selecting its <New Dataset...> entry, you will be presented with Data Source dialog box, where you need to assign dataset name, its type (Microsoft SQL Server), and a corresponding connection string. The easiest way to set the last of these parameters is by pressing the Edit... command button, which activates the Connection Properties dialog box, where (on the General tab) you can simply point to the local server and SQL Server 2005 Express Edition instance, ensure that Windows Authentication is used to log on, and designate a database to connect to (AdventureWorks in our case). This should result in the connection string in the format Data Source=localhost\SQLExpress;Initial Catalog=AdventureWorks. Verify that Use Windows Authentication (Integrated Security) is selected on the Credentials tab and click on OK to return to the Data tab of our report.

At this point, you are ready to designate which data records are in scope. This can be accomplished by employing either the default generic query designer or its graphical equivalent. In the case of the former, you would simply copy and paste the query as it is presented in the MSDN article (with the Command type listbox set to Text). To use the latter, click on a toggle button in the toolbar, which will automatically alter the active window, splitting it into four horizontally stacked panes, intended, respectively, for tables, a grid containing selected columns (along with their aliases, filtering, and ordering parameters), a corresponding T-SQL statement, and results of its execution. The extended toolbar exposes a number of extra command buttons. Use the rightmost one to add required tables, including Address (Person), Contact (Person), CountryRegion (Person), CustomerAddress (Sales), Individual (Sales), and StateProvince (Person) to the newly created dataset. Next, select checkboxes appearing to the left of each of the columns listed in the SELECT statement, namely Individual (Sales).CustomerID, Contact (Person).LastName (with Ascending Sort type and Sort Order of 1), Contact (Person).FirstName (with Ascending Sort type and Sort Order of 2), Address (Person).AddressLine1, Address (Person).City, StateProvince (Person).Name, Address (Person).AddressLine1 (aliased as StateProvince), and CountryRegion (Person).Name (aliased as CountryRegion). Once the query is completed, you can verify its syntactical correctness and execute it to confirm that it generates the desired output (ensure that all relevant foreign key constraints appear in the diagram in the top window pane before you do so). This step completes the data-specific portion of configuring the report definition file within our project.

We are finally ready to tackle the visual aspects of the report. This process involves defining its layout, consisting of static and data-bound (also known as data regions) elements. As we mentioned, our goal is to produce an outcome equivalent to the one generated by the Report Project Wizard we described in the previous article of this series (listing address data for individual customers of AdventureWorks in alphabetical order, based on last and first names, and grouped according to country and region). To accomplish this, we need to begin by switching to the Layout tab of our report definition window within the Business Intelligence Development Studio, activating the Toolbox window (by either selecting its entry from the View menu or pressing the Ctrl+Alt+X key combination), and enabling Layout, Report Borders, and Report Formatting toolbars.

To create a report label appearing on the initial page of the report, select the Textbox item in the Toolbox, drag it over to the top of design surface, and extend its size to accommodate whatever descriptive name you come up with (e.g. Individual Customers of AdventureWorks). Modify its appearance as needed via the Properties window (or via Report Formatting toolbar). To configure a top level grouping (based, in our case, on country or region) add the List element (again, by dragging it from the Toolbox) below the newly created textbox, size it appropriately to fill out the rest of the design surface, and set its DataSetName property to AdventureWorks. Click on the ellipsis button of the Grouping entry to trigger the display of the Grouping and Sorting Properties dialog box. In the Expression listbox of the Group on section, click on the General tab, select =Fields!CountryRegion.Value and assign Ascending order to it via the Sorting tab. The actual value representing each group of records will appear in a textbox, which you drag from the Toolbox over to the form and position at the top of the newly added list (make sure that its name appears as the value of its Parent property). Assign a descriptive name (e.g. txtCountryRegion) to it and select =Fields!CountryRegion.Value in the Value listbox. (Modify its appearance as desired via Report Formatting toolbar or its Font properties).

Individual records returned by our query will be displayed in a table. Drag its icon from the Toolbox to the lower portion of the form and assign AdventureWorks as the value of its DataSetName property. By default, the table consists of three rows marked as Header, Detail, and Footer. To accommodate the second level grouping (based on the value of StateProvince aliased column), we will add an extra row directly underneath the Header. To implement this, select Insert Group... item from the context sensitive menu of a handle of the Detail row (a gray box on its left hand side). In the resulting Grouping and Sorting Properties dialog box, on the General tab, type in a descriptive name (e.g. tblStateProvince), select =Fields!StateProvince.Value in the Expression listbox of the Group on section, clear the Include group footer checkbox, and enable the Repeat group header option. Switch to the Sorting tab and add =Fields!StateProvince.Value to the Expression section with Ascending in the Direction column of Sort on section. Click on OK to finalize your choices.

To populate the Detail row, we will use individual components of our AdventureWorks dataset. In order to view them, activate the Dataset window, which should contain seven entries corresponding to columns referenced in the SELECT clause of our query. Drag the first one ( CustomerID ) from the Dataset window to the leftmost free column in the Detail row (note that this will automatically populate its value with =Fields!CustomerID.Value and enter Customer ID in the Header row. Repeat the same process with FirstName, LastName, AddressLine1 (change its header to Address), and City columns (use Insert Column to the Right option from the context sensitive menu in the table to add extra columns whenever necessary). In addition, to ensure that the header of our table will appear on each page of the report, select the TableRow1 element (by clicking on its handle), and change its RepeatOnNext property to True.

Use the Preview tab of the Report Designer to determine the outcome of our implementation. Obviously, it is possible to further improve its appearance by taking advantage of other, more advanced graphical designer features available within Business Intelligence Development Studio. We will explore some of them by leveraging a number of predefined sample reports (provided by Microsoft) in our next article of this series.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site