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

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:

   http://msdn.microsoft.com/library/en-us/RShowto/htm/hrs_designer_v1_2vhw.asp

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 here section 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 line check 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:

   http://msdn.microsoft.com/library/en-us/RShowto/htm/hrs_designer_v1_2vhw.asp

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 here section are displayed.
7. 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.
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 line check 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.