Click here to Skip to main content
Click here to Skip to main content

Applied MS Reporting Services 101 using Smart Client

, 24 Aug 2006
Rate this:
Please Sign up or sign in to vote.
An applied approach to get you started with MS Reporting Services using Smart Client host.

Sample Image - rsimage1.jpg

Image: 1

 

Introduction

 

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

 

So, are you ready to get your share of 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 feedbacks 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 the basic understanding of the Visual Studio 2005 IDE and comfortable with writing code using C#.  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 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 on most common aspect of report designing with most commonly used controls.  I would strongly encourage you to please go through MSDN documentation for more detailed information.

 

*Updated to add Access Database interface.

 

Let’s roll up our sleeves, it’s reporting time

 

Please take a look at Image 1.  How complex is that report?  How much time do you think it will take to create such a report?  Well, as for complexity, it is a simple report extracted out of source NorthWind->Products (Sql Server 2000) and lists all the products information with summary totals.

 

About time, obviously, it should not take you hours to do it.  About R&D and trial & error time, I leave that to you; dig down deep; the deeper you will explore, the better the treasure you will find.

 

Here it is, the million $ question: How to start? What is going to be the first step?

 

Often, it is very easy to find out what should be the first step.  Have you seen a house built before the foundation? No! So, have I given you a hint here? Sure, we must first develop the Smart Client to host our report.

 

Step 1: Create Windows Application Project

 

Please do the following to create a Windows Application (Smart Client) project:

  • Select File menu -> New -> Project.
  • Choose C# from Project Types pane.
  • In the Templates pane, choose Windows Application for Visual C# projects.

In the Name box, name the project something unique (I named the attached project code – rsWin101) to indicate the application's purpose. In the Location box, enter the directory in which you want to save your project, or click the Browse button to navigate to it.  Once you are done, you will find Form1 added to the project and you can start working on it using Forms Designer.

 

Please update following properties of Form1:

 

Form1.Text = “MS Reporting Services 101 with Smart Client”

Form1.Size = 750, 300

 

Feel free to change any other property of Form1 as per your requirement.

 

Step 2: Add Report Viewer to the Form

 

So, what is report viewer? As we need the DVD player to play a DVD; same goes with the reports, we need a report viewer to have the report preview done.

 

For all those who are brand 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 Report Viewer Control on Form1:

  • Drag ToolBox -> Data -> ReportViewer and drop it on Form1.  This step will create a new instance of ReportViewer with name reportViewer1.  I always wanted to name reportViewer1 to rpvAbraKaDabra, hence, wont let this chance pass by now.  As I picked rpvAbraKaDabra, feel free to pick yours, let those imagination horses run wild!
  • By setting reportViewer1.Dock = Fill, report viewer will fill the entire surface of form for report display purpose.

After step 1 and step 2, your project should look as per Image 2.

 

Sample screenshot

Image: 2

 

Step 3: Add DataSet to the Project

 

Hurray!  We are done with the foundation.  It’s time to put walls around the foundation; eventually these walls will hold the doors and windows of your home.  DataSet is just that for Report Viewer, it holds and provides the raw data from data source to be processed and ready to be outputted on the Smart Client interface.

 

Following step is required to have DataSet added to project:

  • Select Add -> New Item -> DataSet from Solution Explorer.  Change name from DataSet1 to dsProduct and click on Add button to complete the action.

Let’s add a DataTable to our newly created DataSet.  DataTable is essential to load the reporting data; we will use the information from DataSet/DataTable while designing the report.

 

Following step are required to have DataTable added to DataSet(dsProduct):

  • Double click on dsProduct from Solution Explorer; it will open the designer view. Right-click on the designer surface and Add -> DataTable.  Please click on header and change the name to dtProductList.  Please see Image 3.

Sample screenshot

Image: 3

 

Let’s start adding columns to DataTable(dtProductList). Your designer screen should look like Image 4.  Right-click on dtProductList and select Add -> Column to start adding columns to DataTable.

 

Sample screenshot

Image: 4

 

Please repeat the action for following columns:

  • ProductName (String)
  • QuantityPerUnit (String)
  • UnitPrice (Double)
  • UnitsInStock (Double)
  • UnitValue (Double) – A calculated field based on UnitsInStock * UnitPrice

As you are adding columns, by default it is string data type.  Please go to properties windows after selecting column to change it from String to Integer or Double.

 

Please see image 5.  Your DataTable should look the same.  Also, you can see the properties window to change the data type.

 

Sample screenshot

Image: 5

 

Have you heard of “Typed DataSet”? If not, then we have just created a Typed DataSet here.  Please consult online help to know more about Typed DataSet.

 

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 Image 6.  When a report is added to project, it is ready to use the DataSet for designing.

 

Sample screenshot

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 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 new report is added to project, by default, all you will see in report designer is the body section.  Right click on 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 body section.  See Image 7, I have reduced the height of body and increased the height of the header.

 

