Stephen Smith's Blog

Musings on Machine Learning…

Archive for May 2011

Releasable State

with 5 comments

First an advertisement for a new session just added for Sage Summit:

Session: Sage ERP Technology Roadmap on Sunday, July 10 at 1pm – 2:30pm.

Session Description: Join Sage ERP Product leaders who will be discussing the technology evolution of Sage ERP product lines. This session will cover our product journey to the cloud; technologies being used to deliver rich web experience; developments on the Connected Services front(joining On-premise applications with the Cloud enabling us for web and mobile services); what kind of tools/technology/skills are needed to integrate, customize our products in the web world; and collaboration occurring on common components. There will also be time set aside for open dialogue. This session is for Sage ERP Development Partners only.

Now back to our regularly scheduled programming…

Releasable State

One of the key goals of Agile Software Development is keeping software in a releasable state. There are quite a few reasons that this is a good thing which we are going to explore in this blog posting. This is very much related to test driven development which I blogged about here.

Problems with Waterfall

In traditional Waterfall Software Development you have a number of key phases namely the setting requirements phase then the design phase then the implementation (or coding) phase then the QA phase then the maintenance phase. The diagram below gives an indication why this is called Waterfall.

There are a number of problems with this; one of the key ones is that bugs and defects are found quite late and that these tend to accumulate to quite large numbers. There are other problems with the requirements gathering and design phases but those would be a topic for another day. Generally it’s far cheaper to fix bugs right when they occur and everything about the code is fresh in people’s minds. As the time between coding and discovering a bug increases, the cost (or time) to fix it increases quite quickly. Fixing a bug from two or more stages in the development process ago is especially expensive. These bugs also cause havoc to the scheduling process. Estimating how long it takes to code something is usually marginally accurate. Estimating how long it will take to fix say a hundred or more bugs is really a crap shoot. Usually when Waterfall projects start to fall behind, there is a feeling that you can make it up by squeezing the verification phase, but practically, what happens is that as a project falls behind then the verification phase grows faster, since more bugs are found and they take longer than expected to fix.

Besides making scheduling un-predictable, what often happens in this environment is that you miss a couple of deadlines; the product is feature complete and has a couple of successful beta sites. But QA are still finding quite a high number of bugs and the outstanding bug count is high. What tends to happen next is a rationalization that the bugs that QA are finding won’t affect customers or that they are things like adherence to standards or in obscure corners of the product and that given the two beta sites are happy then why not go ahead and release. The product is then released; many bugs are found in the field, customers are very unhappy. Then there is a huge panic to address these bugs in a first service pack. This then delays the start of development of the next version and guarantees the process will repeat itself. This then lead to quite a dangerous downward spiral.

Generally this is a gloomy prospect that leads to the various software development death marches that you hear about.

Agile to the Rescue

Agile Software Development wants to address this problem in a couple of ways:

  1. Fix bugs right away, don’t let the time between coding and bug fixing happen.
  2. Don’t let bugs accumulate. Fix bugs right away, it is never ok to let bugs accumulate for later.

One of the main tenants of Agile Development is that you want to keep the software in a releasable state. You never want to get yourself into a situation where you know you have to fix a lot of bugs before releasing. The philosophy has to be that if you know about something in the product that would prevent it from being released right now, then that must be addressed immediately. This statement refers to quality and not feature completeness. Features are added one at a time off a priority queue and then it’s up to Product Management to decide the commercial readiness of the product.

Sure you could try to apply these two points to Waterfall, but the process and philosophy of Waterfall works against you. The goal to finish each phase on time tends to drive away good intentions to not let this happen yet again.

The Agile Development process operates by building a product backlog of user stories that you want to implement into the product. These are in a priority order (by business value) so the teams take stories from the top of this queue to implement. These are then implemented in two or three week “sprints”. At the end of each sprint you produce a working increment of your product. This is shown in the diagram below:

The stories in the product backlog are small enough that they can be fully implemented, tested and documented inside of a single sprint. When done, stories need to be “accepted”. Stories are not done or accepted unless fully tested, code reviewed, documented and complete. If a story is not accepted then it isn’t made part of the product, the team doesn’t get any credit for the work and it will need to be completed next sprint. The key point is that at the end of each sprint there isn’t a bunch of half-finished work checked in causing bugs and general havoc. From the diagram you might want to think of each sprint as a mini-Waterfall, however that would be wrong, a sprint is quite different, but again that’s another topic, perhaps for a future post.

