Building a Basic Report - SQL Server 2008

Let’s build a basic report that lists all employees from the AdventureWorks database, so that you can see Reporting Services in action. This is just a simple example to get you started. Later in this chapter, you’ll learn how to use more advanced features and the new data regions available with Reporting Services in SQL Server 2008.

Launching the Designer

Your Reporting Services report design will begin in BIDS. After launching BIDS, you should create a new project. In the project type list, you should see Business Intelligence Projects, as shown in Figure. Select that project type, and then select Report Server Project as the template. Enter BasicReport as the name, and then click OK.

Creating a new Report Server project

Creating a new Report Server project

Working with Data Sources and Datasets

Now that you have your project, you need to add a data source to your report. Without data, you will not have very much to report on! To do this, right-click the Shared Data Sources node in Solution Explorer and select Add New Data Source. For this example, in the dialog box that appears, type in your server name for your SQL Server and select the AdventureWorks database from the drop-down list. Click OK, and you now have a data source.

However, you can’t stop here. The data source defines what data you want to connect to, but you really haven’t defined the data on which you want to report. You need to create a dataset from your data source. To do this, add a new report to your project by right-clicking the Reports node in Solution Explorer and selecting Add ➤ Add New Item. In the dialog box, select Report and click Open. Report Designer should launch in the main window, and you should see a link in the middle of the design surface that you can click to create a data source and dataset. In the Data Source Properties dialog box, select Use Shared Data Source Reference, and then choose DataSource1 from the drop-down list.

Reporting Services drops you into the generic Query Designer by default, which is used for writing your queries. Paste the following SQL statement into the Query Designer window: Select FirstName, LastName, EmailAddress From Person.Contact

To test your query, click the Run button on the toolbar, represented by the exclamation point icon. The Query Designer window should look similar to Figure at this point. As you build more and more reports, you will appreciate the Query Designer and the ability to execute your queries to see what data is returned. This report has a simple query, but you could write distributed queries across multiple data sources that will work with Reporting Services.

Query Designer with sample results

Query Designer with sample results

Laying Out and Previewing the Report

The next step is to lay out your report. Click the Design tab at the top, and a blank surface should appear. This is your report body. Creating the layout is a matter of dragging report controls, such as tables, lists, and matrices, onto the report body. These are available in the Toolbox window on the left, along with many other report controls. Drag and drop a Table control onto your report.

Next, select the Report Data tab on the left side of the IDE. Reporting Services creates fields for the data that is returned in your dataset. You can also create calculated fields, which do not exist in the dataset but instead are created by writing VB .NET expressions. For example, you could have created a FullName field that combines the first and last name together.

Drag and drop your fields onto your table. Drag FirstName into the detail cell in the first column. Drag LastName into the middle detail cell. Finally, drag EmailAddress into the last detail cell. Notice how headers are automatically created as you do this.

Now that you have your simple report laid out, preview it. To do this, click the Preview tab at the top of the designer. The report should look similar to Figure.

Previewing the sample report

Previewing the sample report

Working with Expressions

The final touch for your report is to add a little splash of color using VB .NET expressions. You will alternate row colors so that the rows are easier to read, making even rows a different color than odd rows. Reporting Services supports expressions on many different properties in your reports. One such property is the background color for your detail row in your report.

The Expression Editor allows you to build expressions, using an expression language based on VB .NET. With the Expression Editor, you can easily write expressions and combine items such as constants, globals, operators, and common functions.

Go back to your design by clicking the Design tab. Select the entire detail row by clicking the leftmost icon for the row, which looks like three bars. Next, in the Properties window, find the BackgroundColor property, click its drop-down list, and select <Expression . . .>. In the Expression Editor, paste the following expression, as shown in Figure, and then click OK:
=iif(RowNumber(Nothing) Mod 2,"LightGreen","White")

Expression Editor being used to set background color

Expression Editor being used to set background color

This expression will check the row number in your report. RowNumber is a built-in function, and if it’s an odd row number, the BackgroundColor will be LightGreen; otherwise, the color will be White. Click the Preview tab at the top of the form to view the report. You should see a lovely green-bar report, reminiscent of the old green-bar printer days, similar to that shown in Figure.

Basic report with expression for row highlighting

Basic report with expression for row highlighting

Deploying Your Report

You’ll want to deploy your report to your server so that other users can use your report. To do this, you need to set some properties on your project to define where to place the report. Right-click the BasicReport solution in Solution Explorer and select Properties. In the dialog box, enter the path to your server in the TargetServerURL text box. This path should be something like http://servername/reportserver/. Click OK.

To deploy your report, select Build ➤Deploy BasicReport. You will see status information at the bottom of your screen on how the deployment is progressing. Once deployed, your report is ready to be used by all users who have permissions for viewing the report.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics