<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

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

Published on
13,688 Points
7,188 Views
5 Endorsements
Last Modified:
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.
 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”.

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

  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.

 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.

 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.

 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.

 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.

 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.

 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.

 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.

 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.

 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

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

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

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

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)

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

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

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!
5
Comment
Author:jpaulino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Other articles by this author
Suggested Courses
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month