Customizing Crystal Reports for Sage ERP Accpac
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.
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.
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.
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).
Formats a date in the same way the Accpac does in UI programs. The number should be of the form yyyymmdd.
Formats a time value in the same way Accpac does in UI programs. The number should be of the form hhmmsshh.
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:
ID Total Value
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)
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.
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:
6 BCD (fixed precision numeric)
7 Short Integer
8 Long Integer
The forth parameter is not used (but you must provide one, empty string is ok).
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:
- 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.
- 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.
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.