SSRS - calculate percent change with variable years and number of years

Question

Hopefully, I can explain this clearly! Our report allows user to select a variable number of academic years. They can be consecutive or not. In this case, say the user selects 2017, 2015, 2013. The report presents like this:

       AcYear     TotCredits     %Change
       2013       251            0.00
       2015       255            0.00
       2017       1102           0.00

Because the SQL that provides the datasource is assuming the previous year is one year prior. So 2015 is being compared against 2014, even though we're not selecting that info, which is why it's 0. 2017 is being compared against 2016, even though the user selected 2015. So, either in the initial SQL or in the report table expression, how would I go about getting the credits for the most immediately prior SELECTED academic year, no matter how many were selected, and then calculating the percent change based on that??

(If the user selected consecutive years, say 2017 and 2016, the data looks like this:)

        AcYear     TotCredits     %Change
        2016       458             0.00%
        2017       19            -95.85%

This is the self join to get the "previous" year:

from
    cte
    left join cte as prev1
        on cte.academic_year - 1 = prev1.academic_year
        and cte.subject          = prev1.subject

Thanks for any ideas!

Answer 1

You could try to use previous function to see whether it works or not. You need to make sure Year has been sorted. Below is design

select * from (select 2012  as year, 12  as amount
union all
select 2013  as year, 5  as amount
union all
select 2014  as year, 6  as amount
union all
select 2015  as year, 4  as amount
union all

select 2016  as year, 24  as amount)tt
where year in (@t)

Expression:

=iif(Fields!year.Value-previous(Fields!year.Value)=1,(Fields!amount.Value-previous(Fields!amount.Value))/iif(IsNothing(previous(Fields!amount.Value) ), 0,previous(Fields!amount.Value)),0)

Then you will get the result like below

Zoe

Answer 2

Surprisingly easy/elegant fix, just took me a long time to find it. The original import bit:

SELECT
    cte.academic_year
  , cte.subject
  , cte.subject_desc
  , cte.credits  as cur_credits
  , prev.credits as prev_credits
FROM
  cte
  LEFT JOIN cte as prev
    on cte.academic_year - 1 = prev.academic_year
    and cte.subject = prev.subject

The new improved code:

SELECT
    t.academic_year
  , t.subject
  , t.subject_desc
  , t.credits  as cur_credits
  , prev_credits = prev.credits

FROM
    cte t
    OUTER APPLY
        (SELECT top 1 credits from 
         cte 
         WHERE t.academic_year > academic_year
           and t.subject = subject
         ORDER BY academic_year desc) prev

It would have been very nice to use LAG, but alas, we are on SQL2008.