How to create a bar graph that has a trend line in a chart in Report Designer

View products that this article applies to.

SUMMARY

This article describes how to plot data on a chart as a bar graph that has a trend line by using Report Designer. You must have two sets of data to plot a bar graph that has a trend line. This article discusses two methods to plot data based on the complexity of the query that is used to retrieve the data:

Create a bar graph that has a trend line by using a simple query
Create a bar graph that has a trend line by using a complex query

Back to the top

INTRODUCTION

This article describes how to use Report Designer in Microsoft SQL Server 2000 Reporting Services to plot data on a chart as a bar graph that has a trend line. You can use a trend line in a chart to plot a set of achieved results or values against a set of standard goals or values. Therefore, to plot a bar graph that represents the achieved results or values by using a trend line that represents the standard goals or values, you must have two sets of data. This article discusses two examples that use two different sets of data values to plot a bar graph that has a trend line.

Back to the top

MORE INFORMATION

To create a chart that uses two different sets of data, you must create a query that returns a dataset. This dataset must combine two different sets of data that are retrieved from one or more data sources. The complexity of the query may vary based on the organization of the data in the data source.

Back to the top

Create a bar graph that has a trend line by using a simple query

If the data in the data source is organized so that the two different sets of data can be retrieved easily, you can use a simple query to create the chart. The following example illustrates how to create a bar graph that has a trend line by using a simple query:

In Report Designer, use the following information to create a new report:

Data source
------------------
Name: Northwind
Type: Microsoft SQL Server
Connection string: data source=<Instance of SQL Server>;initial catalog=Northwind

Query string
------------------
SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)

Report name
------------------
TrendReport

Note When you create the report project, you must provide the appropriate credentials to access the database on the instance of the SQL Server.

In Layout view, add a chart control. Name the chart control TrendChart.

For more information about how to add a chart to a report, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/aa179406(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa179406(SQL.80).aspx)
In the fields pane, select the following fields, and then drag the fields to the Drop data fields here section of the TrendChart chart control:
- UnitsInStock
- UnitsOnOrder
Note If the Drop data fields here section is not displayed, double-click the TrendChart chart control. The Drop data fields here section, the Drop category fields here section, and the Drop series fields here section are displayed.
In the fields pane, select the following fields, and then drag the fields to the Drop category fields here section of the TrendChart chart control:
- CategoryID
- ProductName
Right-click the TrendChart chart control, and then click Properties.
In the Chart Properties dialog box, click the Data tab.
In the Values list, click Units On Order, and then click Edit.
In the Edit Chart Value dialog box, click the Appearance tab.
Click to select the Plot data as line check box, and then click OK.
In the Chart Properties dialog box, click OK.

In Report Designer, a chart that has Units In Stock as the bar graph and Units On Order as the trend line is created.

Back to the top

Create a bar graph that has a trend line by using a complex query

If the data in the data sources is organized so that the two different sets of data cannot be retrieved easily, you have to use a complex query to retrieve the dataset. The following example illustrates how to create sample tables and insert rows. The sample also illustrates how to create a chart that uses a complex query to retrieve a dataset by combining two different sets of data that are retrieved from the sample tables. To create a bar graph that has a trend line by using a complex query, follow these steps:

Start SQL Query Analyzer.

In the pubs sample SQL Server database, create three tables to store the data that is used to plot the bar graph and the trend line in the chart. To do this, run the following command in the query window:

use pubs
go

