Hi - I think I’ve solved it, but with a very lengthy ‘case when’

Thanks Jason - It’s an SSRS report

Paul - It’s showing detailed info from the current month but they want to see summary totals for the coming months on the same screen, so they set the parameter to display the current month and want it to automatically pull in the totals for the next 3 months up in the corner, if I add multiple parameters for them to fill in they’ll gt annoyed (first world problems, I know!)

I’ve added this to my existing sprawling SQL statement and I now use the ‘fp’ column to filter on, so if the current fp is

‘55’ I can set the summaries to pull in totals for 55+1, 55+2, 55+3

select

(CASE WHEN

fp.FiscalYear = ‘2014’ and fp.FiscalPeriod = ‘1’ then ‘1’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘1’ then ‘2’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘2’ then ‘3’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘3’ then ‘4’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘4’ then ‘5’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘5’ then ‘6’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘6’ then ‘7’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘7’ then ‘8’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘8’ then ‘9’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘9’ then ‘10’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘10’ then ‘11’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘11’ then ‘12’

when fp.FiscalYear = ‘2015’ and fp.FiscalPeriod = ‘12’ then ‘13’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘1’ then ‘14’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘2’ then ‘15’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘3’ then ‘16’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘4’ then ‘17’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘5’ then ‘18’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘6’ then ‘19’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘7’ then ‘20’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘8’ then ‘21’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘9’ then ‘22’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘10’ then ‘23’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘11’ then ‘24’

when fp.FiscalYear = ‘2016’ and fp.FiscalPeriod = ‘12’ then ‘25’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘1’ then ‘26’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘2’ then ‘27’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘3’ then ‘28’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘4’ then ‘29’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘5’ then ‘30’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘6’ then ‘31’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘7’ then ‘32’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘8’ then ‘33’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘9’ then ‘34’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘10’ then ‘35’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘11’ then ‘36’

when fp.FiscalYear = ‘2017’ and fp.FiscalPeriod = ‘12’ then ‘37’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘1’ then ‘38’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘2’ then ‘39’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘3’ then ‘40’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘4’ then ‘41’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘5’ then ‘42’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘6’ then ‘43’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘7’ then ‘44’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘8’ then ‘45’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘9’ then ‘46’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘10’ then ‘47’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘11’ then ‘48’

when fp.FiscalYear = ‘2018’ and fp.FiscalPeriod = ‘12’ then ‘49’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘1’ then ‘50’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘2’ then ‘51’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘3’ then ‘52’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘4’ then ‘53’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘5’ then ‘54’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘6’ then ‘55’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘7’ then ‘56’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘8’ then ‘57’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘9’ then ‘58’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘10’ then ‘59’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘11’ then ‘60’

when fp.FiscalYear = ‘2019’ and fp.FiscalPeriod = ‘12’ then ‘61’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘1’ then ‘62’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘2’ then ‘63’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘3’ then ‘64’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘4’ then ‘65’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘5’ then ‘66’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘6’ then ‘67’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘7’ then ‘68’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘8’ then ‘69’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘9’ then ‘70’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘10’ then ‘71’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘11’ then ‘72’

when fp.FiscalYear = ‘2020’ and fp.FiscalPeriod = ‘12’ then ‘73’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘1’ then ‘74’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘2’ then ‘75’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘3’ then ‘76’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘4’ then ‘77’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘5’ then ‘78’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘6’ then ‘79’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘7’ then ‘80’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘8’ then ‘81’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘9’ then ‘82’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘10’ then ‘83’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘11’ then ‘84’

when fp.FiscalYear = ‘2021’ and fp.FiscalPeriod = ‘12’ then ‘85’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘1’ then ‘86’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘2’ then ‘87’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘3’ then ‘88’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘4’ then ‘89’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘5’ then ‘90’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘6’ then ‘91’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘7’ then ‘92’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘8’ then ‘93’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘9’ then ‘94’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘10’ then ‘95’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘11’ then ‘96’

when fp.FiscalYear = ‘2022’ and fp.FiscalPeriod = ‘12’ then ‘97’

else ‘0’ end) as FP

FROM Erp.FiscalPer AS fp