Report Viewer Control

I thought it would be useful to blog something about the ReportViewer control which was new to VS 2008. This control takes rdlc report definitions, client based versions of the rdl report definitions of SQL Server Reporting Services. Designing the reports is largely the same as in SSRS but there are some gnarly issues when using the ReportViewer control which have taken some experimentation and Googling around to overcome. The following relates to ReportViewer in VB win forms.Mostly it applies to asp.net web forms as well.

What is the ReportViewer control?

Well it is just that, a control, and it has to be placed in a form. It is in effect a container for a report. The wizard allows you to pick a report and you can change it later via the properties box for the control but there are issues with data sources after that, which I'll cover later.
If you want to specify parameter values, you have to add those to your form as text boxes or combo lists and pass through the values through to the report. That's where the fun starts.

I've found it's often easier to view the report definition as xml and edit this directly, especially if you need to change the sql definition for the data source, for instance if you've changed the data type of a field.
Filtering

There are two ways of doing this: by passing the filter as a parameter through to the report or by filtering the report dataset loaded with the form.
I never got the first method to work. I might revisit the problem now I have a greater understanding of the ReportViewer control. The control is quite unforgiving to coding errors and I've found that most problems are related to data in one way or another.
In a report which shows overdue actions for a case worker, I wanted to be able to filter the report by Case Worker. The easiest way I found of doing this was to select the case worker from a list on my form and then refill the table adaptor and refresh the report viewer.

Dim awid As Integer = cboCaseWorker.SelectedItem

Me.taCaseworkerActivityReport.Fill(Me.dsReport.spCaseworkerActivityReport, Me.dtStartDate.Value, Me.dtEndDate.Value, awid)

Me.ReportViewer1.RefreshReport()

where, dsReport is my dataset, spCaseworkerActivityReport is a sproc and dtStartDate, dtEndDate are date picker controls on the form, used to specify the period.

Passing parameters

In SSRS, this is pretty easy, you define the parameter in the report, including the fact that its value will be selected from a list and that the data for the list is from a data source, also defined within the report. When you display the report in the browser, SSRS automatically provides the drop down list, the user makes a selection and the selected value is passed to the report.
ReportViewer doesn't do any of this for you. Instead you have to add your own text or combo boxes to the form and pass the selected parameter to ReportViewer with code.
You start by defining the parameters in the report in the usual way. Right click on top left corner of the report and select Report Parameters

In this case I am passing in a single parameter, Period, which is the period for the report, as a text string. This will be displayed in a text box on the report header. Note setting the prompt is largely pointless here - it is never displayed.

In the form's Load event, or a function called by it, add the following code


Dim p As ReportParameter
 p = New ReportParameter("", ""))

ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p})
Me.ReportViewer1.RefreshReport()

where in this case is  and is my formatted text string (note both arguments are strings, even if the parameter data type isn't)

I never successfully managed to pass through more than one parameter to a report, e.g.

Dim p1 As ReportParameter
Dim p2 As ReportParameter

p1 = New ReportParameter("Param1", "1"))
p2 = New ReportParameter("Param2", "2"))


ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p1,p2})

Sub-reports
Sub-reports are just reports dropped into a sub-report control placed in the parent report. The issue with handling sub-reports in the ReportViewer control is in setting the data source for the sub-report.

If you don't set the data source for the sub-report, when you run the report you will see the message

Error: Subreport could not be shown

Actually, it is necessary to set the data source for each list or table in the sub-report. If the sub-report contained two tables, you would need to set two data sources.
To fix the error message, assume I have a form, frmReport which contains a ReportViewer control, ReportViewer1.

I start by adding an event handler in the Load event of the form

Private Sub frmReport_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

AddHandler ReportViewer1.LocalReport.SubreportProcessing, _
AddressOf Me.SetSubDataSource

Me.ReportViewer1.RefreshReport()


End Sub

Next define the Sub called by the event handler. In this example, I am assuming the report has two sub-reports, SubReport1 and Subreport2, each with a single table. For each sub-report, I fill a tableadaptor for the sub-report filtering the dataset by the ID of the row in the main report. I then add a new report data source, e.g. "DataSet_vwSubReport1"

The data source name should be copied from the table or list dataset name. Right click on top left corner of table or list and open the properties dialog

