In this how-to article we will show you how to create a graph with both column and line series, showing the maximum
and the average amount of sales on a monthly basis using the AdventureWorks sample database.

You can click on Execute Query... just to check if everything is OK with the database connection.
Click Finish when you are ready.

In the Available data sources list you should see the datasource you've already created.
Select it and click Next.

In the next page you should select the fields which will be used to build the chart. Since the report will display aggregated sums
of the order lines by month, you need only two fields: LineTotal and OrderDate.
Select OrderDate field and drag it to Categories box.
Select LineTotal field and drag it to Values box.
It will be shown as Sum(LineTotal), as the Sum is the default aggregate function,
but in this example we will display the maximum amount of sales in the column series and the average amount of sales in the
line series, so you should choose the Max function from the drop-down menu. Click Finish once you are ready.

The graph will display the column series and it should look like this:

Since the graph is displaying date-time values on its X-axis, it’s better to change the axis scale from Category to DateTime.
In order to use the DateTime Scale your series need to have a valid expression set for its X values.
To do so, select the graph, open or expand the Series property and set "= Fields.OrderDate"
as an expression of your series' X values as shown below:

To change the scale type, select the X-axis by clicking on it or on its labels and in the
Property Browser window select DateTime Scale for the Scale property:

If you wish, you can make some additional adjustments to your graph such as resizing, aligning, setting a proper
LabelFormat
(i.e. {0:Y}) and
LabelAngle
to 90º on your X axis.

To add another series to the graph, you can right-click on the graph and select Add Graph Series…
from the context menu. This will bring up a dialog window with all the chart types to select from.
Select Line, Line with Markers and click OK.
The Graph wizard will be displayed again. Repeat step 4 for choosing and dragging the data source fields to
Categories and Values boxes, but this time choose Avg as an aggregate function for the LineTotal field. Click Finish when you are ready.

The Graph should now display both the Column and the Line series and it should look like this:

You may have noticed the duplicated axes in your graph. They appear because the newly created line series comes with an auto-created
coordinate system, which is unnecessary in this case. To get rid of it, you can set up the series to share one coordinate system.
In order to have your line series shown properly on a DateTime scale, you need to set "= Fields.OrderDate"
as an expression for its X property. The property is illustrated below at step 11.

To change the line series’ coordinate system, you have to click on it and then select the desired coordinate system in the Property browser window:

The graph will display the both series properly and you can remove the unnecessary coordinate system from the coordinate systems collection. You can also name the series’ legend items accordingly
using the property LegendItem.Value of both of your series.
Finally your graph should look like the one shown below:

Reference

Other Resources

Give article feedback

Tell us how we can improve this article

Code samples are inaccurate / outdated.
I expected to find other / more information.
There are typos / broken links / broken page elements.
Content is inaccurate / outdated.
Other
By checking this box you consent to Progress contacting you by email about your response on this page.