Stephen Smith's Blog

Musings on Machine Learning…

Archive for the ‘Reporting’ Category

Sage Intelligence 7.4

with 11 comments


With our upcoming Sage 300 ERP 2014 version we will be integrated with Sage Intelligence Reporting 7.4. Within our product this will also be labeled as version 2014. This is a fairly major upgrade to the Sage Intelligence package and definitely worth a look. There have been a great many feature, usability and performance enhancements throughout the product.

This product is integrated to many sister Sage ERP packages and this is where Sage is investing in improving Excel based reporting. For ERP a big usage of this is for Financial Reporting, but don’t forget this is a useful tools for many other applications like Sales Analysis.

Financial Reporting

Many of our Sage sister products used Microsoft FRX as their financial reporter. This product has now been discontinued by Microsoft. To provide a replacement we spent quite a bit of time adding features contained in FRX into Sage Intelligence. Sage 300 never used FRX, but we now benefit from all this work with better Financial Reporting from Sage Intelligence.

For Sage 300 ERP installed on-premise, it still includes the Financial Reporter bundled with General Ledger. This F/R is still there and supported. We just added support for Excel 2013 which was a major job due to the changes in Excel from a multiple document interface (MDI) to a single document interface (SDI) along with Excel now having data execution protection (DEP) turned on. We understand that people have invested a lot in developing many powerful reports with this technology and are quite happy with how it works.

For the Sage 300 Online product we have removed this Financial Reporter and are only hosting Sage Intelligence for Financial Reporting. With this cloud offering we are looking to the future. With version 1.0 of this product we are running the regular desktop version of Excel to work with Sage Intelligence. But this isn’t really what we wanted. As a cloud offering we wanted to integrate to the cloud version of Excel, namely the browser based Excel 365 version. However the API for this product isn’t sufficient yet to do sophisticated Financial Reporting. Once Excel 365 develops sufficient power, we will be integrating Sage Intelligence to this and allow cloud based use of Office 365. By using Sage Intelligence it means that when we switch from the local installation of Excel to using the cloud version, all your report will continue to work and won’t need to be re-written. For the Sage 300 built-in Financial Reporter it will only continue to work with the local installed version of Excel. As a result of this besides adding features needed for FRX users, we have also added functionality to Sage Intelligence to make it more comparable to the built in Financial Reporter.


We now load all the data into memory at the beginning and then do all reporting from this in-memory database. This way as you change reporting options and interact with the report, you get far better performance than before. No more re-reading things from the database as you go along.

Excel Support

Sage Intelligence 7.4 now supports running Excel 2013 both 32 and 64 bits. For Sage 300 ERP the built-in F/R supports Excel 2013 but only running in 32 bit. If you absolutely need 64 Bits then give this solution a look. However MS does install 32 bits by default now for Excel 2013 due to all the compatibility problems with the 64 bit version (it isn’t only us). Similarly Sage Intelligence also no uses .Net framework 4 which is better supported and avoids installing old versions of the .Net runtime.


Let’s look at a few of the new things in the product. First we’ve modernize the toolbar in the Report Manager to the Office ribbon style. We’ve also fixed up the wording and hints to make this a bit easier to use.


As part of providing an out of the box Financial Reporting solution for Sage 300 ERP, we’ve added a number of sample reports that match the Financial Reports bundled with our built in F/R. Below is he home page for the Financial Ratio report:


And then the actual report. Note that there are a few more ratios here than in the built-in report along with graphs and such.


For the Balance Statement and Income Statement reports each three are incorporated into one Sage Intelligence report, each on a separate tab. One of the income statements is shown below.


The new Report Designer boasts a fresh new User Interface to supplement a powerful generation engine utilizing the Report Designer In-Memory technology allowing for improved performance and usability. This tool makes it very easy to create new powerful Financial Reports without having to edit every Excel cell by hand. Automated layout generation for a single company:


Plus screens to specify your account ranges and labels:



Sage 300 ERP 2014 will be bundled with Sage Intelligence 2014 which is version Sage Alchemex Intelligence 7.4. This is a fairly major release which greatly increases the power of Sage Intelligence as a Financial Reporter. It adds many features to be comparable to both FRX and to the built-in F/R from General Ledger. This version supports Excel 2013, including the 64 bit version and this is the product that we will be adding cloud based Excel 365 support to.

Sage 300 ERP Inquiry Queries for 2012

with 2 comments


We introduced the Sage 300 ERP Inquiry tool in our 6.0A release. This tool was part of the new Web Based Portal. I blogged about it here, this blog was written before release when we were call it the Adhoc Query tool. With the 6.0A release we had query templates for General Ledger (G/L), Accounts Payable (A/P) and Accounts Receivable (A/R). With our upcoming 2012 release, we are adding query templates for Inventory Control (I/C), Order Entry (O/E) and Purchase Orders (P/O).

The new query templates (or data domains) that are being added are:

  • Inventory Items
  • Inventory Item Transactions
  • Order Entry Invoices
  • Order Entry Sales History
  • Purchase History
  • Purchase Orders

Remember that the intent of the Inquiry tool was to be easy to use, so anyone can inquire on their data without requiring any assistance or support.


Let’s look at a few simple examples. For instance, say you want to know everything that our favorite customer, Ronald Black, has purchased from us? We can then use the “Order Entry Sales History” template and select our customer as 1200 from a “Finder”, and immediately see a list of everything he purchased. We can then add a totals line to see the total. If we wanted to we could group this by year or period and see subtotals for these.

Suppose we wanted to know what our total sales of “Halogen Desk Lights” is and who is purchasing them?

These were just two simple examples using Order Entry Sales History. Hopefully with these data domains for the operations modules, you can get answers to the questions you have about what is happening in your company.

The intent is that you can inquiry and report on questions to do with your inventory and operational transaction histories to help you with projections, better manage inventory levels and to direct marketing to your customers based on their history. Hopefully the P/O queries will help you better manage your vendors and to help your company control costs by having more visibility into its purchasing patterns.

Remember you can print these, export these, choose the columns and sort order. You can have any number of selection criteria; there are five types of totals that can all be grouped by a field. Again the primary idea is keep the operation of this screen really simple, so anyone can ask questions on their data this way.


If you still need additional data queries (data domains) you can get a developer to create these for you. I blogged on how to do this here. Any of these that you created previously will continue to work. Just beware that you will need to move them to the inquiry61a folder when you upgrade. Unfortunately there is still the limitation that you can’t add menu categories to the Inquiry menu, but at least now there are 3 more existing places to choose from.


We continue to fill out the web based functionality we introduced in Sage 300 ERP Version 6.0A. This is just one of the many new features that our next release Sage 300 ERP 2012 provides.

Written by smist08

May 19, 2012 at 9:11 pm

ERP and Crystal Reports

with 3 comments


I put a questionnaire on how people liked Crystal Reports up on the “Sage Partners, Employees & Alumni Networking Group” group on LinkedIn and received a lot of good discussion. I’ve previously blogged on customizing Crystal Reports for Sage 300 ERP (Accpac) here. This blog is more about the history of how ERP to Crystal integrations have gone in the past and the various challenges faced.


Accpac (now Sage 300 ERP) was originally developed by the Basic Software Group in the eighties in Vancouver. They started a project to have a new WYSIWYG report writer as part of the product rather than the codes based approach being used. This project was cancelled and some of the people involved quit and started their own company to develop Crystal Reports (then Quick Reports). This was originally developed as an add-in product for the MS-DOS based Accpac Plus. Then they generalized the product to dynamically look at any database and that became the Crystal Reports of today. The original founders then struck it rich when they sold their company to Seagate in 1994, which was trying to branch out from hard disks to software. Eventually Seagate gave up on this and Crystal went private and independent as Crystal Decisions. Shortly after in 2003 it was sold again to Business Objects. Then in 2008 Business Objects was bought by SAP where it lives today.