Public Sub SetSubDataSource(ByVal sender As Object, _
  ByVal e As SubreportProcessingEventArgs)

  If e.ReportPath = "SubReport1" Then
    Me.taSubReport1.FillUsingId(Me.dsReport.vwSubReport1, Id)
    e.DataSources.Add(New ReportDataSource("DataSet_vwSubReport1" _
    , Me.dsReport.vwSubReport1))

  ElseIf e.ReportPath = "SubReport2" Then
    Me.taSubReport2.FillUsingId(Me.dsReport.vwSubReport2, Id)
    e.DataSources.Add(New ReportDataSource("DataSet_vwSubReport2" _
    , Me.dsReport.vwSubReport2))
  End If
End Sub


This is manipulating the collection of ReportDataSource objects of the report. A ReportDataSource object has a name and a value which is the data source.



Sub-reports - filtering by main report

In the subreport we must a) define a parameter and b) set the table filter to the value passed via the parameter, e.g. for subject activities


a) define a parameter, SubjectId, by right clicking outside the drawing area to display popup

and select Report parameters


 





b) set the table filter by clicking on the table and right click in top left corner

Select properties to display table properties dialog and select filter tab. set up the filter

The expression to be filtered is the SubjectId field and the filter value is the value of the SubjectId parameter. 

Finally, pass the parameter value from the main report to the subreport . In the main report open the parameters property of the subreport control

The parameter name must match that defined in the subreport. In this example, the value is the value of the SubjectId field.


Switching Reports in Report Viewer


The MCPD operates in both English and Dhivehi and reports can be delivered in both languages. I'll blog elsewhere about the issues of dual language, especially when one of the language uses a non-latin character set which runs right to left.
 My approach to the dual language problem for reports is to use the same Windows form, with ReportViewer Control for each language setting and to reference different reports for the English and Dhivehi versions.



For each Language the underlying datasource is different, one delivering text values in English and one in Dhivehi.

In the form.designer.vb comment out the lines


'Dim ReportDataSource1 As Microsoft.Reporting.WinForms.ReportDataSource = New Microsoft.Reporting.WinForms.ReportDataSource



and


'ReportDataSource1.Name = "MCPDDataSet_vwRptE_CaseReport"
'ReportDataSource1.Value = Me.vwRptE_CaseReportBindingSource
'Me.ReportViewer1.LocalReport.DataSources.Add(ReportDataSource1)
'Me.ReportViewer1.LocalReport.ReportEmbeddedResource = "MCPD.E_CaseReport.rdlc"



In the form.vb load event or a custom New or elsewhere add lines to clear the existing report data sources

• declare a ReportDataSource variable
• set its name and value
• add this new ReportDataSource to the report
• finally we set ReportViewer Control's rdlc in code as the project name (MCPD) and report object.


Public Class CaseReport

Private mReportDataSource As New Microsoft.Reporting.WinForms.ReportDataSource


'set report and report datasource
Me.ReportViewer1.LocalReport.DataSources.Clear()
 If LanguageCode = "EN" Then
  mReportDataSource.Name = "MCPDDataSet_vwRptE_CaseReport"
  mReportDataSource.Value = Me.vwRptE_CaseReportBindingSource
  Me.ReportViewer1.LocalReport.DataSources.Add(mReportDataSource)
  Me.ReportViewer1.LocalReport.ReportEmbeddedResource = "MCPD.E_CaseReport.rdlc"
Else
  mReportDataSource.Name = "MCPDDataSet_vwRptD_CaseReport"
  mReportDataSource.Value = Me.vwRptD_CaseReportBindingSource
  Me.ReportViewer1.LocalReport.DataSources.Add(mReportDataSource)
  Me.ReportViewer1.LocalReport.ReportEmbeddedResource = "MCPD.D_CaseReport.rdlc"
End If


You must add tableadaptor and binding source to the form.vb{Design]. The easiest way to do this is to select each one in turn using the reportviewer tasks panel

Selecting the report will add the datasource, tableadaptor and bindingsource controls to the designer

Gotchas

Report viewer is quite unforgiving on coding errors and quite unhelpful in indicating the source of the error. In almost all cases I have found the problem to be connected with data. Below I've listed errors I have made which have caused the ReportViewer control to not display the report.

1) If you change the size of a text field in the database, it is necessary to set the property for that field in the dataset designer for the table AND for any view using that field. If a report uses a table or view where the actual field size in the db and the property in the ds designer are different, it can lead to an error the first time the fill adaptor is called (if you step through the code in debug mode, you can see the fill method fail)

2) If the report parameter is set to the wrong type, e.g. if an integer parameter Id, is defined as string (default) instead of integer, the subreport will not display

3) if there is no select permission on the sql datasource (view, table) for the connection string, the sub report will not display. No error will be raised.