Sage 100

Is the goal to get the GL account balance for each fiscal period?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

No, I need to get the correct fiscal period start date in order to report by period correctly. I have multiple reports that I would not rather not have to hard code fiscal year dates since they change per fiscal year. 

------------------------------
Tina Meacham
RKL eSolutions, LLC
------------------------------


Original Message:
Sent: 04-12-2024 12:25
From: Doug Higgs
Subject: Sage Intelligence Fiscal Year 4-4-5

Is the goal to get the GL account balance for each fiscal period?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles

When I look at the connector for the fisical year detail on the prebuilt report I see the formula as 

DateAdd(mm,-1, "FP1"."PeriodEndingDate" + 1)

Could try an excel formula like this =DATE(LEFT(GL_FiscalYearDetail.PeriodEndingDate,4),MID(GL_FiscalYearDetail.PeriodEndingDate,5,2),RIGHT(GL_FiscalYearDetail.PeriodEndingDate,2))-1

if the connector formula isn't working right. What were the results you were getting. Maybe we can tell by the pattern what the formula is doing to see how to address it.

When I look at the connector for the fisical year detail on the prebuilt report I see the formula as 

DateAdd(mm,-1, "FP1"."PeriodEndingDate" + 1)

Hi Todd

In test ABC if you change the fiscal year to something like the attached excel, then run any of SI fiscal year reports. The beginning date is wrong. I adjusted days, but it worked on one month but not the other months. This is fiscal year my client has. 

Could try an excel formula like this =DATE(LEFT(GL_FiscalYearDetail.PeriodEndingDate,4),MID(GL_FiscalYearDetail.PeriodEndingDate,5,2),RIGHT(GL_FiscalYearDetail.PeriodEndingDate,2))-1

if the connector formula isn't working right. What were the results you were getting. Maybe we can tell by the pattern what the formula is doing to see how to address it.

When I look at the connector for the fisical year detail on the prebuilt report I see the formula as 

DateAdd(mm,-1, "FP1"."PeriodEndingDate" + 1)

Don't do the calc in connector. Do it in Excel and have multiple years from GL_FiscalYearDetail, then calc based on previous PeriodEndingDate by adding one day to it. I created the same periods in my test system for year 2023 and 2024.

Hi Todd

In test ABC if you change the fiscal year to something like the attached excel, then run any of SI fiscal year reports. The beginning date is wrong. I adjusted days, but it worked on one month but not the other months. This is fiscal year my client has. 

Could try an excel formula like this =DATE(LEFT(GL_FiscalYearDetail.PeriodEndingDate,4),MID(GL_FiscalYearDetail.PeriodEndingDate,5,2),RIGHT(GL_FiscalYearDetail.PeriodEndingDate,2))-1

if the connector formula isn't working right. What were the results you were getting. Maybe we can tell by the pattern what the formula is doing to see how to address it.

When I look at the connector for the fisical year detail on the prebuilt report I see the formula as 

DateAdd(mm,-1, "FP1"."PeriodEndingDate" + 1)