Sample screenshot

Image: 7

 

While inside the report designer, if you explore the Toolbox, you will see variety of controls which can be used to design 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 available controls.

 

Header Section

 

Let’s start designing the header.  We will start by dragging two TextBox and dropping on header section.  Texbox can show both static and dynamic data.  Line control is used to separate header from body section.

 

After dropping controls over 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 TextBox control by selecting it and start typing inside.

 

Please change following properties of Title TextBox:

 

Value = “Product List”

Color = Purple (you like purpule too for title right?)

 

Please change following properties of Date TextBox:

 

Value = ="Run Data: " & Today

Color = Purple (you like purpule 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 desired names to all objects in report; I choose to stay with 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.

 

Sample screenshot

Image: 8

 

Body Section

 

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; body section was added for us automatically.  All we have 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 information.  Body section can expand as per the growth of 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 information.

 

Out of Table, Matrix and List, the three most commonly used control on 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 Table control on body section of 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 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 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 will hold.

 

Sample screenshot

Image: 9

 

I am sure majority of us have used Excel or something similar; think of same for Table control as 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 if you decide to type it out, starting with 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 Table control.  Please make sure to select footer cell on column 4 and 5 inside 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 report designer surface and select Page Footer (see Image 7). 

 

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

 

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

 

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

 

Tip: Make sure all expression you type must start with “=” in front of it.

 

Please make sure your report looks like Image 10.  As you can see I have introduced some color and right alignment to numeric data etc.  Feel free to try out all the different formatting options, just think of Table control as mini spreadsheet with columns and rows and now you know all the formatting you can try on them.

 

Sample screenshot

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 “Fields!” keyword.

 

Sample screenshot

Image: 11

 

Step 5: Let’s write some C# code to bring life to our report

 

Phew… I hope you guys are not exhausted already.  Hang in there; we are on last step now.  It’s like we have waited for that long nine months and the time has come to witness the miracle of birth.

 

From solution explorer, select Form1.  Right click on surface of form and select View Code. 

 

using System.Data.SqlClient;

using Microsoft.Reporting.WinForms;

 

Make sure the Form1_Load event has following code:

 

private void Form1_Load(object sender, EventArgs e)

{

    //declare connection string

    string cnString = @"(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

        rpvAbraKaDabra.LocalReport.ReportEmbeddedResource =

        "rsWin101.rptProductList.rdlc";

 

        //prepare report data source

        ReportDataSource rds = new ReportDataSource();

        rds.Name = "dsProduct_dtProductList";

        rds.Value = dsReport.Tables[0];

        rpvAbraKaDabra.LocalReport.DataSources.Add(rds);

 

        //load report viewer

        rpvAbraKaDabra.RefreshReport();

    }

    catch (Exception ex)

    {

        //display generic error message back to user

        MessageBox.Show(ex.Message);

    }

    finally

    {

        //check if connection is still open then attempt to close it

        if (conReport.State == ConnectionState.Open)

        {

            conReport.Close();

        }

    }

}

 

You might be wondering why I have used “TOP 5” for select query; the reason is, I wanted to limit the output so that I can show you summary total in Image 1.

 

Tip: Name property of ReportDataSource object should be always “DataSet_DataTable”. 

 

Can I use Access instead of SQL Server 2000?

 

Yes, you can use the Access database.  Please make sure the following changes are applied to the above mentioned code to get the data reported from NorthWind Access Database.

 

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

 

using System.Data.OleDb;

 

private void Form1_Load(object sender, EventArgs e)

{

    //declare connection string

    string cnString = @"Provider=Microsoft.Jet.OLEDB.4.0;

      Data Source=c:\nwind.mdb;User Id=admin;Password=;";

 

    //declare Connection, command and other related objects

    OleDbConnection conReport = new OleDbConnection(cnString);

    OleDbCommand cmdReport = new OleDbCommand();

    OleDbDataReader 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

        rpvAbraKaDabra.LocalReport.ReportEmbeddedResource =

        "rsWin101.rptProductList.rdlc";

 

        //prepare report data source

        ReportDataSource rds = new ReportDataSource();

        rds.Name = "dsProduct_dtProductList";

        rds.Value = dsReport.Tables[0];

        rpvAbraKaDabra.LocalReport.DataSources.Add(rds);

 

        //load report viewer

        rpvAbraKaDabra.RefreshReport();

    }

    catch (Exception ex)

    {

        //display generic error message back to user

        MessageBox.Show(ex.Message);

    }

    finally

    {

        //check if connection is still open then attempt to close it

        if (conReport.State == ConnectionState.Open)

        {

            conReport.Close();

        }

    }

}

 

Conclusion

 

Although, I tried to keep the language of this article as simple as possible; however, please feel free to get back to me if you need any further clarification.  I consider myself a budding author; I have to learn a lot; it is the reader like you, who has always helped me to improve my writing.

 

I am looking forward to receive any comments/suggestion you have for me.

 

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.