If you are a SaaS product, potentially you could deploy the output of each sprint to your live web server and have all your customers immediately benefit from the work performed last sprint. Within Sage we are trying to operate with a SaaS mindset and I previously blogged about that here. Once you have working software, you have a lot of options as to what to do with it. You have far more opportunities to distribute your software, if not live, then to get more customer feedback to provide more improvements in later sprints.

A key to staying in a Releasable State is having a good set of automated testing tools that can be run continuously on the product so that bugs are found immediately and not allowed to creep in. I blogged on our efforts to become a more test driven organization here.

Generally with-in Agile you are trying to reduce accumulated in quality debt and technical debt. These are defects or parts of the program that badly need updating. The more of these that you accumulate, the harder a program is to maintain. With-in Agile you track these and work very hard to ensure you are always reducing these.

The key thing missing from the diagram above on Agile Development is the lack of any sort of regression or final quality validation phase. This is the desired end state; it takes a bit of time to get to a place when you are really confident you can drop these. We are endeavoring to shorten them with each release with the goal of removing them entirely one day. This speeds up our releases, by eliminating this work and makes things more predictable, since otherwise going into regression you don’t know what you will find, and then the time to fix anything found is also unpredictable.

The further you let a project get from releasable state, the less predictable the completion date. Worse you don’t know how much of this accumulated quality debt you are going to have to pay to release, how negatively this will affect customers or how badly this will hamper future product maintenance. To work optimially you need to really keep a hard line on these. The idea of being able to release at the end of every sprint is a good rallying cry to keep these all under control.


The Agile Development Process’s goal of fixing bugs right away as they are introduced, performing continuous automated testing and removing the regression testing tail end processes provided software development organizations far better predictability in their development efforts.

This also makes your development process far more adaptable in changing direction as business and competitive conditions change. Since it is far easier to change direction when going from releasable state to releasable state as you go from short sprint to sprint.

Written by smist08

May 28, 2011 at 4:17 pm

Customizing Crystal Reports for Sage ERP Accpac

with 20 comments

The main reporting engine used by Sage ERP Accpac is Crystal Reports. All transaction listing reports, setup reports, forms and checks are produced by Crystal Reports. Financial Reports and Business Intelligence reports are produced by Excel based reporting tools like Accpac Intelligence, Accpac Insights and the built in G/L Financial Reporter. Customizing the main Accpac reports is a matter of loading the report into the Crystal Reports designer, editing the report and saving it. This sounds easy, but Crystal is a very sophisticated reporting engine and some of the reports in Accpac are quite complicated. This blog posting looks at various topics in customizing reports.

Customization Directories

When you customize a report you can just save it over the existing report in Accpac. However if you do so, then this report could be overwritten by the next product update or will be overwritten if you un-install and re-install the product. Plus you may want different reports for different users or for different companies. The Accpac Customization Directories feature is the solution to these problems.

Here you set the user id and company name and then the directory for where you want the customizations for these combinations stored. You can use the wildcard “*” to indicate all users or all companies. Under the directory you specify here you need to store the reports in a subdirectory structure similar to how they are stored under Accpac. For instance if you customize and A/R 6.0A English report then in the above example you would store it in c:\myreports\ar60a\eng. This way it keeps reports separated by version, application and language to avoid conflicts.

Complicated Reports

Accpac has a lot of options and configurations. Reports have to handle all these possible combinations like multi-currency versus single-currency, G/L activated versus G/L not activated, National Accounts used versus no National Accounts, etc. As a result many sections in Accpac reports are visible based on formulae as are many columns and such. Sometimes if things get too complicated there will be two versions of a report, perhaps one report for single currency and a separate report for multi-currency. This is often if one is portrait while the other is landscape. Generally we try to keep the number of reports down, since this simplifies the long term maintenance of the reports. So if we add a new column we only need to add it to one report rather than five. Below is a screen grab from one of the most complicated reports in Accpac, the PJC Adjustment Posting Journal.

In this report there are many sections all with formulae that indicate when to show them. If you scroll down this report, you would see quite a few sub-total and total sections as well. Customizing this report is quite a challenge. To figure out how it works and then to carefully edit in the middle of this report can be quite daunting. Fortunately this shows the worst case and most reports aren’t this bad.

To approach editing this report you need to find out the section you want to customize and then just concentrate your attention on that one section ignoring all the others. In the screen shot below we brought up the section expert on a G/L account section. Then the X-2 button is red indicating there is a formula that controls the suppression and then if you press that button you get the formula that controls things.

With-in Accpac many things in reports are controlled by formulae. So if you are having trouble finding what is controlling things, look for the X-2 buttons being red indicating there is a formula present to control what is going on.

User Function Libraries

Crystal Reports has many built in functions that can be used in its formula language. However it also gives the ability for applications to add their own functions to the Crystal formula language. Then these functions can be used like any other function inside Crystal Reports. Accpac adds quite a few functions to Crystal. Generally these functions are used to format Accpac data in the same manner the UI forms format them, as well as get useful data out of Accpac that isn’t stored in the database. Below is a list of the functions Accpac adds with a description of what each does.

IsTrue (String)

This function will take in a string, and return its equivalent boolean value. Will return True if the string is TRUE, T, or 1 (any case).

pwFormatDate (Number)

Formats a date in the same way the Accpac does in UI programs. The number should be of the form yyyymmdd.

pwFormatTime (Number)

Formats a time value in the same way Accpac does in UI programs. The number should be of the form hhmmsshh.

TrimZeros (String)

Trims trailing zeroes off a string value. Ie changes “xyz0000” into “xyz”.

LookUpString(String, String, String)

First parameter is a language code (numeric code). Second parameter is a list of language codes. Third parameter is a list of language names. Used to decode language fields in bank services.

SaveNumber(String, String, String, String)

Saves a number into a file. First parameter is the file name. Second is the number to be stored. Third is the file mode flag (“TRUE” to create new file else appends). The forth parameter is the number ID as defined in GetBankTotal below. This function is used in conjunction with GetBankTotal below.


Function reads number from a file and calculates bank total. Parameter is the file name to read. Returns the total. This function is used in conjunction with save number. File contains records of the form:

struct BankTotal
char ID;
char Space;
char Total[30];

ID                                 Total Value
‘1’               Entries
‘2’               NSFs
‘3’               Deposits
‘4’               Checks


Print one space if the blank spaces or null string is parsed. Print a parameter passed.


Translate number to an account type. Parameter is the type. Does the following converstion:

Account Type Number (input)                             Account Type String (returned)
1                                 “IN”
2                                 “DB”
3                                 “CR”
4                                 “IT”
5                                 “UC”
6                                 “DA”
7                                 “AD”
8                                 “CA”
9                                 “AC”
10                                “PI”
11                                “PY”
12                                “ED”
13                                “UD”
14                                “AD”
15                                “CB”
16                                “GL”


Removes leading zeroes from a string.


Returns the System Manager product edition (1=Enterprise, 4=Corporate, 6=Small Business).


Checks if a license is valid. First parameter is the two letter prefix (such as CS), second parameter is the version (such as 53A). Returns 0 if ok, -1 if not found, -2 if expired.


Returns “CS”.


Returns “OB”.


Returns “NA”.


Returns “OB”.


Switches numeric strings to numbers, where the following are hardcoded:

   * Negative sign is “-” and precedes the number, with no space after the sign
* Decimal sign is “.”
* There’s no group (“thousands”) separator

Such strings (i.e. opt fld VALUE data) would have been created with bcdToStr.

(Since those strings come from DB string fields, we use locale-independent representations.)  Using Crystal’s ToNumber on such strings would fail when the locale changes (i.e. negatives become (1,5) instead of -1.5).

pwFormatString(String, String, Number, String)

Formats a standard database field for display. First parameter is a language code. Second parameter is the string to format. Third parameter is a database field type:

Number             Type
1                      String
2                      Binary
3                      Date
4                      Time
5                      Float
6                      BCD (fixed precision numeric)
7                      Short Integer
8                      Long Integer
9                      Boolean
100                   Money

The forth parameter is not used (but you must provide one, empty string is ok).

pwGetString(String, String)

Loads a string from pwuflLLL.dll where LLL is a language code like eng. (I.e. pwufleng.dll). First parameter is the language code. Second parameter is string to translate.


So what do you need to do when you upgrade versions of Accpac? Usually you would do the following:

  1. Copy the customized reports to a new application version under your customization directory, for instance copy c:\myreports\ar60a\eng to c:\myreports\ar61a\eng.
  2. After you have activated the database to the new version, then re-verify the reports against the database. Verify is a Crystal function that checks that the report is in sync with the database.

Strictly speaking the report will still work as long as the database doesn’t change dramatically. If we just added some fields to a table, the report will still work without needing anything to be done. In the early days of Accpac before our Crystal Reports were based on the ODBC driver, you had to verify the reports no matter what, since any change in the database, no matter how small would result in a report not running without running the Crystal verification function. Now a days the reports are more robust and the reports will continue to work as a long as the tables it uses are still there (and we very rarely remove tables). But it doesn’t hurt to re-verify the reports and it is a quick operation.

