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