SSRS report - show data from next financial period?

Epicor ERP 10
1

Hi

We have an SSRS report showing the quote pipeline for the current month (Financial Period).

Management would like to also see a summary for the next month’s totals.

How can I write this in?
Mid year it works fine using a simple “Show data where financial period = current period +1” but what happens when the current FY and period is 2019, p12 and you want to show 2020, p1 ? +1 won’t work there.

Do I need to do a ‘case when’ and assign every period its own numerical value? e.g.
2019,p12 = 12
2020, p1 = 13
2020, p2 = 14
for example?

Feel like I am missing something super obvious… it’s been a tough Monday!

2

Is this a BAQ report or a direct SQL?

3

Simple way, without using formulas, is to set a date criteria for the report. So you can have a Date From to Date To fields. That would take care of it. I assume you are using the QuoteHed as part of your report data set. That table has the DueDate.

On the other hand, if you must specify “Next Period”, it can probably be done with a few formulas.

4

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

5

how about

(CASE 
WHEN fp.FiscalYear = 2014 AND fp.FiscalPeriod = 1 then 1
WHEN (fp.FiscalYear = 2014 AND fp.FiscalPeriod >=2) then 0
ELSE ((fp.FiscalYear - 2015) * 12 + fp.FiscalPeriod +1) end)

edit

fixed for odd condition where FPs 2-12 for FY 2014 aren’t defined

1 Like
6

Can you just pick a starting spot?
like 2014 or even 2000… doesn’t really matter as long as you go far enough back.

select (fiscalyear- 2014) * 12 + fiscalPeriod
select (fiscalyear- 2000) * 12 + fiscalPeriod

2 Likes