mercredi 22 juin 2016

how to perform division with multiple conditions in a group SSRS

How can I calculate change YearMonth over YearMonth 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

Aucun commentaire:

Enregistrer un commentaire