Saturday, April 27, 2013

[ C# ] - Building RDLC report template and displaying it in ReportViewer Control

1. Introduction to RDLC Reports

In this RDLC tutorial, I will walk you through generating a report using the Microsoft supplied report template (.rdlc file). We will get the data from the “Titles” table of the Microsoft supplied “Pubs” database.

To generate the report on the database table, we are going to use following things:

  1. A “DataSource” that supplies the data from the database.
  2. A “Report Template” that defines the visible elements and its location. [.rdlc file]
  3. A “ReportViewer Control" supplied by the dot net framework.
  4. A “C-Sharp Form” that houses the ReportViewer control in it.

The interaction between the different aspects in generating the report is shown below:

Figure 1

In the "Report Template (.Rdlc file)" we define the report elements. This includes database fields, text labels, Images etc. The Report Template defines what you want to display and in the format you want to display. Once the report template is ready, it can be assigned to a "ReportViewer Control" that can be hosted by any container say for example a Dot Net Form.

2. About the RDLC Report Example

The finished report in the design view looks like the below screen:

Figure 2
From the designer you can see that we are going to display the data for the report in a “Table Control”. In the Page Header of the report we are going to display an image. Similarly in the page footer we are going to display the report title as well as the page number of the report. I will explain these in details when we move to specific section of the report.

The below screen shot shows how the report will look like when we run it:

Figure 3

Note that in the data part (number 4) of the report the rows are highlighted when the price is 20 or more. Let us start building this report and as you guess there is no piece of code that you need to write here. The above screen shot is actually a "C-sharp Form" with a report viewer control in it. When you want to display the report, you can call this from different dialog. Ok, Let us Begin. Start a new Visual C# Windows application.

3. Preparing the DataSource for RDLC Report

Once you started the project click on the Menu Option “Add New Data Source…” as shown in the below screen. This will open a wizard and use this wizard you can contact the database and bring the data to your project by picking a table, views, stored proc etc.

Figure 4

In this example, SQL server database on the local machine is connected and Titles table from the Pubs database is retrieved. The first option in the Data menu brings the Data Sources window that you can dock to any side of your development environment. The video shown is setting the Data Source to the Titles table of the Pubs database.

Video 1: Get Data Source for the Titles Table

4. Report Design – Display data in Table Control

Ok. The previous shown you setting up the data source. The next step is adding the report template to the project. You can add the report template to the project using the “Add new item” context menu option and then select the “Report” from the listed templates. Once the report design view is displayed, add the table control from the toolbox to the report. The report design view look like what is shown below:

Figure 5

The report control shows three sections in it. Header is useful to display the column titles. In the details section of table control the data retrieved from data base flows from start to end. Once the data portion is displayed in the detail part of the table control, in the footer you can usually place some summary information or totals etc. In our example we are going to place a static text called “End of Report”.

The table control by default displays three columns. However, you increase the number columns by clicking the “Insert Column to Left” or “Insert Column to Right” options based on the decision of how many column of data the report is willing to display. You can drag a table field from the Data Source Window and drop it to the Detail cell of the table control and doing so will automatically display the column title in the corresponding header cell. In the below video, I placed 4 columns from the Titles table to the Table control which is in the report template.

Video 2: Position the required database fields in the report

5. Show the initial report in the Report viewer

The report template is ready. The ReportViewer control will be assigned with the report that we designed (Initial Design) in the previous step. The below screen shows a ReportViewer control in the form:

Figure 6

After assigning the “.rdlc” file to the ReportViewer control, dock it to the entire form so that the user will get a better look at the report. That is all we need to show a report using the stuffs that comes with Visual studio installer itself. Assigning the report to the report view control and running the test run is shown in the below video with explanation:

Video 3: Assigning the Report Template to ReportViewer Control & Running the report

6. Report Design – Page Header and Page Footer

Section four of this article talked about Report Header and report footer. Now we will add the Page header and Page Footer for the report. Before we add the page header and footer we should understand how it would differ from the report header and footer. Look at the depiction that treats report spawns for three pages below:

Figure 7

From above picture you can note that Report Header starts before the report details section starts. Report Footer starts after the detail section ends. Also be aware that the details section takes responsibility of displaying the database records and computed fields. Page Header and Page Footer will be displayed on every page. OK! Now if I ask you where should I keep the page numbers when I am designing the report, you will promptly reply Page Footer.

To add Page header and page footer to the report template, click the square-shaped area in right corner of the report (focused in the below pic) and then right click to bring the short-cut menu. From this menu, you can choose the page header and page footer.

Figure 8

Once page header and page footer are added to the report template, add an image control to the report template’s page header section. This image will be populated using the embedded image. You can add embedded image from report menu as shown below:

Figure 9

Once the image is embedded in the project, the following properties should be set to the image control added in the Page header section of the report:

Figure 10

Similarly, Two text box controls added to the Page Footer section of the report. These text boxes are used to display report name and page numbers. To get report name and page numbers, the controls value property is set with an expression that computes Page Number and displays report name. You can see setting the Page Header and Footer from the below-shown video:

Video 4: Setting the Page Header and Page Footer

7. Report Design - Highlight Costly Books

To highlight the costly books, select the details row and set the background property and color property through an expression. These expressions are shown below:

= iif(Fields!price.Value >= 20, "Black", "White"): For Back ground color
= iif(Fields!price.Value >= 20, "White", "Black"): For Color property

Figure 3. shows you the highlighted rows. You can build the above-shown expression  using the expression builder. Highlighting the costlier books by building the expression is shown in the below-given video.

Video 5: Building the expression from DB fields to highlight a row

Source Code : Download

1 comment:

  1. Hi Sivaraman, very good job,
    If I want to add a column to the report to display the record number, how to do this?


Leave your comment(s) here.

Like this site? Tell it to your Firend :)