Datapipe Reports

Within Accpac we have what we call “Datapipe Reports”. These are reports that are built on an Accpac supplied Crystal database driver. We use these reports to supply data that isn’t readily available through ODBC. This includes filtering data for security purposes or performing complicated calculations on the data. Also if ODBC doesn’t retrieve the data with good enough performance then we can write a tailored datapipe report to retrieve the data quickly.

One complaint about datapipe reports is that the columns returned are fixed. So if the datapipe doesn’t return the data you need then what do you do? The solution is to add a sub-report that is based on ODBC to retrieve the additional data that you need.


This blog post just covered a few topics in customizing reports. Crystal Reports is a very large and sophisticated program, with it, there is great flexibility in the types of reports and forms that you can produce. There is great power in what you can accomplish. But with this power comes a certain amount of complexity that it takes a bit of learning and practice to overcome.

Written by smist08

May 21, 2011 at 10:24 pm

Posted in sage 300

Tagged with , ,

ERP Vision 2020

with 2 comments

The ERP space has been innovating at quite a fast pace recently. Although double entry accounting has remained the same for hundreds of years, ERP has been incorporating new technologies like mad including social media, mobility, web based, CRM and many others. This blog posting is to try to paint a picture of what the system might look like to a Corporate ERP user in the year 2020. At this point all these technologies should be incorporated and become completely seamless. Of course many new technologies will be developed between now and 2020 that we won’t have imagined, this is more looking at the mature versions of what we have today, but perhaps in a bit of a raw state.

2020 is only nine years away. Projecting out to 2020 is the same as projecting to 2010 from 2001. A lot of the big things in 2010 were around in 2001, they just weren’t developed enough for broad usage. 2001 was experiencing an Internet bubble that burst and many of the things being developed took until 2010 to mature (which was longer than most of the bubble companies could survive). Today we seem to be in an Internet bubble again. It could be that all the ideas from this bubble will take until 2020 to really come to fruition which again might be longer than many of the current companies can survive.

By 2020 the nature of corporate IT will have changed dramatically. IT will be much more about managing contracts with service providers and much less about maintaining hardware and software. Maintaining a data center with all the physical requirements like power, air conditioning and backup is very expensive and it makes a lot of sense to centralize this and exploit economies of scale. Upgrading software installed locally is an expensive and time consuming process. Doing this for dozens of applications can be quite daunting. Most applications will be cloud based and companies will use them on a service type basis based on usage rather than purchasing software outright. Software providers will be responsible for the maintenance of the software and performing upgrades in a manner that is transparent to their users.

For actual users of ERP and CRM software, they will be able to access it from anywhere, whether at home, on the road, on a plane or even in the office. The software will be far easier to use, will run on any device (phone, pad, computer, etc.), will offer active help based on usage patterns and knowledge of common problems other users encounter. Provide instant sophisticated analytics and dashboards. Information will be more accurate and more readily available anyway in an easily consumable form. People will be far better supported in their decision making by having the right data always easily accessible. ERP and CRM software will keep them apprised of what other people in their organization are up to and allow all sorts of social interactions as they perform their work. As fuel costs continue to rise and environmental concerns take a more central stage, having the flexibility for people to work where ever they are will become more and more crucial.


ERP and CRM applications will standardize on running as Browser clients written in pure HTML5/JavaScript/CSS. This allows anywhere access to the application on any device. You will be able to access all your business applications as easily from a phone or a large screen monitor. Perhaps by 2020 we will be up to HTML6 or 7, but the basic idea of an open standard that anyone can implement and that you can write software to, that will run anywhere will be stronger than ever. These technologies truly deliver on the dream of being able to write software that will run anywhere with a very rich interactive experience.


Devices will continue to proliferate. Now that applications are standardized on HTML5/JavaScript/CSS, this allows huge freedom to hardware companies to provide new devices with all sorts of form factors and input mechanisms.

Many people will still be using mice and keyboards. But touch screens, voice input and gesture type devices (like Kinect) will make computing seem much more like we saw in the movie Minority Report.

By 2020 we will have a much greater proliferation of devices for all sorts of specialized purposes. The one thing in common they will have will be the ability to run Web applications. This means modern ERP and CRM software will run directly on these devises as seen as they are invented.

Connections to the Internet will be seamless and universal. Today we are struggling with this, but by 2020 the infrastructure should all be in place to deliver the Internet in this manner.


We’ve seen huge improvements in usability for Web based applications. Applications like Facebook could have never taken off and reached 500 million users if each user needed to be trained and coached in how to use the application. We are seeing that usability going into single user finance and book keeping programs today. Small business applications are getting easier and easier to use. By 2020 we should see these usability improvements permeating mid-market ERP, CRM and HR applications.

