This is the last installment of a four part article on SQL Server Reporting Services:

• Part 1, provided a step-by-step guide to basic report creation and
• Part 2 took a tour of some of the core SSRS features and functions that you’ll need to develop dynamic reports
• Part 3 focused entirely on the built-in SSRS chart control

Here, in part 4, we focus on Report Definition Language (RDL). The definition of RDL provided by MSDN2 is succinct and hard to better, so I’ll use it here:

“A report definition contains data retrieval and layout information for a report. Report Definition Language (RDL) is an XML representation of this report definition.”

We’ll examine the component parts of a typical RDL file and discuss how knowledge of RDL will enable you to refine and customize your reports. We’ll then take a look at Report Builder Tool, the new ad-hoc reporting tool that ships with SQL Server Reporting Services 2005. As a report developer, it allows you to create a report-building tool that then allows end-users to build custom reports based on the report definitions you’ve provided. Confused? Fear not, all will become clear.

In order to follow my examples, you will need to have SQL Server 2005, SQL Server 2005 Reporting Services, and Visual Studio 2005 correctly installed and configured. If you need some help with this, please refer back to the links in Part 1 of this series. Next, download the code file for this article (see the Code Download link above). The code file contains a sample Visual Studio project and a SQL Script for creating the ReportDemo database. If you’ve not done so already, go ahead and create the database using the ReportingDemoDatabaseScript.sql script or, alternatively, by restoring the provided ReportingDemo.bak file.

Now start a new Business Intelligence project in Visual Studio 2005 (or using BIDS, which installs with SQL Server 2005). Select Project | Add Existing Item to add to the project the shared datasource (ReportDb.rds) and the sample report definition file, FirstReportMan.rdl

Report Definition Language

Report Definition Language (RDL) is an XML-based schema for defining reports, and the reports that SSRS 2005 generates from the Visual Studio 2005 report designer are basically just XML. Each report has a header, footer and body. The schema defines:

• Report layout- the body of the RDL file defines all of the objects that will be displayed in the report, including fields, images and tables
• Each dataset, the data source for each dataset and database connection information (where no data source is used)
• A set of fields in each dataset that can be populated with data
• Any parameters that are used in the report

You can read the full specification for RDL at:

http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx

Dissecting a typical RDL file

The easiest way to get familiar with the basics of RDL is to dive right in and take a look at the component part of a typical RDL file.

Let’s take a look the RDL for the FirstReportMan.rdl included in the download project. Open Visual Studio and open the sample project FirstReportMan.rdl. In the solution explorer, right-click on the FirstReportMan.rdl and select the View Code option. This opens the report in XML mode. The full XML for this file can be found in the code download bundle, but we’ll just step through the major sections here.

Document element

Let’s first take a look at the document element:

The document element is called Report – no surprise there. It references two XML namespaces (these are now deprecated):

• http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition
• http://schemas.microsoft.com/SQLServer/reporting/reportdesigner

DataSources

The first element of the RDL file, below the namepsace reference is DataSources. In this element you can define a data source, or multiple DataSources, for your reports:

As you can see, this DataSource tag references our shared data source, ReportsDB, and it also references a GUID for the data source. If you weren’t using a shared data source the schema looks like this:

Margins

Oddly enough, the margins for the report are defined next. Remember this is XML; the tag order doesn’t really matter and it will vary from report to report. You will find the other tags, such as the report GUID, scattered around reports:

Report parameters

ReportParameters is the next major section. Each report parameter has a name, a data type and a label, which is used when the user is prompted to enter a value. Each parameter can also have a ValidValues section, which defines the list of valid values that will be used to populate the dropdown parameter list that is displayed to the user when they are prompted for a parameter value:

In this RDL file, we have two parameters, ReportTitle and ReportUserID. The ReportTitle parameter is a string parameter and has two valid values, MyFirstReport and CustomerReport, which correspond to the labels, Title1 and Title2, respectively. If the users selects Title1 from the dropdown, then the report title is displayed as MyFirstReport, and so on. The ReportUserID is also a string, and has no valid values defined for it.

Body

The report Body section is next. It has two main tags under it: Height and ReportItems.

