Building Reports

After you have gotten your server up and running, you can start building reports. SQL Server 2005 Reporting Services gives you two options for building reports: Report Designer, which is the report design tool of choice for IT professionals and developers, and Report Builder, which is targeted toward end users.

Using Report Designer

The Report Designer in SQL Server 2005 is an updated version of the one that shipped in SQL Server 2000 Reporting Services. One of the nice things in SQL Server 2005 is that you don't need to have Visual Studio installed to use the Report Designer. SQL Server 2005 comes with Business Intelligence Development Studio (BIDS), which is a version of the Visual Studio 2005 shell. If you already have Visual Studio 2005 installed, the Report Designer is added alongside the existing projects. As you have seen in earlier chapters, BIDS is the same tool used to develop SQL Server 2005 Analysis Services databases and SQL Server 2005 Integration Services packages.

BIDS is the environment to use for the following type of tasks:

• Designing reports

• Editing a report built in Report designer or Report builder

• Deploying a Report project to various Reporting Servers

• Optimizing report queries

• Enhancing reports with parameters or multiple data regions

Launch BIDS from the SQL Server Program Group in the Start menu. If you select Business Intelligence Projects from the dialog, you get started with a blank report server project or use the report server project wizard to get a quick start, as shown in Figure 17-2.

Figure 17-2. Creating a new Reporting Services project.

A report server project consists of a set of reports, resources, and data source definitions. A report server project is also the unit of deployment to a report server.

Tip

Right-clicking on the name of a report in the project window and choosing View Code lets you edit the report definition text as shown in Figure 17-3. Reports are defined in RDL, an XML schema that is documented in SQL Server Books Online. Some operations, such as replacing all occurrences of a string within a report, can be done more easily if you edit the RDL file directly. Just be careful when you edit the file because an invalid RDL file will not load back into the graphical editor.

Figure 17-3. Reporting Services folder security dialog.

SQL Server 2000 Reporting Services reports can be opened with the SQL Server 2005 Report Designer. When the report is saved, it changes to the 2005 Reporting Services format and cannot be used with SQL Server 2000 Reporting Services any longer.

The bottom line is this: Open the report in the SQL Server 2005 Reporting Services Report Designer available in BI Development Studio only when you are ready to stay with SQL Server 2005 for good.

After you open a report, you are presented with the Report Designer (see Figure 17-4), consisting of three panes labeled Data, Layout, and Query. The Data tab is where you will define the query (or queries for a report) and preview the results.

Figure 17-4. Reporting Services query designer.

The SQL Server 2005 Report Designer supports a wider variety of data sources compared with the SQL 2000 version. Supported data sources include

• SQL Server Relational Database

• OLE DB

• ODBC

• Oracle

• SQL Server Analysis Services (discussed later in this chapter)

• Report Builder Models (discussed later in this chapter)

• XML

• SAP NetWeaver BI (included with Service Pack 1)

It is important to understand that Reporting Services completely separates the query from the report layout. Although each data source may have a different query designer, after the data is retrieved, it is treated the same.

After you have defined your query, you are ready to arrange the data on the report surface. This is done in the Layout tab, as shown in Figure 17-5.

Figure 17-5. Reporting Services Layout tab.

Tip

If you are familiar with other report editing tools, you might wonder where the report header and page headers are. In a Reporting Services report, the report header is simply the section of the body that appears before any of the data regions. The page header and footers are available if you right-click on the Layout surface or select them in the report menu. In addition, the table and matrix data regions are capable of having elements repeat on each page. You can access this behavior through the Properties dialogs for each control.

As you start building reports, you will notice that most of the properties of the objects in your reports can be expressions (see Figure 17-6). Learning how to use expressions is critical to being able to get dynamic report behavior, such as conditional formatting or visibility. One of the nice features of the SQL Server 2005 Report Designer is a new version of the expression editor, available whenever an expression is available for a property.

Figure 17-6. Reporting Services Expression Editor.

Because the expression language used is Visual Basic .NET, the expression editor uses the IntelliSense features of Visual Studio to provide statement completion and syntax checking.

Deploying Reports

After you have finished editing your reports, you are ready to deploy them to your report server. If you used the Report Server Project Wizard (see Figure 17-7), you already have been prompted for a location for deployment. If not, you need to specify the URL in the project properties, as shown in Figure 17-7.

Figure 17-7. Reporting Services project properties.

After specifying the location of your Report Server, you can deploy by selecting Deploy Report Project from the Build menu.

Using Report Builder

