Generating and Hosting a SQL Server Reporting Services Report Using SQL Server 2005 Business Intelligence Development Studio
page 1 of 1
Published: 16 May 2006
Unedited - Community Contributed
Abstract
This step-by-step, hands-on tutorial guides the reader in creating a simple report using the SQL Server Business Intelligence Studio IDE and hosting the same on the IIS web server.
by Jayaram Krishnaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 74896/ 157

Introduction

Reporting is one of the most important activities of an organization because it provides a specialized and customized view of the data of various forms that live in databases or are stored in archives.  The report is useful in making business decisions, scheduling business campaigns or assessing the competition.  The report itself may be required in hard copy in several document formats such as DOC, HTML, PDF, etc.  Often times it is also required in an interactive form for retrieving data from the data store and viewed on an interactive interface, including a web browser.  The Microsoft SQL Server 2005 Reporting Services, popularly known by its acronym SSRS, provides all that is necessary to create and manage reports and deploy them on a report server with output available in several document formats.  In Visual Studio .NET 2003 IDE it was supported via Business Intelligence Project Type and presently it is an integral part of MS SQL Server 2005 accessed via SQL Server Business Intelligence Development Studio as shown in Figure 1, the entry point for this application.

Figure 1

 

The objectives of this tutorial

This tutorial guides you through the basics of creating a report using the SQL Server Business Intelligence Development Studio based on a query run against the Northwind database on an SQL 2005 Server (Enterprise Edition) on Windows XP Media Center edition.  In particular, the following query is run against the database "Northwind" containing data that is related by three tables, Orders, Order Details and Products.  The query will return some 44 records from the database.  This query was designed using the SQL Server 2005's query editor.

Listing 1

SELECT Orders.CustomerID, Orders.OrderDate,
Orders.ShippedDate, Orders.ShipCity, Orders.ShipName,
Products.QuantityPerUnit
FROM  [Order Details] INNER JOIN Orders ON 
[Order Details].OrderID = Orders.OrderID 
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
WHERE (Orders.OrderDate > CONVERT (DATETIME,'1998-05-04 00:00:00', 102))

Another objective of this tutorial is to show how this report may be hosted on a web server so that multiple users can have access to this report.

An out-of-the box installation of SQL Server 2005 does not have any user databases.  The sample database must be downloaded before being attached to the database using either the SQL Server Management Studio or using stored procedures.  For this tutorial a Northwind database detached from a SQL 2000 Server was attached to the SQL 2005 server as described in an earlier tutorial.  In any case, the procedure shown in this tutorial will not depend on the database that is used, provided a suitable query is run against the database.  The links shown above should provide ample help in either moving a database to SQL 2005 Server or practice running queries in SQL Server 2005.

Creating a Report Project

You start creating a Report Project by clicking on the short-cut MS SQL Server Business Intelligence Development Studio shown above.  This opens up the SQL Server Business Intelligence Development Studio IDE.  Click on "File" in the menu bar to choose the submenu item New Project.  This opens up the New Project window (as shown in Figure 2) where you highlight Business Intelligence Projects.  In the Visual Studio installed templates choose the Report Server Project and change the default Report Project 1 to something different.  For this tutorial the name RSP was chosen.

Figure 2

This creates a report server project RSP with two folders, Shared Data Sources and Reports, as shown in Figure 3.  As created, there are no reports in this Project.

Figure 3

 

 

Highlight the project node and right click to pop-open the menus from which choose Add à Add New Item, as shown in Figure 4.

Figure 4

This opens up the Add New Item Window as shown Figure 5.  In this window highlight the Report wizard and provide a name for your report, in this tutorial it is named Hodentek_RSP.rdl.  You will be using the Wizard to tailor your report.

Figure 5

When you click on Add, the Welcome to Report Wizard window shows up as shown in Figure 6.  In this screen you can read all the steps through which the wizard guides you to get the report going.  Click on the button "Next."

Figure 6

Connecting to data

This brings up the Select the Data Source step of the wizard as shown Figure 7.  Here you can select the type of data source; the default being the Microsoft SQL Server.  For the New Data Source you can provide a name by replacing the default DataSource1.  In this tutorial it is named srcRSP.

