Progress® Telerik® Reporting R3 2017

How to: Create Graph with Column and Line Series

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.

How to create a Graph with Column and Line Series

  1. Start the Graph Wizard:

    • If you use Standalone Telerik Report Designer

      From the Insert menu select Column and then Clustered Column. This will bring up the Graph Wizard which will help you create the first series.

      select Column
    • If you use the Visual Studio Report Designer:

      Initiate the Graph Wizard from the Visual Studio toolbox. Then select Clustered Column chart type and click OK. This will bring up the Graph Wizard which will help you create the first series.

  2. Add new SqlDataSource:

    1. Set the connection string to the demo AdventureWorks database.

    2. Set the query to the following one:

      SELECT SOD.LineTotal, SOH.OrderDate
      FROM
      Sales.SalesPerson S
      INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesPersonID = S.SalesPersonID
      INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
      WHERE SOH.OrderDate between cast('2003-01-01' as datetime) and cast('2003-12-31' as datetime)
      and S.SalesPersonId = 283
    3. You can click on Execute Query... just to check if everything is OK with the database connection. Click Finish when you are ready.

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

  4. 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.

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

    Graph Layout 1
  6. 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:

    SettingXValue
  7. 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:

    Change Scale Type

    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.

  8. 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.

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

    Graph Layout 2
  10. 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.

  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:

    Change Coord System
  12. 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:

    Graph Layout 3

See Also