Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting Services 2005 101 using Web Client

By Asif Sayed,

Introduction

What is your favorite GUI client? Tough question right, especially, if we've choices as Windows/Web and Mobile to pick from. To stay competitive, software vendors are increasingly under pressure to address every possible client under the sun!

As I've demonstrated in my pervious article, how to host MS Reporting Services 2005 with Windows Forms; this time, I'll show the similar technique, however, I'm picking on ASP.NET Web Client.

I assume the reader has the basic understanding of the Visual Studio 2005 IDE and comfortable with writing the code using C# (VB.NET in case if you want to see the Access Database sample). You don't have to know MS Reporting Services to understand this article; although, any previous experience with report writing would help to fast track yourself.

Although, I'm calling this article 101, my intention is to adopt the applied approach rather then discussing each and every topic associated with reporting services. I'm touching on most common aspect of report designing with most commonly used controls. I would strongly encourage you to please go through the MSDN documentation for more detailed information.

You can find my previous article here: http://www.codeproject.com/useritems/rswin101.asp

Reports for Web Client, no big deal!

Please take a look at the Image 1. Creating a report like that for the Web Client is as simple as designing the Web Page. Trust me, a few drag and drops and changing few properties thats all it requires! The result is a simple report listing all products information with standard header, body and footer.


Image: 1

The report output will have the data from the data source NorthWind->Products (SQL Server 2000); if you cannot connect to SQL Sever, you can always use Access to try out this tutorial (please see the separate code for using Access Database).

I'm sure many of you have already tried your hands on creating a ASP.NET website using VS 2005; if you have not, then don't worry, we'll just do that in Step 1. Let's get started with creating the Web Client for our report.

Step 1: Create ASP.NET Web Site

Please do the following to create an ASP.NET Web Site:

  • Select File menu -> New -> Web Site.
  • Choose File System from Location.
  • Choose Visual C# from Language.
  • In the Templates pane, choose ASP.NET Web Site.

Next to the File System drop down, enter the name of the project including the path (I named mine C:\mySites\rsWeb101) or you can use the one offered by default or click the Browse button to navigate to it. Once you are done, youll find the web page default.aspx created and opened in designer with focus set on HTML Source.

Tip: If you try to run the application in debug mode at this time, a dialog box will ask you to enable debugging, just click OK button to do this.

Please update the following property of page default.aspx:

<title>Reporting Services 2005 101 using Web Client</title>

Feel free to change any other properties of the default.aspx as per your requirements.

Step 2: Add Report Viewer to the Page

Report Viewer is the other half of the Client-Side reporting components provided with the Visual Studio 2005. For all those who are new to report writing, I would say, report viewer gives life to your reports.

It not only previews you the output, further, it also facilitates you to generate the information in the various popular formats (pdf, excel etc.). You can also take a hard copy print of the report while you are viewing the output.

Please perform following actions to setup the Report Viewer Control on the Page:

  • Make sure page default.aspx is currently selected with focus on design mode
  • Drag ToolBox -> Data -> ReportViewer and drop it on the page. This step will create a new instance of ReportViewer with name reportViewer1. You can enjoy naming ceremony to your hearts content; Ill stick with reportviewer1 this time.
  • By setting reportViewer1.Widht = 100%, report viewer will fill the entire surface of page for report display purpose.

Tip: Please make sure reportViewer1.ProcessingMode = Local, otherwise youll get error while rendering the report as source not available.

After the step 1 and step 2, your project should look as per the Image 2.

Image: 2

Step 3: Add DataSet to the Project

Alright, weve the page ready with the viewer control properly placed; it's time to start working on getting the data from the source and use it to create the report. We'll use the ADO.NET Typed DataSet; to hold the raw data from the data source and use the schema to design the report.

The following step is required to have the DataSet added to the project:

  • Select Add -> New Item -> DataSet from Solution Explorer. Change the name from DataSet1 to dsProduct and click on Add button; it'll ask you to place the data set in App_Code folder, click on Yes button to confirm. Please Cancel the TableAdapter Configuration wizard; well add DataTable using the DataSet Designer.

Let's add the DataTable to our newly created DataSet. DataTable is essential to load the reporting data; we'll use the information from the DataSet/DataTable while designing the report.