The height property simply defines the height of the body area. It is the ReportItems elements that is the true star here. All of the objects displayed in the report are contained in this section of the RDL file. If you examine this carefully, you will see that there is a table definition and two textbox definitions under ReportItems. Each specifies its height and width, z-order, and top and left positioning. Inside the table you will find separate definitions for:

Let’s start wih the definiton of our table, MyFirstTable. In the Details section of the table definition, we define each detail row of the table, which in this case are populated with values for FirstName, LastName, State, Date of Birth and Status. If the data were grouped or sorted in any way, this would be defined in this section, using the <grouping> and <sorting> elements respectively. Here we show only the FirstName cell: 

We then move on to the Header area of the table. Here we define each cell of this table Header. These correspond to the column headings of our table. For example:

Finally, we have the TableColumn element, which defines the appearance of each column in the table:

Page header

After the body, we come to the PageHeader. This section also has a ReportItems section to contain any display objects just as the body does. It also has tags for whether or not to display the header on the first and last page of the report. This is a useful feature if you have ever tried to have a report with a cover page in a tool that doesn’t support this option.

DataSets

DataSets is the next section, with each DataSet element, predicatbly, describing a dataset that is used in the report. Each Dataset has a query definition with command type, command text, and reference to the datasource. The list of fields follows. Each field has a field name and data type. So, our ReportData dataset is described as folllows:

Last, but not least, is the code section. This section is simply the text of the custom function we embedded in the report.

Writing your own RDL

Okay, so now you have a good idea of the RDL that is written behind the scenes when the SQL Report Designer is doing its thing. This understanding of the report designer gives you the ability to manually tweak a report when necessary. I’ve found that I have had to do this less in SQL Server 2005 than in the 2000 version. However, knowing RDL empowers you in other ways too. You are not restricted to using the Visual Studio Report designer, because SQL Reports are not in some proprietary format. If you wish, you can roll your own report builder tool or RDL generator.

I recently built such a tool for a client. They wanted a reporting tool that could build and publish reports on the fly. Using SQL Report Designer, I created a base report that had the client’s standard header and footer. The body was left blank – as was the report name. I saved the RDL to an XML file. When someone submitted a query on which to base a report, I opened the base XML in an XML Document Object. The tool parsed the SQL statement for the fields and created the ReportItems in the body. Next, the tool created the dataset information and then published the report to their reporting server. The rs.exe utility that ships with SSRS 2005 can be used to publish reports from your application or from the command line.

One important note on using the XML Document Object to build a report: I had to remove the report definition schema from Report tag before it would load into the XML Document object. Just before I saved it added it back in as an attribute.

Using the Report Builder

The Report Builder is a neat addition to the SSRS package. It allows developers to create a tool that is delivered to the user via the web, using one-click deployment. The tool allows the user to select from pre-defined fields and functions as the basis for building their own reports. The user can use the simple drag and drop interface to build custom reports. It’s a great way to give limited control over reporting to your user base, without giving them too much authority on the database.

Defining a report

In this exercise we are going create a report definition that will allow the end-users to customize their own reports. Start by creating a new project type called Report Model Project from the Business Intelligence options in Visual Studio 2005. Look in the Solution Explorer. You should have a project with three folders underneath it.

1. Data Sources
2. Data Source Views
3. Report Models.

Adding a Data Source

This works almost the same as it does in regular reporting services. When you add a new data source (Project -> Add New Item) you get a wizard. The wizard will automatically look for the data source on your local report server or which ever report server you configure the project to use. If you have not defined the correct data source, you can create a new data source object like we did previously.

Adding a Data Source View

Data Source Views allow you to define which tables the user has to select from when they are creating a report. It also allows you to define the relationships between those tables so that the report knows how to join them. Table relationships can be based on foreign keys or a name matching schema. If you choose some type of name matching option the report will attempt to join tables based on columns with the same name or based on primary keys.

Select Project -> Add New Item from the menu and choose Data Source View. Click next to get past the first page of wizard. The first screen asks you to select a data source to use for this view.

As you can see, there is also the option of Create a new data source here as well. Select the data source you set up in the previous step.