Figure 7

Notice that there is no connection string, without which you cannot connect to a database.  Click on the Edit button which takes you to the next window shown in Figure 8, the Connection Properties.  The data source is MS SQL Server, but the provider is called SQLClient.  This is a new provider that came with Visual Studio 2005.  You can click on the arrow by the side of the server name and it comes up with HODENTEK.  However, the proper way of calling the MYSORIAN server on the HODENTEK machine is HODENTEK\MYSORIAN.  If you were to use HODENTEK instead, you would be facing a connectivity problem.  Unfortunately, the Wizard does not pick up the right name for the server.  For authentication the windows integrated authentication was used.  With this information you can list out the databases that you can pick up from the list in the drop-down box for the Select or Enter a database name.  On this server there is only one user database, the Northwind.  You may also test your connection; which came out successful as shown in the same picture, superposed.

Figure 8

When you click "OK" to the previous screen you will see the screen shown in Figure 9.  Now the connection string is entered into the window as shown.  This is a very simple string with just two entries, Data Source=HODENTEK/MYSORIAN and Initial Catalog=Northwind, separated by a semi-colon.

Figure 9

Designing a query

When you click on the button "Next" in the above window you will be presented with the next window - Design the Query shown in Figure 10.  In this window you will be guided through the steps so that you can define the query you want to run against the database of your choice, Northwind.  Here, if you knew the query string before hand you can enter.  However, the query will be designed using the interface so that the resulting query would be the one you are going to turn into a report.  In effect the query shown earlier in the beginning of the tutorial will be generated using the query designer.

Figure 10

 

Click on the button "Query Builder...."  The window shown in Figure 11 comes up with everything empty except for two icons at the top left portion of the window.  The actions to follow will generate a Query string and will be entered into the text area below the label "Query string:."

Figure 11

Click on the left icon, Generic Query Designer, which opens up the window shown in Figure 12 with the Query Builder toolbar items exposed.  Using the query designer tools menu you can do a number of things shown in this list.

Show/hide the following:

Diagram pane

Grid Pane

SQL Pane

Results pane

Run the query

Verify the SQL

Use group by clauses

Add tables

This window has four areas.  In the top pane, "Diagram," you will be adding the tables that will be used in the query.  In the "Criteria" pane you will be making choices of the columns from the tables, their sort order and the criteria that need to be satisfied.  In the "SQL" pane, the SQL statement corresponding to the choices you made in the "Criteria" pane will be interactively created.  After satisfying that the syntax is correct by clicking on the related toolbar item, you may "run" this query by clicking on the "run" toolbar item shown by the "!" icon.  The results of running this query can be seen in the "Results" pane.  You may also show or hide the various panes by making appropriate choices from the menu bar.

Figure 12

To add table, right click in an empty area in the "Diagram" pane and choose the Add Table from the pop-up menu shown in Figure 13.  In the toolbar of this window a number of actions can be taken as shown in the following list.

Figure 13

When you click on Add Tables...all the tables from the database will be shown in the pick-list, as shown in Figure 14.  In addition to tables you may also add Views, functions and synonyms.

Figure 14

You may choose one or more tables from this list of tables, a combination of tables, views and functions to generate your query.  Clicking on the table name in the pick list will add the table(s) to the top pane.  Figure 14 shows three of the table that participates in the query.

This next window in Figure 15 shows three of the tables added to the "Diagram" pane.  The tables that are added carry with them the relationships that are defined by the Primary Key and Foreign key relationships present in the base tables.  The orderID relates Order Details table with Order tables and the ProductID relates the Order Details table to the Product table in a relational context.  

Each of the columns in the tables has a checkbox associated with them.  By placing a checkmark in the box you may choose to add the column to your "set" of columns returned by the query.  A checkmark added to the table in the "Diagram" pane automatically adds the column in the "Criteria" pane as well as the "SQL" pane.  The default SQL statement in the "SQL" pane will grow to include all your choices.

