Walkthrough: Creating a ReportViewer Report

  • Article

This walkthrough shows how to create a simple table report in a Visual Studio Windows Forms application project based on the AdventureWorks2008 sample database. You will add a report to your project using the Report Wizard and add a ReportViewer Windows Forms control to a Windows Form so the report can be viewed by users of the application.

For a demonstration of how to create a drillthrough report using the reportviewer control, see Create a Drillthrough (RDLC) Report with Parameters using ReportViewer (SSRS Tutorial).

For more information on designing reports with the Visual Studio Report Designer, see Report Designer (Visual Studio).

Prerequisites

To use this walkthrough, you must have access to the AdventureWorks2008 sample database. The query used in this walkthrough will not work with an earlier version of AdventureWorks. For more information about how to get the AdventureWorks2008 sample database, see Walkthrough: Installing the AdventureWorks Database.

This walkthrough assumes that you are familiar with Transaction-SQL queries and ADO.NET DataSet and DataTable objects.

To create a new Windows Forms application project

  1. Open Visual Studio. On the File menu, point to New, and then select Project.

  2. In the Installed Templates pane, expand Other Languages and select Visual Basic.

  3. Choose Windows Forms Application.

  4. In the Name box, type SimpleReport.

  5. In the Location box, enter the directory in which you want to save your project, or click Browse to navigate to it.

  6. Click OK.

    The Windows Forms Designer opens, showing Form1 of the project you created.

  7. Click on the form. From the View menu, choose Properties Window. Expand the Size property to show Width and Height. Set Width to 500 pixels.

To define a data set and a data table

  1. In the Project menu, select Add New Item.

  2. In the Add New Item dialog, click DataSet. Type a name for the dataset and click Add. The default name is DataSet1.xsd.

    This adds a new XSD file to the project and opens the Dataset Designer.

  3. From the Toolbox in the Dataset Designer, drag a TableAdapter control onto the design surface.

    This starts the TableAdapter Configuration Wizard.

  4. On the Choose Your Data Connection page, click New Connection.

  5. If this is the first time you create a data source in Visual Studio, you will see the Choose Data Source page. In the Data Source box, select Microsoft SQL Server.

  6. In the Add Connection dialog box, perform the following steps:

    • In the Server name box, enter the server where the AdventureWorks2008 database is located.

      The default SQL Server Express instance is (local)\sqlexpress.

    • In the Log on to the server section, select the option that provides you access to the data. Use Windows Authentication is the default.

    • From the Select or enter a database name drop-down list, click AdventureWorks2008.

    • Click OK to continue to return to the wizard, then click Next.

  7. If you specified Use SQL Server Authentication in the previous step, select the option whether to include the sensitive data in the string or set the information in your application code.

  8. On the Save the Connection String to the Application Configuration File page, type in the name for the connection string or accept the default AdventureWorks2008ConnectionString. Click Next.

  9. On the Choose a Command Type page, select Use SQL Statements, and click Next.

  10. On the Enter a SQL Statement page, enter the following Transact-SQL query to retrieve sales data from the AdventureWorks2008 database, and then click Finish:

    --SET DATEFORMAT mdy

SELECT      
   PC.Name AS Category, PS.Name AS Subcategory, 
   DATEPART(yy, SOH.OrderDate) AS Year, 
   'Q' + DATENAME(qq, SOH.OrderDate) AS Qtr, 
   SUM(DET.UnitPrice * DET.OrderQty) AS Sales
FROM Production.ProductSubcategory PS INNER JOIN
   Sales.SalesOrderHeader SOH INNER JOIN
      Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID INNER JOIN
      Production.Product P ON DET.ProductID = P.ProductID 
      ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN
   Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN ('20020101') AND ('20031231'))
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 
   'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

    You can also click on the Query Builder button and use Query Builder to create a query and validate it using the Execute Query button.

  11. Click Finish.

    The Dataset Designer now shows the DataTable definition for DataTable1 with fields named from the columns and column aliases of the query (Category, Subcategory, Year, Qtr, and Sales). You will use these fields when binding data to your report.

    Note

    If you need to change the fields in your data table, right-click on the DataTable1 header or the DataTable1TableAdapter header on the Dataset Designer page. Choose Configure, which restarts the TableAdapter Configuration Wizard.

  12. Save the DataSet1 file.

