Reporting Service on Yukon

Background

SQL Server Reporting Services is the long-awaited reporting technology from Microsoft, providing the means to design, author, render and deploy business reports to users, customers and employees, via the web or the company intranet. The reporting engine is built in to the SQL Server 2005 database (and provided as a free add-on with SQL Server 2000) and the report manager is integrated with Business Intelligence Development Studio, providing an effective and familiar environment for all SQL Server and .NET developers.

Reporting service is basically a reporting server that uses SQL Server as its backend database; all reports are deployed on the reporting server. It also provides Role Based Security so that you can access only reports for which you have access rights. The basic idea is to have a single location where all reports are deployed, and provides a single point of access; this created a very flexible environment to deploy your reports over the enterprise.

Requirements

You need to select Reporting Service module while installing SQL Server 2005 (Yukon). Other great news is, now Yukon itself provides Development IDE for developing Report Project called as Business Intelligence Development Studio.

• .NET Framework 2.2.
• SQL Server 2005 with Reporting Service.
• IIS 5.0 or 6.0.

Creating own Basic Report

To start with Reports, you need to create Report Project using Business Intelligence Development Studio. To Open Business Intelligence Development Studio, execute the following steps.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business Intelligence Development Studio.
2. On the File menu, point to New, and then click Project.
3. In the Project Types list, click Business Intelligence Projects.
4. In the Templates list, click Report Project.
5. In Name, type TestReport.
6. Click OK to create the report project.

Once Report Project is created, you can start developing your reports. you can create these reports using Report Wizard or adding reports and data source manually. To start with we will take a look at Report Wizard as it is very simple to use. Once you are comfortable with, you can start exploring the features of Reporting Services.

Create Report with Wizard

For our tutorial, we will take a very simple scenario, where we will display employee name, employee no, employee's official address and department. For this we need to create Employee master and Department master tables. Employee master will refer Department using Department Tcode.

