NEW YORK — As every penny counts, businesses are asking front-line workers to step up efforts to "upsell," to persuade a buyer to take an extra or two that adds to their transaction.
The tried-and-true retail tactic helps McDonald's sell millions more fries each year. Approaches can be modest — like McDonald's "Would you like fries with that?" — or more pushy — such as the waiter asking for the third time whether you want another glass of wine.
As long as it does not alienate customers, the technique can add to the bottom line.
Southwest has gained $100 million in annual revenue with an upsell to its Business Select-class fliers. For $20, up to 15 of them can buy their way to the front of the line for first crack at Southwest's non-reserved seats and carry-on space. They also get frequent flier credit and a cocktail.
"Technically it's an upsell to our Business Select fliers. We wanted to add value rather than slap on a fee for everyone. But the reason was to boost revenue" says Beth Harbin, Southwest's spokeswoman.
That kind of sell works better than a pushier approach, says marketing expert Steve McKee.
"If you are putting customer needs first and being sensitive to what people need in this environment, it can help you," says McKee, author of When Growth Stalls: How It Happens, Why You're Stuck & What To Do About It. "You can sense when someone is trying to upsell you vs. when someone is trying to meet your needs. In this environment, people are going to be ultrasensitive to someone who is trying to take money out of your pocket."
Some other examples:
•Old Navy. The 1,000-store retail chain's "Steal that Look" effort trains workers to put together and sell customers on an entire outfit of apparel, shoes and accessories. They are given scripts "so it's not so robotic and trains them to have more targeted talking points," says spokeswoman Louise Callagy. The goal is "to engage customers more directly while they are in stores to improve overall sales."
•Hudson News. The chain is using Dasani bottled water, a top-seller at its 400 newsstands in stations and airports, to add sales by asking buyers of other items, "Do you want water with that?"
"We started it at the end of last year when the economic crisis really started to rear its little head," says spokeswoman Laura Samuels. "We started to look for things that would be beneficial for customers and increase our bottom line."
There are 15% to 20% fewer shoppers, she says, but they are spending more. "That's a lot of, 'Do you want bottled water?' "
Not everyone, however, thinks "upsell" is the best strategy.
T.G.I. Friday's trains workers to push its loyalty program of discounts and promotions for regulars.
"By recognizing (regulars) in ways they appreciate, we increase their frequency of visits," says Andrew Jordan, chief marketing officer.
The added payoff, says Jordan: Regulars "spend significantly more per average check."
I blogged about Accpac and its databases here. This was a general article about how Accpac works with the various databases it supports. This article is more specific to Microsoft SQL Server and covers various topics that affect Accpac using SQL Server for its databases. I’ll be specifically talking about SQL Server 2008, but most of what I say applies equally to SQL Server 2005. Generally SQL Server is quite a reliable and low maintenance database server that doesn’t require much in the way of configuration and administration. Accpac uses SQL Server in a fairly straight forward manner issuing standard SQL statements without using SQL Server specific modules. This means Accpac runs on all editions of SQL Server, since we don’t use features only available in higher editions, and it means we can work with most of SQL Server’s built in services and configurations. We store the database in an open manner, following standard practices, which allows you to access your Accpac data using standard SQL aware tools including most reporting solutions. I also blogged on business continuity here, which is related to the topics discussed in this article.
Setup - Collation
One of the first decisions you need to make is a database collation sequence. This defines the character set used in the database as well as the sort order for the database. Generally choose the one that is best for your location and the language you use. However there are a couple of notes to consider:
Most western character set names either end in _BIN or _CI_AS such as Latin1_General_BIN or SQL_Latin1_General_CP1_CI_AS. The _BIN means that this is a binary sort order where strings will be sorted using a computer binary order, the upshot is that A-Z are before a-z and things like accented characters will be at the end. The CI_AS ones are case insensitive and will be sorted in dictionary order meaning AaBb… and accented characters are inserted in with their base letter. A few consequences of this for Accpac are:
- 1. If you are migrating from Pervasive.SQL, then since Pervasive uses a binary sort order, if you choose a _BIN sort order, there shouldn’t be any problems. If you choose a _CI_ one in SQL Server then since this is case insensitive, you will get record already exists errors if you load a database that has key fields that only vary by case. Typically this happens in unit of measures ilke Ea, EA, ea. If you want to make this conversion you will need to fix up your Pervasive database before DB-Dumping.
- 2. If you chose a _CI_ sort order then since strings are case insensitive, when you do searches, say from the finder, then it doesn’t matter the case you enter, so searching for steve would return Steve, STEVE or steve. This is the main reason people want to use this sort order.
- 3. If you want to change the sort order, you will need to DBDump create a new database in the new sort order and DBLoad, however you could get problems with the keys and need to make some manual adjustments based on the errors returned.
Setup - Configuration
At a minimum, you want to have TCP/IP and Shared Memory enabled as protocols. TCP/IP is required for the new Sage ERP Accpac 6.0A Portal, and is the network protocol that works best for the rest of Accpac. Shared Memory is a very high performance protocol that will be used if the program using SQL Server is running on the same computer as the SQL Server service, basically bypassing the whole network stack. The main gotcha here is SQL Server express which will have TCP/IP disabled and will further be configured to not receive external requests (connections tab of the server properties in the management studio). This is configured from the SQL Server Configuration Manager:
If you right click on the TCP/IP protocol, choose properties and then select the second tab, you can see the network port being used by SQL Server.
Database Setup
If you are running multiple instances of SQL Server, then you can enter the servername\instancename in Database Setup, however you can’t do this in the portal configuration. In the portal configuration you must you the server name and the correct port number. Each instance of SQL Server will be using a unique port number.
When Accpac first supported SQL Server we only supported access by a pre-setup ODBC data source. This led to problems with people setting these up incorrectly, since there are quite a few settings that were possible. Then later on we allowed you to connect by just specifying the server name (or servername\instancename) in database setup. Then we would correctly setup an ODBC data source for you (usually servername32). This then worked far more reliably. We left the ODBC option there so people already setup this way wouldn’t be affected, or if there was some reason that special configuration values were required, you could still set them. I don’t know of any such special configuration values, not do I know of any good reason to use an ODBC data source rather than the server name. So I would strongly recommend you always use the server name in Database Setup to configure your SQL Server companies.
Database Dump and Load
DBDump and DBLoad are two utilities that come with Accpac that allow you to easily dump databases into a database independent format and reload them. These allow you to easily move Accpac companies between locations or between different database servers. I blogged on these previously here. These are very handy utilities and have some interesting side effects like re-indexing and compacting databases (since they completely re-create and reload the database). However they aren’t as fast as the built in SQL Server backup and restore facilities.
64 Bit SQL Server
Although Sage ERP Accpac is a 32-bit application, it works just fine with the 64-bit version of SQL Server. You cannot directly load 32-bit DLLs into a 64-bit application, but this isn’t how SQL Server works. Accpac (or any other program) is never loaded into SQL Server’s process space. All communications is between two processes using a standard well defined protocol, so it doesn’t matter whether either is 32 or 64 bits. Generally you want to run the 64-version of SQL Server on a 64-Bit version of Windows Server 2008. This way you can get better performance by adding more memory (the 32-Bit versions don’t really use memory beyond 4gig).
One caveat is that the ODBC data sources used by Accpac are 32 bit ones and stored in the 32-bit part of the registry. To see them you have to use the 32-bit version of odbcad32.exe which you need to run from the strange location of: C:\Windows\SysWOW64\ODBCAD32.exe.
Network Bandwidth
If you are running in a standard workstation setup client/server configuration then each workstation communicates with SQL Server over the network. For best performance you want as good network connectivity as you can get, but generally a 100 Megabit connection is fine.
If you are running using Citrix or Terminal Server then if the SQL Server is also on the Terminal/Citrix server then it can use shared memory access which provides good performance. However this is tempered by how much memory and processor resources are required by the Accpac Business Logic, UIs and SQL Server. Generally once you get past a few users then it works better to separate the SQL Server over to a separate machine with a high speed connection to the Citrix/Terminal server, ideally having them both on a gigabit backbone. As we move to the Web, a Web Server will replace the Citrix/Terminal server and then the same advice would apply for the Web Server.
Another recommendation I’ve heard is to ensure you only use switches and not hubs and to beware that if you are using VOIP that you aren’t saturating your network bandwidth with phone traffic.
Disk Storage
I know a lot of people that swear by various DAS (Direct Attached Storage),NAS (Network Attached Storage) and SAN (Storage Area Network) solutions. But my own experience has been a bit negative with these. I find them very useful for storing large amounts of files, like virtual images, photos and such. But I find them un-reliable and often with performance problems when using them to store databases being directly accessed by a database server. There are also now cloud services to have your database in the cloud and your application server on premise, but I tend to be skeptical of the performance and uptime of such services. However these all make great backup devices.
My own preference is to use RAID storage on the server. RAID 0 is the fastest, but doesn’t have redundancy (and so never recommend this). Then the other RAID levels offer various levels of redundancy, so if a drive fails, it can be replaced without losing any data. Have a look at the link:http://en.wikipedia.org/wiki/RAID. The decision of which to go with depends a bit on how much redundancy you want to how much you are willing to pay. RAID 5 is very redundant, but tends to suffer a bit in performance.
A recommendation we often give is to locate your LDF files to RAID 1 storage and your MDF and other IDX files to RAID 1+0 storage.
Even with the reliability of RAID, make sure you still backup your company databases whether mirroring to offsite servers, backing up to rotating external hard drives, DBDumping, etc. Also remember it’s a good idea to test that you can restore your backups. Remember that the value of your company database far exceeds the value of the equipment it is stored on.
Disk Mirroring
SQL Server supports a number of database mirroring options; meaning that you can have your SQL Server stream database changes to a mirror SQL Server. Besides acting as a hot standby in case of failure, you can use this as a reporting server. Often reporting through Crystal can put a fairly big strain on the SQL Server, and one way offload this is to configure users that only do read-only access via reporting to connect to the mirror server. This way a department that runs many reports won’t impact the main data entry that is going on.
Summary
These are just a few tips and tricks for dealing with Accpac and SQL Server. Generally as your database becomes larger and the number of users increases, so does the amount of care you need to take of your database and network infrastructure; but, for smaller installations generally you just need to ensure you take regular backups. Of course if you don’t want to deal with database administration at all, then you might want to consider using a cloud service like AccpacOnline.
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.
GetBankTotal(String)
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
PrintNonNull(String)
Print one space if the blank spaces or null string is parsed. Print a parameter passed.
TranslateType(Number)
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"
RemoveLeadZero(String)
Removes leading zeroes from a string.
smGetSeries()
Returns the System Manager product edition (1=Enterprise, 4=Corporate, 6=Small Business).
LicenseStatus(String,String)
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.
OptionMulticurrency()
Returns “CS”.
OptionOptionalTables()
Returns “OB”.
OptionNationalAccounts()
Returns “NA”.
OptionOptionalFields()
Returns “OB”.
pwNumericStringToNumber(String)
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.
Upgrades
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.
Summary
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.
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.
Question from the Community.
Context:
When entering a quote or an order (Q2O) with the new SageCRM Q2O screen integrated with Sage ERP Accpac.
Issue:
If the user selects a kitted item they are prompted at the point of completing the line that you must provide a kit version for the kit just selected. The issue is that this field is not available to select the kit version within the new SageCRM Q2O entry screen.
Solution:
Within the setup of each kit within Inventory Control the default kit version must be set. If this value is set then the Q2O screen does not prompt for the kit version.
Additional thoughts:
Another possible solution is to use the Sage ERP Accpac 6.0 SDK and customize the Q2O list of items on the Q2O entry screen and add this field. This would require research to determine the feasibility of this idea as it would require a rather complex finder that would either show a BOM version or a Kit version depending on whether or not the item selected on the line is a BOM or a Kit item. This is because within the full Quote or Order Entry screen within Sage ERP Accpac 6.0 this field pulls double duty for the BOM/Kit version.
Sage ERP Accpac and SageCRM have a very tight integration. But the current generation of Accpac is a Windows desktop application and SageCRM is a Web Browser based application. SageCRM will display a fair amount of Accpac data in its web pages, but at some point you drill down into the Accpac application and an Accpac screen will be run. This causes a disconnect for the customer as they switch from the SageCRM Web application to the Accpac Desktop application. There are also performance problems with this context switch and some installation difficulties getting the “Web” based version of the current Accpac installed and configured properly since it uses ActiveX controls.
Now as Sage ERP Accpac transforms itself into a true zero-client Ajax HTML/Javascript Browser based application (with no ActiveX controls), we want that to leverage this new technology to improve our SageCRM integration. For Accpac 6.0 we will start that process by offering native Web based versions of our Quote and Orders screens that will run natively inside SageCRM. These screens will be styled like all the other CRM screens and customers shouldn’t be able to tell the difference whether they are working with one of these Accpac screens or any of the regular CRM screens.
The styling and screen aren’t fully developed yet, but below is an early prototype of the start of a Quote screen running as part of the CRM web page.
The key points are that the screen lives inside CRM, it does not pop out of the application and when it’s finished it will be styled to look like all the other screens.
This way a sales person running SageCRM, won’t know he’s running multiple Sage applications as he manages his contacts, leads and opportunities as well as entering quotes and orders. To him it is all one application.
There is a fair bit of extra work going on behind the scenes to ensure that the workflow is continuous, data is automatically transferred from one step to the next (like opportunity to quote to order), and data is automatically synchronized in both directions with Accpac. If some one edits an Order in Accpac, then the opportunity and other data is kept in sync in CRM. We are also smoothing out some of the supporting workflows, like how a lead is promoted to a customer.
There are a couple of other screens like an Order Summary screen that are being added to our SageCRM integration using the new Web Based UI framework. But many of the other screen like if you drill down into A/R or PJC will still result in the VB screen being run in 6.0. We will be looking to fix up these in Sage ERP Accpac 6.1.
Hopefully this is a step to making the End-to-End application experience much more seamless for customers. Combining this with the SData initiative, we should start seeing much closer integrations between Sage products as they incorporate all these new and exciting technologies.
Sage ERP Accpac 5.6A recently shipped with:
- New Serialized Inventory, Lot Tracking
- Improved Bank Reconciliation
- Sage Accpac Intelligence
- Simplified Installation and Data Activation
- Free Training
- Built in A/R Inquiry Tool
- Many other great features.
Now we can look ahead to Sage ERP Accpac 6.0A and 6.1A. In previous blog posts I’ve talked about many of the features individually, but here I just want to summarize and present the current roadmap.
The main goal of Sage ERP Accpac 6.0A is: Solving Customer Requests with Web Technology
– Usability: Improve Productivity
• Role Based, Personalized Portal
• Task Oriented Navigation
• Contextual User Assistance
– Reporting: Faster Information Access
• Integrated Dashboards/KPI’s
• Ad-hoc Inquiry Tool – Quick Information Lists
– Integration: Streamline Processes
• Enhanced Quote-to-Order Workflow for SageCRM Users
• Elimination of Lanpak Requirement for SageCRM User for Order Entry
• SageCRM 7.0 Support
Then the main goal of Sage ERP Accpac 6.1A is to bring all the Accpac Accounting Application screens to the Web. The Views (Accpac’s Business Logic), Reports and Database Schema will be largely the same as previous versions. The big change will be moving the User Interface forms from a Visual Basic Windows Desktop program to becoming a true Web Based application.
User Interface -> Modernized with “Web 2.0” Experience
• Improved Productivity for Faster Data Entry
• Improved Learnability
• Financials: General Ledger / Accounts Receivable / Accounts Payable / Bank Services / Tax
• Operations: Order Entry / Inventory Control / Purchase Order
The look of the product and all the screens is quite updated; see my previous blog postings for screenshots and fuller descriptions. Below is the current product roadmap slide with the mandatory disclaimer.
One of the big features in Accpac 6 is SData support. What is SData? SData is a web services protocol based on REST. What is REST? REST is a web services protocol that is very popular among Internet companies. There are basically two main competing standards for web services. One is SOAP which is promoted by Microsoft, Oracle and IBM; this is a fairly heavy duty protocol which requires a fair bit of infrastructure (which is provided by the companies promoting this protocol). The other is REST which was invented as a University Research project and basically uses existing Web Technologies such as HTTP and RSS to implement a web services protocol. The nice thing about REST is that it doesn’t require any extra middleware. You are basically doing everything via standard web URLs. The infrastructure that supports this is basically just the standard Internet. SData is just an extension to REST, similar to Google’s GData which is also based on REST. REST protocols are used by Google, Amazon, eBay, Yahoo and all the main Internet companies. Gartner estimates that 75% of web services in use on the Internet are REST based. REST/SData also provides a standard mechanism for performing CRUD (create, read, update, delete) on all records.
From Accpac, every Accpac View (or Business Logic Object) will be exposed via SData. This provides third party ISVs a complete, efficient Web Services interface to communicate to Accpac with. Also all Sage applications are being made SData aware, meaning that all Sage applications can manipulate SData Web Services (or feeds). For instance here is some Accpac A/R Customer and G/L Account data displayed within a Sage CRM Dashboard using SData.
From a simple wizard you can select any Accpac SData feed and once you’ve chosen a feed, you can select which columns you wish to display. Additionally Sage CRM and Accpac will be converting to use SData for our integration needs, this transition will occur over several versions of both products. In the same way you will be able to access CRM data from Accpac using SData.
All the new Sage ERP Accpac 6 screens are written entirely using SData. This means the screen can access any Accpac Views via SData, but additionally they can access any SData feeds from any product. In Accpac 5.x, the screen controls were mapped via datasource controls to views and view fields. This was great, but it limited you to only connecting our standard controls to fields in Views developed in our SDK. Now that the controls are mapped to a standards based SData fields, it means they will be able to be connected easily to much more data than just Accapc data. Look for other Sage products to start providing SData interfaces in their coming versions.
In the Sage ERP Accpac 5.x series of product, all screen definitions were part of the Visual Basic programs that made up each user screen. The only way to get at these screen definitions was to embed the OCX for that screen in a VB program or VBA macro and then manipulate the screen programatically. With Accpac 6.0, we wanted to make many screen customizations way easier. The solution was to separate the screen definition from the User Interface program. In Accpac 6.0 each screen is defined in an XML file that contains all the layout information. This screen can be edited in a Visual Screen Designer that is part of the product, or since XML is just a text file, it can be edited in a standard text or XML editor. For that matter the screen can be generated from another program. The definition of the screen layout file (xsd file) is available and the format is completely open.
In Accpac 5.x, we saved a lot of programming time by binding the individual UI elements to datasources. This meant that an edit control was actually attached to a View field via its properties in the VB form designer. Then the control would be data aware and handle setting its data into the view and retrieving updated data from the View. In this way, no programming was required for most of the forms on the screen. We have exactly the same concept in Sage ERP Accpac 6.x. Only each control is bound to a field with in an SData feed and that SData feed could theoretically come from any Sage application that provides SData feeds or from any other application that has adopted the open SData format. Thus again no programming is required for most fields on the form.
Then we have gone one step further and provided standard actions that can be attached to controls in the layout. In Accpac 5.x you had to code all the logic that happened when a button was pressed. In Accpac 6.x we provide standard actions, so for instance you can attach a save action to the save button which will perform the save operation on the configured SData feed. This way we can develop simple forms with navigation, save, new and delete operations, with no programming. The declarative layout actually produces a complete running program can run with no additional code. Many of the Accpac setup and other simple screens can be developed this way, greatly speeding the development of Accpac 6.x.
The layouts for Accpac 6 are quite different from the screens in Accpac 5.x. For one thing all controls were placed on 5.x screens at specific x,y co-ordinates. In Accpac 6, all layout is handled by browser document object model layout nodes. Nothing is specified in x,y coordinates, you just specify which controls line up and how and then the process is dynamic depending on the length and size of the text. This is a great benefit when translating into languages that take more space like Spanish since the form will automatically adjust to the longer text. When running a right to left language like Arabic, it’s even smart enough to be able to re-arrange all the controls into a right to left ordering. All this is just functionality built into every browser and something we gladly don’t have to worry about.
These are just some of the new things that declarative layouts are bringing to Sage ERP Accpac 6.0A. This will really be a strong and flexible foundation to develop on.
The new Sage ERP Accpac 6.0 Portal can display a selection from a number of Data Portlets. Think of these as a new dashboard of key performance indicators and corporate summary data. All the data is up to date when the pages is displayed and can be refreshed at any time. Below is a selection of Data Portlets from A/R, G/L and A/P.
These give you a nice graphical view of the status of your company. Then if you hover over a piece of the graph it will tell you the data, plus you can click on any of the hyperlinks to run a report. You can also configure the Portlet to give you the data you need.
Implementing these Data Portlets wasn’t just a matter of accessing Accpac data the same old way; we had to optimize the database schema so that we could quickly retrieve this data with in seconds. We had to change the index structure of a number of A/R and A/P tables to speed up the Aging Portlets. We had to collect extra summary data at posting time to support the days outstanding Data Portlets. We have been running benchmarks on quite a few large customer databases to ensure the performance is sufficient to make these Data Portlets useful. We are aiming to make the Portal and Data Portlets fast enough so you can run six such Data Portlets; but, we anticipate a given user would typically have two of these on their home page.
To support printing Financial Reports from the G/L Data Portlets we needed to add tables to hold the generated data, so Crystal Reports could run off the correct data.
The G/L Account Groups now have a new “Group Category” to allow you to configure how your Accounts will appear in the G/L Portlets.
Hopefully these Data Portlets will give customers a useful view of their company as soon as they sign-on to Accpac and provide a useful starting point to getting their work done.