Dynamic Data Grouping Using MS Reporting Services

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. (Everyone likes a pay raise, right?) In this article, I will guide you step by step 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 share of a pay raise? Why not! Who knows, your neatly done report can just do that.

Prior to this article, I wrote three others that addressed different issues related to the reporting services. However, all of them were targeted towards the intermediate-advanced level audience. From all the feedback I received, one was common: Quite a few of you asked for an article that was specifically geared towards the novice-beginner level.

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

Note: Although I call this article "101," my intention is to adopt the applied approach rather then discuss each and every topic associated with reporting services. I touch on most common aspects of report designing with most commonly used controls. I would strongly encourage you to please go through the MSDN documentation for more detailed information.

Roll up Your Sleeves—It's Reporting Time

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

Figure 1

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-dollar question: How do you start? What is the first step?

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

Step 1: Create a Windows Application Project

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

  • Select File menu -> New -> Project.
  • Choose C# from the 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? Just As you need a DVD player to play a DVD, the same goes with the reports. You need a report viewer to have the report preview done.

For all those who are brand new to report writing, I would say that 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, and so forth). You also can make 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 the name of reportViewer1. I always wanted to name reportViewer1 to rpvAbraKaDabra; I 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 the form for report display purposes.

After Steps 1 and 2, your project should look like Figure 2.

Figure 2

Step 3: Add DataSet to the Project

Hurray! You 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 the data source to be processed and ready to be output on the Smart Client interface.

The following step is required to have DataSet added to project:

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

Add a DataTable to your newly created DataSet. DataTable is essential to load the reporting data; you will use the information from DataSet/DataTable when designing the report.

The following step is 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 the header and change the name to dtProductList. Please see Figure 3.

Figure 3

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 columns to DataTable.

Figure 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 columns, by default it is the string data type. Please go to the properties windows after selecting a column to change it from String to Integer or Double.

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

Figure 5

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

Step 4: Add a Report to the Project

All right. So far, you created the project, and added Report Viewer and DataSet. Now, it is the time to deal with star of the show! It's time to create that neat report.

The following steps are required to have Report (rptProductList.rdlc):

  • Select Add -> New Item -> Report from Solution Explorer. Change its 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 be similar to Figure 6. When a report is added to project, it is ready to use the DataSet for designing.

Figure 6

Whether this is your very first report or you are a reporting junkie like me, you have to deal with the most basic building blocks of report writing, which are: Header, Body, and Footer.

Typically, reports are designed with a specific page size and layout in mind. Your 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 attempt. As you can see in Figure 1, you have the Report Name and Report Date in the header section. The body section has the product list information together with summary totals; the footer carries the Page Numbers.

Start working on the Page Header:

When a new report is added to the project, by default, all you will 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 header and body section. See Figure 7; I have reduced the height of body and increased the height of the header.

Figure 7

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

Header Section

Start designing the header. Drag two TextBoxes and drop them on the header section. Texbox 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 the associated properties. You will designate one TextBox to the report title and another one to show the current date. You can directly type static text into the TextBox control by selecting it and typing inside.

Please change following properties of Title TextBox:

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

Please change following properties of Date TextBox:

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

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

You can specify desired names to all objects in report; I chose to stay with default name for most of the controls. However, for demo purposes, I did specify "txtTitle" to Title TextBox.

Please refer to Figure 8; your finished design for header should look relatively the same.

Figure 8

Dynamic Data Grouping Using MS Reporting Services

Body Section

The Body section, also referred to as the details section, is by far the most important part of the report. As you can see, when you added the report to the project, the body section was added automatically. All you have to do is start putting controls on it.

Traditionally, the Body section is used to display details (in the 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, a report is designed with the intention to have one physical page (Letter/A4 and so forth) 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 controls on Body section, you will use Table control for your example. All three can repeat information; Matrix goes a step further and even produces Pivot output.

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; this means you can either type static text on it or specify a dynamic expression.

Before you start designing the Body section, add two more columns (remember that you have a total of five columns in the report). Adding columns is easy; please do the following to get new columns added to the report:

  • Select Table Control inside Body section.
  • Click on the right-most column header (I assume you are adding new columns to the 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 the column based on length of data it will hold.

[rsWin09.JPG]

Figure 9

I am sure the majority of you have used Excel or something similar; think of the same for the Table control as a mini worksheet. You can apply borders, change the font of an individual cell, and so on. So, all you have to do is to think of the desired formatting theme and start applying it.

Starting with the first column, please click on an 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"

Continue to do so the same for the Detail section. Here, one thing to know is, instead of text you have to type the expression which is columns from dsProduct.dtProductInfo. You either can type the expression or simply drag and drop the column from Data Sources Toolbar (see Figure 7 on the left side).

In case you decide to type it out, starting with 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 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 the column header automatically if the column header is empty.

Finally, add a summary total in the footer section of the Table control. Please make sure to select a footer cell on columns 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 am using a built-in function SUM() to find out total stock value of all the products listed in report.

Footer Section

Before you start writing some cool C# code to bring your report alive, finish the report footer section. As you have added a report header earlier, similarly you have to right-click on the open report designer surface and select Page Footer (see Figure 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 have used PageNumber and TotalPages. Both are Global variables maintained by the reporting engine.

Tip: Make sure all expressions you type must start with "=" in front of them.

Please make sure your report looks like Figure 10. As you can see, I have introduced some color and right alignment to numeric data. 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 it yourself.

[rsWin10.JPG]

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 fields in DataSet can be accessed with the "Fields!" keyword.

[rsWin11.JPG]

Figure 11

Step 5: Write Some C# Code to Bring Life to Your Report

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

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

using System.Data.SqlClient;
using Microsoft.Reporting.WinForms;

Make sure the Form1_Load event has the 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 the select query. I wanted to limit the output so that I can show you the summary total in Figure 1.

Tip: The Name property of ReportDataSource object should always be "DataSet_DataTable".

Conclusion

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 a lot to learn. It is the reader, like you, who has always helped me improve my writing.

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

Thank you for reading; I sincerely hope this article will help you to know reporting services better through my applied approach.



About the Author

Asif Sayed

Asif Sayed has over fifteen years experience in software development and business process architecture. He works as a senior systems analyst with a leading energy retailer in North America. He also teaches .NET technologies at Centennial College in Scarborough, Ontario. His professional journey started from India and currently he is based in Toronto Canada.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Learn How A Global Entertainment Company Saw a 448% ROI Every business today uses software to manage systems, deliver products, and empower employees to do their jobs. But software inevitably breaks, and when it does, businesses lose money -- in the form of dissatisfied customers, missed SLAs or lost productivity. PagerDuty, an operations performance platform, solves this problem by helping operations engineers and developers more effectively manage and resolve incidents across a company's global operations. …

  • Live Event Date: December 18, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this upcoming webcast …

Most Popular Programming Stories

More for Developers

RSS Feeds