1. Create Tables

   • Create Table
     • Employee Table : Emp_Employee
       1. Emp_Name
       2. EMP_OFF_EMAIL
       3. EMP_NO
       4. EMP_DEP_TCODE
     • Department Table : Mst_Department
       1. DEP_DESC
       2. DEP_TCODE

   Refer the following script to generate the necessary tables.

   if exists (select * from dbo.sysobjects where id = 
              object_id(N'[dbo].[EMP_Employee]') 
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[EMP_Employee]
   GO
   if exists (select * from dbo.sysobjects where id = 
           object_id(N'[dbo].[Mst_Department]') 
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[Mst_Department]
   GO
   CREATE TABLE [dbo].[EMP_Employee] (
    [Emp_Tcode] [int] IDENTITY (1, 1) NOT NULL ,
    [Emp_Name] [varchar] (50) NOT NULL ,
    [Emp_No] [int] NOT NULL ,
    [Emp_Off_Email] [varchar] (50) NULL ,
    [Dept_Tcode] [int] NULL 
   ) ON [PRIMARY]
   GO
   CREATE TABLE [dbo].[Mst_Department] (
    [Dept_Tcode] [int] IDENTITY (1, 1) NOT NULL ,
    [Dept_Desc] [varchar] (50) NOT NULL 
   ) ON [PRIMARY]
   GO

   You can execute this query in new interface called Studio Manager.

2. Start the Wizard

   To start with wizard, follow the below steps:

   1. Open the Solution Explorer.
   2. You will find two Folders under TestReport Project.
   3. Right click on Reports and select the Menu Add New Item.
   4. This will open New Item dialog box, select Report Wizard and click OK.

   This will open the below screen. This screen provides the task list that Wizard is going to perform for you.

   

   Click on Next.

3. Select Data Source

   The first step of wizard is to select the data source. So first let's understand what data source means.

   1. Data Source

      A data source contains connection information, such as the data source type, a connection string, and credentials. Reporting Services provides the following types of data sources: SQL Server, Analysis Services Oracle, ODBC, and OLE DB. Developers can create data processing extensions to provide additional types of data sources.

      One can have shared data source or private data source. Private data source will be embedded in to the system and cannot be used for other reports. If you have many reports that need to connect to the same database, the better option is shared data source.

   2. Selecting Data Source

      Second screen of wizard provides you the interface to create the necessary data source. Here you have to specify the name for data source, type of data source, query string and credentials to access the database.

      For this tutorial, we will select type as Microsoft SQL Server and name as MyDataSource. Now clicking on Edit will splash up the Query String builder similar to VS.NET Query String Builder which provides you database name, user name, Password and Initial database.

      

      

   3. Credential Dialog

      Using Data Source Credentials, one can specify authentication process as below:

      • Windows authentication.
      • Specific user name and password (impersonate).
      • Prompt for credentials whenever user access the report.
      • No credentials.

      For our tutorial we will select Windows authentication.

      

   Once Data Source name and credentials are provided, click on Next.

4. Select Query

   Once you specify the data source, we have to build the DataSet. DataSet is the object that contains query to be executed against the data source. Yukon provides the similar interface as SQL server QueryBuilder.

   

   

   Add the two table Emp_Employee and Mst_department. Join these two tables based on DepartmentId. And check Emp_Name, DOJ, Emp_Off_email from emp_employee table and Dep_Desc from mst_department table. This should provide you with the following query.

   SELECT dbo.EMP_EMPLOYEE.EMP_NAME, dbo.EMP_EMPLOYEE.EMP_NO, 
          dbo.EMP_EMPLOYEE.EMP_OFF_EMAIL, dbo.EMP_EMPLOYEE.EMP_DES_TCODE,
          dbo.MST_DEPARTMENT.DEP_DESC
          FROM dbo.EMP_EMPLOYEE INNER JOIN
          dbo.MST_DEPARTMENT ON 
          dbo.EMP_EMPLOYEE.EMP_DEP_TCODE = dbo.MST_DEPARTMENT.DEP_TCODE

   Click on Ok. And click on Next on main wizard window.

5. Select Report type

   Yukon provides two types of report formats as below:

   1. Table format
   2. Matrix format

   

   In our case, we will select Table Format and Click on Next.

6. Select Report Format

   Select the format that suites you.

   

   These many steps are enough to create the basic report. If you want to add group to you report, you can click on Next or Click on Finish to switch to Report Designer view.

   For this tutorial, we will select the style and and Click on Finish>>|.

   This will take you to the final summary page as shown below.

   

   Click on Finish and will lead you the report layout designer.

   

   You can preview the report by clicking on preview tab that will compile the report and show you the output.

   

   Build and Deploy Report

   To start deployment, right click your application and select properties, the following window will appear. You will find the property "OverwriteDataSources" to be false, make it to true, then select the target folder, this can be anything you like. Then enter the location of your reporting server here, it is localhost however it can be a domain, IP address or any location you want as long as reporting service is installed to it.

7. Set TargetServerURL & TargetForlder

   Before you deploy, you have to set the TargetServerURL and TargetFolder where you want to deploy the reports. In our case, we want to deploy the reports to local reportserver in subdirectory say "TestReport" so set http://localhost/ReportServer and "TestReport" values. respectively.

   

8. Deploy through IDE

   After you are done, press F5 or right click the project and select deploy, the minute this is done your reports are deployed on your reporting server. This will deploy the "EmployeeDetail.rdl" and Data source <Datasource>.ds file to ReportServer.

Managing Reporting Service

You can start by accessing your reporting service by going to http://localhost/reports. This is where you can manage your reporting service. You can view reports and other information directly from this web interface, manage subscriptions, security, data sources and others. Mostly, we won't be using it in this article except for viewing reports.

The Reporting Service Web Management provides browsing folders that contain reports, data source names that you have deployed.

The above figure shows the ReportServer Windows service. As you can see, it must be running to be able to access, view and deploy reports from development tool.

Viewing Report in IE

Once we have deploy the reports to ReportServer, we can view this reports directly through IE. You need to have IE 5.0 or above to view it correctly.

So open the IE, provide the URL as http://localhost/ReportServer and Press Enter. This will provide you with directory browser. Here we will get our "TestReport" directory list as below.

From here you can click on TestReport which will show you the "EmployeeDetail" Report listed. On clicking the link, you will get the report. You can copy this URL and can place it as IFrame source or Frame source to share this report details with some other Web pages.

Summary

Hope this walkthrough, provides you the glimpse of eagerly awaited Yukon Reporting Service. If you have any queries regarding the same, do contact me, I will try my best to solve it.