Microsoft Reporting Services (Sub Reports, Charts, Parameters, Expression Editor, etc.): VB.NET, Visual Studio 2008, Microsoft Reporting Services, Local Reports

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build great reports for your applications. Reports may contain tabular, aggregated, and multidimensional data, plus they can include charts, and can be used in WinForm or ASP.NET.

EE1.png

73 KB
overview

The purpose of this “How To” article is to show the basic steps of creating a report, define the data source at runtime, work with parameters, include images, use the expression editor, and how to feed data to a sub report and chart. Finally it will also demonstrate some easy ways for you to customize the ReportViewer control.

Introduction

To start, I have created an easy SQL table with some data for this example. It's a list of electronic equipment like PDA, Desktop, etc. From that table, you need to create a DataSet with two different DataTables. Just name it “dsReport.xsd”.

EE2.png

25 KB
dataset

The first DataTable, “products”, contains a list of all of the products in the table. Then the second one, “groupTotal”, is an aggregated (Group By) query with the groups and the sum of quantities that will be used in a sub report, and in a chart.

After this, you need to add a report to your application. Just select it and give it an appropriate name. For this example, I have chosen "rptProducts".

EE3.png

52 KB
add new item

With the report created and opened, you have a new menu in the menu bar called “Report”. Select the Page Header and Page Footer to show these sections in the report. After this, just drag a Table from the toolbox into the body section of the report.

DataSource

Now it’s time to define the DataSource. From the Report menu, select Data Source. It will open a window where you can choose the data sources available in your application. Pick the created DataSet.

EE4.png

46 KB
report data sources

NOTE: The name of the “Report Data Sources”, can be renamed, and, will be used in code later as you will see!

After you define the data source for your report, you will see the available DataTables in the Data Sources explorer (normally available in the Solution Explorer window). Now, from the "Products" table, drag the columns to the Table object that has already been added to the body section.

EE5.png

6 KB
data source

The Table object works like an Excel Spreadsheet. You can merge cells, format cells, change the backgroundcolor, etc. This example uses a currency field. To show you how easy it is to format the cells, right-click in the field cell and select Properties. In the Properties window, go to the Format tab and define the Currency format for that cell.

EE6.png

67 KB
formatting

You can change the format for the other fields as well, like bold, colours, titles, etc., to obtain a professional look.

Images

To include images in your application, you can embed them, and then use them as a logo (for example). You can also get it from the database, if they are stored as Binary data.

To embed the images in your report, you just need to go to menu Report and select Embedded Images. In the new window, select the New Image button and browse to your image.

EE7.png

31 KB
embedded images

Close the window. Then, from the toolbox, add an Image control to the report. In the Image properties, select the image name from the combo box, available in the Value property.

Expression Editor

One of the nice features of the Visual Studio 2008 Microsoft Reporting Services Expression Editor is the available intellisense when you need to create some formulas.

In the Footer section, you can add two Textboxes. In one of them, you can include the page number and the total pages. For that, you already have some built-in formulas in the Global category.

EE8.png

52 KB
expression editor

As you can see in the following image, you can use VB formulas (most of them) in the Expression Editor. Intellisense helps a lot to prevent errors and remember formula syntax.

EE9.png

50 KB
expression editor with intellisense

Parameters

You can use parameters for a lot of different things. For this example, I will use only to pass information from the application to the report.

In the Report menu, select Report Parameters. Define two parameters: “ApplicationUser” and “ApplicationLevel” of type String.

EE10.png

36 KB
report parameters

Then, in the report, you can use the Expression Editor to define the parameters (they will be defined in the code) for the Textboxes. They will be available in the parameters category.

EE11.png

37 KB
set image with expression editor

Chart

You can use a lot of charts in the reports and they are very easy to customize and feed with data. Add a Chart control from the toolbox to the body section of the report. Double click on it and it will show the “Data Field” and “Category Field” areas. Drag the “Group” and “Totals” fields from the DataSource Explorer.

EE12.png

17 KB
chart

