Create a Graph with Column and Line Series
|Product||Progress® Telerik® Reporting Graph Report Item|
How can I create a Graph with Column and Line series?
The suggested approach demonstrates how to create a Graph with both column and line series and displaying the maximum and the average amount of sales on a monthly basis based on the AdventureWorks sample database.
Start the Graph Wizard:
(For the Standalone Telerik Report Designer) From the Insert menu, select Column > Clustered Column. As a result, the Graph Wizard starts.
(For the Visual Studio Report Designer) Initiate the Graph Wizard from the Visual Studio toolbox. Then, select the Clustered Column chart type and click OK. As a result, the Graph Wizard starts.
Click the Add New Data Source... button and select SQL Data Source:
Set the connection string to the demo AdventureWorks database.
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
- Click Execute Query... to check if everything is fine with the database connection. Click Finish when you are ready.
From the Available data sources list, select the data source you've already created and click Next.
On the next page, 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 the LineTotal and OrderDate fields. Select the OrderDate field and drag it to the Categories box. Select the LineTotal field and drag it to the Values box. It will be shown as Sum(LineTotal) because the Sum is the default aggregate function.
However, for the purposes of this example, you will display the maximum amount of sales in the column series and the average amount of sales in the line series. Therefore, choose the Max function from the drop-down menu. Click Next once you are ready.
The Wizard has detected that the field used as a category is of type
DateTimeand this page will let you configure the scale for the graph. In most cases, the
Autooption will work fine, so you will leave it as is. Complete the Wizard process by clicking Finish.
Now the graph displays the column series:
The graph is displaying the date-time values on its X-axis, and the
Autoscale option divides each column into one column per month.
To add another series to the graph, right-click the graph and select Add Graph Series… from the context menu.
As a result, a dialog with all chart types will appear. 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 Next and Finish when you are ready.
When adding a new Series, a new Category group will also be created. This solution uses a field of type
DateTimefor the category which will set the scale of the X-axis to a
DateTime Scaleand will correctly display the two series on top of each other.
With any other type, like
Logarithmic Scale, or
Category Scale, the two Series will be displayed next to each other and not overlapping correctly. To fix this, select the newly-created series and change the
CategoryGroupto the group used by the first series.
Name the legend items of the series accordingly by using the
LegendItemfor both of your series.
The following image shows the final look of your Graph.