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.
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.
Tim for 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, give the project a unique name (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, it also facilitates you to generate the information in the various popular formats (PDF, excel etc.). You can also take a hard copy (a printed version) 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 reportViewer1. I always wanted to name reportViewer1 to rpvAbraKaDabra, hence, I won't let this chance pass. 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.
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.
The following steps are required to add the DataTable to the 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.
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.
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.
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.