Creating a Simple Bar Chart

For the bar chart report, we’ll create a new report.

Step 1: Create a bar chart report

Using the techniques from other chapters, create a new, blank report definition named “Chapter5Report1”. In its definition, add a Chart.XY element to the Body element in the report.

Figure 5.1: Definition Editor Panel after adding Chart element

Set its Chart Type attribute to a value of “Bar” and its set the Chart Title attribute to a meaningful description (i.e. “Top 10 Customers”). An ID is not required for a chart.

Step 2: Define the table data layer

A chart, like a data table, needs a Data Layer element to retrieve the data it will display. Add a DataLayer.SQL element beneath the Chart element. 

Figure 5.2: Definition Editor Panel after adding DataLayer element 

Give the data layer an appropriate ID and set it to use the connNorthwind connection.

Use the following SQL query for its Source attribute:

SELECT TOP 10 CustomerID, COUNT(CustomerID) AS OrderCount

FROM Orders

GROUP BY CustomerID

ORDER BY COUNT(CustomerID) DESC

This SQL query returns a summary of the top 10 customers in the database by number of orders.  

Hint: The SQL “TOP” clause behaves slightly different between SQL Server and Access. SQL Server always returns the number of rows requested, but Access returns more than the number of rows requested when a “tie” is encountered.    

Step 3: Define the column to display on the x-axis and y-axis

Set the attributes for the chart element as follows:

Attribute	Value
Chart Type	Bar
Data Column Y-axis	OrderCount
Chart Title	Top 10 Customers
Data Title	Number of Orders
Label Column X-axis	CustomerID
Label Title	Customer ID
Show Data Values	True

Hint: Use the Extra Data Column element to display multiple bars on a bar chart.    

Step 4: Format the bar chart report

All that remains is to format the chart. Generally the default formatting is not acceptable. 

• Set the Height and Width attributes to display a 400 X 600 chart.
• Set the Color attribute to a value of “Green” to control the bar color.
• Set the 3-Dimensional property to a value of “8” to control the depth of the bars.  
• Set Border Color, Grid Horizontal Color, and Grid Vertical Color to “Silver”. 
• Set Top Border, Bottom Border, Right Border, and Left Border to provide room for the title and axis labels

Set additional attributes for the chart element as follows:

Figure 5.3: Attributes Panel after setting Chart element attributes 

The resulting bar chart is presented as shown below:

Figure 5.4: The Bar Chart example 

We can easily change the chart style by changing the Chart Type attribute to “Area”:

Figure 5.5: The Area Chart example

Or to a "Line" chart:

Figure 5.6: The Line Chart example

Or to a "Spline" chart:

Figure 5.7: The Spline Chart example