Posts Tagged ‘Sage Intelligence’


Providex Documentation for Sage 100

February 2, 2014

Our team has found the Providex database hard to build solutions with. Sage has built the UQE for use in Sage Intelligence to overcome limitations like multiple left joins but important features like sub-queries are still not supported.

Hit the image below for access to some documentation on Providex:

Screen Shot 2014-02-03 at 6.14.48


Creative BI: Consolidated Financial Reporting

January 30, 2014

Consolidated Financial Reporting using Sage Intelligence. Also applies to non Financial Reporting….


Consolidated Financial Reporting


Creative BI: Manufacturing Requirement Planning

January 4, 2014

Many of our manufacturing customers use Builds of Materials in their ERP to aid in the manufacturing process: configuring recipes, managing inputs, planning for requirement and managing projected profits.

Although these are very useful for a manufacturing environment, for a system to truly be useful to its owner, it must incorporate business information from Sales Orders, Purchase Orders, Component QOH and Unfinished Builds. And finally, what the business manager cares most about is the delivery of this information on an interactive reporting platform.

In this article I wish to share the concept of reporting on shared components between Builds….

One of our customers recently requested that we add the 2 fields below with the red borders to the ‘BOM Requirements Planning’ solution that we had built for them. I thought this was a great idea due to the following considerations…..

When planning requirements in a manufacturing environment, it often happens that component items are assessed and ordered in the context of a particular Assembly. As an example, if Assembly A and B each require 10 components of item Y and both A and B need to be built, then in the context of A and B alone, only 10 of item Y are required when the reality is that 20 of item Y are required.

Hence, the field below named ‘OrderQtyAll’ is one that discloses the total number of components required, including those that are required for builds other than the current build being reported on and analyzed. Consequently the field named ‘RequiredAll’ now reflects the true quantity required by considering QOH, All component items indirectly on order across all builds, Sales Orders and Purchase Orders.



Profit and Loss Report (Pastel Partner)

October 24, 2013


Solution Description

This Profit & Loss report reveal financial performance information of the company across multiple fiscal years

It answers questions like:
‘How much profit did the company make for a given period of operation?’
‘What is the contribution of Gross Margin to fixed costs’
‘What percentage of revenue does each expense line constitute?’

…..and consequently, where can adjustments be made to increase profits

This solution reports Profit and Loss activity over as many years as have been transacted in Sage Pastel and is not limited to 2 years like the standard Sage Pastel reports

This model groups the report into categories that match your business structure.
It does this by using the Financial Categories, Report Writer Category, External Link Field and Entry Types, all of which are customisable by the user of Sage Pastel.

Download this solution here >>

Supported Versions

Microsoft® Excel® 2007+

Pastel v11+




Customer Profiling – Simply Accounting 2011 Report

December 7, 2011


This solution provides business information for the profiling of Customers. Use the dimensions in this report to profile customers by Sales and ‘Days to Pay’ for any period.


Depending on the report variant selected, this report provides rapid access to sales-side information and filtering by ‘Customer’, ‘Invoice Number’, ‘Payment’, ‘Paid/Unpaid Invoices’, aging of AR into ‘Current’, ’30 Days’, ’60 Days’, ’90 Days’, ’90 Days Plus’, with the option to isolate aging that is Project related or Non-Project related. This report displays all AR transactions up to the aging ‘cut-off’ date selected at run-time.

This solution has two performance variants:

V1.0 Customer Profiling including Invoice value, Maximum days to pay, Minimum days to pay and Average days to pay. This Free variant is limited to invoices created over the past 60 days.

V1.1 Customer Profiling including Invoice value, Maximum days to pay, Minimum days to pay and Average days to pay.

All of these variants contain common dynamic features:

  • Swiftly and easily filter by available reporting dimensions
  • User selection of the date range for reporting and a second level filter by document date
  • Two views available – Customer and Invoice view
  • Drill-Down from Customer to Invoice level

Note: This solution focuses only on fully paid invoices

All Free Variants available here.

Versions: Excel 2007 +



October 10, 2010

For those who have clients without the Operations Suite installed, I know what it feels like to run the Dashboard Analysis Report in Sage Accpac Intelligence and receive that dreaded error message about there being no Order Entry tables. I have amended the standard Dashboard Analysis Report and the output looks like this:



This report can do with some improvement but at least it gives you the chance to show your clients the dashboard on their data.

To enable such clients to use the dashboard, download the report below and import it into SAI.


As usual, to import a report, the receiving system requires a second Report Manager license and/or a Connector module license. I recommend the following steps for use:

  • Click the link above to download the file
  • In the Report Manager, right click on an existing folder and select the ‘import report’ menu option
  • Browse to the file, select it and choose positive answers to all questions

The report is now ready for use in the folder to which it was imported.

Contact Craig if you wish to include drill-down features for Customers, Items, Account-Sets and GL Accounts.



August 16, 2010

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.

  1. 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.


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

Craig Juta