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

Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Published:
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
8,711 Views
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.