Creating reports using Microsoft Reporting Services

1.     On UI Folder create ReportViewerUI.vb as shown below:

Controls-Name

Combobox-cboReports

Datetimepicker-dtFrom and dtTo

Button-btnPreview

MicrosoftReportViewer-MSReportViewer

2.     Create Reports Folder, on MarioSoft.Sales Project.

3.     Right click Reports folder, add Dataset object and named it dtReports.xsd

4.     On the toolbox, add DataTable control. Type in table name and columns as shown below:

5.     Right click Reports folder again, and add Report object and named it rptCustomers.rdlc

6.     On the toolbox, add table control. Put column headers as shown below.

7.     On Solution Explorer, select the data sources tab, and you will see the DataSet you created (refer step 3) as well as the datatable you added. Simply drag the column of the datatable to the specific column on your table control.

Sidenote: Your Solution Explorer folder structure would be look like this:

8.     On the ReportViewerUI.vb code window type the following:

Imports Microsoft.Reporting.WinForms

Imports Microsoft.ReportingServices

Public Class ReportViewerUI

    Private Sub DisplayReport()

        Dim reportPath As String = "MarioSoft.Sales.rptCustomers.rdlc"

        Me.MSReportViewer.LocalReport.ReportEmbeddedResource = reportPath

        Dim sReportDataSource As New ReportDataSource

        sReportDataSource.Name = "dtReports_Customers"

        sReportDataSource.Value = CustomersBAL.GetCustomersRPT

        MSReportViewer.LocalReport.DataSources.Add(sReportDataSource)

        Me.MSReportViewer.RefreshReport()

    End Sub

Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreview.Click

        Select Case cboReports.Text

            Case "Customers"

                DisplayReport()

        End Select

    End Sub

End Class

9.     On the CustomerBAL.vb code window type the following:

Public Class CustomersBAL

    Public Shared Function GetCustomersRPT() As DataTable

        Dim customerDAL As New CustomersDAL

        Return customerDAL.GetCustomersRPT

    End Function

End Class

10.     On the CustomerDAL.vb code window type the following:

Imports System.Data.OleDb

Public Class CustomersDAL

    Dim db As OledbConnection

    Public Sub New()

        db = dbs.Connect

    End Sub

    Public Function GetCustomersRPT() As DataTable

        Dim sql As String

        sql = "SELECT * FROM Customers ORDER BY name"

        Dim cmd As New OleDbCommand(sql, db)

        Dim sda As New OleDbDataAdapter(cmd)

        Dim dt As New DataTable

        sda.Fill(dt)

        Return dt

    End Function

End Class