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.
