CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
Arsee
Arsee

Reputation: 671

SSRS Line Chart Connecting two values in one line chart

I have a line chart that contains 3 values (Previous Year Actual, Forecast and Current Year Actual) and a Category groups of (Month-Year ex: Jan-2016). I am able to place the 3 values in a line chart fine, but I'm having a difficulty charting a "continuous line (with marker) " meaning first I'd like to chart the Current Year Actual Value and at the last month, for example, Feb-2016 the chart line for the Forecast will show. I can do it in MSExcel, the line from the left is the actual and the highlighted in yellow is the forecast, but I unable to do it in SSRS. Please advise. LineChart

Upvotes: 0

Views: 501

Answers (1)

Jesse Potter
Jesse Potter

Reputation: 847

Combine your data in your select query. You can use a UNION statement like the below example.

SELECT actual_sales AS 'sales'
    , calendar_day AS 'calendar_day'
    , 'actual' AS 'sales_type'
FROM actual_sales_data
UNION
SELECT projected_sales AS 'sales'
    , calendar_day AS 'calendar_day'
    , 'projected' AS 'sales_type'
FROM projected_sales_data

Then you can chart both types of sales (actual and projected) in a continuous line because it will be one dataset.

Here is some sample data you can use with the example query:

DECLARE @actual_sales_data TABLE (actual_sales int, calendar_day DATE)
DECLARE @projected_sales_data TABLE (projected_sales int, calendar_day DATE)

INSERT INTO @actual_sales_data
SELECT 100, '1/1/2016'
UNION
SELECT 200, '1/2/2016'
UNION
SELECT 150, '1/3/2016'
UNION
SELECT 180, '1/4/2016'
UNION
SELECT 210, '1/5/2016'
UNION
SELECT 230, '1/6/2016'
UNION
SELECT 200, '1/7/2016'
UNION
SELECT 220, '1/8/2016'


INSERT INTO @projected_sales_data
SELECT 220, '1/8/2016' -- This data point matches the last actual sales number so that SSRS will draw a continuous line
UNION
SELECT 250, '1/9/2016'
UNION
SELECT 220, '1/10/2016'
UNION
SELECT 180, '1/11/2016'
UNION
SELECT 250, '1/12/2016'
UNION
SELECT 210, '1/13/2016'
UNION
SELECT 270, '1/14/2016'
UNION
SELECT 200, '1/15/2016'
UNION
SELECT 290, '1/16/2016'

SELECT actual_sales AS 'sales'
    , calendar_day AS 'calendar_day'
    , 'actual' AS 'sales_type'
FROM @actual_sales_data
UNION
SELECT projected_sales AS 'sales'
    , calendar_day AS 'calendar_day'
    , 'projected' AS 'sales_type'
FROM @projected_sales_data

Upvotes: 0

Related Questions