All of Sage 100, 300, 500 and X3 ERP use Crystal Reports as their main reporting engine. Sage HRMS and Sage CRM also use Crystal. We all adopted Crystal long ago at some stage or another. Sage 300 ERP adopted Crystal when we switched from using CARET (the Computer Associates report writer) back at version 3.0A.

Most products started integrating with Crystal in the 16-Bit world of Windows 3.1. We all integrated via the DLL interface that was to the DLL: crpe.dll. This interface let us print reports to a printer, preview or file. It let us set parameters that were passed from application’s entry forms to Crystal for things like to/from ranges. The Crystal runtime that included crpe.dll was installed to a directory c:\windows\crystal. This meant that any product that installed the Crystal runtime would usually overwrite what was already there, meaning the last product using Crystal to be installed would usually work, whereas most other products using Crystal would then stop working. This was the famous DLL hell.

In these days we had to use native drivers for accessing the database, Sage ERP 300 shipped two versions of its reports, one set using the native Btrieve driver and the other using ODBC for SQL Server. This tended to be a maintenance headache. Having multiple reports for different databases, or even different page sizes like letter vs. A4 vs. legal. Having different reports depending whether G/L is used or not. Over the years as Crystal functionality has improved and ODBC drivers have improved, we’ve been able to reduce the number of report versions. We now ship one version of reports using ODBC that is just configured to look at the correct database regardless of type.

Then we all started to move to 32 bit Windows with the advent of Windows 95. Crystal produced a 32-bit version and the new interface DLL was crpe32.dll. This was a close match to the 16-bit version and programs could work with Crystal in the 32 bit world very similarly to how they did in the 16 bit world. However DLL hell still remained. Plus we started to see the appearance of Terminal Server and Citrix. To keep users separated on TS, anything that a user installs to the Windows directory actually goes to a local Windows directory. The problem now is that the Crystal directory would go to the local user’s private windows directory and hence Crystal would only work for the user that installed the product and not for any other users on the Terminal Server. This then led to a manual procedure of copying the Crystal subdirectory either to the main Windows directory or each user’s local Windows directory.

With Crystal Reports 9, Crystal moved to eliminate the DLL hell problem. They dropped support for calling the crpe32.dll directly and moved the Crystal runtimes file over under Program Files under a version labeled directory. This allowed multiple version of the Crystal runtime to be installed at the same time and fixed the problems with Terminal Server and Citrix. However applications that used Crystal had to switch from using the crpe32.dll to using Crystal’s COM interface.

Crystal X and XI then followed and these worked very similarly to Crystal 9. Another nice thing was at this point Crystal had adopted an extensible file format, so the file format stopped changing between versions making upgrades easier.

Crystal Reports 2008 then dropped their COM API and now only supports integration via .Net and Java interfaces. This presented a number of problems, namely since these interfaces were quite different than the ones that went before them. Plus Crystal Reports was a fairly major upgrade to XI and seemed to introduce quite a few bugs. At this point ERP packages integrating to Crystal either had trouble with the new interfaces or had trouble with existing reports not running properly. This led to a bit of a low adoption of CR 2008. However the file format remained the same, so you could use CR 2008 to edit reports that would later be rendered in the ERP package via an earlier Crystal runtime, and again co-existence of multiple versions isn’t a problem due to the side-by-side DLLs.

Crystal has now released Crystal Reports 2011 and this fixes most of the problem in CR 2008. So we should start to see ERP packages moving forwards again. This version still only supports .Net and Java interfaces but has fixed a number of bugs and deficiencies that were causing adoption problems.


For setting parameters and running reports, most ERP packages can do this fairly handily. However when you start to push the integration, what are some of the problems you can run into?

Sage ERP 500 (previously MAS 500) tries to simplify report customization by actually generating reports based on a higher level report designer built into the Sage ERP 500 product. This then eliminates a lot of the challenges in customizing reports inside the Crystal Designer. However this means that Sage ERP 500 uses the Crystal Design API and this API changes a lot from version to version making upgrades much harder. Sage ERP 500 also controls access to its database via a customer ODBC driver that then adds the Sage ERP 500 security model to the generic ODBC access.

Sage ERP 300 (previously Accpac) has a concept called “datapipes”. These were originally created for CARET as a performance enhancement and then taken over to Crystal. The concept is that you use these in place of ODBC to connect to the database and then these datapipes use the Sage 300 ERP database ERP to access the database. This allows the datapipes to use special knowledge of the Sage 300 ERP database schema to provide faster access. We use them to “flatten” out subreports, since accessing subreports in Crystal can be slow. We also use these for controlling access to things like G/L accounts with our G/L security module so users don’t see data they aren’t entitled to.

Reports Server

Using the Crystal Reports runtime from an ERP package is fairly straight forwards and easy, it can be installed with the ERP package and usually works quite well, so the user doesn’t have any extra hardware, installation or management problems. If you need to customize reports you need to buy and install Crystal Reports, but to just run reports there is no extra overhead. However much new development by Crystal is requiring the Crystal Reports Server.

The Crystal Reports Server is a central server that manages the printing and distribution of reports. You can use it to schedule when reports run and who gets the results. Most new Crystal development is now requiring a Reports Server to operate. For instance if you want to use the new Crystal Data Universe to make creating reports easier then you need a Reports Server to hold these. Similarly several new technologies like Crystal’s dash-boarding technology require the Crystal Server.

For us ERP vendors, we now have to evaluate the ROI for our customers. Is requiring this new server worth it? Is it worth the hardware, setup and licensing costs? Long term, is this tying the ERP package too closely to Crystal making adopting other technologies harder? At this point I don’t have a clear answer to these questions, but we are working with the new things in Reports Server to see their costs and benefits.


Crystal Reports has a long and interesting history and has become the de facto standard in report writers across all ERP packages. It is very full functioned and works with any database server. It takes a bit to learn how to use it effectively and is a very powerful tool for Business Partners that use it. The main complaint about Crystal is that it’s hard for end users to customize their own reports, due to some of its complexities.

Written by smist08

January 21, 2012 at 4:32 pm

Drilling Down from Crystal Reports in Sage ERP Accpac 6

with 7 comments

Sage ERP Accpac 6 brings the ability to drill down from data in a Crystal Report. This ability is only available in the new Web version and not from the VB version. Report designers have the ability to add hyper-links to reports, which will trigger a user interface form to be run when the link is clicked on. The Crystal Report can pass data to the UI form to enable drill down. In the screen shot below the cursor is over the first money amount in the report, so an underline appears indicating you can click on this number (the underline appears when the cursor is over the link).

When you click on the link, the Crystal Viewer sends a JavaScript notification to our Portal which then takes that and runs the web URL indicated. This runs another Accpac UI form, which doesn’t display, but instead immediately runs another report to show the drill down information.

Having a look at the Balance Sheet Report in Crystal:

You can select a field and select Format – Hyperlink.

Then set the hyperlink to a formula (the formula button is red in the previous screen shot) that you edit in the formula editor:

Basically with this you are setting the data that will be sent to the Accpac portal when you click on the hyperlink. Here you specify what you want done, including the URL of the UI form to run and build its parameters. You build the parameters using the Crystal formula language to build a string or URL parameters, which the UI form can interpret when it runs. To the end user it then appears that from reports you can drill down to both other reports and to UI data entry forms.

As we develop the new Web Based version of Accpac we are putting hyperlinks everywhere. This allows you to drill down from almost anything. This functionality will be far more pervasive than we’ve had in the past. Adding drill down to the current product has always been very popular, since customers want to know how various numbers are calculated, what was added up to give them a total or summary. Hopefully a popular feature.

Written by smist08

June 4, 2010 at 10:46 pm