Written by Lorna Link, GP Product Manager
An interesting find from Brian Bell, GP Implementation Consultant – A client just reset their GP fiscal year end from 3/31 to 12/31 and found that Management Reporter doesn’t follow the same rules as GP.
We used a 9 month stub year GP’s fiscal year label went from 1996 to 1995 for the oldest historical year. Management Reporter CALCULATES its fiscal year label from the last date of each fiscal year, so it’s using 1996 still as the oldest fiscal year and not using GP’s fiscal year labels at this point.
You can modify the FiscalCalendarView by running the following script against the DDM database:
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER VIEW [dbo].[FiscalCalendarView] AS SELECT TOP 100000 RANK() OVER(ORDER BY PER.OrganizationId, FY.Id, PER.Starts, PER.Ends) as PeriodSequenceNumber,
RANK() OVER(PARTITION BY FY.id, PER.OrganizationId ORDER BY PER.Starts) as PeriodNumber, PER.Id, PER.Starts as PeriodStart, PER.Ends as PeriodEnd, FY.Starts as YearStart, FY.Ends as YearEnd, CAST((SELECT MIN(Years.Name) FROM FiscalYear Years WHERE Years.OrganizationId = FY.OrganizationId) + DENSE_RANK() OVER(PARTITION BY PER.OrganizationId ORDER BY FY.Starts) – 1 AS Int) As FiscalYear, IsHistorical, PER.IsClosed, PER.OrganizationId FROM Period PER INNER JOIN FiscalYear FY ON PER.FiscalYearId = FY.Id AND PER.OrganizationId = FY.OrganizationId ORDER BY PER.Starts GO
Note that if you have to re-create the DDM database for any reason, you will need to re-run the script provided above.