So many users of Sage products ask us what options are available to them for Consolidated Financial Reporting. Whether you have an existing set of reports that you would like to convert and improve, like FRX for MAS90/200/500 or Financial Reporter for Accpac, etc. or if you just want to have a set of reports matching your wild imagination, this video should give you an idea of what is possible using Sage Business Intelligence.
Archive for the ‘Sage Accpac Intelligence’ Category
The Intelligence Store was built in response to our customers requiring immediate access to reports.
It may have exactly what you need: http://theintelligencestore.com/
There are different rules for the licensing required across Sage platforms for permitting the import of reports and the following rules apply to each platform:
- Sage Simply Accounting: Any Sage Intelligence license structure will permit the import of reports
- Sage Peachtree: Any Sage Intelligence license structure will permit the import of reports
- Sage Accpac: At least 2 Report Managers OR 1 Connector License is required to permit the importing of reports
- Sage MAS90/200: At least 2 Report Managers OR 1 Connector License is required to permit the importing of reports
- Sage MAS500: At least 2 Report Managers OR 1 Connector License is required to permit the importing of reports
All the reports in the IntelligenceStore are used with Sage Intelligence which is the reporting product of choice, shipped with all the Sage Accounting platforms listed in the IntelligenceStore. After purchasing a report from the IntelligenceStore, you simply import the report into Sage Intelligence and begin using it.
Sage Intelligence reports generate into an Excel template and MS Excel is therefore a prerequisite to using Sage Intelligence. For all the reports in the IntelligenceStore, except where otherwise noted, Excel 2007 or higher is required.
Another tool that does no harm when using Sage Intelligence is good Excel skills. These are not a prerequisite for using reports in the IntelligenceStore but with good Excel skills you can easily improve reports that you download.
The Report Designer which simplifies the creation of Financial Statements using SAI, has been available for MAS users for some time and is imminently available to users of SAI in the Accpac environment too. In summary, it provides a user-friendly Excel interface to create and modify Financial Reports.
For those consultants who prefer to be prepared for new technology, have a gander at these videos: http://community.alchemex.com/group/excelgeniereportdesigner
I thought I would sharpen your appetite for more Sage Intelligence Reporting muscle. For those consultants familiar with FRX reporting trees, this new feature is said to match and exceed those. For those consultants not familiar with reporting trees, they provide a fast way of slicing GL Accounts into Natural Account and Segments and reporting by ranges on these dimensions.
I am not sure of the ‘to-market’ date yet but this provides an idea of what is to come………
Migrating existing Financial Reports from FRX and FR (in the case of Accpac) can be done with the help of this video.
In producing this video I found it hard to know how much detail to include and how much to exclude. I think I leaned more toward providing a brief overview of the 6 steps required to do the basic migration and I leave the subsequent creativity required by good BI solutions to you. I hope it helps you…………………
Sometimes it just feels right to show your customers a few screen shots of the standard or close to standard report-set that accompanies Sage Intelligence. The workbook below is a free download and it may assist you in these situations.
The MAS 90, 200 and 500 community have had the luxury of access to the ‘Report Designer’ and the Accpac community will soon possess this luxury too. It is a neat tool that affords the user of Sage Intelligence an interactive reporting experience. I have included a snapshot below and you can expect much more content on this in future blog posts.
- Report Level Security at installation will by default be switched off and must be switched on within the Security Manager to take effect
- Only users added to the Administrative role will be allow to Add/Edit/Delete reports within the Report Manager
- The list of Users within Accpac will be synchronised with the list of users in this Security Manager.
How can I secure my Sage Accpac Intelligence reports and modules?MODULE LEVEL SECURITYEach SAI module that can be launched from the ACCPAC UI will follow the standard ACCPAC Roles, Users and Authorization processes that ACCPAC uses for securing all its other modules. Using the the standard security screens in ACCPAC it will be possible to Allow or Restrict given users (through Roles) from launching the relevant SAI modules.REPORT LEVEL SECURITYTo provide more granular level security at a report level, an Accpac Intelligence Security Manager is provided that allows users to be granted access to run specific reports only. For this the following rules will apply:- Report Level Security at installation will by default be switched off and must be switched on within the Security Manager to take effect Only users added to the Administrative role will be allow to Add/Edit/Delete reports within the Report Manager The list of Users within Accpac will be synchronised with the list of users in this Security Manager.
I know that this diagram might be intuitive for the readers of this blog….after all, only the smartest audience accesses it. I found it in an article written by the Alchemex team and thought it might be of assistance to those readers with visual preferences. It suggests an approach to thinking through the solution delivery process.
- use the Sage Accpac Intelligence Connector to create a connection to a data source
- add a data container which may constitute a table, join, SQL query or existing view
- add expressions (database fields) which are exposed in the container referred to above
- now fire-up the Report Manager and create a folder
- right click the folder and select the option to create a report, linking the report to the container created above
- select a set of expressions (database fields) from those available and sort these as they should appear in the report
- run the report
- make the desired changes to the resulting Excel template
- link this template back to the report for future use
Craig Juta is a SAI and Excel BI expert operating out of Alberta, Canada
Sage Accpac Intelligence supports the ability to create a report in one system (say for example on your business partners installation of SAI), and export this report as an ‘.al_’ file, email to one of your customers, and they can then import this report using the Report Manager or Connector module.
The single FREE Report manager that ships with Accpac 5.6 does not allow reports to be imported and so in order for your customer to be able to benefit from this feature the following options are available:
- invest in at least one more Report Manager license
- invest in a Connector license
Either of these investments enable importing and exporting of reports.
Note: Exportng a report produces an ‘.al_’ file which contains the SQL query, the report structure and also the Excel template linked to that report.
Multi-company consolidation is one of the features of SAI that sets it apart from other BI tools and this consolidation is not limited to financial reporting. There are 2 ways to do this – manually setup each company or use the built-in consolidation feature.
To use the built-in consolidation feature, observe the following steps:
- Be sure to have successfully installed and activated the Connector module
- Activate Sage Intelligence in each of the companies that are to be included in the consolidation
- From the Report Manager, select the report named ‘Financial Pack Consolidated’
- Click the button to the right of the ‘Company List’ textbox to expose a list of all the companies having Sage Intelligence activated
- Select all companies required
- You are now ready to run the report and have all the GL accounts from each company available to you in the resulting MS Excel template
Craig Juta, SAI enabler
Sage Accpac Intelligence has a built in feature that creates the basis for a consolidated set of reports and it provides a way to do this consolidation manually. In both cases the Connector module is required to perform this consolidation – see my blog on the steps to consolidate. The word consolidated is used here to mean that it will return the GL account and balances from multiple databases and place them beneath each other, ready for consolidation.
What the consolidation feature does not do is aggregate the GL account balances for each unique GL account across companies. This would be a task for the person designing and creating the Excel template. There are of course many ways to do this and the quality of this design decision can be measured against the ‘less is more’ principle, including the spreadsheet design best practices included in this blog.
One neat way of doing this is to group all unique GL accounts from the different companies together, use a ‘subtotal’ function to aggregate the values and use the grouping feature in Excel to hide the detail.
Craig Juta, Excel Master
A templates is created by linking an Excel workbook to a SAI report. The advantages of creating this link include 1) retaining the development investment made into the workbook and 2) having an identified master Excel workbook used for a particular purpose.
Below are some commonly used features when working with templates:
- To access the template for the purpose of making changes, right click a report and select the ‘design’ option- this will open the template and allow any changes to be saved
- To access the template for the purpose of making changes, run the report, change the resulting template and link it back to the report
- To abandon the relationship between a report and its Excel template, right click the report and select ‘unlink template’ – this will leave the report unlinked to a template and when run will send the dataset to a new workbook
- To link a report to a previously linked template, select the report and then select the ‘…’ button to the right of the ‘Report Template’ text box and then select a template from those available
When assessing the quality of SAI report designs, the most salient characteristic that I seek to identify is the lack of ‘quantity’ and ‘complexity’. A report may meet complex business needs and may require a very large work effort but this complexity does not have to relayed to the user of the report.
A feature on SAI that compliments this concept, is the Union Reports feature. Union Reports make it possible to deliver multiple, disparate datasets to the Excel template.
To create a Union Report, include the following steps:
- Create multiple standard reports
- Right click on the folder in which the Union Report will reside
- Select ‘Union Report’ and the select the sub-reports from the list, that you would like to include
- In the properties of the Union Report, right click one of the sub-reports and select properties
- In the properties window, change the sheet index to which the dataset from this report should flow
Now when this Union Report is run, each sub-report will run consecutively, placing the respective datasets on each sheet.
Union Reports compliment the ‘Less is More’ concept by avoiding the need to use multiple Excel workbooks for reporting and providing the opportunity for a dashboard based on multiple, disparate data sources for business decision-making.
Craig Juta, SAI Consultant
You might have run the Financial Pack in SAI and found that some GL accounts are missing.
The Financial Pack intentionally excludes GL accounts that have not been used during the current year or that do not have budgeted amounts against them.
To abandon this aggregate filter, in the report properties window of the ‘Trial Balance Sub’ report, select the ‘Aggregate Filters’ tab and remove the 2 filters that reside there. Now all available GL accounts will appear in the report template when run.
To access the ‘Trial Balance Sub’ report, refer to the blog post explaining Union Reports.
Filters and Parameters both serve the same purpose – limit the records delivered to the Excel template to those that meet the logical condition applied by the user.
Both have 3 steps for a basic setup from the filters/parameters tab – Click ‘Add’ -> select a field from the field list -> select the logical condition from the list -> type a value (in the case of a filter) and a default value (in the case of a parameter) for the logical condition to use (this value may be typed, selected (by hitting the ‘…’ button) or a system variable added (by hitting the ‘@’ button).
The difference is that the filter always uses the user-defined filter value and the parameter requests a value from the user each time the report runs.
In the properties window, within the columns tab, hit the ‘Add’ button to add columns to the list.
ADDING COLUMNS – The columns available to you are limited to those in the source container. To add columns (fields) to the source container so that additional columns are available in this window, the Connector module is required.
REMOVE A COLUMN – In the ‘columns’ tab, select the field to be deleted and select the ‘Remove’ button. This action removes the field from the report but retains it in the associated container found in the Connector module.
MOVE THE POSITION OF A COLUMN – A column can be moved by dragging and dropping or by selecting a column and clicking the ‘Move up’ or ‘Move down’ button to the right of the window. Moving the position of a column in the list also moves it in the position in which it is placed when the data is dumped into the Excel template.
COLUMN PROPERTIES – As per the screen shot below, an aggregate function may be applied to a field by right clicking on a field and selecting ‘properties’ and then selecting the aggregate function to apply as per below. Because agregation reurns less records, it is used when returning large datasets to Excel and the users of the report do not require much detail.
Note: Aggregation can only be applied to a ‘measure’ field which in turn groups the ‘dimension’ fields accordingly.
Intelligence – SAI training in Western Canada
The ‘columns’ tab in the properties of a standard report hosts the listof fields available to the report in the order and with the text as they appear in the Excel template once the report is run. These fields may be added to, deleted, shuffled around to display in a different order or have their individual properties set .
Please see the related posts that explore each of these acions in detail.
xlIntelligence SAI training
There are some common and some unique properties when dealing with Standard(blue) reports and Union(green) reports. Accessing the poperties of any report requires the ‘show advanced’ check box to be checked.
Circled below are the properties available to standard reports and not to Union reports:
Each of these properties and the Standard/Union report relationship are explored in other blog posts in this SAI blog.
bxInteligence – SAI solution provider
The output of your report which is a populated Excel template may be published to a network location and shared with other network users who have MS Excel installed. To do this you must access th properties of the report. Accessing the properties of a locked report requires the report to be unlocked by making a copy of that report – simply copy the report and paste it to any existing folder in the SAI Report Manager. Now you may follow these 2 steps to share the output with others:
- check the ‘show advanced’ check box to view all report properties
- in the ‘generate output file’ text box, type or browse for the desired location of the output file
Now every time the report is run it will create a copy of the output template to the set location, over-writing any previous copy with the same name.
Craig Juta, SAI solution provider
A report may be scheduled to run at a specific time using a combination of SAI functionality and the windows task scheduler. There are 2 ways to do this and we will explore 1 way in this session.
- Select the desired report, right click it and select the ‘Generate Schedule Command’ option 2. If the report expects parameters to be entered, these will be requested from you and saved in the schedule command
3. The schedule command required by windows scheduler will be coppied to the clipboard and SAI will show this screen 4. Now invoke the windows scheduler – ‘Control Panel -> System and Security -> Administrative Tools -> Windows Scheduler
5. One of the entries in the scheduler is the ‘scheduler command’ which is currently on your clipboard and is required to be pasted in the appropriate textbox
The report will now be invoked by the scheduler at the chosen time and frequency. One of the drawbacks of scheduling reports is the hard-coded nature of the parameters entered for the report. There are a few tips and tricks to overcome this and I will be exploring these in future posts.
NOTES ON SCHEDULING:
To have access to the scheduler command in Sage Intelligence, the installation of Sage Intelligence must have at least two Report Manager licenses OR one Connector license. The recipients of a Scheduled report only require a valid license of MS Excel and not a Sag Intelligence license (unless they need to access Sage Intelligence Addins).
Normally the user will not be sitting in front of the machine running the scheduled report and it is therefore beneficial to apply 2 properties to the report (select the report and check the ‘show advanced’ checkbox’ to see report properties):
The 2 properties applied above are 1) Generate Output File which saves the template to a shared network location and 2) Close Book on Completion so that the open workbook does not linger on the machine that ran the report
Financial Report ‘D’ requires account groups to be mapped to a set of arbitrary account categories AND allows the user to drill down to the transactional records for an account within the Excel workbook
Financial Report ‘S’ requires account groups to be mapped to a set of arbitrary account categories AND invokes another report for the purpose of drilling down to the transactional records for an account, therefore requiring an active connection to Sage Accpac for this drill down to function
Financial Report ‘SB’ does NOT require account groups to be mapped but instead uses the account groups as they have been defined in Accpac AND invokes another report for the purpose of drilling down to the transactional records for an account, therefore requiring an active connection to Sage Accpac for this drill down to function
Sage Accpac Intelligence
For those clients who have only one company set up in Accpac, only use the core modules of Accpac and have basic reporting needs, investing in the Connector module is a decision that can be deferred.
To customers who maintain multiple companies in Accpac, I say ‘when can a dynamic and consolidated view of operations and capital employment ever be a bad idea?’ and therefore the SAI Connector module is a good investment decision.
For customers using Sage CRM, Payroll and other add-on products the SAI Connector module just makes sense.
To customers using Project and Job Costing (PJC), I say it is a ‘no-brainer’ to make that investment considering the unlimited creativity that can be applied to PJC reporting using the Connector module.
It consolidates data from various sources, grants access to data not available in the standard set of SAI reports and only one license is required per site regardless of how many Report Manager or Viewer licenses exist. For all decision-makers who have Sage Accpac as their ERP, if you have an inclination to using consolidated, dynamic reports to provide you with objective decision-making tools then I suggest – invest in the SAI Connector module.
When including complex formulae in your report design, it is often prudent to provide notes to those formulae when trying to decifer them at a later time or for the sake of other users of the spreadsheet.
To do this you may make use of the ‘N’ function which converts a text string to the number zero. In this way you can add the result of your ‘N’ function to the rest of your formula without affecting the desired formula result.
= sumif (RngPer, A$1, Rng_Km) /countif(RngPer, A$1) + N(” Returns the avg mileage per period for each active truck”)
Craig Juta, Excel Trainer, Western Canada
Once a copy has been made of a standard locked report, the MS Excel template linked to that report can be modified.
The relationship between the Excel template and the report is a one-to-one relationship and a template may be linked to or unlinked from a report .
There are at least 2 advantages to having this report-template relationship:
1) The master Excel template used for a particular reporting output is kept in one place, avoiding the need for template version control
2) Any investment into an Excel template’s structural changes are retained because the most recently changed template may be linked to the report to be populated when it is run
To link a template to a report, right click the report and select ‘create and link template’ from the short cut menu. Templates linked to a report are stored in the BXData folder in the Sage Accpac root directory.
A template already linked to a report may be changed without engaging in the linking process. This can be done by right clicking a report and selecting ‘design’ from the short cut menu which will open the template and allow any changes made, to be saved.
Craig Juta, Alberta Canada
Sage Intelligence Expert
All the standard reports that ship with SAI are locked for editing. The need to edit reports arises from the following subset of needs:
- changing the features of a particular MS Excel template and linking this template back o the report to retain those changes for the next time that report is run
- changing the properties of reports, including the fields required, filtering, adding parameters to a report, attaching add-ins and macros to a report, saving the report to a particular network location, etc.
To create an ‘unlocked’ copy of a standard report, right click a report and select copy and then right click on an existing folder to paste the report into that folder. This report is a copy of the original report and includes a copy of the original template linked to this report. The user can now make full use of the report writing capabilities of the Report Manager to amend this report and template for the purpose of delivering the right information to the right people at the right time.
Craig Juta, SAI specialist
The SAI Analysis module supports the creation of Microsoft local cube files or .cub files off any ODBC compliant datasource. However, SAI also supports the ability to connect into existing Microsoft SQL Analysis Services Cubes.
-Launch the Connector module and browse to the very last connection in the listing called Microsoft OLE DB Provider for OLAP Services.
-Right click on this connection, and choose Add a new connection, and give it a name.
-Fill in your properties regarding the OLAP server i.e. name, database name, credentials
-Right click on this connection and select “Check test” to confirm that you have a valid connection.
-Right click on this connection and choose add a data container, and you should be provided with the existing list of OLAP data cubes in your server for selection.\
-Now launch the report manager and select “Add Report” and choose “Cube Report” and select the relevant OLAP cube data container from the Connector module that you just made available in the Connector module.
-Accpac Intelligence uses Microsoft Excel pivot tables as the default cube browser
-The advantage of this offering is that your customers can have a seamless and consistent experience for running all their reports whether they be direct from source or via OLAP data cubes.
-The above process, eliminates the need to have the SAI Analysis module, as you are connecting to pre-created OLAP cubes. Should you need to create the data cubes yourself, the Analysis module is used to define and create dimensions and measures for your cubes, and build them seamlessly.
Only the BXDATA folder (found in the Sage Accpac root directory) needs to be backed up as this stores all the Pervasive and/or SQL Excel report templates as well as the Alchemex.svd file, which contains all the report and connection properties that relate to the installation.
To use the standard SAI backup facility, from within the Connector or Report Manager, click on File in the menu > Back up Metadata & Templates and choose a file name for your backup. SAI will create a .cab file (compressed file) that consists of all your SAI Excel report templates and metadata (alchemex.svd) which is required to restore your SAI installation to its former state.
To restore your SAI installation to its former state, reinstall SAI from the DVD, and extract the contents of your backup file (*.cab) to the SAI BXDATA folder, typically found in C:\Sage Software\Sage Accpac\BXDATA
Sage Accpac Intelligence can be used without consuming a Lanpack license. This may be done by launching Sage Accpac Intelligence from the Start Menu while Sage Accpac is closed
Accpac consumes a Lanpak when you logon to the Accpac desktop, so if you have a Sage Accpac Intelligence license, you can access the module to run reports. Should all Accpac lanpaks be consumed, users that are assigned Accpac Intelligence licenses can launch the BI module from outside of Accpac and create, edit and run reports.
Following are the basic steps in the installation and deployment of SAI for the use of the 1 complimentary Report Manager license (the detail of each item will be explored in future blog posts and Intelliwidgets):
-Appropriate SAI modules check box checked upon installation
-Activate SAI in Administrative Services and allocate a license to a particular user, using the SAI ‘License Manager’ module (this links the license to the user).
-Then fire up the Report Manager on the workstation on which the license will be used (this action links the license to this workstation)
-Set up a security group for Sage Accpac Intelligence and then assign this group to Sage Accpac Intelligence for the appropriate user in the ‘User Authorisations’ window
-Set up security roles in the SAI ‘Security Manager’ (optional)
You are now ready to run reports using the Report Manager
It is important to clearly understand the limitations of the 1 complimentary report manager license that ships with Sage Accpac v5.6.
-It does NOT allow the user to setup multi-company consolidated reporting
-It does NOT allow reports to be imported (this requires the acquisition of either a ‘Connector’ license or 1 additional ‘Report Manager’ license)
-It does NOT allow the user to access fields other than those predefined in the standard report containers
As at today there are no standard SAI reports for Project and Job Costing (PJC) and clients with a need for SAI reports for PJC will need to consult with an SAI expert to develop such reports.
Remember that the development of PJC reports requires a ‘Connector’ license because the SAI container objects required for such reports do not ship standard with Accpac v5.6.
Any report depending on the Sage Accpac Purchase Order, Order Entry or Inventory Control modules will return an error when trying to run such a report.
The standard reports included in this category include: ‘Dashboard Analysis’, ‘Inventory Master’, ‘Sales Master’ and ‘Purchase Master’.
In the case where these modules are absent, there are standard reports that can be run that depend on the General Ledger module.
The standard reports included in this category include: ‘Financial Reports’, ‘Financial Trend Analysis’ and ‘General Ledger Transaction Details’