The following step is required to have DataTable added to DataSet(dsProduct):

  • Double click on dsProduct from the Solution Explorer; itll open the designer view. Right-click on the designer surface and Add -> DataTable. Please click on the header and change the name to dtProductList. Please see the Image 3.

Image: 3

Let's start adding columns to DataTable(dtProductList). Your designer screen should look like the Image 4. Right-click on dtProductList and select Add -> Column to start adding the columns to the DataTable.

Image: 4

Please repeat the action for the following columns:

  • ProductName (String)
  • QuantityPerUnit (String)
  • UnitPrice (Double)
  • UnitsInStock (Double)
  • UnitValue (Double) A calculated field based on UnitsInStock * UnitPrice

As you are adding the columns, by default it is a string data type. Please go to the properties windows after selecting column to change it from String to Integer or Double.

Please see the image 5. Your DataTable should look the same. Also, you can see the properties window to change the data type.

Image: 5

Step 4: Add Report to the Project

Alright, so far we created the project; added Report Viewer and DataSet. Now, it is the time to deal with star of the show! Let's create that neat report.

The following steps are required to have Report (rptProductList.rdlc):

  • Select Add -> New Item -> Report from Solution Explorer. Change name from Report1.rdlc to rptProductList.rdlc and click on Add button to complete the action.

Typically, after the add action is finished your screen should be similar to the Image 6. When a report is added to the project, it is ready to use the DataSet for designing.

Image: 6

Whether this is your very first report or you are a reporting junkie like me; weve to deal with the most basic building blocks of report writing, which is: Header, Body and Footer.

Typically, reports are designed with specific page size and layout in mind. Our report is Letter size and Portrait layout. You can explore various properties attached to the report layout by right clicking anywhere on open designer surface and select properties.

It is always advisable to draw a prototype of your report on the paper, before you start the design attempt. As you can see in the Image 1, weve Report Name and Report Date in the header section. The body section has the product list information together with the summary totals; and footer carries the Page Numbers.

Let's start working on the Page Header:

When a new report is added to the project, by default, all you'll see in the report designer is the body section. Right click on the report designer surface anywhere other then body and select Page Header. This will add header to report. Feel free to adjust the height of header and the body section. See Image 7, I've reduced the height of the body and increased the height of the header.

Image: 7

While inside the report designer, if you explore the Toolbox, you'll see variety of controls, which can be used to design the report. For our example, we will use, TextBox, Line and Table control. I would encourage you to go through the online documents if you need detailed information for all the available controls.

Header Section

Let's start designing the header. We'll start by dragging two TextBox and dropping them on the header section. Texbox can show both the static and the dynamic data. Line control is used to separate the header from the body section.

After dropping controls over the report designer surface, you can control the look and feel by changing the associated properties. We'll designate one TextBox to report the title and another one to show the current date. We can directly type static text into TextBox control by selecting it and start typing inside.

Please change the following properties of the Title TextBox:

Value = Product List

Color = Purple (you like purple too for title right?)

Please change the following properties of the Date TextBox:

Value = ="Run Data: " & Today

Color = Purple (you like purple too for title right?)

Please note the Value property for the Date TextBox starts with a = sign. This is not a simple static text, instead it is an expression. This expression is a result of the string Run Date and VB.NET script keyword Today (to get current system date).

You can specify desired names to all the objects in the report; I choose to stay with the default name for most of the controls, however, for demo purpose I did specified txtTitle to Title TextBox.

Please refer to the Image 8; your finished design for the header should look relatively same.

Image: 8

Body Section

Body section, also referred as the details section, is by far the most the important part of the report. As you can see when we added the report to the project; body section was added for us automatically. All we've to do is start putting controls on it.

Traditionally the Body section is used to display details (in our example it is product information) usually more then one row of the information. Body section can expand as per the growth of the reported data. Often report is designed with intention to have one physical page (Letter/A4 etc.) output; in this case Body section still can be used to display the information.

Out of Table, Matrix and List, the three most commonly used control on Body section; well use Table control for our example. All three can repeat information; Matrix goes a step further and even produces the Pivot output.

Let's drag and drop the Table control on the body section of the report designer surface. If you notice, this action will produce a table with three rows and three columns. You may have also noticed that center column also has been labeled: Header, Detail and Footer.