You can also do this in the Chart Properties window in the Data Tab. In this window, you can customize the series colours, legend, 3D effects, filter, axis, etc.

Using the Code

In the previous steps, you have used several data sources in the report. Now you will add the new data source, filtered or not, and that will be the data you will see.

Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms

Public Class Form1

    ' Connection string definition
    Private connString As String = _
            "Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\myDatabase.mdf';_
		Integrated Security=True;User Instance=True"

    '''  <summary>
    ''' Form load event
    '''  </summary>
    Private Sub Form1_Load(ByVal sender As Object, _
		ByVal e As System.EventArgs) Handles Me.Load

        Try

            ' Call the customization method
            Call customizeReportViewer(Me.ReportViewer1)

            ' Add a button to the ReportViewer
            Call AddReportViewerButton()

            With Me.ReportViewer1.LocalReport

                ' Report path
                .ReportPath = Application.StartupPath & "\..\..\rptProducts.rdlc"
                .DataSources.Clear()

                ' Set the parameters
                Dim parameters(1) As ReportParameter
                parameters(0) = New ReportParameter("ApplicationUser", "jpaulino")
                parameters(1) = New ReportParameter("ApplicationLevel", "Administrator")
                .SetParameters(parameters)

            End With

            ' ----------------------------------------------------
            ' Datasource for the main report (where price > 200)
            ' ----------------------------------------------------
            Dim SQL As String = "SELECT * FROM products WHERE price > @price"
            Using da As New SqlDataAdapter(SQL, connString)
                da.SelectCommand.Parameters.Add("@price", SqlDbType.Int).Value = 200

                Using ds As New DataSet
                    da.Fill(ds, "products")

                    ' You must use the same name as defined in the report 
		  ' Data Source Definition
                    Dim rptDataSource As New ReportDataSource_
			("dsReport_products", ds.Tables("products"))
                    Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)

                End Using

            End Using

            ' ----------------------------------------------------
            ' Datasource for the Chart
            ' ----------------------------------------------------
            Dim SQL_Chart As String = "SELECT [group], SUM(quantity) AS _
			Total FROM products GROUP BY [group]"
            Using da As New SqlDataAdapter(SQL_Chart, connString)
                Using ds As New DataSet
                    da.Fill(ds, "groupTotal")

                    Dim rptDataSource As New ReportDataSource("dsReport_groupTotal", _
				ds.Tables("groupTotal"))
                    Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)

                End Using
            End Using


            ' Refresh the report
            ReportViewer1.RefreshReport()


        Catch ex As Exception
            MessageBox.Show(ex.Message, My.Application.Info.Title, _
			MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:

Select all Open in new window

SubReports

You can use one or many subreports in your report. To do that, you need to fill the datasource for each of them. Since you cannot access a subreport's data source directly, you have to define a handler for SubreportProcessing, and then when the subreport is loading, you can retrieve the data from the database, and set its data source.

It may look hard, but it’s easy to accomplish!

Define the data source for the subreport and use a Table object, like in the main report. Add the fields to the Table object. In the main report, add a subreport object from the toolbox, and choose the Report Name property, to name the subreport name.

Then in the form load event of the main report, add a handler for the SubreportProcessing.

AddHandler ReportViewer1.LocalReport.SubreportProcessing, _
				AddressOf SubreportProcessingEvent

1:
2:

Select all Open in new window

Finally, in the SubreportProcessingEvent, you just define the new datasource the same way.

    '''  <summary>
    ''' When the subreport is being processed/loaded, fills the datasource
    '''  </summary>
    Sub SubreportProcessingEvent(ByVal sender As Object, _
			ByVal e As SubreportProcessingEventArgs)

        Try

            Dim SQL As String = "SELECT [group], SUM(quantity) AS _
				Total FROM products GROUP BY [group]"
            Using da As New SqlDataAdapter(SQL, connString)
                Using ds As New DataSet
                    da.Fill(ds, "groupTotal")
                    Dim rptDataSource As New ReportDataSource_
			("dsReport_groupTotal", ds.Tables("groupTotal"))
                    e.DataSources.Add(rptDataSource)
                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message, My.Application.Info.Title, _
			MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Select all Open in new window