In the "Criteria" pane you can make choices about the order as well as the "Filter" conditions you want to impose on the results returned.  In the example shown here, no sort order has been chosen, but a "filter" condition has been imposed to return only those rows that have the "orderDate" older than that corresponding to "5/4/1998."  It is possible to impose multiple conditions on multiple columns in the tables.  You will note that the query that is generated is the same as the one that is desired as mentioned in the beginning of the tutorial.  The report will present the results of this query.  You may check the syntax of the query as well as preview the result of running this query as shown in the "Results."  This query will be returning 44 rows of data as shown in the bottom of the "Results" pane.

 

 

 

 

 

 

Figure 15

When you click on the button "OK," your SQL will be entered into your Design the Query window as shown in Figure 16.

Figure 16

After you click on the "Next" button in this window you will get to the step "Select the Report Type" (shown in Figure 17) where you design the layout of the report.  

Report Type

Basically there are two choices, either tabular or matrix.  Matrix type will be more suitable for matrix data regions which are related to cross-tab or pivot tables.  The tabular data regions are more suited to static table data.  Here, the tabular type is chosen.

Figure 17

Clicking on the "Next" button will take you to the window shown in Figure 18.

Table Design

In the report the data will be housed in a table.  If the data is large it may overflow a page and may have to be placed on several pages.  Here you can define how the data needs to be grouped.  In this tutorial it is based on the CustomerID.  The user interface shown in Figure 18 is a simple, Microsoft standard technique of selecting fields as found in several other products, for example Microsoft Access.  You click on the item CustomerID in the Available Fields and click on PAGE--> button.  The CustomerID will go to the displayed field's area.  This means the data will be presented with each page having a CustomerID and its related data.

Figure 18

Clicking on "Next" will take you to the window shown in Figure 19 where you may add a style from a set of preconfigured styles.

Adding style to the table

Tables in reports must be aesthetically pleasing.  In this step of the wizard you can choose from a number of styles for your report as shown.  Clicking the Next button will take you to the finish line.

Figure 19

When you click on the "Next" button in the above window, you will get to the window in Figure 20 which shows the completion of the wizard. Here you can see all the report related information as it was processed by the wizard.

Figure 20

When you click on the button "Finish" the report will be finished with several windows open showing several important items related to the report.

The window marked "Datasets" in Figure 21a shows the details of the data that is used in creating the report.  It shows the source that is used for the data as well as the various columns selected that will appear in the report.

Figure 21a

If needed you may modify the column by highlighting the column to be modified and choosing the menu item that pops-up after right clicking the highlighted column as shown in Figure 21a_1. However, no modification will be made for the present report.

Figure 21a_1

 

The Layout tab

Figure 21b shows the details of the Hodentek_RSP.rdl report.  It consists of three tabs, a Data, Layout and a Preview.  This window opens up with the default Layout tab in view.  Presently, it is showing the "BODY" of the report.

Figure 21b

The report generally follows the banded structure that is common to most reporting software.   You can access the whole report layout structure by right clicking on an empty area in the layout window as shown in Figure 21b_1 (this can also be seen by expanding the node of the RDL file as a file with XML content shown later).

The composition of the "Body" of this report consists of a textbox control for the title of the report -Hodentek_RSP, a textbox control that is bound to the data that brings in the CustomerID [=Fields!CustomeriD.Value]to the top of every page, a table that formats the column title and column value for each page of the report.  You may also add Report Headers or Report Footers to the report and you can suitably modify the formatting of the report.

Figure 21b_1

Figure 21C shows the properties of the report page.  You may make modifications in this design phase.

Figure 21C

 

The Data tab

You can review the Data that is used in the report by clicking on the Data tab which opens up the query designer bringing up the query designed earlier, as in Figure 22.  If needed, you may make changes to the query that gets reflected in the report.  However, you may have to make appropriate modifications to the report.  You may also use the data from another dataset or a totally new data set by clicking and following the instructions to generate a new dataset on the ellipsis, "<New Dataset…>."  Again, for this tutorial what is already designed will be used.

Figure 22

 

 

 

The preview tab

In the Preview tab you can see a preview of your report, the finished product.  Figure 23 shows the preview of this report.  This shows the first page of the report consisting of some 8 pages. You can move from page to page using the navigation button on the report.

