Click here to Skip to main content
15,861,172 members
Articles / Web Development / ASP.NET

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

Rate me:
Please Sign up or sign in to vote.
4.88/5 (55 votes)
1 Jun 2009CPOL6 min read 224.2K   7.6K   167   27
This article shows 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 subreport and charts. Finally it will demonstrate some easy customizations to ReportViewer.
ReportViewerDemo

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.

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”.

MicrosoftReports/rpt002.png

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".

MicrosoftReports/rpt003.png

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.

MicrosoftReports/rpt004.png

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.

MicrosoftReports/rpt005.png

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.

MicrosoftReports/rpt006.png

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.

MicrosoftReports/rpt007.png

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.

MicrosoftReports/rpt008.png

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.

MicrosoftReports/rpt009.png

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.

MicrosoftReports/rpt010.png

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.

MicrosoftReports/rpt011.png

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.

MicrosoftReports/rpt012.png

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.

VB.NET
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

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.

VB.NET
AddHandler ReportViewer1.LocalReport.SubreportProcessing, _
				AddressOf SubreportProcessingEvent

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

VB.NET
'''  <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

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.

VB.NET
'''  <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

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

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

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.

VB.NET
'''  <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

When the button is pressed:

VB.NET
'''  <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

Points of Interest

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!

History

  • 1st June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Portugal Portugal
Jorge Paulino
Microsoft Visual Basic MVP
Portuguese Software Developer
VB.NET, ASP.NET, VBA, SQL
http://vbtuga.blogspot.com/

http://twitter.com/vbtuga

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mathiudi2-Aug-13 6:40
Mathiudi2-Aug-13 6:40 
GeneralAmazing! Pin
travistanley12-Dec-12 21:38
travistanley12-Dec-12 21:38 
GeneralMy vote of 5 Pin
travistanley12-Dec-12 21:38
travistanley12-Dec-12 21:38 
GeneralMy vote of 5 Pin
antonache_radu@yahoo.com1-May-12 19:44
antonache_radu@yahoo.com1-May-12 19:44 
GeneralMy vote of 5 Pin
LindaFitriani14-Jan-12 23:50
LindaFitriani14-Jan-12 23:50 
QuestionChange the text value at runtime Pin
d_wolfman232-Oct-11 14:18
d_wolfman232-Oct-11 14:18 
AnswerRe: Change the text value at runtime Pin
jpaulino2-Oct-11 20:46
jpaulino2-Oct-11 20:46 
QuestionHow to create a chart in a report from a business object data source? Pin
jonijoni4-Feb-11 4:14
jonijoni4-Feb-11 4:14 
QuestionCan we have some asp.net example please? Pin
Gururaj Kamath20-Aug-10 6:43
Gururaj Kamath20-Aug-10 6:43 
QuestionSub reports is it possible within a table? Pin
fedepaka12-Mar-10 6:58
fedepaka12-Mar-10 6:58 
AnswerRe: Sub reports is it possible within a table? Pin
jpaulino12-Apr-10 11:49
jpaulino12-Apr-10 11:49 
GeneralHelp, problem with conversion Pin
Instead srl16-Feb-10 0:18
Instead srl16-Feb-10 0:18 
GeneralRe: Help, problem with conversion Pin
jpaulino12-Apr-10 11:46
jpaulino12-Apr-10 11:46 
GeneralSwitch subreport by code Pin
armax7515-Dec-09 1:40
professionalarmax7515-Dec-09 1:40 
GeneralRe: Switch subreport by code Pin
jpaulino12-Apr-10 11:50
jpaulino12-Apr-10 11:50 
GeneralGreat article Pin
Marcelo Ricardo de Oliveira8-Dec-09 7:32
mvaMarcelo Ricardo de Oliveira8-Dec-09 7:32 
GeneralRe: Great article Pin
jpaulino9-Dec-09 10:05
jpaulino9-Dec-09 10:05 
GeneralRe: Great article Pin
Brisingr Aerowing18-May-12 2:34
professionalBrisingr Aerowing18-May-12 2:34 
GeneralVery Nice article Pin
BhruguPatel16-Sep-09 19:09
BhruguPatel16-Sep-09 19:09 
GeneralNice Work| Pin
fLaSh - Carlos.DF15-Jul-09 12:50
fLaSh - Carlos.DF15-Jul-09 12:50 
GeneralCongratulations Pin
jose.lopes16-Jun-09 13:00
jose.lopes16-Jun-09 13:00 
GeneralThanks Pin
marc van laer14-Jun-09 23:43
marc van laer14-Jun-09 23:43 
GeneralVery good Pin
justwantcode11-Jun-09 23:50
justwantcode11-Jun-09 23:50 
GeneralRe: Very good Pin
jpaulino12-Jun-09 0:24
jpaulino12-Jun-09 0:24 
GeneralNIce Article. Pin
thompsons6-Jun-09 10:42
thompsons6-Jun-09 10:42 

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

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