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”.
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".
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
Private connString As String = _
"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\myDatabase.mdf';_
Integrated Security=True;User Instance=True"
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Try
Call customizeReportViewer(Me.ReportViewer1)
Call AddReportViewerButton()
With Me.ReportViewer1.LocalReport
.ReportPath = Application.StartupPath & "\..\..\rptProducts.rdlc"
.DataSources.Clear()
Dim parameters(1) As ReportParameter
parameters(0) = New ReportParameter("ApplicationUser", "jpaulino")
parameters(1) = New ReportParameter("ApplicationLevel", "Administrator")
.SetParameters(parameters)
End With
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")
Dim rptDataSource As New ReportDataSource_
("dsReport_products", ds.Tables("products"))
Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
End Using
End Using
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
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.
AddHandler ReportViewer1.LocalReport.SubreportProcessing, _
AddressOf SubreportProcessingEvent
Finally, in the SubreportProcessingEvent, you just define the new datasource the same way.
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.
Sub customizeReportViewer(ByVal ctrl As Control)
For Each c As Control In ctrl.Controls
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
End Select
End If
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
End Select
Next
End If
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.
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.
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:
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
