Stephen Smith's Blog

Musings on Machine Learning…

Archive for May 2010

How to Layout Forms in Sage ERP Accpac 6

with 7 comments

Laying out data entry forms on the web is quite different than laying out forms in Visual Basic. In Visual Basic you place each control on the form with absolute (X,Y) co-ordinates. This leads to problems when you translate text strings, if they overflow the space then you have to manually re-arrange the controls. Resizing windows is difficult since the program needs to figure out how to move and size controls as the form’s size changes.

If you’ve done data entry in a Browser, you’ve probably seen that all forms seem to adapt to the size of the browser and to the length of strings. If a string gets longer things just move over. Originally the browser didn’t have the luxury of programs running behind the scenes to move and size controls as conditions changed, so HTML was originally designed to display forms correctly in windows of different sizes and resolutions. The Browser even supports features like in IE where you can choose Page – Encoding – Right to Left and it will automatically re-arrange the page for right to left reading. HTML was originally designed to not depend on the presence of fonts and to handle running with any font size that the user has set. Below is a bitmap of our sign on page in right-to-left mode. It shows a bug in our decorator panel for doing the rounded corners on the “Sign In” button. It also shows the danger of putting things in bitmaps since the right hand pane and text is all a bitmap and as a consequence isn’t adjusted.

