How to display two separate graph in one chart

Question

I have an SSRS report which has two datasets (ds1 and ds2) which calls to a table (tbl1):

Date        LastWeekEMROrders   LastWeekAccLabOrders    LastWeekLabResults  LastWeekPendingProcessing
2014-06-07  23125               21711                   46881               1414
2014-06-14  24963               21551                   44372               3412

The first dataset takes the last week ending date (6/14) and displays in a chart with this query:

SELECT TOP 1 [Date]
      ,[LastWeekEMROrders]
      ,[LastWeekAccLabOrders]
      ,[LastWeekLabResults]
  FROM [db].[dbo].[tbl1]
  ORDER BY [Date] DESC

The second dataset takes the week ending prior to last week (6/7) and displays in another chart with this query:

SELECT *
FROM
(
SELECT [Date]
      ,[LastWeekEMROrders]
      ,[LastWeekAccLabOrders]
      ,[LastWeekLabResults]
      ,[LastWeekPendingProcessing],
      RowNumber = ROW_NUMBER() OVER(ORDER BY [Date] DESC)
      FROM [db].[dbo].[tbl1]
      ) AS d
  WHERE d.RowNumber = 2;

The two queries displays two different charts:

enter image description here

What is the best way to combine the both queries or through SSRS so I can add both in one chart like this:

enter image description here

TMNT2014 TMNT2014 · Accepted Answer · 2014-06-18T16:40:56

Can't you use a single dataset - Assuming each row corresponds to a week

SELECT TOP 2 [Date] ,[LastWeekEMROrders] ,[LastWeekAccLabOrders] ,[LastWeekLabResults] FROM [db].[dbo].[tbl1] ORDER BY [Date] DESC

Set this dataset to one of the charts and you would see the result that your expecting.

How to display two separate graph in one chart

1 Answers