Create and Use DataSet-independent Reports in VB.NET

Scope

 

In this article we'll see a method to use an existing Local Report with an arbitrary Data Source, or, in other words, how to create/draw a Local Report in Visual Studio, saving the .RDLC file for later use, by passing to a ReportViewer control a dynamic Data Source, originating, for example, directly from a query. We'll see how to do that using Visual Basic .NET.

 

Prerequisites

• Visual Studio 2013 Community Edition (or later)

Introduction

I've found myself with the necessity to write a reporting program unbound from any IDE-generated datasets (or, more generally, not bound to a predetermined connection), to be used to execute arbitrary queries in a graphical format. My main target was to avoid having many dataset's schemas in various report applications, creating instead an application that could be fed with an option file, in which to specify the connection to be used, the query to be executed, the query parameters that must be obtained from the user and the RDLC file to use for the report rendering using a ReportViewer control. In a word, I wanted to create an independent reporter, that could be used in any way necessary by just launching it with different option files.

In this article, i will show a method to create a nearly complete separation between an RDLC file and the business logic, to put the basics towards more complex applications in which an approach like this could be useful, as in my case.

 

The following is the wizard.

Before seeing how to bypass an explicit Data Source binding, let's see how Visual Studio normally manages a request to create a new reporting application.

Open Visual Studio, select "New Project", then "Visual Basic" » "Reporting". We could see in the following image that there's a template named "Reports Application".

 

 

Confirming to proceed with it will cause the IDE to a) create a Form on which resides a ReportViewer, b) create a Report1.rdlc, that is the graphical-rendered part of our report and c) start a wizard to connect to a Data Source, selecting the objects that will become a part of the business logic of our application. The following image shows the first page of the wizard.

And that's the part of the wizard in which we're asked to specify what entities should be used:

 

After selecting a connection type and the objects to use, the wizard proceeds in creating a DataSet with a XSD file that represents its schema. It could now show us the fields presents in our, say, table.

 

At this point, our RDLC file could be modified, by adding graphical elements and indicating what fields goes where on it, by dragging the fields from the DataSet in the sidebar. Our wizard had, in fact, created also the TableManager and the TableAdapter to access our underlying data.

 

What is now evident is that, standing on what the IDE has made for us, for each report we must prepare a DataSet, binding all together on a single ReportViewer. That's a secure and easy method until we have a report or two to do, maybe in a single application, but it could become pretty nasty if you're in the need for a more flexible solution, a multi-report application, with many analysis to be run over data, or something that can adapt to various scenarios with minimum efforts. And that's where some "hacks" kicks in, the analysis of RDLC file formats.

An RDLC file that defines a Local Report, is a simple XML formatted file and, as such, it could be opened with a text editor. If we open our newly created Report1.rdlc, we will see that everything we could graphically do on our report will have an XML transposition, but that is true even for the data section. In the following two images we can observe some interesting tags about data presentation. In the first one, we can see a "DataSources" section, in which our DataSet will be shown with its properties. In the second image, there are tags dedicated to the DataSet schema, the connection (that will be saved in the application's configuration file) and some tags named "Fields", used to specify each field name, type, data member name and so on.

 

What if we remove from a RDLC file every reference to schemas and DataSets, leaving only the field references? Could it still be used? Let's see how a RDLC could be managed after such modifications.

 

Editing a Report

Having stripped down all the data references, what remains in my case is a file like the following:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition"
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSet">
<ConnectionProperties>
<DataProvider>System.Data.DataSet</DataProvider>
<ConnectString>/* Local Connection */</ConnectString>
</ConnectionProperties>
<rd:DataSourceID>fda0d98e-6996-4a1d-bfe9-19b29fa568f9</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<DataSourceName>DataSet</DataSourceName>
<CommandText>/* Local Query */</CommandText>
</Query>
<Fields>
<Field Name="Variable01">
<DataField>Variable01</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Variable02">
<DataField>Variable02</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Variable03">
<DataField>Variable03</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportSections>
<ReportSection>
<Body>
<Height>2in</Height>
<Style />
</Body>
<Width>8.47513in</Width>
<Page>
<PageHeight>29.7cm</PageHeight>
<PageWidth>21cm</PageWidth>
<LeftMargin>1cm</LeftMargin>
<RightMargin>1cm</RightMargin>
<TopMargin>1cm</TopMargin>
<BottomMargin>1cm</BottomMargin>
<ColumnSpacing>0.13cm</ColumnSpacing>
<Style />
</Page>
</ReportSection>
</ReportSections>
<rd:ReportUnitType>Cm</rd:ReportUnitType>
<rd:ReportID>809a73c6-2cff-4373-b2bd-e4703abea631</rd:ReportID>
</Report>

No connections, no schemas and a "fake" DataSet named DataSet1 that contains three fields named, respectively, Variable01, Variable02 and Variable03. To keep things easier, let's assume they are all of System.String type (but you could change it depending on your needs). The interesting thing is that it will continue to be a perfectly formatted RDLC file, in other words Visual Studio will open it.

 

What happens when it does

 

Yes, its showing our fake DataSet with its fields. They can be dragged on the report that continues to be drawable and modifiable. So, we can now proceed in modifying everything we need in our report, adding graphical elements, tables, data and so on and saving it. But we need also something that could embed our RDLC file (a ReportViewer), and physical data to populate our report's fields, finding a way to bind the report's fields with dynamic data at run-time level.

 

DataSource query and binding

To do the last part of our job, we will mimic the operations that the wizard has previously done for us. It created a DataSet, probably passing from a DataTable and with a DataAdapter it gained access to the underlying data, binding them with the corresponding fields in the report. Using a simple routine in Visual Basic .NET, we will do that:

Dim dt As New DataTable
Using conn As New SqlConnection("YOUR_CONNECTION_STRING_GOES_HERE")
conn.Open()
Dim _sqlCommand As New SqlCommand("SELECT ProductCode AS Variable01, ProductDes AS Variable02, Barcode AS Variable03 FROM
SampleTable", conn)
_sqlCommand.CommandTimeout = 4096
Dim ta As New SqlDataAdapter(_sqlCommand)
ta.Fill(dt)
End Using
With Me.ReportViewer1.LocalReport
.DataSources.Clear()
.ReportPath = "THE_PATH_TO_A_PHYSICAL_RDLC_FILE"
.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", dt))
End With
Me.ReportViewer1.RefreshReport()

Please note our SQL query. In it, we've used for each field aliases that rename the field themselves to the variables we have in our RDLC, so when the ReportViewer renders our report, it could correctly bind our query fields to the corresponding report's fields.

 

Conclusion

Having seen how to separate a report from an application's business logic, it will become easy to develop programs that will interface themselves with various reports, separately drawn and prepared. This will allow us to have slimmer solutions, avoiding the presence of schemas, datasets, dedicated logic, thus mantaining a better order and future management possibilities.

The following screenshot shows a stamp of my finished program (with some obscured data), that relies on a parameter file in which to specify the filters to ask to the user, the query to be executed and so on.