ReportViewer Customization

The ReportViewer is a control that shows the Microsoft reports in your form or webpage. You can customize most of the controls to improve and customize it as required.

In this article, I will show two easy ways to customize it.

First, you can change the captions in the controls. You can also do other things like disable items, rename tooltips, etc. This is especially nice if you need to change the language of the control, since you only have English available.

For this, I have created this recursive sub that will loop through all of the controls in the ReportViewer and make some changes.

    '''  <summary>
    ''' Loops through all of ReportViewer controls and customize them
    '''  </summary>
    '''  <remarks></remarks>
    Sub customizeReportViewer(ByVal ctrl As Control)

        For Each c As Control In ctrl.Controls

            ' ----------------------------------------------------
            ' Check the text of the available labels
            ' ----------------------------------------------------
            If TypeOf c Is Label Then
                Dim lbl As Label = DirectCast(c, Label)
                Select Case lbl.Name
                    Case "LblGeneratingReport"
                        lbl.Text = "My report is loading now ... "
                    Case Else
                        ' You can add more customizations
                End Select
            End If

            ' ----------------------------------------------------
            ' Change the text in the ToolStrip to Portuguese
            ' ----------------------------------------------------
            If TypeOf c Is ToolStrip Then
                Dim ts As ToolStrip = DirectCast(c, ToolStrip)
                For Each item As ToolStripItem In ts.Items

                    Select Case item.Text
                        Case "Find"
                            item.Text = "Pesquisar"
                        Case "Next"
                            item.Text = "Próximo"
                        Case "of"
                            item.Text = "de"
                        Case Else
                            ' You can add more customizations
                    End Select
                Next
            End If

            ' If the control has child controls
            If c.HasChildren Then
                customizeReportViewer(c)
            End If

        Next

    End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:

Select all Open in new window

Finally, you just have to call it from the form load event, where you have the ReportViewer.

        ' Call the customization method
        Call customizeReportViewer(Me.ReportViewer1)

1:
2:

Select all Open in new window

The second customization shows how to include a new button in the ToolStrip. It will find the main toolstrip and add a new button on the right side.

    '''  <summary>
    ''' Find the main ToolStrip and add a new button on the right side
    '''  </summary>
    Sub AddReportViewerButton()

        Dim ts() As Control = Me.ReportViewer1.Controls.Find("toolStrip1", True)
        If ts IsNot Nothing Then
            Dim tsItem As ToolStrip = DirectCast(ts(0), ToolStrip)
            Dim item As New ToolStripButton
            item.Name = "newButton"
            item.Text = "New Button"
            item.BackColor = Color.Green
            item.ForeColor = Color.White
            item.Alignment = ToolStripItemAlignment.Right

            tsItem.Items.Add(item)

            AddHandler item.Click, AddressOf newButtonClick
        End If

    End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Select all Open in new window

When the button is pressed:

    '''  <summary>
    ''' Shows a messagebox when the new button of 
    ''' the Reportviewer is pressed
    '''  </summary>
    Sub newButtonClick()
        MessageBox.Show("This is my new button!")
    End Sub

1:
2:
3:
4:
5:
6:
7:

Select all Open in new window

Conclusion

This article shows the first steps, and the most important ones, for using Microsoft Reports, and how to handle some code. I hope that this helps you to start using it, and helps you understand how easy it could be to work with them.

I hope it's helpful to you!

Microsoft Reporting Services (Sub Reports, Charts, Parameters, Expression Editor, etc.)

Introduction

DataSource

Images

Expression Editor

Parameters

Chart

Using the Code

SubReports

ReportViewer Customization

Conclusion

VB.NET

Visual Studio 2008

Microsoft Reporting Services

Local Reports

Microsoft Visual Basic.Net

About The Author

More Articles From jpaulino

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Visual Basic.NET Experts