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:
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:
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:[email protected].
