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 2000 database that the application would run upon. Plans to add a SQL Server 2005 instance were in the plans, but not until after this application needed released.

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.

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 2005 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.

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 2.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.

Figure 20

There are the examples of how to run local SSRS reports with and without parameters.  They can be a little daunting at first, but like anything in our world of application development they get easier with time.  I know that, in an ideal environment, SSRS would be set up on the database server and using server side reports is definitely the way to go, but every once in a while you land in a less than ideal circumstance and you have to make due with what is handed to you.  I hope this article helps when you find yourself in that place.