If Report Designer is too complex for your users, you can enable them to use Report Builder, a new tool that enables end users to build their own reports. Report Builder has an Office-like interface that makes it easy to construct a report (see Figure 17-8).

Figure 17-8. Reporting Services Report Builder.

One of the differences between Report Builder and Report Designer is that a semantic model of the data source is used to build reports in Report Builder. This model keeps the query hidden from end users, and they can focus on the contents of their queries rather than query syntax.

The semantic models for Report Builder are created in a Report Model Project in BIDS (see Figure 17-9). When you launch BIDS, you can create a new Report Model project. You can then use the Model Designer Wizard to generate a default model based on the database schema. After the wizard has generated the initial set of entities, you can customize the model based on what you would like to present end users.

Figure 17-9. Reporting Services Report Model in BIDS.

If you are building a Report Builder model over an SQL Server Analysis Services data source, you do not need to use the Report Model Designer. You can simply use Report Manager or SQL Server Management Studio to generate the model from the SSAS schema.

Using Model Data Sources in Report Designer

Although Report Models are primarily used to power Report Builder, they can also be used within the Report Designer. Because the Report Designer has additional features and flexibility that is not present in Report Builder, you can add extra zip to a report created in Report Builder. You can also use Report Designer to craft custom click-through reports that users will see as they explore the model-based data sources (see Figure 17-10).

Figure 17-10. Using Report Builder in Report Designer.

To use a Report Builder model as a data source in Report Designer, select Report Server Model from the connection type drop-down. The connection string will be in the form Server=Report Server URL; DataSource=Model Path. For example, the connection string might look like this:

Server=http://myserver/reportserver;DataSource= /Models/AdventureWorks 

The query design surface for report models in the Report Designer is similar to what you will find in Report Builder. The main difference is that you will build the query independently of the layout, just as with other data sources in Report Designer.

Choosing the Correct Type of Data Source for Your Report

With all the types of data sources available to you, you might be wondering whether you should build reports directly against your source systems, build a Report Builder model on your source system or data mart, or use Analysis Services to build an OLAP database. Several questions can help you determine which is the correct approach for your environment.

• Does the report need to present aggregated or detailed information?

• What types of tools will be used to access the data?

• What is the level of expertise of the people that need to design reports?

• Does the report author have permission to modify the schema of the underlying data?

If the report simply displays detailed data that exists in the underlying relational data and can be accessed with fairly straightforward SQL statements, then building on top of the relational source might be the best option. If the reports will primarily present aggregated data, then things may be much easier if done over an OLAP source.

If calculations or extra metadata need to be added to your reports, then using a model-based approach is better than adding them to every single report. Both Report Builder Models and Analysis Services can shield report designers from the underlying relational data source design. Whereas Report Builder models are entity-based, Analysis Services uses a dimensionally based model (OLAP). Defining these calculations and extra metadata in a Report Builder model or an OLAP database may make the maintenance of the reports easier in the long run because they have to be defined only once in one place and just referred to from each report.

OLAP also enables complex calculations to be added, such as timebased calculations (e.g., Year to Date, Year over Year), cumulative sum calculations, custom aggregations (e.g., weighted average, last non empty) or hierarchically based calculations. In contrast, building a Year-to-Date (YTD) calculation in a relational system can result in over 50 lines of SQL statements because there is no notion of time hierarchy in a relational store. The calculation needs to scan the entire data set to isolate the data for the range of months, then for weeks, months, and quarters.

Which tools will users need to access the data source? If the answer is only Report Designer, then going directly against the transactional source might be the right answer. If you want to make Report Builder available to end users, you need to build a Report Builder model. If you want to make data available in analytical tools such as Microsoft Excel or ProClarity (which has been acquired by Microsoft), then an OLAP database is the way to go.

What are the levels of expertise of the report authors or of the people that will need to manage and maintain these reports? If they are familiar with writing SQL statements and understand the structure of the relational database, then writing reports over such a relational database is perfectly fine. On the other hand, if the report authors or managers are not fully comfortable with SQL and relational third normal form database design, then it may be appropriate to add a semantic layer (OLAP or Report Builder model) over the relational source.

Will report authors have permission to modify the schema of the underlying data source if need be? Some time views or additional relationships may need to be created to achieve the report's design goal. If the report author doesn't have permission to edit the underlying data source because it is a production OLTP system or it is a data warehouse owned by a different business unit, building a Report Builder Model or Analysis Services cube gives you the option to do schema modeling on the data without impacting the underlying relational data source.