VB.NET: Create and Using DataSet-independent reports
Scope
In this article we'll see a method to use an existant Local Report with an arbitrary Data Source, or - in other words - how we can 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 in the necessity of writing a reporting program unbounded from any IDE-generated datasets (or, more generally, not bounded 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 different report applications, creating instead an application that could be feed with an option file, in which to specify the connection to be used, the query to be executed, the query parameters which must be asked to the user, and the RDLC file to use for the report rendering through a ReportViewer control. In a word, i wanted to create an independent reporter, that could be used in any way needed by just launching it with differents option files.
In this article, i will show a method to achieve an almost 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.
Following the wizard
Before seeing how to bypass an explicit Data Source binding, let's see how Visual Studio normally manages a request of creating 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 our IDE to: a) create a Form on which resides a ReportViewer, b) create a Report1.rdlc, which is the graphical-rendered part of our report and c) starting a wizard to connect to a Data Source, selecting the objects that will became part of the business logic of our application. The following image shows the first page of the wizard.
And that's the part of 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 which 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 different scenarios with minimum efforts. And that's where some "hacks" kicks in.
Analysis of an RDLC file
An RDLC file, which 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 (which will be saved in the application's configuration file), and sometags named "Fields", used to specify each field name, type, data member name, etc.
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 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, which contains three fields named, respectively, Variable01, Variable02, Variable03. To keep things easier, let's suppose they are all of System.String type (but you could change it according to your needs). The interesting thing is that it will continue to be a perfectly formatted RDLC file, i.e. Visual Studio will open it.
But what happens when it does?
Yes, its showing our fake DataSet with its fields. They can be dragged on the report, which 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 the same:
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 which rename the field themselves to the variables we have in our RDLC, so when the ReportViewer will render our report, it could correctly bind our query fields to the correspondant report's fields.
Conclusion
Having seen how to separate a report form an application's business logic, it will become easy to develop programs which will interface themselves with different reports, separately drawn and prepared. This will allow us to have more slim solutions, avoiding the presence of schemas, datasets, dedicated logic, thus mantaining a better order and future management possibilities.
In the following screenshot, a stamp of my finished program (with some obscured data), which relies on a parameter file in which to specify the filters to ask to the user, the query to be executed, and so on.
Other languages
The present article is available in the following localizations: