Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘reporting

Sage Intelligence Go!

with 4 comments


A new flavor of Sage Intelligence was demonstrated by Himanshu Palsule during his keynote at Sage Summit called Sage Intelligence Go! (SIG). I thought I’d spend a bit of time this week providing a few more details about what it is and where it fits into the scheme of things.

Sage Intelligence is a business intelligence/reporting tool used by many Sage ERP products. For those who have been around the Sage 300 ERP community, they will recognize it as Alchemex which Sage purchased a few years ago. This is a product that runs as a Microsoft Excel add-in which extracts data from the ERP to be manipulated by the power of the Excel spreadsheet engine. This is a very popular way of doing Financial Reporting since you often want to present the data graphically or you want to perform complex calculations or you want to slice and dice the data using pivot tables. Excel provides a great platform for performing these tasks. The original Financial Reporter bundled with Sage 300 ERP works in a similar manner as an Excel Add-in. Sage Intelligence is used for quite a few things beyond Financial Accounting including Sales Analysis and other predictive type BI functions.

Sage Intelligence has been around for a while and is a good mature product. Since it is written with the full Excel add-in SDK, it must run in Excel and specifically the locally installed version of Excel. This means it isn’t particularly well suited for cloud applications such as Office Online.

Microsoft has now released a newer SDK for developing Office apps. This new SDK is designed to allow you to develop applications that can still run in the locally installed full Microsoft Excel, but they can also run inside the cloud/web based Excel Online as well as the new specialty versions of Office like the one for the iPad.

As Sage ERP applications move to the cloud, you would want to have your Financial Reporter be cloud based as well. You would want to be able to edit Financial Reports in the browser as well as display and interact with reports on any device including your tablet or smart phone. This is where Sage Intelligence Go! comes in. It is written in the new Office Apps SDK and will run on the online and device version s of Excel as well as the full locally installed Excel. This you don’t need a Windows PC at all to use your cloud based ERP and cloud based Financial Reporter. However you still have all the power of Excel helping you visualize and manipulate your reports.


Sage One Accounting

Sage One Accounting by Sage Pastel is such a cloud based ERP system. This product has Sage Intelligence Go! as an option for performing various reporting needs. When SIG started out here, it was much simpler. The original Office Apps SDK was very simple and didn’t nearly have the power of the various older SDKs supported by local Excel. However as time as gone by, the Office App SDK has become much stronger and the functionality is now much more in line with what we expect of such a solution.

Office Apps

For those of you who attended Sage Summit and saw James Whittaker’s keynote, they would have seen the importance Microsoft is placing on the new Office Apps. Basically to switch from using the browser and search, or using Apps from a mobile device App store, you will get your data directly in your business applications via this new sort of App. If you go to the Office App Store, and search on Sage you will find a Sage One app for Outlook and Sage Intelligence Go! If you search a bit more broadly, you won’t find that many non-trivial applications. Sage Intelligence Go! is probably the most sophisticated Office App in the store.

Notice that you don’t run SIG reports from the ERP application, they run from Excel. Once you have the XLSX file, you can just run it anytime from any version of Excel and have full access to your data. This is really a new paradigm for doing reporting. For SIG this works really well. Whether other applications fit this model is yet to be seen.

Web Services

SIG needs to communicate both with the Office API (whether cloud or local) as well as with the ERP to get the data to process. Both of these functions are accomplished via RESTful web services. The communication with the ERP must be via Web Services since these could originate from the SIG Office App running in the Microsoft Office Online Cloud or from the Office App running in the local Excel. It isn’t possible to use the traditional methods of database integration like ODBC when the two may be running in completely different locations.

Basically the Sage Cloud based ERP exposes a standard set of Web Services that are protected by Sage ID. When the user starts SIG, they get a Sage ID login prompt from with-in Excel and then this is transmitted through to the Sage Cloud ERP which uses the Sage ID to look up which tenants this user runs as. This information is related back to SIG in case it needs a second prompt to act which tenant (company) to access.

Sage Intelligence Go consists of a server component that runs in the cloud as well as the Office App that runs in Excel. The Server portion of SIG uses the provided Web Services to load the data to be reported on from the ERP into its in-memory database for processing. This in-memory database is maintained in the cloud where the SIG service runs. Then the Office App part of SIG interacts with the server side to present the correct required data and perform various processing functions.


ERP Integration

This solution requires the ERP provide Web Services that are exposed to the Internet in general, so that it can be access via Excel running anywhere whether installed locally, running on an iPad or running as a Web Application in the cloud. This means these Web Services need to be secure and available 24×7 with very good availability. For this reason you will see SIG first integrating to Sage Cloud based ERPs (like the current Sage One Accounting by Sage Pastel) and later via the Sage Data Cloud which will offer these services on behalf of on-premised installed ERP systems.


Sage Intelligence Go! is our solution for cloud reporting. Its primary purpose is to provide Financial Reporting capabilities, but it is also capable of handling other BI type reporting needs. This is our solution to moving a lot of reporting functions into the cloud.




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

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

Insights 2010

leave a comment »

I just returned from the Sage Insights 2010 conference in Denver, Colorado. The conference was really great and Denver was an excellent city to have a conference in. The good thing about Denver is that the conference center is in the center of the city and most of the downtown attractions are within walking distance. This meant we weren’t trapped in an isolated convention center at the mercy of those facilities.

The keynote addresses outlined Sage’s strategies going forwards. The main emphasis was to balance efforts between three columns:

  1. Providing value for the installed base.
  2. Providing connected “cloud” services.
  3. Developing strategic products for new customer acquisition.

These pillars aren’t just to do with R&D, they affect all aspects of our business, including marketing, programs, sales, support and services.

There tends to be a lot of confusion about what the difference is between developing for the installed base versus developing for new customers. After all both will be customers and their businesses will be very similar. Just one uses our products currently and one (hopefully) will shortly. These tend to be confused with an age old argument with-in our products of how many resources to put into developing “accounting” type application features versus “technology” features. Unfortunately anything that isn’t an accounting feature tends to be bucketed as a technology feature, even though many useful non-accounting features like better installation programs aren’t involving any new technology, just refining what we already use.

So to be clear, providing value for the installed base does not mean just adding some “accounting” type features and developing for new customer acquisition does not mean just adding “technology” type features. For instance three top requested features from the installed base are:

  1. Better reporting
  2. Better performance
  3. Lower Total Cost of Ownership (TCO)

None of these are “accounting” features. All may or may not involve new technologies to solve them. Number 2 may just be a matter of re-factoring existing code in the existing technologies. Number 1 may just mean providing more reports in the current tools, or improving the current reports. Sage ERP Accpac 6.0A is addressing numbers 1 and 3 with web based technologies. It includes a new Accpac Inquiry feature and real time dashboards to address number 1. It moves towards a true web based zero-client model to avoid having to install anything on all the client workstations to reduce TCO. Better performance is address by being able to use more advanced web based testing tools to guide code re-factoring for better performance.

By the same token, from the Win-Loss analysis it appears that we lose sales at the upper end because our operations modules don’t provide all the sophisticated features that larger companies require. So adding functionality like sophisticated re-stocking strategies is an “accounting” feature that will allow us to expand the reach of Accpac, but probably won’t benefit existing customers.

Then there is the question of meeting the competition head on. When going head to head with the competition we want our product to be the best looking and most exciting. Right now that tends to mean being the richest web based application. Many times this is categorized as a new customer acquisition strategy, since we need to impress the clients to make a new sale. But it’s also an existing customer strategy, since if we don’t do this then the competition will attack our install base and if they can convince them that our products aren’t moving forwards, then they can start converting them. So we need the new technologies to both make new sales and to protect our installed base from our competitors. We are lucky that accounting applications are very “sticky” and its hard to convert our customers, but they still can be converted and our install base does need to be tended to.

So we can see that for the existing customer and new customer pillars, each will be addressed by a combination of refining existing functionality, embracing newer technologies and adding application functionality.

The middle column above of connected “cloud” services is all about providing new services to all our customers. This includes a number of existing services like Avalara sales tax calculator and Sage Payment Services’ Exchange gateway. It also envisions many new services perhaps like a hosted software as a service (SaaS) model self service modules. For instance a web site where employees of a customer can go to enter expense reports, which are then entered into the on-premise accounting application (using Sage’s SData web services protocol). The goal of these is that they can be developed once and provide a standard interface and then be connected to every Sage accounting application. Some of these will be add-on modules, some may be complimentary.

After the keynotes, the conference was quite exciting as Sage ERP Accpac 6.0 was released to “alpha”, meaning an official installable image was released to all third party developers. The “beta” release that will be available to all business partners is scheduled to be about 7 weeks away. Accpac 6 was installed on many computers for partners to play with, there were many product demonstrations, which were very well received.

At the end of the conference we offered two days of developer training in the Accpac 6.0 SDK which went quite well.

The next year should be quite exciting as we see all these new initiatives and products rolled out.

Written by smist08

May 24, 2010 at 5:19 pm

Posted in Business, sage 300

Tagged with , , , ,

Sage ERP Accpac 6.0 Adhoc Query

with 15 comments

A common complaint about Accpac today is the difficulty of getting the data out of the product. If there is a stock Crystal report then, great, you can get the data. If there isn’t a stock report that meets your needs then, typically, you need to hire your Business Partner to write a custom Crystal Report. Generally end users find Crystal Reports too intimidating to use on their own. Additionally if you take Crystal Report training, but don’t use the tool regularly, then you will lose the knowledge and have to re-learn it.

Many users use a combination of data export to Excel and printing reports to Excel to get their data into a tool they are more familiar with and then in Excel, sort and sum their data to get the numbers they need.

With the Adhoc Query tool we are looking to allow users to get at their data quickly and easily without jumping through all these hoops. We want to provide an extremely simple interface that anyone can use to query their data. No one will need to know anything about the Accpac database schema or about advanced formatting with groups, headers and footers. As a consequence this tool will give simple but powerful methods to sort, sum and choose your data. A simple way to preview, print and export the data. It will not allow fancy features to make the interface too complicated for occasional users. We are being very strict on keeping the interface simple and accessible. Hence the power will be in the accessibility of the data rather than in the formatting of the data.

Basically we define a number of “data domains” such as “A/R Customers and Transactions”. You choose which data domain you wish to query and then we give a simple interface where you can specify which data you wish, how you want it sorted and summed and then see the data right away instantly in a table control. Then we also give you easy ways to export, print, and email the data to anywhere else that you might need it.

Below is a screen shot of the current Ad Hoc Query tool returning all customers with their name containing the word “Mr.”.

As you can see from the screen shot, the interface is very simple. This is our main objective for this tool, that anyone can use it. But at the same time we want to ensure that you can get at the data you need easily. This is partly why we called it “Adhoc Query” rather than “Adhoc Reports”, because we didn’t want to set the expectation that this was all about formatting. It is in fact all about the data and hence the name.

Written by smist08

December 11, 2009 at 10:59 pm

Posted in Uncategorized

Tagged with , , ,