How can I reperesent yearwise data in ssrs 2005 when I have monthwise query

Question

I Have a such dataset where the query parameter is month name.

Now I have to represent a report of the year this way, where qty will be the totalQty of the month and value will be the TotalValue.

How should I do this. Do I need to use matrix in the ssrs or write a new query. I am not familiar with using matrix. My current Query is,

ALTER PROCEDURE [dbo].[spRPTMonthlyProductionSummaryFORATL]
    @MonthName  Varchar(50)
-- Exec spRPTMonthlyProductionSummaryFORATL 'Sep,2014'
AS BEGIN
Declare @FromDate datetime, @ToDate datetime
Select @FromDate = Convert(Datetime,@MonthName), @ToDate = DateAdd(d,-1,DateAdd(m,1,Convert(Datetime,@MonthName)))
Declare @SQL nVarchar (4000)
SET @SQL = '
WITH
    allocations AS (
        SELECT SC.Name AS BUYER, 
        CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
        CASE WHEN PPC.FactoryName = ''ACWL'' THEN OM.OrderQty END AS Unit2_Qty,
        CASE WHEN PPC.FactoryName = ''DNV'' THEN OM.OrderQty END AS Unit3_Qty,
        CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty * FR.Rate END AS Unit1_Value, 
        CASE WHEN PPC.FactoryName = ''ACWL'' THEN OM.OrderQty * FR.Rate END AS Unit2_Value, 
        CASE WHEN PPC.FactoryName = ''DNV'' THEN OM.OrderQty * FR.Rate END AS Unit3_Value
    FROM LineAllocation LA
    INNER JOIN OrderMaster OM ON LA.OrderRef= OM.OrderRefID
    INNER JOIN AmanOTS..FileRef FR ON FR.FileRefID = OM.FileRefID
    INNER JOIN SystemManager..Contacts SC ON SC.Code=OM.BuyerCode
    INNER JOIN SystemManager..ProductionProcessCostCenter PPC ON PPC.CostCenter= LA.Line
    INNER JOIN OrderMasterCostBreakdown OCB ON OCB.OrderRefID= OM.OrderRefID
    INNER JOIN SystemManager..ProductionProcess PP ON PP.ProcessID = PPC.ProcessId
    where UseDate = @MonthName AND ProcessName =''Sewing'' 
    )
SELECT 
    BUYER, 
Unit1_Qty,
Unit2_Qty,
Unit3_Qty,
Unit1_Value, 
Unit2_Value, 
Unit3_Value,
(ISNULL(Unit1_Qty,0) + ISNULL(Unit2_Qty,0) + ISNULL(Unit3_Qty,0)) as TotalQty,
(ISNULL(Unit1_Value,0) + ISNULL(Unit2_Value,0) + ISNULL(Unit3_Value,0)) as TotalValue
FROM allocations;'

EXEC sp_executesql
@sql
,N'@MonthName varchar(30)'
,@MonthName = @MonthName;
End

Answer 1

I am assuming that you want to show the report for one year starting from the Jan to Dec

WITH
allocations AS (
    SELECT SC.Name AS BUYER, 
    CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
    CASE WHEN PPC.FactoryName = ''ACWL'' THEN OM.OrderQty END AS Unit2_Qty,
    CASE WHEN PPC.FactoryName = ''DNV'' THEN OM.OrderQty END AS Unit3_Qty,
    CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty * FR.Rate END AS Unit1_Value, 
    CASE WHEN PPC.FactoryName = ''ACWL'' THEN OM.OrderQty * FR.Rate END AS Unit2_Value, 
    CASE WHEN PPC.FactoryName = ''DNV'' THEN OM.OrderQty * FR.Rate END AS Unit3_Value,
    UseDate
FROM LineAllocation LA
INNER JOIN OrderMaster OM ON LA.OrderRef= OM.OrderRefID
INNER JOIN AmanOTS..FileRef FR ON FR.FileRefID = OM.FileRefID
INNER JOIN SystemManager..Contacts SC ON SC.Code=OM.BuyerCode
INNER JOIN SystemManager..ProductionProcessCostCenter PPC ON PPC.CostCenter= LA.Line
INNER JOIN OrderMasterCostBreakdown OCB ON OCB.OrderRefID= OM.OrderRefID
INNER JOIN SystemManager..ProductionProcess PP ON PP.ProcessID = PPC.ProcessId
where YEAR(UseDate) = @YearToShow AND ProcessName =''Sewing'' 
)
SELECT 
  BUYER, 
  Month(UseDate) AS Month,
  SUM(COALESCE(Unit1_Qty,0)) Unit1Quantiry,
  SUM(COALESCE(Unit2_Qty,0)) Unit2Qunatity,
  SUM(COALESCE(Unit3_Qty,0)) Unit3Quantity,
  SUM(COALESCE(Unit1_Value,0)) Unit1Value, 
  SUM(COALESCE(Unit2_Value,0)) Unit2Value, 
  SUM(COALESCE(Unit3_Value,0)) Unit3Value,
  SUM(COALESCE((ISNULL(Unit1_Qty,0) + ISNULL(Unit2_Qty,0) + ISNULL(Unit3_Qty,0)),0)) as TotalQty,
  SUM(COALESCE((ISNULL(Unit1_Value,0) + ISNULL(Unit2_Value,0) + ISNULL(Unit3_Value,0)),0)) as TotalValue
FROM allocations
GROUP BY
    Month(UseDate),Buyer;

This will give you the monthwise some for the entire year. Which you will pass as the @YearToShow parameter.

This query is not tested.