SQLServerCentral Article

Reporting Services 2005 101 using Web Client

,

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.

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating