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

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:
  1. 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.
  2. 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:
  3. 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 heresection are displayed.
  4. 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
  5. Right-click the TrendChart chart control, and then click Properties.
  6. In the Chart Properties dialog box, click the Data tab.
  7. In the Values list, click Units On Order, and then click Edit.
  8. In the Edit Chart Value dialog box, click the Appearance tab.
  9. Click to select the Plot data as linecheck box, and then click OK.
  10. 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.

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:
  1. Start SQL Query Analyzer.
  2. 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
  3. 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);
  4. 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.
  5. 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:
  6. 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 heresection are displayed.
  7. In the fields pane, select the SalesMonthfield, and then drag the SalesMonth field to the Drop category fields here section of the TrendChart chart control.
  8. Right-click the TrendChart chart control, and then click Properties.
  9. In the Chart Properties dialog box, click the Data tab.
  10. In the Values list, click
    targetvalue, and then click Edit.
  11. In the Edit Chart Value dialog box, click the Appearance tab.
  12. Click to select the Plot data as linecheck box, and then click OK.
  13. 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.

References

For additional information about how to create a chart in Report Designer, visit the following MSDN Web sites:
Propriedades

ID do Artigo: 842422 - Última Revisão: 20 de jun de 2014 - Revisão: 1

Comentários