Creating a varying number of column results in SSRS based on a range parameter

Question

I have an SSRS report that I wrote a few years ago. I kind of built it like a time bomb. I thought it would stop working in 2022, and I was hoping it would be somebody else's problem by then, but I think it will stop working earlier than I predicted, perhaps in 2018 or 2019. So I feel slightly obliged to fix it if possible. I can easily modify what I've got to add another 5 or 10 years to its lifespan, but I'm wondering if I could have done this differently so that the number of Year columns in the report would vary based on the number of the Years passed with the Year Range parameter.

The report is called Contract Funding by Year and it is designed to report how much funding a Contract received in each year of the contract life. To figure this out, I calculate how many months the contract was active. Then I calculate what the monthly burn rate is (funding/months). Then I calculate for how many months each contract is active for each particular year. Finally, for each month in each specified year, I use the burn rate times months active to calculate how much each funding each contract received per year. Easy, right? :)

When running the report, along with a bunch of filtering parameters, the main parameter that the user must select is a range of years to examine. The possible years start in 1982 (when we opened) and run to 2022 (40 years). Here is where the problem lies; in the way that I created the 40 year range.

The RDL is powered by a Stored Procedure. The SP has 40 year variables declared, which represent the total possible number of years that the report can return. Each of these variables populates a unique column in the SP. In the RDL, there are 40 columns that correspond to each of the SP columns.

They say a picture is worth a thousand words. I hope so!

Here is a piece of the Stored Procedure. You can see the 40 year variables and the 40 year columns.

Here are the results of the SP. There are 38 year columns that are empty for every row.

Here is the RDL. Each of the 40 years is represented by a column. They are either hidden or shown based on the visibility property.

And here is the result of the report.

So, to recap, my question is whether I could have approached this problem differently. The business need was to have a report that can generate a variable number of year output columns based on the user's selection of a year date range.

Thanks, in advance, for your help! Please ask if anything needs clarification.

Jennifer

---

UPDATE: Here is my current code:

declare @StartYear datetime  = '1/1/2009', @EndYear datetime = '12/31/2012'
Begin 
select 
cf.ContractFundingID, cf.StartDate, cf.EndDate
from ContractFunding cf 
where cf.StartDate <= @EndYear and cf.EndDate >= @StartYear
End
GO

I get results like this:

ContractFundingID | StartDate | EndDate 
7363 | 2010-04-01 | 2011-02-28 
8767 | 2009-02-01 | 2011-12-31 
7466 | 2010-07-01 | 2011-06-30 
7503 | 2010-04-01 | 2011-03-31 

But what I want is something like this:

ContractFundingID | StartDate | EndDate | Year 
7363 | 2010-04-01 | 2011-02-28 | 2009 
7363 | 2010-04-01 | 2011-02-28 | 2010 
7363 | 2010-04-01 | 2011-02-28 | 2011 
7363 | 2010-04-01 | 2011-02-28 | 2012 
8767 | 2009-02-01 | 2011-12-31 | 2009 
8767 | 2009-02-01 | 2011-12-31 | 2010 
8767 | 2009-02-01 | 2011-12-31 | 2011 
8767 | 2009-02-01 | 2011-12-31 | 2012 
7466 | 2010-07-01 | 2011-06-30 | 2009 
7466 | 2010-07-01 | 2011-06-30 | 2010 
7466 | 2010-07-01 | 2011-06-30 | 2011 
7466 | 2010-07-01 | 2011-06-30 | 2012 
7503 | 2010-04-01 | 2011-03-31 | 2009 
7503 | 2010-04-01 | 2011-03-31 | 2010 
7503 | 2010-04-01 | 2011-03-31 | 2011 
7503 | 2010-04-01 | 2011-03-31 | 2012 

What I want in the SQL is for each row to represent the intersection of a Contract Funding record against the date range I'm inspecting, so that I can manipulate each year's funding for each contract funding record, which can span multiple years.

Answer 1

It would have been a much tidier solution to use a matrix. This would allow you to return an extra field to say which year the data relates to, and would automatically create the number of columns required. If any years had no data then the use of a CROSS JOIN in the underlying query could have filled the blanks.

I think a bit more research might be beneficial to you before you just try to patch the existing issue

• Have a look here for a description of Matrices

• CROSS JOINs are explained here

• And this is another SO question on how to use CROSS JOINS to fill in gaps

I hope the above help. Please ask if you require more information.

UPDATE

In response to the desired output in the question above, here is an example using a CROSS JOIN to generate the table required.

First, you need a list of years between the dates to search upon. Using a common tbale expression you can hereby get all the dates between @Start and @End dates

DECLARE @Dates TABLE (YearNo INT)
;WITH n(n) AS
(
    SELECT YEAR(@Start)
    UNION ALL
    SELECT n+1 FROM n WHERE n < YEAR(@End)
)
INSERT INTO @Dates(YearNo)
SELECT n FROM n ORDER BY n

This will give a result

YearNo
------
 1999
 2000
 (etc)

You can then use the cross join mentioned previously as follows

SELECT v.ContractFundingID v.StartDate, v.EndDate, d.YearNo
FROM  @VALUES v
CROSS JOIN @Dates d

To give a result of

ContractFundingID | StartDate  | EndDate    | YearNo
------------------+------------+------------+--------
7363              | 2010-04-01 | 2011-02-28 | 1999
8767              | 2009-02-01 | 2011-12-31 | 1999
7466              | 2010-07-01 | 2011-06-30 | 1999
7503              | 2010-04-01 | 2011-03-31 | 1999
7363              | 2010-04-01 | 2011-02-28 | 2000
(etc)

I hope this helps. Ask if you need further assistance