CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012ssrs-tablix
Parkin
Parkin

Reputation: 13

YTD in SSRS Matrix

enter image description hereI need some suggestions on how do i calculate the YTD . i have highlighted in yellow the YTD values that i want to calculate in my report .This question is continuation of question i posted before to calculate the Totals tab.- Totals in Matrix in SSRS

I have also mentioned the formula its using to find out those values.enter image description here

Upvotes: 0

Views: 277

Answers (1)

Jesse
Jesse

Reputation: 873

Well, after much thought, I think I would do this in my SQL because I am just more comfortable there. Here's a below example of how I would do that:

declare @tbl table
(
    Country varchar(2),
    Type varchar(1),
    Names varchar(5),
    Qty int,
    DateValue datetime
)

insert into @tbl
values ('US', 'A','BB-1',10,'12/01/2015'),
       ('US', 'A','BB-2',20,'12/01/2015'),
       ('IN', 'A','BB-1',0,'12/01/2015'),
       ('IN', 'A','BB-2',10,'12/01/2015'),
       ('US', 'A','BB-1',30,'12/01/2016'),
       ('US', 'A','BB-2',40,'12/01/2016'),
       ('IN', 'A','BB-1',50,'12/01/2016'),
       ('IN', 'A','BB-2',70,'12/01/2016')

select 
    t.*,
    YearEnd.YearEnd ,
    case when YearEnd.YearEnd = 0 then null else (t.Qty - YearEnd.YearEnd)/convert(decimal(36,4),YearEnd.YearEnd) end as YearEndCalc
from @tbl as t
left join 
(
    select 
        year(DateValue) as YearValue,
        Type,
        sum(Qty) as YearEnd
    from @tbl
    where 
        month(DateValue) = 12
    group by 
        year(DateValue) ,
        Type
) as YearEnd
on YearEnd.YearValue = year(t.datevalue)-1
and YearEnd.Type = t.Type

Upvotes: 0

Related Questions