by rwalliker » Wed Jan 17, 2018 11:38 am
EXCEL REPORTS You can create custom spreadsheets for Reporting or Forecasting. These can be opened within the program where you can make use of an additional menu bar for manipulating and saving the layout or for refreshing reports or exporting data back into the database. These are ordinary excel spreadsheets that are stored in the cloud. The instructions on a sheet are contained in the first 7 columns and the first and second rows (highlighted yellow in the new template file) of a named range beginning with 'NL' or '_NL'. These instructions tell 4CastPro what data to bring back or write back to the database
Adding a new report Excel Reports can be created offline and uploaded to the model by first clicking the “Choose File” button and then clicking the “Upload” button There is also an 'Add Report' button. You should enter a name for the report and choose from the dropdown a template if you wish. If the “No” dropdown is selected a general template file is added to the list of reports which can be used to build your report.
Against each report there are 4 options • RefreshnSave – Refreshes report from database and saves data in one operation • Download – Downloads file to desktop • Delete • Open • MakeFav – Adds report to main menu
To copy a report – simply download the report you want to copy and then upload with a different name
Menu on opening a Report:
Refresh – Refreshes data from database SavetoDbase – Saves data to database for a Writeback Report Show Instructions – Shows Columns A-F and Rows 1 and 2. You are then provided with the following facilities that allow you to amend a report or build it from template:
Custom Excel Reporting (Refresh Report via “Refresh” Button on Menubar)
Custom spreadsheets import from the database to a named range eg "NL" within the spreadsheet. The following columns (fields) are required within the "NL" named range: Left Column "Import" - Imports where set to "I" or "INEG" for reversed sign You need ACCT, ACCTGP0, TYPE and CC columns(Columns 2-6 of your named range) Data is brought back from the database by 1)Specifying the CC and either ACCT, ACCTGP0 or TYPE in columns 2-6 2)Adding the following Time Series Month or Special Selection fields on Row one of your named range: CYFCT_PER LYACT_PER CYBUD_PER CYFCT_1 to 12 CYFCT_YTD LYACT_YTD CYBUD_YTD LYACT_1 to 12 CYFCT_TOT LYACT_TOT CYBUD_TOT CYBUD_1 to 12 These include Special Selection Summary fields as well as the ordinary database fields eg current period year to date (CYFCT_YTD) 3)Optionally you can specify a forecast version on Row two of your named range eg V1,V2 beneath the Times Series eg V1 will bring back data from Version1 of the forecast 4)Finally hitting 'Refresh' on the menubar to bring latest data back from database The named range needs to include all I columns and all selected data fields You can include rows that are not I rows and columns that are not data columns for Formatting headings etc The yellow report definition section provides the spreadsheet with the details of where to obtain the data, whether to show full units or only 1000’s etc This yellow section can be hidden or locked to provide users with a cleaner view of the report and to stop them amending it if that is what is required.
Custom Excel Forecasting (Writeback via “SavetoDbase” Button on Menubar) The custom excel spreadsheets can also writeback to the database as well as import data so can be used to perform excel based forecasting. Writeback reports allow you to setup spreadsheets at Account level that write back to the database by populating the Export column of the spreadsheet (with E or ENEG) and hitting 'SavetoDbase ' on the menubar when ready to send data back to the database eg SalesForecast or OverheadForecast templates. You can only export at ACCT level for an actual Company(not a node) as the system needs to know where to post data back into the database In the current year balances are only written back to future months preventing you overwriting imported balances. In the case of Statistical data this can be overridden by Putting 'STA' in the TYPE column You save data to the database from a named range "NL" within the spreadsheet by hitting 'Exportxls' on the add-in. The benefit is that you can take existing forecasting spreadsheets and integrate then into your 4Cast model in a controlled manner: -You can protect the spreadsheet leaving only data entry cells available for input. -You can hide the top row and first 7 columns that control which lines import or export to the database.
Meeting more complicated Scenarios 1)Multiple Ranges You can set-up up multiple NL ranges NL, NL1, NL2,_NL,_NL1 etc in one spreadsheet to allow you to say setup a custom board pack spreadsheet. You can simply add a new named range e.g. NL6 for a new page or range in the workbook. The named ranges only have to start with 'NL' or '_NL' so you can give them meaningful names such as 'NL-MainP&L'
2)Import/Export Column Instructions As well as the standard I,E,INEG and ENEG there are other options Import Instructions: I/INEG-For importing (+/-) balances IINV-For importing the Invoiced figures on accounts set up on an accruals basis IADD/IDIS-For importing Fixed Asset Additions and Disposals Import Instructions: E/ENEG-For exporting (+/-) balances. EINV-For exporting the Invoiced figures on accounts set up on an accruals basis EADD/EDIS-For exporting Fixed Asset Additions and Disposals
TYPES OF REPORT FIXED FORMAT This is the standard custom report selecting lines on basis of CC and either ACCT,ACCTGP0 or TYPE You can build a report on an alternative ACCTGP by changing ACCTGP0 to ACCTGP1,ACCTGP2 etc
DOUBLE CRITERIA If building a report with rows selected by ACCTGP then you can use a double Criteria ie Where ACCTGP0="Main Sales" and ACCTGP1="001FCT" This is done by renaming the TYPE column ACCTGP1
CROSSTAB REPORT This is capable of producing for example a by location report If for example ACCTGP1 contained locations then you would populate cell E2 with ACCTGP1 and use Row 2 to define the required location
User Access to Custom Reports Where a user other than a User in the Admin role adds a report the report is Prefixed with the Username and is only visible to that user.
Dashboard The Dashboard feature is a selectable option under Set-up Options/Other It is basically a custom excel spreadsheet named Dashboard.xlsx You can use all the same custom reporting techniques to bring through data from the database Any excel graphs need to be at the top of the spreadsheet. They will then appear on the dashboard