none
Crosstab query to combine 3 tables , to draw graph in SSRS

    Question

  • Hello,

    I need some help. I am so new to SSRS and SQL

    I have three tables : budget, actual and forecast

    i managed to pull information from each table as below

    F23

    ACTUAL
    JAN 456
    FEB 456

    F23 FORECAST
    APRIL 5000
    MAY 300
    JUNE 40


    F23 BUDGET
    JAN 556
    FEB 234
    MAR 445
    APRIL 5567
    MAY 334
    JUNE 44
    JULY 78
    AUGUST  66
    SEP 789
    OCTOBER 345
    NOV 223
    DECEMBER 1234

    I need to create a cross tab query to form a table as below to take this to SSRS to draw a graph

    F23 JAN FEB MAR APRIL MAY JUNE JULY AUGUST  SEP OCTOBER NOV DECEMBER
    BUDGET 556 234 445 5567 334 44 78 66 789 345 223 1234
    FORECAST NULL NULL NULL 5000 300 40 NULL NULL NULL NULL NULL NULL
    ACTUAL 456 456 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    Can someone please help me? how do i achieve this. 

    I tried to create a dataset for each table in the report, but i could not draw three lines in the same graph and after reading some forums i was led to believe i need a cross tab query that i have no clue how to achieve from this.

    PLEASE help

    Thanks

    Jollily

    Monday, March 20, 2017 3:43 AM

Answers

  • Hi joylili,

    You may start from the following script.

    DECLARE @actual TABLE
        ([F23] varchar(10), [ACTUAL] int)
    ;
        
    INSERT INTO @actual
    VALUES
        ('JAN', 456),
        ('FEB', 456)
    ;
    
    DECLARE @forecast TABLE
        ([F23] varchar(10), [FORECAST] int)
    ;
        
    INSERT INTO @forecast
    VALUES
        ('APRIL', 5000),
        ('MAY', 300),
        ('JUNE', 40)
    ;
    
    DECLARE @budget TABLE
        ([F23] varchar(10), [BUDGET] int)
    ;
        
    INSERT INTO @budget
    VALUES
        ('JAN', 556),
        ('FEB', 234),
        ('MAR', 445),
        ('APRIL', 5567),
        ('MAY', 334),
        ('JUNE', 44),
        ('JULY', 78),
        ('AUGUST', 66),
        ('SEP', 789),
        ('OCTOBER', 345),
        ('NOV', 223),
        ('DECEMBER', 1234)
    ;
    
    ;WITH CTE([month], [amount], [F23]) AS 
    (
    	SELECT [F23], [ACTUAL], 'ACTUAL'
    	FROM @actual
    	UNION ALL
    	SELECT [F23], [FORECAST], 'FORECAST'
    	FROM @forecast
    	UNION ALL
    	SELECT [F23], [BUDGET], 'BUDGET'
    	FROM @budget
    )
    SELECT [F23], [JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER]
    FROM CTE
    PIVOT (
    		MAX([amount]) 
    		FOR [month] IN ([JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER])
    	) p
    ORDER BY CASE [F23]
    			WHEN 'BUDGET' THEN 1
    			WHEN 'FORECAST' THEN 2
    			WHEN 'ACTUAL' THEN 3
    		END

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by joylili Tuesday, March 21, 2017 9:42 PM
    Monday, March 20, 2017 4:34 AM
    Moderator
  • Hi there

    actually you need to try something like

    select b.F23, b.BUDGET, COALESCE(f.FORECAST, NULL),  COALESCE(a.ACTUAL, NULL),  from budget as b

    left outer join forecast as f on f.F23 = b.F23 

    left outer join actual as a on a.F23 = b.F23

    • Marked as answer by joylili Wednesday, March 22, 2017 4:55 AM
    Tuesday, March 21, 2017 5:52 AM

