Introduction to Reporting Services - I

Introduction

I am writing this article after 2 weeks of using Reporting Services for creating reports. In this two weeks, I have created 14 reports. Two of them have to be written from scratch because of my mistakes. I have decided to write two part of articles about Reporting Services. In the first part, I will show you how to create a simple report and give some advice about Reporting Services Reports. Before starting, I have to make some assumptions about you.

1. You have successfully installed Reporting Services.
2. You have some background knowledge about ASP.NET.

First part of the article has a feeling of a walkthrough. If you have a computer which has Visual Studio installed but has no SQL Server installed, when you install Reporting Services, you can only install development components to your machine. Development components will enable you to design reports, after you have installed Reporting Services with development tools. Visual Studio .NET will have a new type of project.

File->New Project-> Business Intelligence Projects -> Report Project. Choose a suitable name and click OK.

After this action, VS.NET creates two folders for you. (Actually, these are not real file folders as you will see if you look at the solution folder, but content holders for your files.)

Every RS report needs a data source to connect to a database. Data sources give information to your report such as database name, user, password and which provider it uses. Actually, they encapsulate ADO.NET connection strings inside them.

Left Click to Shared Data Sources and Add New Data Source

This action opens Data Link Properties dialog box as shown below. Provider tab gives you options to choose which data provider you should use. Understand that RS works internally with Microsoft SQL Server. This means that you have to use SQL Server for using RS. But incoming values for your reports can come from any data source which is supported by ADO.NET such as Access, Oracle and any ODBC compliant database.

Connection tab will allow you to create your connection string; your server name, which user you will use to connect that database, and so on. I did not have to use Advanced and All tabs. But they will allow you to set connection timeout and other connection string properties. You can find more information about connection string properties in MSDN help.

From Data Link Properties, create your connection string. This dialog box creates standard ADO.NET connection string. At this point, if you save all, and go to your project directory (in my machine D:\WorkingFolder\TrialProjects\ReportProject1\ReportProject1\), you will see a file named AdventureWorks2000.rds apart from project and solution files in your directory. Open this file using Notepad etc., it is a standard XML file. Its contents are similar to the snippet shown below:

<?xml version="1.0" encoding="utf-8"?>
<RptDataSource 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>AdventureWorks2000</Name>
<DataSourceID>a0cab8a8-033f-4709-8f22-435e98e8b487</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>
  data source=(local);initial catalog=AdventureWorks2000
</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</RptDataSource>

As you see, in your connection string, Integrated Security is defined. If you double click in Shared Data Source, this dialog box is opened:

In this dialog box, if you push Edit button, you will come back to the dialog box you used to create this connection string. Name and types are standard. And connection string is normal ADO.NET connection string. If you want to learn more about connection strings, I advise you to read a introduction article about ADO.NET. In the Credentials tab, you will see this:

This Credentials tab is used for specifying which credential type and how it will be obtained. "Use a specific user name and password" will use a user that has rights to the database, either a database user or NT user. "Prompt for credentials" will ask user to give proper credentials at runtime. The proper credentials can be either database user or NT user as before (if your SQL Server works in mixed mode). I have only used RS with SQL Server, so I do not know how they change with different data providers. Save and look to AdventureWorks2000.rds file again. You will see this:

<?xml version="1.0" encoding="utf-8"?>
<RptDataSource 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>AdventureWorks2000</Name>
<DataSourceID>a0cab8a8-033f-4709-8f22-435e98e8b487</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>
  data source=(local);initial catalog=AdventureWorks2000
</ConnectString>
</ConnectionProperties>
</RptDataSource>

As you can see, your password information is not stored in this file. If you use a source control program to share this file among your team-mates, and if your connection string includes a user name and a password, they will have to explicitly set that user name and password if they want to use the same shared data source. Your password and user information are stored in Reporting Services DB and VS.NET as encrypted.

Using shared data sources works better when you have more than one report. You will have to set only one data source's credentials. And you will be able to change that data source very easily. Even you have one report, I advise you to use shared data sources.

Right Click Reports tab in Solution Explorer and Add New Item and add new Report.

You will see three tabs: Data/Layout/Preview. Create a new Dataset as below.

First of all, this dataset is not the same as ADO.NET DataSet if you know ADO.NET. This dataset holds only one set of results. It is similar to DataTable of ADO.NET.

