Reporting Services 2005 using Web Client

A step by step tutorial demonstrating the power of locally processed Reporting Services with the ASP.NET Web Client. 

Introduction

What is your favorite GUI client? Tuff 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 the MS Reporting Services to understand this article; although, any pervious experience with the 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.

Image: 1

Reports for Web Client, no big deal!

Please take a look at the Image 1.  Creating repot like that for the Web Client is as simple as designing the Web Page. Trust me, few drag and drops and changing few properties that's all it requires!  The result is a simple report listing all products information with 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 Access Database). 

I'm sure many of you have already tried your hands on creating a ASP.NET website using VS 2005; if you are 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 mineC:\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.

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 the same.

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

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; I'll 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 you'll 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, we've 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; 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 the Solution Explorer; it'll 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 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.

Following steps is 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 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

Weather this is your very first report or you are a reporting junkie like me; we've 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, we've 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 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, Lineand 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, 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; we'll 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 surprise 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 we've 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've to type the expression which is columns fromdsProduct.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, it'll 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();         }     } }

I've 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 don't 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

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.