All replies

  • Hi joylili,

    You may start from the following script.

    DECLARE @actual TABLE
        ([F23] varchar(10), [ACTUAL] int)
    ;
        
    INSERT INTO @actual
    VALUES
        ('JAN', 456),
        ('FEB', 456)
    ;
    
    DECLARE @forecast TABLE
        ([F23] varchar(10), [FORECAST] int)
    ;
        
    INSERT INTO @forecast
    VALUES
        ('APRIL', 5000),
        ('MAY', 300),
        ('JUNE', 40)
    ;
    
    DECLARE @budget TABLE
        ([F23] varchar(10), [BUDGET] int)
    ;
        
    INSERT INTO @budget
    VALUES
        ('JAN', 556),
        ('FEB', 234),
        ('MAR', 445),
        ('APRIL', 5567),
        ('MAY', 334),
        ('JUNE', 44),
        ('JULY', 78),
        ('AUGUST', 66),
        ('SEP', 789),
        ('OCTOBER', 345),
        ('NOV', 223),
        ('DECEMBER', 1234)
    ;
    
    ;WITH CTE([month], [amount], [F23]) AS 
    (
    	SELECT [F23], [ACTUAL], 'ACTUAL'
    	FROM @actual
    	UNION ALL
    	SELECT [F23], [FORECAST], 'FORECAST'
    	FROM @forecast
    	UNION ALL
    	SELECT [F23], [BUDGET], 'BUDGET'
    	FROM @budget
    )
    SELECT [F23], [JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER]
    FROM CTE
    PIVOT (
    		MAX([amount]) 
    		FOR [month] IN ([JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER])
    	) p
    ORDER BY CASE [F23]
    			WHEN 'BUDGET' THEN 1
    			WHEN 'FORECAST' THEN 2
    			WHEN 'ACTUAL' THEN 3
    		END

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by joylili Tuesday, March 21, 2017 9:42 PM
    Monday, March 20, 2017 4:34 AM
    Moderator
  • Sam,

    I did exactly as you suggested and got the below code snippet

    ;WITH CTE([month], [amount], [phil], [issue_ID]) AS 
    (
    
    select [phil], [issue_id], Convert(varchar(100),sumbudget) as [sumbudget]
    , 'SumBUDGET' from @BUDGET
    
    union all
    select [phil], [issue_id], Convert(varchar(100),sumforcast) as [sumforcast]
    , 'SumFORCAST' from @FORCAST
    union all
    select [phil], [issue_id], Convert(varchar(100),sumactual) as [sumactual]
    , 'SumACTUAL' from @ACTUAL
    
    )
    SELECT [phil], [JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER]
    FROM CTE
    PIVOT (
    		max([amount]) 
    		FOR [month] IN ([JAN], [FEB], [MAR], [APRIL], [MAY], [JUNE], [JULY], [AUGUST], [SEP], [OCTOBER], [NOV], [DECEMBER])
    	) p
    ORDER BY CASE [phil]
    			WHEN 'SumBUDGET' THEN 1 
    			WHEN 'SumFORECAST' THEN 2
    			WHEN 'SumACTUAL' THEN 3
    		END
    		
    

    and got the below table. Any idea what I am doing wrong? this data has (-) values, decimal values ...

    phil JAN FEB MAR APRIL MAY JUNE JULY AUGUST SEP OCTOBER NOV DECEMBER
    -117728 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -1717 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -199321 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -2015 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -2215 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -3300 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -3346 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -3751 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -44070 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    -97651 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    101254 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    107000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    107062 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    113922 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    11542 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    116 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    116712 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    1250 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    12872 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    13832 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    Tuesday, March 21, 2017 3:05 AM
  • thank you
    Tuesday, March 21, 2017 3:05 AM
  • Hi joylili,

    Here the non-pivoted column is [phil], remove [issue_id] in above query. As for column name in the common table expression, see argument column_name.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 5:08 AM
    Moderator
  • Hi there

    actually you need to try something like

    select b.F23, b.BUDGET, COALESCE(f.FORECAST, NULL),  COALESCE(a.ACTUAL, NULL),  from budget as b

    left outer join forecast as f on f.F23 = b.F23 

    left outer join actual as a on a.F23 = b.F23

    • Marked as answer by joylili Wednesday, March 22, 2017 4:55 AM
    Tuesday, March 21, 2017 5:52 AM
  • Hello Sam

    I somewhat got the code to do union query to do the following:

    phil month_name amount (No column   name)
    F0023 April 1340 BUDGET
    F0023 December 282500 BUDGET
    F0023 February 594 ACTUAL
    F0023 February 1340 BUDGET
    F0023 January 1340 BUDGET
    F0023 January 7019 ACTUAL
    F0023 July -282647 BUDGET
    F0023 March 1303 FORECAST
    F0023 March 1340 BUDGET
    F-0030 April 5387 FORECAST
    F-0030 February 11542 ACTUAL
    F-0030 January 2951 ACTUAL
    F-0030 March 15059 FORECAST
    F-0110 April 133145 FORECAST
    F-0110 April 434145.02 BUDGET
    F-0110 August 633209 FORECAST
    F-0110 December 26292 FORECAST
    F-0110 February 240457 ACTUAL
    F-0110 February 445164.02 BUDGET
    F-0110 January 116712 ACTUAL

    Can you please show me how to do the crosstab with Pivot to have as below:?

    phil month_name Jan feb march  april may june july august  september october novemebr dec
    F-0023 Budget                        
      actual                        
      forecast                        
    F-0030 Budget                        
      actual                        
      forecast                        

    Tuesday, March 21, 2017 9:52 PM