class=Code style="MARGIN: 0in 0in 0pt">        drReport.Close();

        conReport.Close();

 

        //provide local report information to viewer

        rpvAbraKaDabra.LocalReport.ReportEmbeddedResource =

        "rsWin101.rptProductList.rdlc";

 

        //prepare report data source

        ReportDataSource rds = new ReportDataSource();

        rds.Name = "dsProduct_dtProductList";

        rds.Value = dsReport.Tables[0];

        rpvAbraKaDabra.LocalReport.DataSources.Add(rds);

 

        //load report viewer

        rpvAbraKaDabra.RefreshReport();

    }

    catch (Exception ex)

    {

        //display generic error message back to user

        MessageBox.Show(ex.Message);

    }

    finally

    {

        //check if connection is still open then attempt to close it

        if (conReport.State == ConnectionState.Open)

        {

            conReport.Close();

        }

    }

}

 

Conclusion

 

Although, I tried to keep the language of this article as simple as possible; however, please feel free to get back to me if you need any further clarification.  I consider myself a budding author; I have to learn a lot; it is the reader like you, who has always helped me to improve my writing.

 

I am looking forward to receive any comments/suggestion you have for me.

 

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Asif Sayed
Architect FeatherSoft Inc.
Canada Canada
Asif Sayed has over fifteen years experience in software development and business process architecture. He has a consulting firm in Toronto, Canada. His firm provides IT solutions to all sizes of industries. He also teaches .NET technologies at Centennial College in Scarborough, Ontario. Recently he has become member of team as a subject matter experts with Microsoft's Learning Division. He has a book published by Apress with the Title "Client-Side Reporting with Visual Studio in C#".
 
My blog: http://www.dotnetsme.com
My Website: http://www.feathersoft.ca
Follow on   Twitter

Comments and Discussions

 
GeneralRecords per Page PinmemberKen Beale18-Apr-09 3:02 
GeneralPrinting Problem Pinmemberrmsohaila9-Nov-08 20:56 
GeneralRe: Printing Problem Pinmembercojeezy30-Jan-09 13:40 
QuestionHOW TO dynamically set width and height of report file(.rlc) PinmemberMember 329057321-Oct-08 4:31 
GeneralThanks Pinmemberpankaj motiyar19-May-08 21:39 
GeneralRDLC report definition not specified PinmemberS3S9-Apr-08 3:52 
GeneralRe: RDLC report definition not specified PinmemberAsif Sayed9-Apr-08 5:17 
GeneralRe: RDLC report definition not specified PinmemberPepinfarb3-Dec-09 6:21 
Generalcreate Dataset programatically PinmemberS3S8-Apr-08 13:03 
GeneralTwo Matricies on one report in PDF Pinmemberajbarton197216-Nov-07 10:49 
GeneralGrand total in matrix reporting services PinmemberLily Wibisana6-Nov-07 8:32 
GeneralAdding an image to the report file. PinmemberSugandhJain11-Sep-07 20:36 
Generalgrand total Pinmembertts27-Jun-07 11:31 
GeneralRe: grand total PinmemberAsif Sayed28-Jun-07 4:47 
GeneralRe: grand total Pinmembertts28-Jun-07 8:03 
GeneralRe: grand total Pinmembertts28-Jun-07 8:35 
Generalprint problem Pinmembervsnewcomer18-Jun-07 9:55 
GeneralCreating dynamic charts in two columns Pinmembermanoharyarli9-May-07 7:22 
GeneralRe: Creating dynamic charts in two columns PinmemberAsif Sayed10-May-07 4:24 
QuestionHow to get value from report Pinmemberallenman187-May-07 15:47 
Generalimage for background and 100%heigh Pinmemberhamid_m23-Mar-07 2:48 
Generalnice job!! PinmemberHari Om Prakash Sharma14-Mar-07 1:58 
GeneralRe: nice job!! PinmemberAsif Sayed22-Mar-07 10:17 
GeneralExcellent article Pinmemberrnallam3-Jan-07 12:54 
GeneralRe: Excellent article PinmemberAsif Sayed22-Mar-07 10:17 
GeneralGood Article (I have one Question) PinmemberSalan Al-Ani7-Sep-06 4:55 
QuestionNice demo, but Smart Client?? Pinmemberzarkon31-Aug-06 13:25 
AnswerRe: Nice demo, but Smart Client?? PinmemberAsif Sayed31-Aug-06 16:17 
GeneralRe: Nice demo, but Smart Client?? Pinmemberjesuscheung2-Apr-08 22:40 
GeneralHI Pinmemberemman101725-Aug-06 16:26 
GeneralRe: HI PinmemberAsif Sayed25-Aug-06 16:58 
GeneralRe: HI Pinmemberemman101725-Aug-06 17:18 
GeneralRe: HI Pinmembermsali11-Mar-07 5:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 24 Aug 2006
Article Copyright 2006 by Asif Sayed
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid