November 24, 2010
Often when creating reports we’re asked to show the current year vs. last year or select the current fiscal year or period as a date range for values returned. Sometimes dates can be hard-coded, especially when the fiscal year is the calendar year. But some reports can be made more dynamic by using the fiscal periods set up in Dynamics GP. It can also help simply to see what is set up in GP at times, so that you know how to best structure the report. Below are two queries that can help with this.
All fiscal years:
SELECT YEAR1 [Year], FSTFSCDY First_Day, LSTFSCDY Last_Day, NUMOFPER Number_of_Periods, CASE HISTORYR WHEN 0 THEN 'Open Year' WHEN 1 THEN 'Historical Year' END Year_Status FROM SY40101 ORDER BY YEAR1
Sample results:
Fiscal periods for the current year:
SELECT
D.PERIODID Period_Number,
D.PERNAME Period_Name,
D.PERIODDT Starting_Date,
D.PERDENDT Ending_Date,
D.YEAR1 Fiscal_Year
FROM SY40100 D
INNER JOIN
SY40101 H
ON H.YEAR1 = D.YEAR1
WHERE
D.FORIGIN = 1 AND D.PERIODID <> 0
and GETDATE() between H.FSTFSCDY and H.LSTFSCDY
ORDER BY D.PERIODID
You can change the line in blue above if you need to see a different year. Sample results:
Victoria,
Sorry to ask, but you mention above you can change the line in blue to see a different year. What is the code for a different year?
Mark
Mark,
If you want to specify the year, you could use the following:
and D.YEAR1 = 2010-Victoria