The Browser accomplishes this by representing pages as a tree called the Document Object Model (DOM) (http://en.wikipedia.org/wiki/Document_Object_Model). In this tree there are nodes for layout and there are nodes that contain data like text or images. Typically each layout node will have multiple children.

Inside the Sage Web Toolkit (SWT), layout nodes are represented by layout panels. This way you place the panel on the form, then any controls you place in the panel become its children. You can place layout controls inside other layout controls to build up more complicated layouts. You never specify (X,Y) co-ordinates when building a SWT form. You simply build it by putting controls inside other controls. This makes customization easier, since it won’t easily overlap controls, so if you add a control to a form, all the other controls will move out of the way to make room.

For instance to layout a simple label with an edit box and a button might be done as follows. First a vertical panel is added to be the root object, then a horizontal panel is placed inside the vertical panel. Inside the horizontal panel is placed a label and a text box. So now the label and text box will always be horizontally aligned. The text box will always be to the right of the label and will move over if the text in the label gets longer. Then the button is added to the vertical panel under the horizontal panel. So this form is built without specifying co-ordinates and the text in the label can be any length and the form will adjust to it. If another control is added to the vertical panel between the horizontal panel and button then the button will move down to make room. This makes translation and maintenance of the form very easy since you don’t need to re-arrange everything when you add something or a string’s length changes.

In tree view you can more clearly see how objects are placed inside other objects, or how the tree that makes up the DOM is built up.

These screens shots are of the Accpac SDK’s UI Designer which runs as an Eclipse plug-in that you use when developing forms. The forms themselves are stored as XML files and you can edit this with any XML or text editor as well as the visual screen designer.

The layout panels that are part of Accpac are:

  1. Caption Panel – like a group box, has a caption and puts a box around its contents.
  2. Decorator Panel – wraps a control in 9 images to allow effects like rounded corners.
  3. Disclosure Panel – a panel that can be opened and closed to hide or show the contents.
  4. Dock Panel – Allows you to place controls in the N, S, E, W corners or center.
  5. Flow Panel – Controls placed here flow one after the other as space permits.
  6. Grid Panel – Constructs a grid of a set size with a control in each grid cell.
  7. Horizontal Panel – Controls placed here are arranged horizontally.
  8. Vertical Panel – Controls placed here are arranged vertically.

This isn’t very many layout panels, but by nesting and combining these you can develop very sophisticated layouts, that will adapt to changing text lengths, font sizes, screen resolutions and other factors. Typically we make heavy use of the Grid, Horizontal and Vertical Panels.

For a more complicated example here is part of A/R Invoice Entry:

And in tree view:

If you’ve worked previously either with Java program user interfaces or with web designers, you will probably be familiar with these concepts. In Java the layouts aren’t part of the form, like they are here. Similarly some web tools abstract the layout controls quite differently. But if you are familiar either with Java UIs or web page design, then these concepts should be easy to understand. If you are coming from a Microsoft development tools background then these concepts will take a bit of getting used to. However once you do get used to them, you won’t really want to go back. Generally you have to think a bit more when initially laying out a form. But once a form is done, maintenance becomes very easy going forwards.

Update 2011/11/05: Have a look at this posting on using CSS to layout forms.

Written by smist08

May 28, 2010 at 10:27 pm

Posted in sage 300

Tagged with , , , ,

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 Macro Tips and Tricks

with 19 comments

Sage ERP Accpac uses Visual Basic for Applications (VBA) (http://en.wikipedia.org/wiki/Visual_Basic_for_Applications) as its macro language. This is the same macro language used by Microsoft Office and many other Windows desktop applications. With VBA macros you can access the Accpac Business Logic layer and enter documents, perform processing like posting batches or print reports. Since every screen in Accpac is an ActiveX control, you can embed any screen in a VBA macro and then manipulate/customize it in a standard uniform manner. Writing VBA macros is a huge topic; there are many books available on the topic along with courses at Sage University on VBA specifically for Accpac. This blog posting just lists a few useful tips and tricks that people find helpful when programming Accpac macros. These assume a general knowledge of writing basic Accpac macros. These aren’t in any particular order and tend to jump all over the place.

Upgrading to a New Version of Accpac

If your macro doesn’t customize an Accpac User Interface Screen then often your macro will just run without any changes. The exception tends to be if you are using a set of Views where their required compositions change. When you run you might get an error saying a required View composition is missing. In this case you should re-macro record the Accpac screen that uses these Views and add the missing View compositions. This tends to especially effect macros that are entering Order Entry documents. The main view compositions added for 5.6A are all to do with Serialized Inventory and Lot Tracking which were fully integrated into the core accounting modules.

The rest of this section applies to macros that customize Accpac User Interface Screens. It would also apply to you receiving a new version of some third party ActiveX control. When you insert an ActiveX control into a VBA macro, VBA reads all the information from the control and caches it in an EXD file. Unfortunately VBA doesn’t check when a new version of the control is installed and keeps using the out of date information. Full VB does something similar with OCA files, but is smart enough to update these when a control is updated. Worse the EXD file is strictly based on the base file name of the control, not its class id in the registry and not on the directory where it’s installed.

When we install a new version of Accpac we install our ActiveX controls with the same control names, but with new class ids. This way macros and other users of these controls can still use either version. When we install Product Updates we overwrite the current control, but have compiled the control with “binary compatibility” so all its interfaces are still compatible. For product updates, it is usually worth deleting the EXD files as we have seen problems with these in spite of binary compatibility.

When we upgrade to a new version, we need to delete the existing control from the macro and then insert the new control, along with deleting all the EXD files. The procedure for this is as follows:

  1. Delete screen control from form
  2. Delete icon from toolbar
  3. Remove reference from Tools – References
  4. Save macro and exit VBA
  5. Go to c:\documents and settings\username\Application Data\Microsoft\Forms
  6. Delete the *.exd files
  7. Load the macro
  8. Add the new control to the palette
  9. Make sure the version is correct (from the path)
  10. Insert the control back onto the UserForm
  11. Make sure the name matches what you used before
  12. Save the macro

References

References that you add to your macro project must match exactly the references on your client’s computers. For instance if you add a reference for an Office 2007 object like an Excel worksheet and your client has Office 2003, then the macro won’t run and will have to be corrected by removing the Office 2007 reference and adding one for Office 2003. Most COM/ActiveX library references are version specific. Also note the previous section on deleting EXD files. Similarly your version of Accpac must match the client’s to the Service Pack level.

Also beware of components you might have that a client doesn’t. For instance you might have full Visual Basic installed, which provides a large variety of useful COM libraries to use. However your client probably won’t have VB installed, so you must ensure the library use are using is freely distributable and install it on your client’s computer.

Windows has a number of optional components like Windows Scripting Host. If you require this, you will need to provide instructions on how to add this to Windows.

Debugging

Use Debug.Print to print things out to the intermediates window to trace things (the intermediates window is closed by defaults, so you need to open it to see the output).

The built-in VBA debugger is very powerful and a great way to step through your program to see what is going on. Set a breakpoint just before the problem area and step through your code. Takes a bit of practice to know when to “step in” versus “step over”. Run to cursor is a very useful function.

Always include an error handler in your macro. Any View error will trigger an exception and there will be a text error message that will tell you why the exception happened. Even if you don’t get errors, you still want this error handler in case your clients get errors. For instance if your client makes an optional field required and your macro doesn’t provide it, is very hard to debug if you don’t have an error handler, but an error handler will instantly tell you what is wrong.

If you are running a long process like importing hundreds of order and you just want it to go through and sort out the errors later, then write all the errors to a file with code such as:

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
WriteToErrorLog Err.Description
Else
lCount = Errors.Count

If lCount = 0 Then
WriteToErrorLog Err.Description
Else
For lIndex = 0 To lCount – 1
WriteToErrorLog Errors.Item(lIndex)
Next

Errors.Clear
End If
Resume Next

End If

Public lErrorFileHandle As Long

Public Sub WriteToErrorLog(sMessage As String)
If lErrorFileHandle = 0 Then
Dim sFileName As String
sFileName = “c:\popjctest.txt”

lErrorFileHandle = FreeFile(1)
Open sFileName For Output Access Write As #lErrorFileHandle
End If

If Trim(sMessage) = “” Then
Close #lErrorFileHandle
lErrorFileHandle = 0
Else
Print #lErrorFileHandle, sMessage
End If

End Sub

Getting Information From Accpac

Use macro recording to get a starting point. This is especially useful to get the view.open and view.compose calls correct. Often a lot of noise and hard coded values are recorded that need to be cleaned up. The main source of errors we see in macros are due to bad view compositions, so this is really quite important.

Use RVSpy (from the Accpac – Tools start menu). This tool spies on every View call made. Use it to see what Accpac does and use it to see what your macro is doing. Often easier to use if you turn off recording of view functions you aren’t interested in like attribs or presents.

Use the Application Object Model that is posted on our web site. This contains very useful information on the Views and underlying database tables (useful for reporting).

Use the Accpac U.I. Info tools (from the Accpac – Tools start menu). This gives info on UI programs as well as views.

If your macro isn’t working, try doing the same thing in the Accpac UI to ensure you aren’t being blocked because of some sort of settings or something.

Read the macro chapters in the System Manager User’s Guide, these have some good information on how the Accpac Views and macros work.

Keep a Backup of Your Macros

Always keep a backup of your macros, or use a source control system like SubVersion (http://subversion.apache.org/) or Git (http://git-scm.com/). Sometime a macro file might become corrupted, or you might accidentally delete some important code. It’s always nice to go back to a previous version that isn’t too old.

Global Variables

Set global variables to Nothing when the macro completes. This is useful when debugging. If you are running the VBA Editor, then global variables are not cleared when the macro end automatically (they are cleared when VBA exits). So if you re-run the macro it will run with the global variables still having their values from the last run which can cause confusion. Worse even if you assign each variable as you start, this means the old value will be cleared at this point sometimes causing errors if they are objects.

Objects like datasources must be set to Nothing before the macro terminates or VBA may GPF as it shutsdown.

Only One UI

You can only embed one UI in a VBA project at a time. All the Accpac screens use the same variable names and due to bugs in VBA this causes problems. You should be able to refer to them by object name like AccpacCS2000.datasource versus AccpacAP1000.datasource, but VBA gets confused and won’t work properly.

Don’t Use Init

We have replaced Init with RecordGenerate, RecordCreate and RecordClear to make it clear what we are trying to do. Init did all of these and sometimes did the wrong thing. Newer Accpac Views will only accept the Record* functions and will reject Init.

VBA Versus VB

VBA has a number of limitations imposed by Microsoft, so you are motivated to buy full VB. These limitation include:

  • Having a menu
  • Having an icon (for when minimized and in the task bar)
  • Receive events from popup forms
  • Resizable windows

VBControlExtender

In VB you have to define the controls of type VBControlExtender because unlike VBA, VB can’t do the conversion for you. Here’s an example getting the Click event of a notebook in full VB:

Dim WithEvents tabctl As VBControlExtender

Private Sub AccpacBK1600UICtrl1_OnUIAppOpened()
Set tabctl = AccpacBK1600UICtrl1.UIAppControls(“tbOptions”).GetControl

End Sub

Private Sub tabctl_ObjectEvent(Info As EventInfo)
If Info.Name = “Click” Then
MsgBox (“tab clicked”)
End If

End Sub

(In VBA we could just access the tabctl directly without using a VBControlExtender).

Well that wraps up my blog posting for this week. Next week I’m attending Sage Insights 2010 in Denver Colorado. If any readers are also attending, make sure you say “Hi”.

Written by smist08

May 14, 2010 at 2:35 am

Posted in sage 300

Tagged with , ,

On the Sage GCRM Contract

with 5 comments

Sage has standardized on a REST based Web Services protocol called SData. This protocol is documented at http://sdata.sage.com/. As a first step this will standardize the technology that you use to communicate with Sage applications. You will be able to issue SData requests to multiple Sage products such as Accpac, Abra and SageCRM. As opposed to using a different technology to talk to each, such as COM, .Net, Java, Soap, DLL, etc. All the products will still maintain their existing APIs, but they will all add SData to their repertoire. Even if you are a reporting type application, crossing multiple Sage products can be difficult as they tend to use different databases or have very different schemas. SData is a nice way to hide these differences and to provide a single consistent programming interface.

The next step is to standardize the actual data that is exchanged via SData. Towards this end, Sage is releasing a number of “contracts” that can be used to interface to a given application. For instance the Sage Global CRM (GCRM) Contract (http://sdata.sage.com/sdatacust_GCRM.html) is a standard contract supported by all Sage CRM products including Act!, SalesLogix and SageCRM. If someone integrates to one of these products using SData and the GCRM Contract, then it will be very easy to integrate to any of the other CRM products.

The GCRM contract was created by Sage for mostly selfish reasons. Sage owns dozens of ERP packages in dozens of regions around the world. Having each of these create a point to point integration with one of our CRM packages was proving to be a huge amount of development effort. Plus the results weren’t consistent, some of the integrations were better than others. This started as an attempt to standardize what was being done repeatedly over Sage again and again. However as the work progressed we realized there are a lot of other benefits. Not only does this save Sage some development time and money but it will:

  • Provide a standard interface for others to do similar integrations. For instance an ISV could integrate another CRM package to multiple Sage ERP’s by taking advantage of the GCRM contract.
  • ISVs will be able to integrate to multiple Sage applications at once (usually part of a suite of products) using only 1 technology.
  • The various application contracts are well documented at http://sdata.sage.com/sdatacust.html making life a bit easier.
  • Using REST based web services is an efficient way to produce a nice lightly coupled integrations.

Each application will still have a native SData interface where you can get at the full functionality of that product. But these standard contract interfaces will make it far easier for ISV’s to integrate their vertical solutions to multiple Sage products.

Many applications have a component of their data that is shared and needs to be synchronized. For instance much of the data that CRM maintains for its companies is shared by the ERP package in its customer’s information. In most CRM to ERP integrations a large part of the integration is keeping this company/customer data synchronized in the two applications. Similarly for an HR application to ERP integration, typically the employee database tables need to be synchronized in the two applications. SData provides a standardized protocol to allow multiple applications to synchronize data in a uniform manner (http://sdata.sage.com/sdatasync.html).

At the core of the GCRM Contract is the specification of how to synchronize the tradingAccount (company/customer), contact, postalAddress, phoneNumber and email tables. The GCRM contract specifies the fields that make up these standard contract definitions (how CRM and ERP expose their native tables) and what the applications need to do to provide the synchronization. In this case the hard part is handled by the CRM application which hosts the synchronization engine. The ERP package just needs to provide an SData feed for each of these tables in the correct format. Then CRM will periodically ask ERP if anything has changed and if so to provide the updated records. The protocol then has the details of how to handle conflicts and how to catch up if one application was unavailable for a period of time.

The GCRM Contract really consists of three parts:

  1. The base customer/company tables that need to be synchronized and the protocol to handle that.
  2. A number of real time data SData feeds in a standard format to access much of the data in the CRM product.
  3. A set of SData feed definitions that allow CRM to provide a basic Order Entry screen, to query pricing information from the ERP and feed orders into the ERP package.

These three parts can be used independently. For instance Accpac could use the GCRM contract synchronization contract to replace the current method and start incorporating CRM data into its own UIs that is retrieved by the real time SData feeds, but then rather than using the CRM Order Entry screen, surface the Accpac Order Entry screen inside of CRM (the quote-to-order feature: https://smist08.wordpress.com/2009/12/17/sage-erp-accpac-6-0-quote-to-orders/).

As Sage moves ahead with adopting our REST based Web Services, SData, you will be seeing more and more new technologies based on this. Generally Sage is looking to develop many SData based technologies that can be shared across all Sage products and can be utilized by all Sage development partners.

Written by smist08

May 7, 2010 at 8:35 pm

Diagnosing Problems Running Accpac Part 4

with 10 comments

So far in this series we’ve looked at the steps taken to start the desktop, sign-on and run an application user interface form. With this part we’ll look in detail at the report printing process and various problems that can occur there. Accpac uses Crystal Reports for all transaction reports and all printed forms. For Financial Reports we provide an Excel add-in that generates the Financial Reports from within Excel. We also sell a number of Excel based Business Intelligence tools including Accpac Insights and Intelligence. Then there are a number of third party reporting tools such as Stonefield Query or Orchid Information Manager. This blog posting is mostly about Crystal Reports as it comes integrated into the core Accpac product, but also mentions a few things about our built-in Financial Reporter near the end.

Since CA-Accpac 3.0A we have used Crystal Reports as our main reporting tool. Within System Manager we provide a number of interfaces to generate reports. These interfaces then use Crystal Report’s SDK’s APIs to load and generate the actual report. To run a report, we need to load and initialize the Crystal Report Engine, load our report (RPT file), set a number of parameters (like to/from ranges, sort orders), and generate the report (whether to preview, printer, email or export).

Reports are run from regular Accpac User Interface programs that gather the parameters for the report like To/From ranges and sort orders. Then when the user hits the Print button, the Crystal API is called to generate the report. Each Accpac application saves information about its reports in an xxRPT.INI files in its application directory (for instance accpac\ar56a\arrpt.ini). These INI files define the Crystal Report filename and all the parameters the report takes. There is also a datapipe.ini file that is used to assist Crystal when editing datapipe reports, but isn’t used to run reports from Accpac.

Accpac reports are language specific, so the actual Crystal report file is saved in a language specific subdirectory (like accpac\ar56a\eng). If you customize reports and don’t want to overwrite the reports that came with Accpac (or risk having Accpac overwrite your customized report) then you can save it in a customization directory. You define customizations directories in “Administrative Services”. You can have different directories by user and by company. Or you can use “*” to specify all users or all companies. Note that the directory structure under your customization directory needs to match that as under the Accpac Program Directories. If you customize ARCULT01.RPT and specified you customization directory as c:\mycust, then you need to put the file in c:\mycust\ar56a\eng\arcult01.rpt.

Crystal Report 8.5 and prior installed to a Windows\Crystal directory. This caused problems for Terminal Server users because it installed into a single user’s Windows\Crystal directory rather than a shared one for all TS users. So then someone had to copy this directory for all the other users. With Crystal Reports 9 and later, it is installed under program files\common files which then works fine for all TS users. Using the Windows\Crystal directory caused versioning problems. If you installed a product that came with the Crystal 8.0 runtime, it would happily install it to Windows\Crystal and then applications that required 8.5 wouldn’t run anymore. The advantage to the Crystal 9 and later runtimes is that they install in directories by version number and will run as side by side DLLs, meaning you can use multiple versions at once. Once we got past Crystal 8.5, support calls greatly diminished for reporting related problems.

Accpac has two sorts of Crystal Reports. There are Crystal Reports that access data via ODBC. These reports send SQL queries through the ODBC interface to whichever database houses your Accpac data. Since these reports use ODBC they do not rely on Accpac and so you can easily preview them inside the Crystal designer, they are relatively easy to customize, and you can easily add other fields and tables. The other sort of report is the dreaded “Datapipe Report”. These reports access Accpac through a custom Crystal database driver that accesses the Accpac data through a “Datapipe DLL” which uses the Accpac “Database Driver” API to access the company database. The datapipe DLL returns a fixed set of fields that you can’t extend, so customizing datapipe reports is more difficult, its also difficult to preview them inside the Crystal Designer. You can add an ODBC subreport to a Datapipe main report to customize, but this can introduce performance problems. The original purpose of Datapipe Reports was to speed up reports that have slow performance due to a complicated structure or that result in complicated database joins. However other functionality has been added over the years, namely supplying complicated calculated fields to Crystal along with adding G/L Security support to G/L’s reports. Since datapipe reports access data through the Accpac Database API, you can use DBSpy to spy on them. If you want to spy on ODBC reports you need to use a tool like ODBCTrace (http://support.microsoft.com/kb/274551) or SQLTrace.

Accpac also adds a number of Crystal User Function Libraries to Crystal Reports. These include u2lcapw.dll and u2lchks.dll. These are installed into: C:\Program Files\Common Files\Business Objects\3.0\bin (or something similar depending on the version of Accpac you are running). These DLLs add functions into the Crystal Reports formula language. They perform functions like formatting dates to match how they look in Accpac, or calling Accpac to spell our money amounts for checks. Most of these are fairly easy to use and don’t cause trouble, but some like the money spelling routine need to load other Accpac DLLs to run and require Accpac be installed and usable.

There are a number of methods to troubleshoot reports. One easy method is to just record a macro, this will record a VBA macro of the report printing process. Then you can look at the recorded macro to see if anything looks suspicious, like bad report parameters. Next, you can use Accpac  Spy to spy on the “Accpac Server Report”, which will give you a similar view, but with more detail from the COM interface layer. The final level is to set the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\ACCPAC International, Inc.\ACCPAC\Debug\Repcmd to “D”. This will make the lower level Accpac report processor output additional debugging information to the Windows Debug Console. The best way to see these messages is to use the DbgView utility from System Internals (now Microsoft). Download it from http://www.sysinternals.com.

If you are creating custom Crystal Reports and want to distribute them to other people and have them run on all the supported Accpac databases, then before shipping them you need to verify them using either Pervasive or Oracle. If the last time they were verified was with SQL Server then the report will use SQL Server extensions and won’t work when run against Pervasive or Oracle. Its fine to develop using SQL Server, just do a final report database verify against one of the other databases before handing your report out.

If you run Web deployed or run reports from Accpac forms that are launched from SageCRM, then your reports are going to be displayed inside the Crystal Reports ActiveX views in the Browser. Additionally these reports will be served up either by the Crystal RAS server or Crystal Enterprise (depending on your version and Crystal’s current naming scheme). When having problems here you need to check that CE is running ok. Often running the Web Deployment Wizard again can help if something in the environment changed.

Accpac Financial Reports from the General Ledger module are produced by Excel. We provide an Excel plug-in that will drive Excel to generate Financial Reports as well as provide an FR menu in Excel to help design Financial Report specifications. The main problem that people run into is having Excel configured to not run macros. You need to change macro security to allow the macros associated with FR to run. Otherwise ensure you have a version of Excel compatible with the version of Accpac you are running. Not having Excel at all is another problem. Financial Reporter accesses Accpac data using our regular Business Logic Objects (Views). This means you can spy on what is going on using both DBSpy and RVSpy.

Well, that concludes this series on diagnosing problems running Accpac. Hopefully it gives an idea of what is going on as Accpac performs some of these functions and gives some suggestions of things to try to diagnose problems.

Written by smist08

May 1, 2010 at 7:55 pm

Posted in sage 300

Tagged with , ,