Using ReportViewer

ReportViewer is a tool that enables you to incorporate database reporting in your VB applications. The ReportViewer is a Microsoft component that is part of Visual Studio; it is the client-side "cousin" of the SQL Server Reporting Services tool. ReportViewer provides an alternative to Crystal Reports and other third-party reporting tools.

NOTE: ReportViewer is available with Visual Studio Professional version or higher - it is NOT available with VB 2010 Express.

This sample application generates a sales report from the modified Northwind Access database (the same database that was used in the Introduction to SQL tutorial). A copy of the database is included in the download of this project's files. The application allows a user to specify a date range, and a sales report will be generated for that specified period. Subtotals are produced for order and date. The report looks like this when run:

With ReportViewer, you need not specify the data connection and related query during the design process. You can design the report independently, then hook it up with code later. That is the approach taken with this sample application.

To build the application, follow the steps below.

Start a new project (I named this "ReportExample"). Go to the project properties page, and on the References tab, check "Microsoft.Reporting.WinForms". For that matter, make sure you check "System.Data.OleDb".
Set up the form as shown on the right. Name the form "frmReportDemo" and set its Text to "Report Demo". The "from date" DateTimePicker is named "dtpFromDate"; the "to date" DateTimePicker is named "dtpToDate". The Format for both is "Short". The buttons are named "btnGenerate" and "btnExit".
Add a second form to the project. Name it "frmReportViewer" and set its Text to "Report Viewer".
Add a ReportView control (found under the "Reporting" section of the Toolbox). Set its Dock property to Fill.
To begin building the report, we must first add a DataSet to the project. From the Project menu, select "Add New Item ...".
From the "Add New Item" dialog, select Dataset, and name it "dsSalesRpt.xsd".
After adding the dataset, the Dataset Designer tab should be active. RIght-click on an open area of the designer and choose Add --> DataTable from the context menus.
A DataTable object will appear in the designer. Rename this "dtSalesRpt".
What we want to do now is to add columns to this DataTable object: one column to match each column of the SELECT query that the report will be based on. To add a column, right-click  on the DataTable and choose Add --> Column.
Name the OrderDate, and set its DataType property to System.DateTime.
Repeat this process to add the following columns: · OrderID (System.Int32) · CustomerID (System.String) · ProductName (System.String) · UnitPrice (System.Double) · Quantity (System.Int32) · Discount (System.Double) · ExtPrice (System.Double) Your DataTable should look like the one in the screen shot on the right.
Now that the DataSet and its associated DataTable have been established, we must now add the actual report object to the project. From the Project menu, select "Add New Item ...".
From the "Add New Item" dialog, choose "Report Wizard", name it "rptSalesRpt.rdlc", and click the Add button.
On the first screen of the Report Wizard, enter "dsSalesRpt_dtSalesRpt" for Name, choose "dsSalesRpt" for Data source, and choose "dtSalesRpt" for Available datasets. Then click Next.
On the "Arrange fields" screen of the Report Wizard, drag OrderDate and OrderID fields to the "Row groups" box, and drag all of the fields to the "Values" box. Note: when you move a numeric field to the Values box, it will place a "Sum" function around it by default. We only want a Sum for the ExtPrice field. To remove the "Sum" function from the others, click the drop-down arrow to the right of the field name and uncheck "Sum" from the context menu. Click Next when done.
On the "Choose the layout" screen of the Report Wizard, uncheck "Expand/collapse groups". (By default, "Expand/collapse groups" is checked, which places groupings of detail and subtotal items on the report, which can be expanded or collapsed with "+" and "-" buttons. This may be desirable in some cases, but not for this example.) Click Next to proceed.
On the "Choose a style" screen of the Report Wizard, choose a style from the list. The default is "Ocean", which is fine with me - but if something else strikes your fancy, go for it. After choosing the style, click Finish.
The Wizard has now placed what they call a "tablix" (a cross between a table and a matrix) on the design surface of the report designer.  This represents the body of the report. It's a good start, but we have some clean-up work to do.
First, remove all of the "Sum" fields in OrderID, UnitPrice, Quantity, and Discount columns.
On the detail line, for the Ext Price column, we don't want the Sum function; we just want field itself. (We DO want the Sum on those total lines, however.) if you hover your mouse over the "Sum(ExtPrice)" cell on the detail line, the little "field list" icon should appear; when you click it, a drop-down menu of your fields should appear, from which you want to choose "ExtPrice".
Next, we will resize some columns. Note that you have two pairs of Order Date and Order ID columns. The first pair represents the groupings that the report will be based on. The wizard will be using these, but we don't necessarily need to show them, as they will also be shown as regular detail fields.  To effectively hide them, resize them with the mouse to be as narrow as you can. Then resize the other columns as you see fit. In particular, you'll want to make the Product Name column wider.
We now want to right-justify the column headings for the last four numeric columns. Select those four columns, then choose Format --> Justify --> Right from the menu as shown.
Now for some formatting. Right-click on the OrderDate field, and choose "Text Box Properties" from the context menu.
Choose "Number", then "Date" from Category, then "*1/31/2000" from Type. Click OK when done.
For UnitPrice, Discount,  Ext Price, and three total ExtPrice subtotal fields,  repeat the process above, except choose "Currency" from Category, select "2" for Decimal places, and check the box for "Use 1000 separator".
Now we are going to dress up the subtotal lines. On the first line below the detail line, click cell below Quantity and drag your mouse to the cell below Discount, right-click and choose "Merge Cells" from the context menu.
Right-click in the merged cell and choose "Create Placeholder ...".
In the Placeholder Properties window, click the "fx"  button to the right of the Value textbox.
In the Expression screen, type the expression shown. (The system will generate the "Fields!OrderID.Value" part for you if you select "Fields" from the Category list, then double-click "OrderID" from the Values list.) Click OK when done. Then click OK to dismiss the Placeholder Properties window.
Repeat the above process to create an expression for the OrderDate subtotal line. On the row directly below the row you just worked with, merge the two cells under Quantity and Discount, right-click inside to get the menu where you choose "Create Placeholder", on the Placeholder Properties window, click the "fx" button to the right of the Values textbox, then type the expression you see on the right in the Expression window. Click OK to close out the Expression window, and again to close the Placeholder Properties window.
Repeat the above process one more time to create an expression for the report (grand) total line. On the row directly below the row you just worked with, merge the two cells under Quantity and Discount, right-click inside to get the menu where you choose "Create Placeholder", on the Placeholder Properties window, click the "fx" button to the right of the Values textbox, then type the expression you see on the right in the Expression window. Click OK to close out the Expression window, and again to close the Placeholder Properties window.
Next on the agenda is to add a report header and report footer. Before we do so, however, we are going to add some report parameters. Parameters are useful if you want to display variable data in a report heading at run time. In the case of this sample program, we want to show the "from date" and "to date" for the date range selected by the user (e.g. "Northwind Sales Report for 12/1/1994 through 12/31/1994"). (Yes, the Northwind sales data is ancient history.) To add parameters to a report, go to the View menu (in the top menu bar of Visual Studio), and choose Report Data.
The Report Data window should appear. Right click on "Parameters" and choose "Add Parameter...".
In the Report Parameters Properties window, type prmFromDate for the name. It is OK to leave the Data type as Text. Click OK. Repeat this process to add another parameter named prmToDate.
Now that we've added the parameters, we are going to add the page header. Right-click on an open area of the report designer and choose "Add Page Header".
The header area appears above the tablix, ready for us to place items in there.
The first order of business is to move the column headers from the tablix detail area to the header. If left in the detail area, they will only show on the first page of the report; we want to show the column headers on every page. Drag your mouse across the row of column headings so that they are all selected. Then right-click and choose "Cut".
Click in the header area, then right-click and choose "Paste". Position the pasted row above the columns as shown here.
Select the "old" (now blank) row, right-click and choose "Delete Rows".
From the Toolbox, under Report Items, take a Text Box and place it in the upper left corner of the header area.
Right-click on the Text Box and choose "Expression..."
In the Expression screen, type the expression shown. (Note that you can have the system generate "Today" for you by selecting Common Functions --> Date & Time from the Category list, then double-clicking on Today from the Item list.) Click OK when done.
Right-click on the Text Box again and choose "Text Box Properties..."
On the Text Box Properties window, select Font, and set the font to Tahoma, 10pt size, Bold, color Midnight Blue. Click OK when done.
Add another Text Box to the right of the first one and size it as shown.
Right-click on the Text Box and choose "Expression..." In the Expression screen, type the expression shown. (Note that here we are making use of the Parameters.You can get the system to generate the parameter parts of the expression by selecting Parameters from the Category list and then selecting the desired parameter from the Values list.) Click OK when done.
Right-click on the Text Box again and choose "Text Box Properties..." On the Text Box Properties window, select Alignment, and then select "Center" under Horizontal.
Still in the Text Box Properties window, select Font, and set the font to Tahoma, 14pt size, Bold, color Midnight Blue. Click OK when done. Note: Setting the font to the larger size may require you make the Text Box taller once you return to the report designer screen.
Now we are going to add the page footer. Right-click on an open area of the report designer and choose "Add Page Footer".
The footer area appears below the tablix, ready for us to place items in there.
Add a Text Box to the footer area and size it as shown (use the entire width of the footer area).
Add the Expression as shown. (Note that you can have the system generate the "Globals!PageNumber" part for you by selecting Built-in Fields from the Category list, then double-clicking on PageNumber from the Item list.) Click OK when done.
Right-click on the Text Box again and choose "Text Box Properties..." On the Text Box Properties window, select Alignment, and then select "Center" under Horizontal. Click OK when done.
A couple of last minute things: right-click on an open area of the report designer and choose "Report Properties..."
With "Page Setup" selected, select "Landscape" for Orientation, and set all Margins to 0.5in. Click OK when done. I realized "after the fact" that my design was too wide for Portrait orientation, which is the default. I realized this when I printed a hard copy of the report and columns were cut off, spilled on to extra pages, etc. - it looked fine on the screen! Changing to Landscape got me closer, but still too wide. Changing the Margins to 0.5 did the trick (the default is 1 inch for all margins).
Note: To prevent surprises like the one described above, you can show a ruler on the design surface. Do this by right-clicking on an open area of the report designer and choose View --> Ruler.
That concludes the design portion of the report setup process. Now all we need is some code to actually make it work. For the Click event of the btnGenerate button, use the code listed on the right.	Private Sub btnGenerate_Click(sender As System.Object, e As System.EventArgs) Handles btnGenerate.Click Dim objConn As OleDbConnection Dim objCmd As OleDbCommand Dim objReader As OleDbDataReader Dim objDataset As DataSet = New dsSalesRpt Dim strSQL As String         strSQL = ""         strSQL = strSQL & "SELECT O.ORDERDATE"         strSQL = strSQL & "     , O.ORDERID"         strSQL = strSQL & "     , O.CUSTOMERID"         strSQL = strSQL & "     , P.PRODUCTNAME"         strSQL = strSQL & "     , OD.UNITPRICE"         strSQL = strSQL & "     , OD.QUANTITY"         strSQL = strSQL & "     , OD.DISCOUNT"         strSQL = strSQL & "     , (OD.UNITPRICE * OD.QUANTITY) * (1 - OD.DISCOUNT) AS EXTPRICE"         strSQL = strSQL & "  FROM (ORDERS AS O INNER JOIN [ORDER DETAILS] AS OD ON O.ORDERID = OD.ORDERID)"         strSQL = strSQL & " INNER JOIN PRODUCTS AS P ON OD.PRODUCTID = P.PRODUCTID"         strSQL = strSQL & " WHERE O.ORDERDATE BETWEEN #" & Format(dtpFromDate.Value, "M/d/yyyy") & "# AND #" _                         & Format(dtpToDate.Value, "M/d/yyyy") & " 11:59:59PM #"         strSQL = strSQL & " ORDER BY O.ORDERDATE, O.ORDERID"         objConn = New OleDbConnection(DB_CONN_STRING)         objCmd = New OleDbCommand(strSQL, objConn)         objCmd.CommandType = CommandType.Text         objCmd.Connection.Open()         objReader = objCmd.ExecuteReader         objDataset.Tables(0).Clear()         objDataset.Tables(0).Load(objReader)         objReader.Close()         objConn.Close() Dim rds As ReportDataSource = New ReportDataSource ' Note: "dsSalesRpt_dtSalesRpt" was name we gave to the report dataset ' when setting it up with the wizard         rds.Name = "dsSalesRpt_dtSalesRpt"         rds.Value = objDataset.Tables(0) Dim paramList As New Generic.List(Of ReportParameter)         paramList.Add(New ReportParameter("prmFromDate", Format(dtpFromDate.Value, "M/d/yyyy")))         paramList.Add(New ReportParameter("prmToDate", Format(dtpToDate.Value, "M/d/yyyy"))) With frmReportViewer ' Note: When assigning the ReportEmbeddedResource in the statement below, you must ' qualify the report object name with the project name (in this case "ReportExample") .ReportViewer1.LocalReport.ReportEmbeddedResource = "ReportExample.rptSalesRpt.rdlc"             .ReportViewer1.LocalReport.DataSources.Clear()             .ReportViewer1.LocalReport.DataSources.Add(rds)             .ReportViewer1.LocalReport.SetParameters(paramList)             .ReportViewer1.RefreshReport()             .Text = "Sales Report"             .ShowDialog() End With End Sub
Don't forget the code for the Exit button.	Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click Me.Close() End Sub
There's also a module called "modDatabase" that has the connection string variable.	Module modDatabase Public DB_CONN_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _                                     & My.Application.Info.DirectoryPath & "\NWINDVBP.mdb" End Module
Now run the program. Enter the dates as shown (remember this data is from the 90's). Click "Generate Report" when done.
The report should display as shown. Use the Print button on the toolbar to print a hard copy.

Download the project files for this sample application here.