I recently started working with Sage Intelligence. On my very first report I hit a snag and couldn't figure out how to present the period end date in the heading of the report in the format: ""For the xx Period(s) ending mmmm dd, yyyy"" . This seems like a no brainer. Isn't there a formula for this? To make it worse my project required period end dates ending on irregular intervals. March ended on 03/28/2014.
The period end dates are available in the template on a hidden worksheet called ""FiscalYearDetail"" but the lookup is difficult because the Period, Year, and Company are in 3 separate columns.
Here is a solution that works. It adds another column to the ""FiscalYearDetail"" tab with a single cell containing the combined values: Company, Fiscal Year, Fiscal Period.
Launch the Connector Module.
Select the MAS90 ODBC Driver Connection Type.
Select the SageMASConsolidation Connection.
Select the ""Report Designer Add-In - Fiscal Year Detail 1-0"" Container.
Right Click and add an expression.
Expression Name: FiscalPeriodKey.
Expression Type: Excel Formula.
Expression Source: CompanyCode & FiscalYear & FiscalPeriod.
Apply the changes.
Repeat these changes to any other Fiscal Year Detail Container you might want to use.
The next time the report is generated, a new column will appear on the ""PeriodEndDetial"" tab. This column will contain the combined companycode, Fiscal year, and fiscal period. EX: ABC201005. Now use the INDEX / MATCH Functions in Excel to lookup the correct row in the PeriodEndDetail tab. Column 5 is the Period End Date and Column 4 is the Period Begin Date.
In the Match statement below: $B$5 is the lookup key I want to find ""ABC201005"".
=INDEX(FiscalYearDetail!PeriodEndDate,MATCH($B$5,FiscalYearDetail!FiscalPeriodKey,0),0)
In the Example below $D$3 is the period code and $D$2 is the date value returned by the index command above.
=""For the "" & $D$3 & ""Period(s) Ending "" & TEXT($D$2,""mmmm dd, yyyy"")