Now, don't be surprised if I tell you that Table control is nothing but bunch of TextBox attached together! Yes, each and every Cell in the Table is like TextBox, which means you can either type the static text on it or specify a dynamic expression.

Before we start designing the Body section, let's add two more columns (remember weve total of five columns in the report). Adding columns is easy; please do the following to get new columns added to report:

  • Select Table Control inside Body section
  • Click on right most column header (I assume we are adding new columns to right side)
  • Right click on header and select -> Insert Column to the Right

Make sure your report resemble to Image 9. Feel free to adjust the width of column based on length of data it'll hold.

Image: 9

I'm sure majority of us have used the Excel or something similar; think of same for Table control as mini worksheet. We can apply the borders, change the font of the individual cell etc. etc. So, all you've to do is to think of desired formatting theme and start applying it.

Starting with the first column to the last one, please click on the individual column header cell and type the following text:

Header 1: Product Name

Header 2: Packaging

Header 3: Unit Price

Header 4: Units in Stock

Header 5: Stock Value

Let's continue to do so the same for the Detail section, here one thing to know is, instead of the text we're to type the expression, which is columns from dsProduct.dtProductInfo. You can either type the expression or simply drag and drop the column from Data Sources Toolbar (see Image 7 on left side).

In case if you decide to type it out, starting with the first column to the last one, please click on the individual column detail cell and type the following text:

Detail 1: =Fields!ProductName.Value

Detail 2: =Fields!QuantityPerUnit.Value

Detail 3: =Fields!UnitsInStock.Value

Detail 4: =Fields!UnitPrice.Value

Detail 5: =Fields!UnitsInStock.Value * Fields!UnitPrice.Value

Please take notice of Detail 5: it is the calculated output by performing multiplication of the Units in Stock and Unit Value.

Tip: If you drag and drop the column to detail section of the Table control, itll try to add the column header automatically, if column header is empty.

Finally, let's add summary total in the footer section of the Table control. Please make sure to select the footer cell on column 4 and 5 inside the Body section and type the following text:

Cell 4: Total Value:

Cell 5: =SUM(Fields!UnitsInStock.Value * Fields!UnitPrice.Value)

Please check the expression in Cell 5; I'm using a built-in function SUM() to find out the total stock value for all the products listed in report.

Footer Section

Before we start writing some cool C# code to bring our report alive, let's finish the report footer section. As we've added the report header earlier, similarly we've to right click on the open report designer surface and select the Page Footer (see the Image 7).

Drag and drop a Line and TexBox control on the Footer section. Please type the following expression inside the TextBox:

Value: ="Page: " & Globals!PageNumber & "/" & Globals!TotalPages

As you can see I've used the PageNumber and the TotalPages, both are Global variables maintained by the reporting engine.

Tip: Make sure all the expression you type must start with = in front of it.

Please make sure your report looks like the Image 10. As you can see I've introduced some color and right alignment to numeric data etc. Feel free to try out all the different the formatting options, just think of Table control as the mini spreadsheet with the columns and the rows and now you know all the formatting you can try on them.

Image: 10

Expression Builder

Expression builder is a very powerful feature of the Reporting Services. As you can see in the Image 11, Stock Value is calculated with the help of SUM function. All the fields in DataSet can be access with the Fields! keyword.

Image: 11

Step 5: C# interface code

From the solution explorer, select the default.aspx. Right click on the surface of the page and select View Code.

using System.Data.SqlClient;

using Microsoft.Reporting.WebForms;

Make sure the Page_Load event has following code:

protected void Page_Load(object sender, EventArgs e)

