How to use Microsoft SQL Server Reporting Services in ASP.NET

[Niranjan Singh]

There are times in a developer’s life when you get to use great new tools.  Recently, at a client site, I was
asked to create a few web based reports that would fit into the application that I was working on.  I was
given my options; create them using a repeater control, Crystal Reports or Microsoft SQL Server
Reporting Services (SSRS).

Each of these comes with their own benefits and detractions straight from the top. 

The repeater control is easy to get the data formatted, but getting the pages to break in the correct spot is
notoriously painful.

Crystal Reports is standard, a little painful, but easy to develop.  However, in production there would have
been some licensing difficulties.  I was “encouraged” to not use Crystal.

SSRS are great when they are installed on a SQL Server.  However, this client did not have them installed
and had no intention of installing them on the SQL Server 2008 database that the application would run
upon.

SSRS was the preferred option, not only by the client, but by me as well. Then the dilemma struck me,
I had never used SSRS locally. Thus began my local SSRS adventure.

The Scenario

Before we get started on actually writing our local report, we should delve into the database that we will be
using. As with most example applications we will be working with the Northwind database.  We will write
a report based on the Customers and Orders Tables. I will be demonstrating this using parameters and
without using parameters. The basic query that I will be using in both instances is below.

Listing 1

Select OrderID,        OrderDate,        ShippedDate,        CompanyName,        ContactName,        ShipName,        ShipAddress,        ShipCity,        ShipRegion,        ShipPostalCode,        ShipCountry
from   Customers cst
inner join Orders od on       od.CustomerID = cst.CustomerID 

First, I will create a new website called SSRSExample.  I will be using Visual Basic 2010 as my
development language. Also, since this is an example, I am going to go light on the design and focus
more on the process of creating the report.

Figure 1

The first thing that we want to do, now that the project is created, is to add the Bin and App_Code folders
to the project.

Figure 2

Now that the Bin and App_Code folders exist in the project, we need to add a dataset in the App_Code
folder.  Do this by right clicking on the App_Code folder and selecting Add New Item.  Select DataSet,
change the name, and click Add.

Figure 3

The Table Adaptor Configuration Wizard will be a pop-up that will ask you to define the data that will be
used to populate the dataset.  First, select your database server from the list or create a new connection to
the server then click Next.

Figure 4

You will be asked to save your connection in the web.config file. I will say yes; it will make adding the
next DataSet a little easier. Then Click Next.

Figure 5

This first example will use the Use SQL Statements option. As you can see you can create a stored
procedure from here or use an existing one.  In my development I use existing stored procedures. They
give me more flexibility and control over the application. For the example though, we will just "Use SQL
Statements."  Click Next.

Figure 6

This next page of the wizard will allow you to enter the SQL Statement with which you want to populate
the DataSet.  Click Next, Next, then Finish.  No options need changed on the following two screens.

Figure 7

After finishing you will then see the following screen.

Figure 8

Now we will open up the Default.aspx page and add a ReportViewer control to the page.

Figure 9

A blank report viewer will be added to the page, resize to fit.  I will make this one 800x600 from properties
of Report Viewer control.

Figure 10

Now, right click on the project name and Add New Item.  Select Report and change the name.  Click Add.

Figure 11

A blank report will be added to the project and a new control tab will be added with the Toolbox and Server
Explorer tabs.  This tab holds the controls for the dataset we just created.

Figure 12

Now that the report is open, add a Table to the report.

Figure 13

Start adding boxes to accommodate the data that we want to display. Some formatting may need to take
place to fit everything into the 800 x 600 format that we have created on the Report Viewer that we placed
on Default.aspx.  I have added 5 boxes and dragged and dropped the OrderID, CompanyName, OrderDate
, ShippedDate and ShipAddress into the fields.  I have added formatting to the two date fields and some
concatenation to the Address field. The next few images will show how the formatting was done.

Figure 14

Now that the report has been created we need to link it to the ReportViewer on Default.aspx.  Click on
the to open up the ReportViewer Tasks box and select the report from the drop down list. This will
add an ObjectDataSource to the page.

Figure 15

Rename the ObjectDataSource (odsExample1) and click on the again to choose the data source to use
with the report.

Figure 16

At this point you can run the application! So hit F5 and watch the magic happen.

Figure 17

Now, granted, this is a simplistic report, there is no filtering that has taken place, just a mess of a report. 
The question now becomes, how do we create a report that will accept parameters and give a little more
meaningful results? That is a good question, and I will show you.

We will use the same project; just add two new pages, a new report and a new DataSet.  I will only
highlight the differences between the two.  The two pages that will be added will be a criteria selection

page and a report page. The new DataSet will accept a parameter (CustomerID).

Here is the new query in the Example2 DataSet, notice that I added a where clause and a parameter
@CustomerID.

Figure 18

The report that I created for this one looks identical to the one in example 1.  However, it is now pointing
to the new DataSet.

This is the basic, very basic form for the Example2Select.aspx page.  Another feature of the .NET
Framework 4.0 that I find very useful is Cross Page Post Backs.  I will explain a little about them now,
since I am using them for the page submissions.

Figure 19 

To do a cross page post back, you have to set the PostBackURL property of the Submit Button.  I have
set the property to ~/Example2.aspx.  When the button is clicked this will send the post back straight to
the page referenced in the PostBackURL property.  In order to make this as easy and as powerful as
possible I have also added the following public read only property to the Example2Select.aspx page:

Listing 2

Public ReadOnly Property CustID() As String  Get   Return tCustID.Text     End Get
End Property

When setting up the page that is being submitted to, the following code needs placed in the .aspx page markup:

Listing 3

<%@ PreviousPageType VirtualPath="~/Example2Select.aspx" %>

This tag sets up a reference to the page that is posting to this page, in this case, Example2Select.aspx. The
power of this tag will become clear with the next piece of code that runs before this page is rendered. This
codes job is to reference Example2Select.aspx and get the CustID property that was entered and then pass
it into the odsExample2 ObjectDataSource. The power in setting the above tag is in how the CustID
property is referenced, PreviousPage.CustID.  Without the PreviousPageType being explicitely set,
conversions would have to be done just to get the CustomerID.  This allows for a Strongly Typed Reference
to the CustID property.

Listing 4

Protected Sub odsExample2_Selecting(ByVal sender As Object, _         ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) _         Handles odsExample2.Selecting           e.InputParameters("CustomerID") = PreviousPage.CustID
End Sub

These are the only real differences in the two processes.  So, with no further ado, the screen shots of the
application running with parameters.