CODEWITHSUNDEEP
sqlt-sqlreporting-services
Serdia
Serdia

Reputation: 4428

how to perform division with multiple conditions in a group SSRS

How can I calculate change Year\Month over Year\Month for the past 4 years when Year and Month in a group in SSRS. I need like that: enter image description here Row 2016 vs 2015 % Change is a division of January 2016 Premium/ January 2015 Premium and so on

enter image description here

Something like CASE WHEN year=2016 and month = 1 then 2016 premium/2015 premium I am trying to:

IIF(Fields!YearNum.Value=2016 and Fields!MonthNum.Value=1, Fields.Premium.Value/IIF(Fields!YearNum.Value=2015 and Fields!MonthNum.Value=1,Fields.Premium.Value,1),Nothing)

My query looks like that:

SELECT  b.YearNum,
        b.MonthNum,
        b.MonthName,
        SUM(Premium) as Premium,
        ISNULL(sum(case when TransactionType IN ('Policy', 'Reinstatement') then 1 ELSE 0 END),0) as Bound,
FROM    tblCalendar b 
                    LEFT JOIN   Test_Plaza_ProductionReport a  ON b.MonthNum = MONTH(a.EffectiveDate) AND b.YearNum=YEAR(a.EffectiveDate)
WHERE YEAR(EffectiveDate) <> 2017
GROUP BY    b.YearNum,
            b.MonthNum,
            b.MonthName

enter image description here

Upvotes: 2

Views: 412

Answers (2)

poppertech
poppertech

Reputation: 1294

Setup:

To mimic your setup, I manually created the following table (dbo.Unpivoted):

Initial Data

Pivot Query:

Next, I pivoted the data to yield the following result:

Pivoted Data

SELECT [Year] AS [Year], [January], [February]
FROM
(SELECT * FROM dbo.Unpivoted) src
PIVOT
(
Sum(src.Premium)
FOR src.[Month] IN ("January", "February")
) as PivotTable
ORDER BY [Year] DESC;

Self-Join Query:

Based on the above results (now called dbo.Premium), I used a self-join to calculate the year-over-year percentage increase:

Results

Select Cast(upyear.[Year] as varchar) + ' vs ' + Cast(downyear.[Year] as varchar) + ' % Change' [Year],(upyear.January/downyear.January - 1) January, (upyear.February/downyear.February - 1) February
From dbo.Premium upyear
INNER JOIN
dbo.Premium downyear
ON upyear.[Year] = downyear.[Year] + 1;

Update: The combined query

CREATE TABLE #Combined([Year] VARCHAR(50), January FLOAT, February FLOAT)

INSERT INTO #Combined SELECT CAST([Year] AS varchar) AS [Year], [January], [February]
FROM
(SELECT * FROM dbo.Unpivoted) src
PIVOT
(
Sum(src.Premium)
FOR src.[Month] IN ("January", "February")
) as PivotTable;

INSERT INTO #Combined SELECT CAST(upyear.[Year] AS VARCHAR) + ' vs ' + CAST(downyear.[Year] as VARCHAR) + ' % Change' [Year],(upyear.January/downyear.January - 1) January, (upyear.February/downyear.February - 1) February
From
#Combined upyear
INNER JOIN
#Combined downyear
ON upyear.[Year] = downyear.[Year] + 1;

SELECT * FROM #Combined
ORDER BY 
    CASE 
        WHEN LEN([Year]) = 4 THEN 1
        ELSE  0
    END DESC,
    [Year] DESC;

Begin
    Drop Table #Combined
End

Upvotes: 0

Kostya
Kostya

Reputation: 1605

it seems you are using matrix so you don't need to specify the month try expression like:

=(sum(IIF(Fields!YearNum.Value=2016, Fields.Premium.Value,0)) - sum(IIF(Fields!YearNum.Value=2015, Fields.Premium.Value,0)))
/sum(IIF(Fields!YearNum.Value=2016, Fields.Premium.Value,0))

Upvotes: 0

Related Questions