Using Local Microsoft SQL Server Reporting Services in ASP.NET
 
Published: 12 Jul 2007
Abstract
This article examines the steps the author took to make the local reports work using Microsoft SQL Server Reporting Services in ASP.NET.
by Shaun Eutsey
Feedback
Average Rating: 
Views (Total / Last 10 Days): 45637/ 75

Introduction

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.

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 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 Solution

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

Conclusion

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.



User Comments

Title: http://www.ralphlaurenukoutlet.org.uk/   
Name: franklin marshall outlet
Date: 2012-07-25 11:55:58 AM
Comment:
[url=http://www.franklinmarshalloutletonline.es/]franklin marshall outlet[/url] [url=http://www.franklinmarshalloutletonline.es/]frankin & marshall[/url] [url=http://www.franklinmarshalloutletonline.es/]franklin and marshall online[/url] [url=http://www.franklinmarshalloutletonline.es/]franklin & marshall outlet[/url] [url=http://www.franklinmarshalloutletonline.es/]franklin and marshall outlet[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/]Mens Franklin & Marshall[/url] [url=http://www.franklinmarshalloutletonline.es/Womens-Franklin-and--Marshall/]Womens Franklin and Marshall[/url]
[url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-and-Marshall-Hoodie-Sweatshirts/]Franklin and Marshall Hoodie Sweatshirts[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin---Marshall-Jackets/]Franklin & Marshall Jackets[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-and-Marshall-Long-Sleeved-T-Shirts/]Franklin and Marshall Long Sleeved T-Shirts[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-and-Marshall-Knitwear/]Franklin and Marshall Knitwear[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-Marshall-Fleece-Pants/]Franklin Marshall Fleece Pants[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin---Marshall-Polo-T-shirts/]Franklin & Marshall Polo T-shirts[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-and-Marshall-Short-Tees/]Franklin and Marshall Short Tees[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin-Marshall-Shirts/]Franklin Marshall Shirts[/url] [url=http://www.franklinmarshalloutletonline.es/Mens-Franklin---Marshall/Franklin---Marshall-Short-Pants/]Franklin & Marshall Short Pants[/url] [url=http
Title: NIKE NFL jerseys   
Name: NIKE NFL jerseys
Date: 2012-07-02 10:11:12 AM
Comment:
http://www.jersey2shop.com
http://www.cheapjersey2store.com
http://www.jerseycaptain.com
http://www.yourjerseyhome.com
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes
Cheap NFL,NBA,MLB,NHL
,heap jerseys,2012 nike nfl Jerseys,nba jersey and shorts,oklahoma city thunder jersey,official jeremy lin new york knicks jersey,NFL Jerseys Wholesale,blake griffin jersey blue,NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
,Wholesale cheap jerseys,Cheap mlb jerseys,]Nike NFL Jerseys,Cheap China Wholesae,Wholesale jerseys From China,2012 nike nfl Jerseys,Jerseys From China,,2012 nike nfl Jerseys,Revolution 30 nba jerseys,jersey of nba chicago bulls direk rose ,nfl jerseys,green bay packers jerseys wholesale,Buffalo Bills nike nfl jerseys sale,good supplier soccer jerseys,cool base mlb jerseys,Revolution 30 nba jerseys,2012 stanley cup nhl jersey,
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes. www.yourjerseyhome.com
Title: image is not showing   
Name: Muhammad Aurangzeb
Date: 2012-02-20 7:37:35 AM
Comment:
Image on this article is not showing. any other URL where i read this article with picture.
Thanks
Title: Nice article   
Name: ashwini
Date: 2011-02-23 1:55:55 PM
Comment:
Hi, this is very good stuff . Its very easy to understand.Thanks for explaining step by step
Title: Excellent article   
Name: Tahir
Date: 2010-12-31 1:40:11 AM
Comment:
excellent, to the point and informative.

Thanks
Title: Good Article   
Name: Usman Saeed
Date: 2010-04-01 3:04:17 AM
Comment:
this was quite good
Title: you are fool   
Name: abc
Date: 2010-03-27 2:48:33 AM
Comment:
you r wating our time means your fool and dont submit any doc like this ok .fool fool fool
Title: Useful Article   
Name: Suresh
Date: 2010-01-30 1:09:14 AM
Comment:
Hi Mr Shaun Eutsey
it's very nice and simple
Title: It's Nice   
Name: Ramana
Date: 2010-01-22 4:50:44 AM
Comment:
It' nice and very useful.Thax alot.
Title: It's very useful   
Name: Mohammad
Date: 2009-10-21 3:02:46 AM
Comment:
Hi Mr Shaun Eutsey
Your article is very useful for me but images is not clear.
thanks
Title: Mr   
Name: Sathish
Date: 2009-09-01 7:57:45 AM
Comment:
Awesome!!! This worked like a champion. Very informative and helpful. Thanks
Title: perfect!!!!   
Name: Vaastu
Date: 2009-08-04 12:45:17 AM
Comment:
first of all thank u very much :)
this article really help me to understand ,how to code & design a SQL report Step by Step .
very informatics and very ease to understand with the help of attached snapshots.
This Alliance doing gr8 job Carry on !!!!
Title: Thanks!!   
Name: Pooja
Date: 2009-06-21 9:01:56 PM
Comment:
Very Nicely Explained. Thanks!!!






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


©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-07-20 12:23:22 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search