To add a new report definition file using the Report Wizard

  1. From the Project menu, select Add New Item.

  2. In the Add New Item dialog box, choose Report Wizard.

  3. In Name, type Sales Orders.rdlc and then click Add.

    A graphical design surface opens behind the dialog box.

  4. In the Dataset Properties page, in the Data source drop-down list, select the DataSet you created.

    The Available datasets box is automatically updated with the DataTable you created.

  5. Click Next.

  6. In the Arrange Fields page do the following:

    1. Drag Category from available fields to the Row groups box.

    2. Drag Subcategory from available fields to the Row groups box, below Category.

    3. Drag Year from available fields to the Column groups box.

    4. Drag Qtr from available fields to the Column groups box, below Year.

    5. Drag Sales from available fields to the Values box.

  7. Click Next twice, then click Finish.

    This creates the .rdlc file and opens it in Report Designer. The tablix you designed is now displayed in the design surface.

To add a title to the report

  1. Click the tablix on the designer surface so that column and row handles appear above and next to the table.

    Note

    Handles are gray boxes that appear above and next to the tablix. You use handles to perform various actions on columns groups, row groups, and the tablix itself. The handles that run across the top of the tablix are column handles. The handles that run down the side of the tablix are row handles. The handle where the column and row handles meet is the corner handle.

  2. Click the corner handle to select the entire tablix to show the cross arrow.

  3. Move the tablix down on the design surface by dragging the cross arrow.

  4. From the Toolbox, drag a Text Box item to the area above the tablix in the design surface, and then position it by dragging the cross arrow.

  5. With the textbox selected, type Company Sales. If necessary, drag the textbox border to expand it.

  6. With the textbox selected, click the Bold button in the Report Formatting toolbar, and then select 16pt in the Fond Size box.

  7. With the textbox selected, click the Foreground Color button in the Report Formatting toolbar.

  8. In the Choose Color dialog box, select the Midnight Blue color and click OK.

To add the ReportViewer control to your form

  1. Click Form1.vb in Solution Explorer.

  2. From the View menu, choose Designer.

  3. From the Reporting section of the Toolbox, drag the ReportViewer control to the form.

  4. Open the smart tags panel of the ReportViewer1 control by clicking the smart-tag glyph on the top right corner. Click the Choose Report drop-down list and select SimpleReport.Sales Orders.rdlc.

  5. From the smart tags panel, click Dock in parent container.

    Throughout the rest of this walkthrough, you can build your application and view the report in the form at any time. If you want to see how the incremental changes of your report design affects the end report, build and view the report as the last step in each of the following procedures.

  6. (Optional) Press F5 to build your application and view the report in the form.

To format a currency field

  1. In the Report Designer window for the Sales Orders.rdlc file, right-click the upper-left cell with the [Sum(Sales)] value and then click Text Box Properties.

  2. Select the Number tab.

  3. In the Category list, select Currency.

  4. In the Symbol list, select English (United Kingdom).

  5. Click OK to close the dialog box.

  6. Perform the same steps to all other cells with the [Sum(Sales)] value.

  7. (Optional) Press F5 to build your application and view the report. Observe the change of the number format. Note that no currency conversion is made, but only the number format is changed.

To format tablix layout

  1. In the Report Designer window for the Sales Orders.rdlc file, drag the cursor to select the two empty cells in the top left corner of the tablix.

  2. Right-click the selected cells and select Merge Cells.

  3. Right-click the merged cell and select Text Box Properties.

  4. Click the Border tab. In the Preview area, click the border toggle buttons to remove the top and left borders.

  5. Click OK.

  6. Click the tablix to show its handles. For the column with the header Subcategory, expand the column width by dragging the right side of the column handle. This way you can prevent the header text from wrapping to the next line.

  7. Press F5 to build your application and view the report.

See Also

Reference

ReportViewer.Drillthrough

LocalReport.SubreportProcessing

ReportViewer.Drillthrough

LocalReport.SubreportProcessing

Concepts

Using the ReportViewer Tasks Smart Tags Panel

Other Resources

Create a Drillthrough (RDLC) Report with Parameters using ReportViewer (SSRS Tutorial)

Designing and Implementing Reports Using Report Designer (Reporting Services)

Samples and Walkthroughs