Using Local Microsoft SQL Server Reporting Services in ASP.NET
page 3 of 4
by Shaun Eutsey
Average Rating: 
Views (Total / Last 10 Days): 29788/ 70

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

View Entire Article

User Comments

Name: franklin marshall outlet
Date: 2012-07-25 11:55:58 AM
[url=]franklin marshall outlet[/url] [url=]frankin & marshall[/url] [url=]franklin and marshall online[/url] [url=]franklin & marshall outlet[/url] [url=]franklin and marshall outlet[/url] [url=]Mens Franklin & Marshall[/url] [url=]Womens Franklin and Marshall[/url]
[url=]Franklin and Marshall Hoodie Sweatshirts[/url] [url=]Franklin & Marshall Jackets[/url] [url=]Franklin and Marshall Long Sleeved T-Shirts[/url] [url=]Franklin and Marshall Knitwear[/url] [url=]Franklin Marshall Fleece Pants[/url] [url=]Franklin & Marshall Polo T-shirts[/url] [url=]Franklin and Marshall Short Tees[/url] [url=]Franklin Marshall Shirts[/url] [url=]Franklin & Marshall Short Pants[/url] [url=http
Title: NIKE NFL jerseys   
Name: NIKE NFL jerseys
Date: 2012-07-02 10:11:12 AM
We are professional jerseys manufacturer from china, nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes
,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, nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes.
Title: image is not showing   
Name: Muhammad Aurangzeb
Date: 2012-02-20 7:37:35 AM
Image on this article is not showing. any other URL where i read this article with picture.
Title: Nice article   
Name: ashwini
Date: 2011-02-23 1:55:55 PM
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
excellent, to the point and informative.

Title: Good Article   
Name: Usman Saeed
Date: 2010-04-01 3:04:17 AM
this was quite good
Title: you are fool   
Name: abc
Date: 2010-03-27 2:48:33 AM
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
Hi Mr Shaun Eutsey
it's very nice and simple
Title: It's Nice   
Name: Ramana
Date: 2010-01-22 4:50:44 AM
It' nice and very useful.Thax alot.
Title: It's very useful   
Name: Mohammad
Date: 2009-10-21 3:02:46 AM
Hi Mr Shaun Eutsey
Your article is very useful for me but images is not clear.
Title: Mr   
Name: Sathish
Date: 2009-09-01 7:57:45 AM
Awesome!!! This worked like a champion. Very informative and helpful. Thanks
Title: perfect!!!!   
Name: Vaastu
Date: 2009-08-04 12:45:17 AM
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
Very Nicely Explained. Thanks!!!

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

©Copyright 1998-2023  |  Page Processed at 2023-09-25 6:35:22 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search