CREATE TABLE [dbo].[Sales] (
	[salesid] [int] NULL ,
	[salesvalue] [int] NULL ,
	[salestypeid] [int] NULL ,
	[salestimeid] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SalesTime] (
	[salestimeid] [int] NULL ,
	[salesmonth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SalesType] (
	[salestypeid] [int] NULL ,
	[salestypename] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Insert data into the tables that you created in step 2. To do this, run the following command in the query window:

insert into SalesTime(SalesTimeId, SalesMonth) values (1, 'Jan');
insert into SalesTime(SalesTimeId, SalesMonth) values (2, 'Feb');
insert into SalesTime(SalesTimeId, SalesMonth) values (3, 'Mar');

insert into SalesType(SalesTypeID, SalesTypeName) values(1, 'Achieved');
insert into SalesType(SalesTypeID, SalesTypeName) values(2, 'Target');

insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(1, 56, 1, 1);
insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(2, 40, 2, 1);
insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(3, 70, 1, 2);
insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(4, 100, 2, 2);
insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(5, 50, 1, 3);
insert into Sales(SalesID, SalesValue, SalesTypeID, SalesTimeId)
values(6, 65, 2, 3);

In Report Designer, use the following information to create a report:

Data source
------------------
Name: pubs
Type: Microsoft SQL Server
Connection string: data source=<Instance of SQL Server>;initial catalog=pubs

Query string
------------------
SELECT null as targetvalue, Sales.salesvalue as achieved, SalesTime.salesmonth, SalesType.salestypename
FROM Sales INNER JOIN
       SalesType ON Sales.salestypeid = SalesType.salestypeid INNER JOIN
       SalesTime ON Sales.salestimeid = SalesTime.salestimeid
where salestypename='Achieved'
union
SELECT b.salesvalue as targetvalue, null achieved, SalesTime.salesmonth, SalesType.salestypename
FROM Sales b INNER JOIN
      SalesType ON b.salestypeid = SalesType.salestypeid INNER JOIN
      SalesTime ON b.salestimeid = SalesTime.salestimeid
where salestypename='Target'

Report name
------------------
TrendReport

Note When you create the report project, you must provide the appropriate credentials to access the database on the instance of the SQL Server.

In Layout view, add a chart control. Name the chart control TrendChart.

For more information about how to add a chart to a report, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/aa179406(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa179406(SQL.80).aspx)
In the fields pane, select the following fields, and then drag the fields to the Drop data fields here section of the TrendChart chart control:
- targetvalue
- achievedvalue
Note If the Drop data fields here section is not displayed, double-click the TrendChart chart control. The Drop data fields here section, the Drop category fields here section, and the Drop series fields here section are displayed.
In the fields pane, select the SalesMonth field, and then drag the SalesMonth field to the Drop category fields here section of the TrendChart chart control.
Right-click the TrendChart chart control, and then click Properties.
In the Chart Properties dialog box, click the Data tab.
In the Values list, click targetvalue, and then click Edit.
In the Edit Chart Value dialog box, click the Appearance tab.
Click to select the Plot data as line check box, and then click OK.
In the Chart Properties dialog box, click OK.

In Report Designer, a chart that has achievedvalue as the bar graph and targetvalue as the trend line is created.

To retrieve the data from the tables that you created in step 2, you can run the following simple query:

SELECT     Sales.salesvalue, SalesType.salestypename, SalesTime.salesmonth
FROM         Sales INNER JOIN
                      SalesType ON Sales.salestypeid = SalesType.salestypeid INNER JOIN
                      SalesTime ON Sales.salestimeid = SalesTime.salestimeid
ORDER BY SalesType.salestypename

This query returns output that is similar to the following:

salesvalue  salestypename salesmonth 
----------- ------------- ---------- 
56          Achieved      Jan
56          Achieved      Jan
70          Achieved      Feb
70          Achieved      Feb
50          Achieved      Mar
50          Achieved      Mar
56          Achieved      Jan
56          Achieved      Jan
70          Achieved      Feb
70          Achieved      Feb
50          Achieved      Mar
50          Achieved      Mar
40          Target        Jan
40          Target        Jan
100         Target        Feb
100         Target        Feb
65          Target        Mar
65          Target        Mar
40          Target        Jan
40          Target        Jan
100         Target        Feb
100         Target        Feb
65          Target        Mar
65          Target        Mar

The data in the query output only includes one set of values. Therefore, you cannot use this query to obtain the dataset that is used to plot the bar graph and the trend line in the chart. If you create the query to retrieve two sets of data from the tables, you can use the query to obtain the dataset that is used to plot the bar graph and the trend line in the chart. The query that you used in step 4 to create a report returns output that is similar to the following:

targetvalue achieved    salesmonth salestypename 
----------- ----------- ---------- ------------- 
NULL        50          Mar        Achieved
NULL        56          Jan        Achieved
NULL        70          Feb        Achieved
40          NULL        Jan        Target
65          NULL        Mar        Target
100         NULL        Feb        Target

The data in the query output includes two sets of data that are retrieved from the tables. Therefore, you can use the query to obtain the dataset that is used to create the chart.

Back to the top