Figure 23

Printing and Exporting the Report

Figure 24 shows the several formats it can be exported out as well as several menu items to Print or review the print layout.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 24

Modifying the report

As previously mentioned, the report can be modified by adding report headers, report footers and even modifications to the dataset.  Corporate logos and formatting of text can also be carried out independent of the style that was chosen earlier.  Figure 25 shows the modified layout which adds a logo, an edited title for the report (the default being the project name) and the time in report footer.

Figure 25

The report with the RDL extension is an XML document, the contents of which can be seen in a text editor.  It carries with it the complete documentation of the data as well as the graphic elements in the report as shown in the listing of the Hodentek_RSP.rdl when viewed for its XML content.  Many of the nodes have been collapsed and only a few significant nodes are expanded.  This listing was derived by pasting the XML on the IE browser to a text editor and saving it as a text file.

Listing 2

<?xml version="1.0"encoding="utf-8" ?> 
- <Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
+ <DataSources>
 <BottomMargin>1in</BottomMargin> 
  <RightMargin>1in</RightMargin>
  <rd: DrawGrid>true</rd:DrawGrid> 
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd: SnapToGrid>true</rd:SnapToGrid> 
+ <Body>
  <rd:ReportID>eaa756c8-a3a4-419f-8f03-da003f03ddac</rd: ReportID> 
  <LeftMargin>1in</LeftMargin> 
+ <EmbeddedImages>
- <DataSets>
- <DataSet Name="srcRSP">
- <Query>
  <CommandText>SELECT
Products.ProductName, Products.QuantityPerUnit, Orders.CustomerID,
Orders.OrderDate, Orders.ShipCity
FROM Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID INNER JOIN Products ON
[Order Details].ProductID = Products.ProductID WHERE (Orders.OrderDate >
CONVERT(DATETIME'1998-05-04 00:00:00', 102))</CommandText> 
 <DataSourceName>srcRSP</DataSourceName> 
  </Query>
+ <Fields>
  </DataSet>
  </DataSets>
  <Width>5in</Width> 
 <InteractiveHeight>11in</InteractiveHeight> 
  <Language>en-US</Language> 
+ <PageFooter>
  <TopMargin>1in</TopMargin> 
  </Report>

Deploying the report on the web server

Before you can serve your report from the web server, you need to have an understanding of the web sites and folders created during installation and how they are related to the SQL 2005 Report Services Server.  When the MS Server SQL 2005 is installed two IIS sites are also created.  These sites are: Reports$Mysorian and ReportServer$Mysorian, as shown in the picture.  The location of the folders for these sites is in the installation directory of MS SQL 2005 Server as shown in the Figure 26.  The Reporting services folder consists of the following folders: Report Manager, The Report Server, the RSTempFiles and LogFiles folders shown in Figure 27.

Figure 26

 

To deploy the report on the web server, right click on the project in the Solution Explorer after highlighting it.  This brings up the property pages for the project.  Insert the appropriate URL for the target server as shown in Figure 27.  The correct address for the target server is as shown in the figure.  Now you may right click the report and click on "Deploy."  This deploys the report to the IIS on this machine.  Since this is an integral part of the SQL 2005 reporting Services, you will see the report added to the SQL Reporting Services which you can review in the MS SQL Server 2005 Management Studio [later in Figure 32].

Figure 27

Report related web site items

Figure 28 shows the relationship between the Reports and ReportingServices virtual sites on the IIS server on the local machine.

Figure 28

 

To access the report you have created browse to the "Home.aspx" page on the server.  This brings up the SQL Reporting Services home page shown in Figure 29 with the "Contents" tab in default view.  It has two items related to "RSP project," a folder "RSP" and a report "Hodentek_ RSP."  The folder "RSP" was added when the report was deployed.  There are other items not related to the "RSP" project.  The "Hodentek_RSP" report was added by using the "Upload File" toolbar item on this page and browsing to the saved location of this file at "C:\Documents and Settings\Jay\My Documents\Visual Studio 2005\Projects\RSP\RSP\Hodentek_RSP.rdl"  on the local machine.  The "RSP" folder will also open to reveal a link to the same report file.