{

//declare connection string

string cnString = @"Data Source=(local); Initial

Catalog=northwind;" + "User Id=northwind;Password=northwind";

 

/*use following if you use standard security

string cnString = @"Data Source=(local);Initial

Catalog=northwind; Integrated Security=SSPI"; */

 

//declare Connection, command and other related objects

SqlConnection conReport = new SqlConnection(cnString);

SqlCommand cmdReport = new SqlCommand();

SqlDataReader drReport;

DataSet dsReport = new dsProduct();

 

try

{

//open connection

conReport.Open();

 

//prepare connection object to get the data through

//reader and populate into dataset

cmdReport.CommandType = CommandType.Text;

cmdReport.Connection = conReport;

cmdReport.CommandText = "Select TOP 5 * FROM Products

Order By ProductName";

 

//read data from command object

drReport = cmdReport.ExecuteReader();

 

//new cool thing with ADO.NET... load data directly

//from reader to dataset

dsReport.Tables[0].Load(drReport);

 

//close reader and connection

drReport.Close();

conReport.Close();

 

//provide local report information to viewer

ReportViewer1.LocalReport.ReportPath =

Server.MapPath("rptProductList.rdlc");

 

//prepare report data source

ReportDataSource rds = new ReportDataSource();

rds.Name = "dsProduct_dtProductList";

rds.Value = dsReport.Tables[0];

ReportViewer1.LocalReport.DataSources.Add(rds);

ReportViewer1.LocalReport.Refresh();

}

catch (Exception ex)

{

//routine to handle error

}

finally

{

//check if connection is still open then attempt to close it

if (conReport.State == ConnectionState.Open)

{

conReport.Close();

}

}

}

Ive used TOP 5 for select query; the reason is I wanted to limit the output so that I can show you summary total in the Image 1.

Tip: Name property of the ReportDataSource object should be always DataSet_DataTable.

Access database example with VB.NET scripting

Same reporting can be done using the Access database or any other data source. One cool think with ASP.NET 2.0 is each individual page can be created with your choice of the scripting language.

I have got few requests as result of my earlier article to show the code using VB.NET. Hence, I thought this is good idea to show the Access example with the VB.NET scripting. I've added a page called vbPage.aspx in the project, you can check it out to see how the code is implemented using VB.NET.

Usually the Northwind database comes with the Access database installation; in case if you dont have it then you can get it from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN

Revised code should look like the following:

Imports System.Data.OleDb

Imports System.Data

Imports Microsoft.Reporting.WebForms

 

Partial Class vbPage

Inherits System.Web.UI.Page

 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Me.Load

Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=c:\nwind.mdb;User Id=admin;Password=;"

 

Dim conReport As OleDbConnection = New

OleDbConnection(cnString)

Dim cmdReport As OleDbCommand = New OleDbCommand

 

Dim drReport As OleDbDataReader

Dim dsReport As DataSet = New dsProduct()

 

Try

conReport.Open()

 

cmdReport.CommandType = CommandType.Text

cmdReport.Connection = conReport

cmdReport.CommandText = "Select TOP 5 * FROM

Products Order By ProductName"

 

drReport = cmdReport.ExecuteReader

 

dsReport.Tables(0).Load(drReport)

 

drReport.Close()

conReport.Close()

 

ReportViewer2.LocalReport.ReportPath =

Server.MapPath("rptProductList.rdlc")

 

Dim rds As ReportDataSource = New ReportDataSource

rds.Name = "dsProduct_dtProductList"

rds.Value = dsReport.Tables(0)

 

ReportViewer2.LocalReport.DataSources.Add(rds)

ReportViewer2.LocalReport.Refresh()

 

Catch ex As Exception

'error handling code

Finally

If conReport.State = ConnectionState.Open Then

conReport.Close()

End If

End Try

End Sub

End Class

You can download the code here

Conclusion

As you can see, we don't have to rely now on HTML coding or any other reporting tool or reporting solution; Microsoft has done a cool job by providing the Reporting Services out of the box with Visual Studio 2005.

I know writing for beginner level audience is always a challenge compare to writing for expert audience. I am relying on your feedback/suggestion to improve my self all the time; so, please keep sending me your thoughts. You can write to me at: asif.blog at gmail dot com.

Thank you for reading; I sincerely hope this article will help you a bit or two to know reporting services better through my applied approach.

Total article views: 11046 | Views in the last 30 days: 10
 
Related Articles
FORUM

Remove repeated column header

Remove repeated column header

FORUM

Issue in designing the report

Issue in designing the report

FORUM
FORUM

Retrieve column headers for INFORMATION_SCHEMA

Get INFORMATION_SCHEMA column headers

FORUM

Page Header in Case of Dyanamic report Header

Page Header in Case of Dyanamic report Header

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones