Sage 100

Anyone have a formula for putting the Period Ending date on a Sage Intelligence report? (with Report Designer Add-in)

Found this on Sage City - change the ABX to the actual company code: 1. Getting just the Month and Year: =""For the Month Ending: "" & TEXT(DATE(GLCurrentYear(""ABX""),GLCurrentPeriod(""ABX""),1),""mmmm, yyyy"" Result: For the Month Ending: January, 2013. 2. Getting the Month, Year and the last day of the Current GL Month: =""For the Month Ending: "" & TEXT(DATE(GLCurrentYear(""ABX""),GLCurrentPeriod(""ABX""),DAY(DATE(GLCurrentYear(""ABX""),GLCurrentPeriod(""ABX"")+1,0))),""mmmm dd, yyyy"") Result: ""For the Month Ending: January 31, 2013.

OK, tell me if this looks like it will only work if you are on calendar periods. My client, of course, is on fiscal periods....

Its referring to current year and current period I don't see why being on fiscal calendar would make a difference, but please let me know.

Isn't using Current Period as the month indicator, isn't it? And that would be wrong if Current Period = 6 but the month is actually December, etc.

OK, here it is (you then have to format it in your statement) - this is based on Fiscal Year being in A1 and Fiscal Period being in A2 on your spreadsheet {=INDEX(FiscalYearDetail!$E2:$E400,MATCH(($A$1&$A$2),FiscalYearDetail!$B2:$B400&FiscalYearDetail!$C2:$C400,0),0)} Note the curly brackets - this is because of the arrays being used - to get those, you have to press CTRL/SHFT/ENTER at the end of the formula. EACH time you modify it. This can be used on any Report Designer Add-In report and does not require any Connector changes. The range thru 400 is arbitrary and should probably be bigger - it's got to cover all of their fiscal years.

That's a more elegant way to do it than I would have done it Beth. Thanks for the heads up!

Just a heads up that when using fiscal period (not natural month ends) and year does not end on 12/31, sage intelligence fiscal year detail tab defaults to a 1/1 start date because of how it is pulling data from sage 100 fiscal detail table. Tried mucking with sql statement but couldn't figure it out, so I created my own fiscal detail tab from the standard tab and calculated my own start dates as +1 day from previous end date. Became an issue when needing to add weekly columns on f/s by pulling xaction detail into report deigned add-in template. Big in retail and restaurants.

Good to know, @MikeFitzgerald - do have a restaurant doing that.

Here's my inelegant solution: 1. Created a tab after the notes tab. 2. Add a table to it with the fields, Period, Month, Descr and Date. 3. Name this table Periods_Table 4. Current Period and Current Year fields are in the report sheet in cells B6 and B7, respectively. 5. Formula to generate the report title is, =""For the ""&VLOOKUP(TEXT($B$6,""00""),Periods_Table,3,FALSE)&"" Ending ""&VLOOKUP(TEXT($B$6,""00""),Periods_Table,4,FALSE)&"" ""&$B$7 Like I mentioned earlier, it's a little tedious to set it up but it works.

Of course, hide any cells/columns/rows/tabs you don't need to see.

Try the attached as a control sheet for changing report periods every month and automating the headers. All the reports point to Year and Period values on this sheet and any header text you need auto-updating points to the appropriate cell with the info you need.