Tutorial 4: Using Charts in Reports

This tutorial extends Tutorial 1: Getting Started by adding a bar chart to the report. The following steps are illustrated in more details:

Prerequisites
Adding a bar chart to the report
Data-binding the chart
Binding to a custom query

Prerequisites

As mentioned above this tutorial extends Tutorial 1. Therefore it is expected that the reader has finished with Tutorial 1 before reading further.

1. Adding a bar chart to the report

The BarChart is another type of report element, just like Label and Picture. The simplest way to create one is to drag it from the Visual Studio Toolbox and drop it onto the page surface in the Designer.

Once the chart is added to the report, we have to position and resize the chart. This particular chart will hold quite a few bars, so we will stretch it along the width of the first page of the report. Set the Location of the chart to "0mm, 0mm" and its Size to "100%, 60mm". Set the ContentSize property of the current page to "180, 90" so that the whole chart is visible in the Designer. Adjust the Location of the existing data range so that it does not overlap with the newly added chart. Set it to "0mm, 70mm".

Here is how the Designer will look like after these modifications:

2. Data-binding the chart

To data-bind the chart, set its DataSource property to nwindDataSet1 (a variable referencing an instance of the data set we added to the report in Tutorial 1), set DataMember to "Products" and DataFields to "UnitPrice".

Now we can bind even more chart properties to this data source. For example, to display the names of the products along the x-axis, set the XLabelsFields property to "ProductName" and the LabelType property of XAxisSettings to CustomText. To display the actual prices of the products on top of each bar in the chart, set the TopLabelsFields property to "UnitPrice" and TopLabelType to CustomText.

Running the application will have the following result:

3. Binding to a custom query

As is observed from the illustration above, the chart bars are very small due to the large amount of products displayed. One way to partially resolve this problem is to set the PageOrientation property of the page to Landscape. This way the chart will span a larger area and the size of its bars will increase. Another way is to filter the data displayed on the chart with some criteria. To do this we need to create a custom query and a new data adapter in our data set.

Navigate to the nwindDataSet.xsd file in the Solution Explorer window and double-click on it to open the data sat diagram in the environment. Right-click on an empty area in the diagram designer and select the 'Add -> TableAdapter...' command from the context menu.

A new table adapter is created in the data set and the TableAdapter Configuration Wizard is displayed. Ensure that the appropriate connection is selected in the combo box and click 'Next'. On the 'Choose a Command Type' step leave 'Use SQL statements' as selected and click 'Next'. The 'Enter a SQL Statement' step appears.

We have to enter the text of our SQL query in the next step. There are several ways to improve the query creation experience, but we will use a ready-made query text that selects only the products with price less than 20 and sorts them in ascending order.

SQL Copy Code
SELECT   ProductName, UnitPrice
FROM     Products
WHERE    (UnitPrice < 20)
ORDER BY UnitPrice

SQL Copy Code
SELECT ProductName, UnitPrice FROM Products WHERE (UnitPrice < 20) ORDER BY UnitPrice

Click the 'Advanced Options...' and uncheck the 'Generate Insert, Update and Delete statements' check box. Click 'OK' to close the 'Advanced Options' dialog box, then click 'Finish'. The table adapter representing our query is now properly initialized and appears on the diagram.

Select the data table representing the newly created query and press F2 to rename it. Replace "Products1" with "CheapProducts" then press ENTER. The name of the corresponding adapter is changed to "CheapProductsTableAdapter" automatically. Save the data set and close it. Recompile the project.

Now get back to the Report Designer. The data adapter representing the new query is now visible in the Toolbox under the 'Tutorial4 Components' tab. Double-click on it in order to create a new instance of the adapter and add it to the report.

Select the BarChart in the report, navigate to its DataMember property in the Properties window and set its value to "CheapProducts". Since the fields of our query have the same name as those in the original 'Products' table, there is nothing more that needs to be changed.

Finally, go back to the report's constructor and add the following line of code (outlined in bold):

C# Copy Code
public myreport()
{
    InitializeComponent();

    // Fill the data adapter from the dataset
  productsTableAdapter1.Fill(nwindDataSet1.Products);
    cheapProductsTableAdapter1.Fill(nwindDataSet1.CheapProducts);
}

C# Copy Code
public myreport() { InitializeComponent(); // Fill the data adapter from the dataset productsTableAdapter1.Fill(nwindDataSet1.Products); cheapProductsTableAdapter1.Fill(nwindDataSet1.CheapProducts); }

Visual Basic Copy Code
Public Sub New()

    InitializeComponent()

    ' Fill the data adapter from the dataset
    productsTableAdapter1.Fill(nwindDataSet1.Products)
    cheapProductsTableAdapter1.Fill(nwindDataSet1.CheapProducts)

End Sub

Visual Basic Copy Code
Public Sub New() InitializeComponent() ' Fill the data adapter from the dataset productsTableAdapter1.Fill(nwindDataSet1.Products) cheapProductsTableAdapter1.Fill(nwindDataSet1.CheapProducts) End Sub

Running the application will now display the filtered list of products as illustrated below: