Introduction

I still remember it was a neatly done report that got me my first pay raise.  Ever since, I am very passionate about report writing (every one likes a pay raise right?).  In this article, I will guide you through step by step on how to create a simple report using MS Reporting Services 2005; and host it with a Smart Client application.

So, are you ready to get your pay raise? Why not! Who knows, your neatly done report can just do that.

Prior to this article, I wrote three others, which were addressing different issues related to the reporting services.  However, all of them were targeted towards the intermediate-advance level audience.  From all the feedback I received, one was common, quite a few of you asked for an article which will be specially geared towards the novice-beginner level.

I assume the reader has a basic understanding of Visual Studio 2005 IDE and is comfortable with writing code using C#.  You don’t have to know MS Reporting Services to understand this article; although, any pervious experience with writing a report would help.

Although, I am calling this article 101, my intention is to adopt the applied approach rather then discussing each and every topic associated with reporting services.  I am touching the most common aspects of report design and I use the  most common controls.  I would strongly encourage you to go through the MSDN documentation for more detailed information.

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 the Add button to complete the action.
• Typically, after the add action is finished your screen should be similar to Image 6.  When a report is added to 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 have to deal with the most basic building blocks of report writing, which is: Header, Body and Footer.

Typically, reports are designed with a specific page size and layout in mind.  Our report is Letter size and Portrait layout.  You can explore various properties attached to 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 paper, before you start the design attempt.  As you can see in Image 1, we have Report Name and Report Date in header section.  The Body section has the product list information together with summary totals; and footer carries the Page Numbers.

Let's start working on Page Header:

When a new report is added to project, by default, all you will see in the report designer is the Body section.  Right click on the report designer surface anywhere other then on the Body and select Page Header.  This will add the header to the report.  Feel free to adjust the height of the header and the Body section.  See Image 7, I have reduced the height of the Body and increased the height of the header.

Image: 7

While inside the report designer, you will see variety of controls which can be used to design report when you  explore the Toolbox.  For our example, we will use, the 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 will start by dragging two Textboxes and dropping them on the header section.  The Textbox can show both static and dynamic data.  The 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 associated properties.  We will designate one TextBox to report title and another one to show current date. We can directly type static text into the TextBox control by selecting it and by typing inside it.

Please change following properties of Title TextBox:

Value = "Product List"
Color = Purple (you like purpose too for title right?)

Please change following properties of Date TextBox:

Value = ="Run Data: " & Today
Color = Purple (you like purpose too for title right?)

Please note Value property for Date TextBox starts with a "=" sign.  This is not a simple static text, instead it is an expression.  This expression is a result of string "Run Date" and VB.NET script keyword today (to get current system date).

You can specify the desired names to all 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 Image 8; your finished design for header should look relatively same.

Image: 8

The Body Section

The Body section, also referred as details section, is by far the most 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 then one row of information.  The Body section can expand as per the growth of reported data.  Often the report is designed with the intention to have one physical page (Letter/A4 etc.) output; in this case the Body section still can be used to display information.

Out of Table, Matrix and List, the three most commonly used control on the Body section; we will use Table control for our example.  All three can repeat information; Matrix goes a step further and even produces 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 also 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 Table is like TextBox, which means you can either type 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 total of five columns in the report). Adding columns is easy; please do the following to get new columns added to report:

• Select the Table Control inside The Body section
• Click on the right most column header (I assume 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 that of the one on Image 9.  Feel free to adjust the width of the column base on the length of data it will hold.

Image: 9

I am sure the majority of us have used Excel or something similar;  the Table control resembles a mini worksheet.  We can apply borders, change font of individual cell etc. etc.  So, all you have to do is to think of desired formatting theme and start applying it.

Starting with first column to the last one, please click on 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 Detail section, here one thing to know is, instead of text we have 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 you decide to type it out, starting with the first column to the last one, please click on 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 Units in Stock and Unit Value.

Tip: If you drag and drop the column to detail section of Table control, it will try to add column header automatically, if column header is empty.

Finally, let's add summary total in 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 following text:

Cell 4: "Total Value:"
Cell 5: "=SUM(Fields!UnitsInStock.Value * Fields!UnitPrice.Value)"

Please check the expression in Cell 5; I am using a built-in function SUM() to find out total stock value of 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 have added report header earlier, similarly we have to right click on open the report designer surface and select Page Footer (see Image 7). 

Drag and drop a Line and Textbox control on the Footer section.  Please type the following expression inside TextBox:

Value:  ="Page: " & Globals!PageNumber & "/" & Globals!TotalPages

As you can see I have used the PageNumber and TotalPages, both are Global variables maintained by the reporting engine.

Please make sure your report looks like Image 10.  As you can see I have introduced some color and aligned the numeric data on the right side along with some other markup.  Feel free to try out all the different formatting options, just think of the Table control as a mini spreadsheet with columns and rows. Now you know all the formatting you can try to apply it.

Image: 10

Expression Builder

Expression builder is a very powerful feature of Reporting Services.  As you can see in Image 11, Stock Value is calculated with the help of SUM function.  All fields in DataSet can be access with the "Fields!" keyword.

Image: 11