CODEWITHSUNDEEP
t-sqlreporting-servicesssrs-2008
user2146212
user2146212

Reputation: 93

Calculate grand total from different years

I was wondering how I would go about calculating the grand total of a certain count base on a year parameter set up in SSRS. When a certian year is selected the previous counts from all the previous years would add up with the current count. Before I used a @year parameter in my stored procedure and it worked in SSRS but this time I want to try hardcoding it then just have a year parameter in SSRS.

Here's a sample of my code that I'm working with at the moment:

select
sum(MITotal.Count) as 'Yearly Count',
year(c_date) as 'year'

from 
(select 
    count(*) as Count,
    pol1.C_STATE,
    month(pol1.c_Date) as Month,
    year(pol1.c_ate) as Year,
    left(datename(mm, C_Date), 3) + ' ' + cast(year(C_Date) as char(4)) as MonthYear
    from contract pol1
    where 
        pol1.C_STATE='wa' 
    group by pol1.C_STATE,month(pol1.c_Date),year(pol1.c_Date), left(datename(mm, C_Date), 3) + ' ' + cast(year(C_Date) as char(4))) MITotal
    group by MITotal.Year

this would return:

2   2009
5   2010
6   2011

How would I go about adding onto the code so that whenever, I choose say 2010 as the parameter year it would add 5+2 or 2011 and it would add 5+6+2 as the grand total. Any pointers would be appreciated.

Upvotes: 0

Views: 105

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

Sum on a CASE statement based on the year parameter like so:

SUM(CASE WHEN Year(c_Date) <= @Year THEN MITotal.Count END) AS 'Historical Total'

Upvotes: 2

Related Questions