Diagnosing Problems Running Accpac Part 4

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.