26 May How to convert a Sage 300 FR Report to Sage Intelligence
To get some of the legalities out the way, please note that this guide is provided ‘as is’ to assist with the conversion process and is not supported by Sage ERP Support as per Sage Support Coverage terms. This document is also not intended to serve as a guide to Sage 300 Financial Reporter or Sage Intelligence
Reporting. You are required to have an understanding of these products to successfully use it. You can find more learning material on both of them through the following:
What you Need:
- An installation of Sage 300 ERP running FR
- The FR report specifications you want to convert
- A Sage Intelligence Report Manager and Report Designer licence
- Microsoft Excel 2007 or later
Lists are retrieved from the General Ledger and contain key information like accounts and budget codes. They are used to create report rows and their fields can be added to formula arguments to extract specific data. The lists included in Sage 300 ERP Intelligence Reporting are Accounts, Currency,Account Structure, Account Groups, Account Segments, Budgets Sets and Segment Codes.
Formulas return balances or header information from your general ledger based on provided arguments which act as filters. Examples of arguments are Account Number, Current Year and Current Period. Each argument can be a cell reference, constant, or named range. All arguments for each formula are explained in the Sage Intelligence help file.
Formulas let you define columns for your layout where the type of formula used determines the purpose of the column, for example, to view the Actual or Budget amounts for a period.
A Reporting Tree allows you to define a reporting structure according to the hierarchy of your business and then view your data based on that hierarchy at the click of a button. Although it’s not necessary to create a Reporting Tree for a report, they do allow you to filter your data over and above the filters commonly used in formulas.
Drawing a Comparison Between FR and Sage
Each of the sections in this chapter looks at an aspect of an FR spec and how it translates to Sage Intelligence. The FR component or concept is first described and then an explanation is given of how this can be replicated or understood in Sage Intelligence.
In FR, any text entered in column A beginning with ‘..’ is treated as a comment and is excluded from the report when it is run. It is therefore excluded from the print area and will not show when the report is printed.
Sage intelligence does not have a comment identifier. If you want to include comments you can enter them as free text and set the print area of your layout to exclude them. They will then not be visible when you print your layout.
FR allows you to enter reporting headings using free text and also provides a number of functions to show company and fiscal calendar information. For example, =FR(“Coname”) returns the company name and =FR(“Year”) returns the Fiscal Year.
Sage Intelligence also allows you to enter report headings as free text and provides three formulas to display company and calendar information. These formulas can be dragged into cells which can be referenced by other formulas. They can also be added directly to other formulas as arguments.
Company Name Returns the full company name from your general ledger after applying the company code filter specified as an argument.
Current Year Returns the current fiscal year from your general ledger after applying the filters specified as arguments.
Current Period Returns the current period from your general ledger after applying the filters specified as arguments.
In FR column headings can be entered using free text.
In Sage Intelligence, column headings can be entered using free text. Excel functions can also be used to calculate calendar information that relates to column headings, for example, the periods in a Rolling Income Statement.
FR allows you to create default rows. A default row defines formats and functions that can be applied to one or more account rows. This saves you time by only having to type out arguments and functions once. The default row is then applied to all rows below it until another default row is defined.
Sage Intelligence doesn’t make use of default rows. It achieves efficiency by letting you drag lists and formulas into a layout. Lists can be edited by deleting rows or columns that aren’t needed or inserting blank rows or columns to group similar items.
You can drag formulas into a single row and edit its arguments, either using the Formula Bar or the Function Arguments window. Excel copy and paste functionality can then be used to quickly and easily copy the formula to all rows in a group of accounts, or the entire layout.
FR lets you create account rows by typing functions, arguments or free text into the cells of a row. Columns A to D are reserved for specific parameters and are excluded from the final report. Each column is discussed individually below. Columns E onwards are used to specify the actual information that will appear in the report.
Account rows in Sage Intelligence can be created by typing the necessary arguments and text into a row or by dragging lists and formulas into the layout and editing them. There are no reserved rows or columns so the layout you create is the end result. If there is information in your layout that is required but that you don’t necessarily want to see, you can hide the rows or columns. Alternatively you can define a print area to show only the rows and columns that you want to see in a printed version of your report.
Apart from entering comments and designating a default row, column A in a spec is reserved for entering account numbers. Individual account numbers, ranges or wildcards can be used. As an example, in the spec in the screenshot below the %% wildcard is used to tell FR to consider all accounts for the account row when the report is run.
In Sage Intelligence, account numbers or other account information, like account group codes for example, can be typed into a cell or can be obtain by dragging the appropriate list into your layout. Sage Intelligence allows for ranges, wildcards and mathematical functions to be used to group account rows. Account numbers are not limited to a specific column.
In a spec, column B is reserved for entering selection criteria. The range of accounts entered in column A is then filtered or restricted by the selection criteria.
In Sage Intelligence, each financial formula can be filtered by a number of criteria to return specific data. For example, the Actual Year to Date formula can be filtered by account, company, year, period, account group code, group category code, account structure code, balance type, currency code, currency type and reporting tree unit. These arguments can be entered into a formula as you would the arguments in a standard Excel formula, either in the Formula Bar or using the Function Arguments window.
In a spec, column C is used to remove rows that meet certain criteria from a report. For example, you can exclude zero balance rows by placing a Z against all account rows or in all default rows.
Sage Intelligence uses the ZeroingII add-in to remove rows that meet certain criteria from a report. The add-in is configured for a report in the Report Manager. The add-in however only works on a report at runtime and there is currently no functionality to dynamically hide or show certain rows once a report has been run out. Macros can be used to achieve this.
We do currently have a Dynamic Range Manager feature on our development backlog that may be released in the next version of Sage 300 ERP Intelligence Reporting. This feature will let you exclude zero rows from layouts in the Report Designer at the click of a few buttons.
Column D of a spec is used to determine whether accounts will be consolidated or not. For example, the value D will print a separate line for each account in the range of accounts and T will return a total for the range.
Sage Intelligence doesn’t currently have dynamic account ranges when using the Task Pane to design layouts (this is provided for in the Layout Generator through the Show Account Detail option). In other words, account groups can be created using ranges, wildcards and mathematical functions, but there is no way to automatically expand a group to list all included accounts. The Dynamic Account Manager feature mentioned in the previous section is intended to solve this problem.
To list all the accounts in a range you can drag the Accounts list into a layout. You can then group similar accounts by inserting blank rows between the different groups.
In FR, arguments can be added in column A of a default row to set the sort order of accounts in the default row area. Arguments can also be included in column A of an account row to determine the sort order of accounts. Certain arguments used in column D of a spec can also influence the sort order.
In Sage Intelligence, accounts or account groups are shown in the order that they are entered in a layout. If lists are used, the items in a list will have a predefined sort order. The sort order for each list is Financial Reporter to Sage Intelligence Conversion Guide Listed below given in the table below. Once a list has been dragged into a layout, Excel’s Sort function can be used to sort the list by one or more columns.
FR provides a number of functions to enter account description information. For example, =FRACCT(“ACCTDESC”) can be used to return the account description of an account. Alternatively free text can be used.
In Sage Intelligence, account description information can be entered using free text, otherwise the relevant information is included in lists. For example, the Accounts list includes the Account Description, Account Group Name and Group Category Description of an account.
FR includes a large number of functions to return balance or net amounts. They can be used in default rows or in individual account rows.
The formulas provided in Sage Intelligence to return financial amounts are listed with their descriptions in the table below.
In FR you can enter a minus sign in the function column of an account row to switch the sign of accounts in that row. For example, when using a FRAMTA formula in an income statement, you would switch the sign of all Revenue accounts in the spec.
In a similar way, you can switch the sign of Sage Intelligence formulas. To do so either place a minus sign at the start of a formula or multiply the formula by -1.
In a spec, Excel formulas are used to create subtotals.
Similarly, Sage Intelligence also uses Excel formulas for subtotals.
The FR functions FRTRN and FRTRNA retrieve totals from transaction history. Specs that use these functions allow you to drill down to transactions once a report is generated. You can identify cells to drill down on by the comment marker in them. Once you right click on a cell and select FR Drill Down, the transactions appear in a new grid style window.
You can drill down on all Sage Intelligence financial formula. To do this, right click on a cell containing a formula and select Drill Down. The results will open in a new sheet in Excel. If the formula you drill down on is returning a single balance, then the drill down will return the transactions that make up the balance in a sheet called Drill Transactions. If on the other hand the formula you are drilling down on is returning the accumulation of multiple balances, then the drill down will first return all the individual balances in a sheet called Drill Balance. From there you can drill down to the transactions of each balance. Note that you can only drill down on cells containing a single Sage Intelligence formula.
Translation of FR Functions to Sage Intelligence Formulas
In Financial Reporter, FRAMT, FRAMTA, FRTRN and FRTRNA are the primary functions that return financial amounts. The values they return are the same for a given argument. Their differences are summarised in the table below.
The following two tables show Net and Balance arguments for the FRAMT, FRAMTA, FRTRN and FRTRNA functions. This is not a complete list but a selection intended to guide you in learning how to translate FR functions. An equivalent Sage Intelligence formula or method of obtaining the result is provided for each argument. In many cases, additional Excel functions can be used to enhance the formulas. For example, using IF, QUOTIENT and MOD functions to determine the correct year, quarter, half or to date values.
The Process of Converting Reports from FR to Sage Intelligence
The high level process of converting FR specs can be seen in the diagram below. Each step is then dealt with in detail in the sections below.
Prepare the FR Specification for Conversion:
The first step is to generate an instance of the spec we want to convert to use as the basis for the conversion.
1. Open Sage 300 and sign in as ADMIN selecting SAMINC as the company
2. Open Statement Designer
3. Open the spec that you want to convert
4. Remove all row suppressing characters from column C of the spec. This ensures that all accounts are displayed when the report is run
5. Run the report using FR View. In the options screen do the following:
- Select a Year and Period that contain financial data
- Set the From account to the first account in the list of accounts and the To account to the last.
This will ensure that all accounts are taken into consideration when the report is run
- Check the boxes to include Audit Information and Formulas
- Click OK
The report will run and will display below the spec area.
6. Create and run a copy of the Sage Intelligence Financial Report Designer. At this point we want to open the Report Designer so that we can copy the FR report we have just generated to it.
- Open the Report Manager
- Create a copy of the Financial Report Designer
- Select the copy you have made and click Run
- When selecting the Year parameter for the report, make sure you select the same year that you selected when running the FR report
7. Copy the spec sheet from the spec workbook to the Report Designer workbook
a. Right click on the sheet name and select Move or Copy. The Move or Copy dialogue will appear
b. For To book, select the Report Designer workbook
c. For Before sheets, select (move to end)
d. Check Create a copy
e. Click OK
From this point on you will only be working with the Report Designer workbook.
8. Delete all rows that are part of the spec range
a. Select all rows that are part of the spec range
b. Right click on one of the selected rows’ handles
c. Select Delete
9. Lastly, clear the Print Area
a. Select the Page Layout tab on the ribbon
b. Click the Print Area icon
c. From the drop down, select Clear Print Area
Your report will look as follows:
Update the Report:
Now that the report is in the Report Designer, we can update it to make use of Sage Intelligence methods and functionality.
1. Replace the existing report headings with new ones
a. Delete the existing headings
b. Add new headings to column D of the layout. The new headings should include a report name and the titles: Company, Year, Period, Currency, Currency type and Reporting Tree Unit.
The information that is entered against these titles will serve as arguments for the Sage Intelligence formulas that are added to the layout.
c. Enter SAMINC next to Company and enter the same year and period you selected when running the spec report next to Year and Period respectively
2. Add new data column headings to the report
a. Copy the existing data column headings and paste them on the same row, to the right of current ones
b. Change the text of the new headings if need be
3. Enter account numbers for all account rows in column D of the layout. These can be copied the from the ACSEGVAL01 argument in column B of the layout
4. Delete columns A, B and C of the layout. It should now look as follows:
5. Replace the Account Descriptions now in column C with static values (i.e. Copy Paste Values)
The Account Descriptions now in column C are currently derived from FR formulas which we do not want to use going forward as they will only return values while Statement Designer is open.
a. Select all the Account Descriptions in column C
b. Press CTRL C on your keyboard to copy the selection
c. Right click on the first Account Description in the selection and click Paste Values under Paste Options in the menu that appears. This will overwrite the current Account Descriptions with static values.
6. Add Sage Intelligence formula to the layout.
For each new data column do the following:
a. Check the FR formula that is used in the corresponding FR data column
b. Look up the corresponding Sage Intelligence formula in the FR to Sage Intelligence formula translation tables provided in section 4.0 of this guide
c. Drag the formula from the Task Pane into the appropriate cell
d. Update the function arguments of the formula using the Function Arguments window
i. With the cell containing the formula selected, click the fx icon next to the formula bar. The Function Arguments window will open
ii. Set the Account field to the cell of the account number in column A of the row. Press F4 three times to absolute reference the column
iii. Set the Company field to the cell containing the value for the Company in the report headings. Press F4 once to absolute reference the cell
iv. Set the Year field to the cell containing the value for the Year in the report headings. Press F4 once to absolute reference the cell
v. Set the Period field to the cell containing the value for the Period in the report headings. Press F4 once to absolute reference the cell
vi. Set the CurrencyCode field to the cell that will contain the value for the Currency in the report headings. Press F4 once to absolute reference the cell
vii. Set the CurrencyType field to the cell that will contain the value for the Currency Type in the report headings. Press F4 once to absolute reference the cell
viii. Set the ReportTreeUnit field to the cell containing the value for the Reporting Tree Unit in the report headings. Press F4 once to absolute reference the cell
ix. Click OK to close the Function Arguments window
The formula should now pull through the correct value.
e. Copy the formula down to all account rows
f. Copy the format of the FR data column to the Sage Intelligence data column
i. Click the column handle of the FR data column
ii. Select the Format Painter tool from the Home tab on the Ribbon
iii. Click the column handle of the Sage Intelligence data column
The reason for doing this is that in FR, negative numbers aren’t indicated with a minus sign but rather with brackets around the number. If a Sage Intelligence formula that returns a negative number is placed in an un-formatted cell, the number will show a minus sign. Copying the formatting ensures that negative values in the Sage Intelligence data column are shows with brackets around them.
7. Switch the sign of Sage Intelligence formula in relevant account rows
You may notice that some of the values in the Sage Intelligence data column display as negative numbers when they show as positive numbers in their FR counterparts and vice versa. This is because in FR, the sign for these accounts was switched using a minus sign; for example, with Revenue accounts in an Income Statement when using an FRAMTA function. This can be confirmed by checking the sign of the FR formulas in the FR data column.
To solve this place a minus sign at the start of all Sage Intelligence formula whose sign needs to be switched.
8. Copy the formulas of all totals from FR data columns to Sage Intelligence data columns
9. Delete the FR data columns from the layout
10. Drag the Company Name, Current Year and Current Period formulas to their respective fields in the report headings. This way, when you next run out your report, the data will reflect the current company, year and period
The layout should look as follows:
11. Add any more customisation you would like to finish off the layout
Save the Layout to the Report:
The layout is now complete. The last thing to do is save it back to the copy of the Financial Report Designer you made earlier.
1. Go back to the Report Manager
2. Select the copy of the Financial Report Designer report you made
3. Click Save Excel Template on the Home tab
4. Select the Report Designer workbook from the list and click OK.
5. Enter a new name for the template in the Specify Template Name dialogue and click OK
6. If the template saves without a problem the Completed Successfully dialogue will appear. Click OK to close it
7. Rename the report in Report Manager if you’d like to give it a name that better describes the report
Now that the transfer process is complete, you may want to add some additional features to your report.
If you would like to hide or remove zero rows from your report every time it is run out then set up the ZeroingII add-in for the report. Information on the add-in can be found in the Sage Intelligence help file under Home > Report Manager > Maintaining Reports > Add-In Functions > Add-In: ZeroingII.
You may also want to create a reporting tree so that you can filter your layout based on the hierarchy of your business. Information on reporting trees can be found in the help file under Home > Report Designer > Reporting Trees > What are Reporting Trees? A number of videos can also be found on the Sage Intelligence YouTube channel. Once you’ve created your tree you can apply it to your layout by dragging a reporting unit into the Reporting Tree Unit field in your report headings. Because we set up the formulas to reference this cell, the values will automatically update to reflect the correct data.
Contact us at firstname.lastname@example.org for further information.