Microsoft KB Archive/842422

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

Article ID: 842422

Article Last Modified on 5/18/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Reporting Services

-



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



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.



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.

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=;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

 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</ul>

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.</li> 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: <ul> CategoryID</li> ProductName</li></ul> </li> Right-click the TrendChart chart control, and then click Properties.</li> In the Chart Properties dialog box, click the Data tab.</li> In the Values list, click Units On Order, and then click Edit.</li> In the Edit Chart Value dialog box, click the Appearance tab.</li> Click to select the Plot data as line check box, and then click OK.</li> 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.</li></ol>

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: <ol> Start SQL Query Analyzer.</li>  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 </li>  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); </li>  In Report Designer, use the following information to create a report: <pre class="fixed_text">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. </li> 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

</li> 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: <ul> targetvalue</li> <li>achievedvalue</li></ul>

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.</li> <li>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.</li> <li>Right-click the TrendChart chart control, and then click Properties.</li> <li>In the Chart Properties dialog box, click the Data tab.</li> <li>In the Values list, click targetvalue, and then click Edit.</li> <li>In the Edit Chart Value dialog box, click the Appearance tab.</li> <li>Click to select the Plot data as line check box, and then click OK.</li> <li>In the Chart Properties dialog box, click OK.</li></ol>

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:

<pre class="fixed_text">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:

<pre class="fixed_text">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.

<div class="references_section">