Programs will continue to gather usage data, for Web based applications this is easier since no sort of “call home” feature is required. Web applications can gather very detailed analytics on how people are using the program. This data can then be used to either provide active help when people get stuck as well as to feed back into the development organization to help make future versions of the program even easier to use. It shows which features are used and which ones aren’t used, so developers can concentrate on improving the parts of the program that are used the most and where the improvements will benefit the most people.

Connected Services

By 2020 all mid-market ERP, CRM and HR systems will have sophisticated Web Services APIs. These will be based on future versions of RESTful Web Services like SData. These interfaces will make it very easy to interface to these programs as well as to link them together in custom ways. Programs running in one cloud will be easily able to interact with programs running in another cloud (or even on-premise).

Since most screens will be accessed by URLs, it will be simple to “stitch together” the screens of multiple applications to create very powerful integrated composite applications.

Applications will link and interact with many different Web applications whether free (like Google Maps or Twitter) or commercial (like Sage Exchange). This tight seamless integration and aggregation of services will greatly help productivity and improve decision making.


With common Web Service based APIs, it will be possible to build Workflow engines that orchestrate the activities in a number of these applications. Custom workflows will be constructed that cross the boundaries from ERP to CRM to HR. These workflows can even cross to other standard Web based services like Maps, Documents or any service with a Web Service interface. The power of combining Workflow with Connect Services will be immense.


Most companies won’t operate their own data centers. It will just be too expensive compared to the various cloud based alternatives. It will be easy to provision more resources and economies of scale will make it very affordable. Companies will just need to ensure their data is backed up and protected sufficiently for their needs, but this will be more a matter of contract management.

With all this data moving to the cloud, techniques will become available to anonymize and share this corporate data. You will get a discount if your corporate data can be added to giant data warehouses where advanced Business Intelligence tools can sift this data and provide useful and accurate data on industry trends.

Virtualization – Legacy Apps

Virtualization technology will continue to improve allowing legacy business applications to live in the cloud and benefit from the economies of scale there. Like all the mainframe COBOL applications that are still alive and working for large corporations worldwide, so too will all the current Windows applications. Rather than be maintained separately in each company that uses it, these will become centralized in specialized data centers that can efficiently virtualize these. Then maintain disaster recovery, backup and maintenance of the software and hardware. Each virtualized instance will maintain the environment and customizations for that particular client. Today, doing this is quite expensive, but once memory and disk management in virtualized environments becomes more efficient then the costs should come down. This then pushes the challenges of software maintenance and upgrade back onto the Software vendors.

Business Intelligence

Business Intelligence will be one area where we will make huge strides. We will see a convergence of search type tools like Google Search with advanced analytical tools based on Data Warehousing that provide very sophisticated slice and dice type applications. Today many BI applications are too complex to work with. You can do amazing data analysis with tools like Mathematica, but right now these tend to be too complicated to fully understand and utilize. Overtime all the sophisticated Mathematics will move into the background and you will be able to ask simple questions like you do into Google search today. But instead of just returning a ranked list of web pages, the algorithms that go into the results will be much more sophisticated and the results might be presented as charts, graphs and spreadsheets.

There is already so much data on the Web that we have a hard time mining it. As we move forwards, as storage gets cheaper and cheaper the amount of available data will continue to increase exponentially. We simply won’t have time to go through it, or search it out by hand. We will become more and more reliant on automated agents that can go out and find the data we need and present it in a meaningful way. We seem to rely currently on “analyst estimates”, but as we go forwards we should start to be able to replace these with real data that is far more accurate.


ERP and CRM applications will incorporate more and more ideas from the current social media sites like Facebook or LinkedIn. You will be able to see what people are up to in your company. You will have far less of the right hand not knowing what the left hand is doing in companies. People will be able to comment and contribute to many decisions allowing better decision making, better consensus and better buy-in.

Today Social Media is very consumer oriented. But many of the companies in the current Internet bubble are trying to solve Social Media for corporations. By 2020 we should see a leading “Corporate Social Media” application emerge that provides true productivity and use for the corporation.


Hopefully this article stirs some thought on discussion on where we want to move the Sage ERP, CRM and HR products over the coming nine years. Some of the ideas in this article are already in play. Some are just markers on potential future product roadmaps. It will be interesting to see how things evolve over the coming years. As the ancient Chinese curse goes: may we live in interesting times.

Written by smist08

May 14, 2011 at 5:36 pm