asp:Feature
Reporting Services 2005 101 Using Web Client
A Step by Step Tutorial Demonstrating the Power of Locally
Processed Reporting Services with the ASP.NET Web Client
By Asif Sayed
Introduction
What is your favorite GUI client? Tough question right,
especially, if we have such choices as Windows/Web and Mobile
from which to choose. To stay competitive, software vendors are increasingly
under pressure to address every possible client under the sun!
I assume the reader has a basic understanding of the
Visual Studio 2005 IDE and is comfortable with writing the code using C#
(VB.NET in case you want to see the Access Database sample). You don t have to
know Reporting Services to understand this article; although, any pervious
experience with report writing will help.
Although, I m calling this article 101, my intention is to
adopt the applied approach rather than discuss each and every topic associated
with reporting services. I m touching on the most common aspects of report
designing with the most commonly used controls. I would strongly encourage you
to go through the MSDN documentation for more detailed information.
Reports for Web Client, No Big Deal!
Take a look at Figure 1. Creating a repot like this for
the Web client is as simple as designing the Web page. Trust me, a few drag and
drops and changing a few properties that s all it requires! The result is a
simple report listing all product information with a standard header, body, and
footer.
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 an Access database).
Figure 1
I m sure most of you have created an ASP.NET Web site
using VS 2005; if not, don t worry we ll do that in Step 1. Let s get started
with creating the Web client for our report.
Step 1: Create an ASP.NET Web Site
Follow these steps to create an ASP.NET Web site:
- Select File | 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 dropdown box, 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, you ll find the Web page default.aspx created and opened in
designer with focus set on HTML Source. (If you try to run the application in
debug mode at this time, a dialog box will ask you to enable debugging; click the
OK button to do the same.)
Next, update the following property of 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 Visual Studio 2005. Report Viewer gives life
to your reports. It not only previews the output, it also allows 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.
Perform the following actions to set up 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 Report Viewer with the name
reportViewer1. You can name it anything; I ll stick with reportViewer1.
- By setting reportViewer1.Width = 100%, Report Viewer
will fill the entire surface of the page for report-display purposes.
Make sure reportViewer1.ProcessingMode = Local; otherwise,
you ll get an error while rendering the report as the source is not available.
After step 1 and step 2, your project should look like Figure
2.
Figure 2
Step 3: Add DataSet to the Project
Alright, the page is 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 the Add button;
it ll ask you to place the data set in the App_Code folder; click the Yes
button to confirm. Please Cancel the TableAdapter Configuration wizard; we ll
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 Solution
Explorer; it ll open the designer view. Right-click the designer surface and select
Add | DataTable. Click the header and change the name to dtProductList (see the
Figure 3).
Figure 3
Let s start adding columns to DataTable(dtProductList).
Your designer screen should look like Figure 4. Right-click on dtProductList
and select Add | Column to start adding the columns to the DataTable.
Figure 4
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 string
data type. Go to the Properties windows after selecting a column to change it
from String to Integer or Double (see Figure 5). Your DataTable should look the
same. Also, you can see the Properties window to change the data type.
Figure 5
Step 4: Add Report to the Project
So far we ve created the project, and added Report Viewer
and DataSet. Now it s time to deal with the star of the show! Let s create that
neat report. Following are the steps required to add Report
(rptProductList.rdlc):
- Select Add | New Item | Report from Solution
Explorer. Change the name from Report1.rdlc to rptProductList.rdlc and click the
Add button to complete the action.
Typically, after the add action is finished your screen
should look similar to Figure 6. When a report is added to the project, it is
ready to use the DataSet for designing.
Figure 6
Whether this is your first report or you are a reporting
junkie like me; we must deal with the most basic building blocks of report
writing; that is, the header, the body, and the 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 the open designer surface and selecting Properties.
It is always advisable to draw a prototype of your report
on paper before you start the design process. As you can see in Figure 1, we
have the report name and report date in the header section. The body section
has the product list information together with the summary totals; the 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 than
the body and select Page Header. This will add a header to the report. Feel
free to adjust the height of the header and the body section. In Figure 7, I ve
reduced the height of the body and increased the height of the header.
Figure 7
While inside the report designer, if you explore the
Toolbox, you ll see a variety of controls that can be used to design the
report. For our example, we ll use the TextBox, Line, and Table controls. 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 controls and dropping them on the header section. A TexBox can show
both the static and the dynamic data. A 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 control to report the title and another to show the
current date. We can directly type static text into a TextBox control by
selecting it and typing inside. Change the following properties of the Title
TextBox:
Value = "Product List"
Color = Purple (you like purple too for title right?)
Next, change the following properties of the Date TextBox:
Value = ="Run Data: " & Today
Color = Purple (you like purple too for title right?)
Note the Value property for the Date TextBox starts with the
= sign. This is not a simple static text; instead it is an expression. This
expression is the result of the string Run Date and the VB.NET script keyword
Today (to get current system date).
You can specify desired names to all the objects in the
report; I chose to stay with the default name for most of the controls;
however, for demonstration purposes I did specify txtTitle to the Title
TextBox. Your finished design for the header should look relatively similar to
Figure 8.
Figure 8
Body Section
The body section, also referred to 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, the body section was added for us
automatically. All we have 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 than one row of the
information. The body section can expand as per the growth of the reported
data. Often, a report is designed with the intention to have one physical page
(Letter/A4, etc.) output; in this case, the body section can still be used to
display the information.
Table, Matrix, and List comprise the three most commonly
used controls on the body section; we ll use the Table control for our example.
All three can repeat information; Matrix goes a step further and 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 the center
column has been labeled: Header, Detail, and Footer.
Now, don t be surprised if I tell you that the Table
control is nothing but a bunch of TextBoxes attached together! Yes, each and
every Cell in the Table is like a 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, we have a total of five columns in the report). Adding
columns is easy; do the following to get new columns added to the report:
- Select the Table control inside the body section.
- Click on the right-most column header (assuming
we are adding new columns to right side).
- Right-click on the header and select Insert Column
to the Right.
Make sure your report resembles Figure 9. Feel free to
adjust the width of columns based on the length of data it ll hold.
Figure 9
I m sure most of us have used Excel (or something similar);
think of the Table control as a mini worksheet. We can apply the borders,
change the font of the individual cell, etc. So, all you have to do is think of
a formatting theme and start applying it.
Starting with the first column, click 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 note is, instead of the text, we have to type the expression (which
are the columns from dsProduct.dtProductInfo). You can either type the
expression or simply drag and drop the column from the Data Sources Toolbar
(see the left side of Figure 7).
In case you decide to type it out, starting with the first
column and proceeding to the last, click 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 note of Detail 5; it s the calculated output
by performing multiplication of the Units in Stock and Unit Value. (If you drag
and drop the column to the detail section of the Table control, it ll try to
add the column header automatically, if the column header is empty).
Finally, let s add the summary total in the footer section
of the Table control. 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)"
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 the
report.
Footer Section
Before we start writing some cool C# code to bring our
report alive, let s finish the report footer section. Because we added the
report header earlier, we must also right-click the open report designer
surface and select Page Footer (again, see Figure 7).
Drag and drop a Line and TexBox control on the footer
section. Type the following expression inside the TextBox:
Value: = Page: & Globals!PageNumber & / &
Globals!TotalPages
As you can see, I ve used PageNumber and the TotalPages; both
are Global variables maintained by the reporting engine. (Make sure all the
expressions you type start with = in front of it).
Make sure your report looks that shown in Figure 10. As
you can see, I ve introduced some color and right alignment to numeric data,
etc. Feel free to try all the different formatting options; just think of a Table
control as a mini spreadsheet with the columns and the rows.
Figure 10
Expression Builder
Expression builder is a very powerful feature of Reporting
Services. As you can see in Figure 11, Stock Value is calculated with the help
of the SUM function. All the fields in DataSet can be accessed with the Fields!
keyword.
Figure 11
Step 5: C# Interface Code
From Solution Explorer, select 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 the 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();
}
}
}
I ve used TOP 5 for the select query (because I wanted
to limit the output so I can demonstrate the summary total; again, see Figure 1.
(The Name property of the ReportDataSource object always should be
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 thing with ASP.NET 2.0 is each individual page
can be created with your choice of the scripting language.
I thought it might be a good idea to show an Access
example with VB.NET scripting. I ve added a page called vbPage.aspx in the
project; 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 you don t have it you can get it here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN
The revised code should look like this:
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
Conclusion
As you can see, we don t have to rely 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.
Writing for a beginner-level audience is always a
challenge. I am relying on your feedback/suggestions to improve; please send me
your thoughts.
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.
Asif Sayed has more
than 15 years of experience in software development and business process
architecture. A senior systems analyst with Direct Energy in Toronto,
Canada, he also teaches
.NET technologies at Centennial College
in Scarborough, Ontario.
He is currently writing a book for Apress about Local Mode Reporting Services Using
Visual Studio 2005, which is going to press at the end of May 2007. Contact him
at mailto:asif.blog@gmail.com.