On this screen you tell the report builder how you want ‘name matching’ to take place when it constructs the SQL statement. As you select the radio buttons the sample code changes in the bottom pane. For now let’s go with the default option (same name as primary key). Click Next.

On this screen, you select which tables and/or views you want to include in the view. Select the Customer table and move it to the list box on the right-hand pane. Click Next.

The last screen of the wizard gives you a chance to name the Data Source View. Click Finish and you’ve created a data source view.

Adding a Report Model

The Report Model takes a Data Source View and wraps so that the end user can use the tables it contains. The Report Model also allows you to select which individual fields from those tables are available to the report.

From the menu, select Project -> Add New Item. When the dialog opens, select Report Model Wizard and name it ReportingDemo.smdl. Alternatively, you can right-click on the Report Model folder and select Add New Data Model. Either way, click past the wizard start screen.

Select the Data Source View we just created and click Next. Note that you could create a data source view from this screen as well. Click Next.

In addition to simply providing you with fields you select, the report builder also gives you the option-adding aggregate functions. You can determine which functions you want to be added on this page: Report Model Generation Rules. For this particular demo, use the defaults and click Next.

The model statistics screen allows you to determine whether you want the database to update its statistics before the report builder is generated. This is generally a good idea. For now leave the defaults and click Next. On this last page you can give the report model a name. If you choose not to, one is supplied based on the data source view you chose for the report.

The only thing left to do is click the Run button. This generated the basis for the report. Once Run has completed, you can examine the options created. The Run button will change to the Finish button. Click the Finish button to complete the wizard.

Build and publish

Lastly, right-click on the project icon and build and publish this project to your report server. This may take a couple minutes the first time you do it. The report builder is actually compiling a windows application and setting it up for one-click deployment over the web.

Using the Report Builder to make a report

When we run the report builder is launches a windows application that allows the user to drag and drop field on a canvas in order to create a report. The user gets to determine which fields are on the report in which order. They can format the report as they wish and run it.

Let’s run the report builder. Remember that everything from now on is meant to be experienced by the users. You must have the .NET Framework 2.0 redistributable installed on your machine before you can run this.

Navigate to your Reports website: http://localhost/Reports/.

Notice that in the tan bar there is a new option called Report Builder. Click on it to launch Report Builder. The first time you do this it has to download the code base, so it may take a few seconds to activate.

Once the Report Builder has opened, you will see a Getting Started panel on the right. Just under that there should be a list box showing the report models. If you have only defined a single report model you may not see this dialog. The report model you created will be used by default. The one you just created and deployed should be listed. Select it. Choose the Table Layout and click OK.

Now let’s get our bearings. On the left you have the Entities list box which lists each table from the report model you selected. When a table or view is selected, all of the field options for that entity are listed beneath it.

The report designer takes up the rest of the screen. Click on the text box labeled Click to Add Title and give your report a title. Next, drag some fields into the table. I added the customers’ First Name, Last Name, City, State and Birth Year. The birth year is available under functions if you expand Date of Birth Just drop each field to the right (or left) of the previous one. The tool will automatically add columns for you.

Click the Run Report button at the top of the page.

This is the result. Notice that by clicking on the column headers you can sort the report. You can toggle back to Design View with the button next to Run Report, but let’s stay here for now. Now click on the Filter button. The Filter dialog appears.

Double click on State. State equals appears in the right pane. A dropdown menu provides you with a unique set of values from the result set. Select CA. and Click OK. The report regenerates, and only your California customers are displayed on the report.

So there you have it. Report Builder allows you to deliver an Ad-hoc reporting tool to you clients. If you find yourself constantly having to produce custom reports for various clients, all off the same data, then this is the tool for you. It allows you to transfer that control to your customer’s hands. That frees you up to do more important work and your customers may even enjoy designing their own reports. For your part you free up your time and look like a hero to your customers and your boss.

Conclusion

I hope this tutorial article has given you some insight into the capabilities of SQL Server Reporting Services 2005. There are many more features for you to discover. Just to name a few, I suggest you look into sub-reports, the charting control, report caching and report subscriptions. Happy reporting…