SSRS - Totals of a parameter year, including 2 prior years

Question

Given the following data set:

DECLARE @temp TABLE (
[Company] [INT]
,[Year] [INT]
,[PAID] [DECIMAL](18,2)
,[Billed] [DECIMAL](18,2))
INSERT INTO @temp
SELECT 2,2005,8328364.00,639739.00
UNION ALL
SELECT 2,2006,12770109.00,599937.00
UNION ALL
SELECT 2,2007,883021.00,34179.00
UNION ALL
SELECT 2,2008,876531.00,31421.00
UNION ALL
SELECT 2,2009,1947464.00,63322.00
UNION ALL
SELECT 2,2010,717350.00,22072.00
UNION ALL
SELECT 2,2011,895174.00,28444.00
UNION ALL
SELECT 2,2012,1091637.00,36853.00
UNION ALL
SELECT 2,2013,699646.00,23355.00
UNION ALL
SELECT 2,2014,1623403.00,58649.00
UNION ALL
SELECT 2,2015,566479.75,20463.00
UNION ALL
SELECT 2,2016,128700.00,2990.84

select *
from @temp

Yields:

My SSRS 2014 report contains a Year parameter.

I'm having a difficult time with totaling based on certain years. I will need to have 3 totals lines, 1 of which will be a total of all years, and the other 2; a 3 year total and a 5 year total. The 3 and 5 year totals will be the total of the paid and billed fields based on the parameter year and 3/5 prior years, including the parameter year.

For example:

Parameter Year = 2015 SSRS output on totals lines should read:

Ideally, I would've liked to handle this totaling in T-SQL itself, but this unfortunately is only a small piece of the puzzle in this particular report and i had to write a complex solution in SQL and SSRS to make it work for the client. Basically, the report when finished will rely on a yearly totals data set without calculated total fields, and all totaling done within SSRS itself.

Can you create another dataset for the year totals based on the parameter year? — SS_DBA
Everything will need to be wrapped up in a main data set, with the exception of a few parameter data sets. The client wants multiple tables stacked together, grouped by company and then companies separated in excel by tabs. I can't find a good way to do that with multiple data sets. — JackalSnacks
I ended up solving this by adding a bool into the SQL result set, and then using this expression in the field: =SUM(iif(Fields!bool.Value = 1,cdbl(Fields!Paid.Value),0.00)) — JackalSnacks

JackalSnacks JackalSnacks · Accepted Answer · 2017-04-18T16:20:17

I ended up solving this by adding a bool into the SQL result set, and then using this expression in the field: =SUM(iif(Fields!bool.Value = 1,cdbl(Fields!Paid.Value),0.00))

SSRS - Totals of a parameter year, including 2 prior years

1 Answers