Figure 29

The report may now be browsed by clicking on the link "Hodentek_RSP" which shows the report on the browser as shown in Figure 30.  The address bar has the following URL, http://localhost/Reports$mysorian/Pages/Report.aspx?ItemPath=%2fHodentek_RSP.  This screen can also be used for exporting the report in several formats: HTML, Excel, CSV, Image, PDF and XML.  These utilize the rendering extensions.  In order to select a format for exporting, click on Select a Format drop-down arrow.  In this tutorial the procedure to subscribe to a report and related information will not be discussed.

Figure 30

Report on the SSRS

Microsoft SQL Server 2005 Report Server is the core of the Microsoft SQL Server 2005 Reporting Services and has a number of components.  It is implemented both as a windows service and as a web service.  The report server has two processors and several extensions, such as rendering extensions, security extensions and data extensions, to address the varied nature of data, security and report delivery needed by organizations.  These are outside the scope of the present tutorial.  Figure 31 shows the RSP project on the Report Server in the Microsoft SQL Server Management Studio with the related folders fully expanded.  The folder is created when the Report Server project is deployed for the first time.  If modifications are made to a report it will be necessary to re-deploy the report so that modifications are reflected in the current report.  It is also possible to manipulate the report in several ways through this interface.

Figure 31

Summary

This tutorial takes the reader step-by-step to create a report based on query using the Microsoft SQL Server 2005 Reporting Services.  The procedure does not involve any coding but uses the RAD capability inherent in the IDE in the form of various wizard programmed macros.  The tutorial also shows how to host the report on the IIS web server and how the reports may be viewed either on the browser or through the Report Server interface.  This tutorial barely touches the rich possibilities of this reporting software, especially those concerned with security, distribution, maintenance and API's.  Microsoft bundled Crystal Reports for use in reporting from early days of Visual Basic, but with the introduction of the fully integrated reporting services of its own, this relationship may cease to exist.



User Comments

Title: Mr   
Name: Arunkumar
Date: 12/23/2010 2:52:02 AM
Comment:
Nice one...Quite good and useful Report related web site items as in fig28
Title: Mr   
Name: Prashant Tokle
Date: 3/23/2010 1:01:30 AM
Comment:
Excellent Article, very Useful :)
Title: Using Stored Procedures   
Name: Olusola Ezekiel
Date: 2/2/2010 10:44:15 AM
Comment:
Pls.I nid to use the report wizard with a stored procedure
Title: Deploy the report developed in 2005 Visual studio on to SQL server 2000 reporting services.   
Name: Mruthyunjaya
Date: 1/5/2010 10:51:04 AM
Comment:
I have created a report using SQL server 2005 reporting services but wanted to deploy the same in SQL server 2000 reporting services as my prodcution server has SQL server 2000 instance.

I am getting an error which states that the reporting definition is not correct. It it because of the version compatability if so is there any work around for me to accomplish the task.

Please advice.
Title: How can I migrate sp_makewebtasks into SSRS   
Name: Jayaram Krishnaswamy
Date: 7/30/2009 7:55:37 AM
Comment:
May be you should first take a loop at SQL Server 2005 Upgrade Advisor and the Package Migration wizard.
Title: How can i migrate sp_makewebtasks into ssrs   
Name: srinivasan
Date: 7/29/2009 11:00:21 PM
Comment:
Hi , I have several sp_makewebtask based reports that are sequentially scheduled via DTS in SQL 2000. So if I migrate to sql 2005 or sql 2008, will SRSS be able to generate reports sequentially ?. Can these be scheduled to run or only manually ?. Thanks,Srini
Title: how to ssrs reports developed and deployee   
Name: Jayaram Krishnaswamy
Date: 7/15/2009 12:36:00 PM
Comment:
If you liked this article you may want to check out my book on SSRS 2008 which may let you learn SSRS very fast.

Links to my books are on my URL.

Thanks,

Jay
Title: how to ssrs reports developed and deployee   
Name: Brahma Reddy
Date: 5/26/2009 7:40:49 AM
Comment:
it is very good used for intial learning






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 9/19/2014 9:51:16 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search