In Query tab, choose a Name. I advise you to give a name here. Because, if you later decide to change the name of your data source, Reporting Services could give errors and you may have to manually update the RDL file (an XML definition used to define your reports). And if you are creating a report with comboboxes to choose parameters, you will have more than one dataset in your report. It will be easier for you if you name it now. 'Data source' is your connection string as I explained. Either you create one for every dataset or you use one shared data source. In 'Command type', you have three choices: text, stored procedure, table direct. Table direct is not supported by SQL Server. And I do not know which databases support this type of command. Text commands are normal queries. If you have a difficulty using stored procedures with Report Wizard, try to use exec [NameOfStoredProcedure]. With this type, wizard will understand your stored procedure. You can later change this text type to stored procedure and remove "exec". I advise you to use stored procedures with your reports. They have a lot of advantages over normal text commands. For example, one of my reports had an error with its query. Its outer join did not work as I supposed it would. And it required me to use inner select. I only had to update the stored procedure in SQL Server. If I have used text as command type, I would have to have updated the report. And believe me, comparing updating stored procedure to updating report definition, updating report definition is more work. You would have to find the RDL file for your report, open VS.NET, open report, etc. etc.

If you click OK now, you will see the query designer as below:

Reporting Services has three types of query designers. One is this graphic query designer which most SQL Server users are familiar from Enterprise Manager. Another is Generic Query Designer. Generic Query Designer has no support for adding tables or selecting fields, but supports all valid SQL. For example, Generic Query Designer supports "CASE" word in SQL command but graphic query designer does not support this. You can create your SQL here, but if you have SQL Server tools installed, I advise you to use them.

Right Click to your Solution and add new project and choose Other Projects-> Database Projects, give a name, and click OK.

You will see a database connection wizard again. Create your connection string as before. After this, you will have two projects in your solution.

We will use this database project to hold our stored procedures and other queries. At this point, add your solution to a source control system. If you do not know what is a source control system, learn it. Believe me, you will need it. Reporting Services is a good product. But as normal with 1.0 of all products, it has some quirks (dare I say bugs). For example, I have changed width of my almost finished report. Reporting Services happily complied with me. But when I tried to run my report: d:\workingfolder\trialprojects\reportproject1\reportproject1\Report1.rdl, the value of the width property for the report ‘body’ is “5010.66mm”, which is out of range. It must be between 0mm and 4064mm.

And Reporting Services gave me a parse error and refused to open my report file. You have two options here. You can roll back to an earlier working version or try to manually edit the RDL definition file. Source control will enable you to roll back to an earlier version very easily. And as a bonus, you will be able to control versions of your SQL queries also.

Right Click to Reporting Services Queries and Choose Stored Procedures. Give a name and click OK.

Wizard will create a stored procedure template for you. I erase comment lines and use this. We will create a very simple stored procedure here with AdventureWorks database, as below:

CREATE Procedure rp_Address
AS
SELECT dbo.Address.AddressID, dbo.Address.AddressLine1,
   dbo.Address.AddressLine2, dbo.Address.City,
   dbo.StateProvince.Name AS StateName,
   dbo.CountryRegion.Name AS CountryName
FROM dbo.Address INNER JOIN
dbo.CountryRegion ON dbo.Address.CountryRegionCode =
   dbo.CountryRegion.CountryRegionCode INNER JOIN
dbo.StateProvince ON dbo.Address.StateProvinceID =
   dbo.StateProvince.StateProvinceID

This SQL will take all addresses from the database. I am trying to give a simple but useful example. You can run your stored procedure in VS.NET, right click and choose Run. Let's go back to our report.

Edit DataSet:

Change Command Type to Stored Procedure, Command Text to rp_Address, and click OK.

Run this dataset. See that our stored procedure works. Choose Layout tab. We have added nothing visual to our report as of now. We shall do so.

Choose View->Toolbox.

This will open the Toolbox menu as above.

Drag and drop a table to the body of your report. Then choose View-> Fields.

This will open Fields box. As you can see, all of the columns in our stored procedure are here.

Drag and drop AddressLine1, AddressLine2 to the Detail part of your table.

Choose Preview. And see that your report runs. Now View-> Solution Explorer. Right Click to Report Project and Properties.

This will open Report Project Properties as below.

Choose Start Item Report1 (only one report exists now), OverwriteDataSourcers true and TargetServerURL to your server (Mine is http://localhost/reportserver/) then click OK.

If you now press F5 or click Debug -> Start, your report will be deployed to the server and run.