Stephen Smith's Blog

All things Sage ERP…

Customizing Crystal Reports for Sage ERP Accpac

with 11 comments


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

Customization Directories

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

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

Complicated Reports

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

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

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

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

User Function Libraries

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

IsTrue (String)

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

pwFormatDate (Number)

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

pwFormatTime (Number)

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

TrimZeros (String)

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

LookUpString(String, String, String)

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

SaveNumber(String, String, String, String)

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

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.

About these ads

Written by smist08

May 21, 2011 at 10:24 pm

11 Responses

Subscribe to comments with RSS.

  1. Very good and valuable post for those who are starting writing Reports using Crystal Reports for Accpac.
    I write a small column on crystal Report with Sage Accpac as well.

    http://goo.gl/BHqIX

    Regards,
    Aggyey

    Aggyey

    May 22, 2011 at 8:44 am

  2. Very good. Especially the section on Accpac fanctions library.

    Just one question regarding datapipes. Standard Crystal installation does not provide the datapipe driver *.dll. So if you install CR from original ditribution or Visual Studio distribution, you do not have Accpac datapipe sources available to create the new report (or verify existing reports). The same thing is with these add-on functions described in the library. How to best handle this situation in order to add them to CR?

    Aivars Olins

    May 24, 2011 at 8:32 am

    • Sometimes its easiest to just run Workstation Setup on that computer, this will install the necessary files. Otherwise we install these to a directory like C:\Program Files (x86)\Business Objects\Common\3.5\bin (depending a bit on which version of Accpac you have). We put the following files here: p2bpipe.dll, p2ctpipe.dll, p2bcsv.dll, p2ctcsv.dll, p2ctcsv.dll, pwufleng.dll, u2lcapw.dll, pwuflesn.dll, pwuflfra.dll, pwuflchn.dll, pwuflcht.dll, u2lchks.dll. If you copy these by hand this will give you the UFLs and Datapipe driver, but the datapipe driver won’t be able to access Accpac data without Workstation Setup working.

      smist08

      May 24, 2011 at 2:51 pm

  3. [...] good discussion. I’ve previously blogged on customizing Crystal Reports for Sage 300 ERP (Accpac) here. This blog is more about the history of how ERP to Crystal integrations have gone in the past and [...]

  4. […] I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have access to all processing functions like Posting Batches or […]

  5. Thanks Stephen for the great article. I have a question about how to add new tables/fields to a report (standard report or subreport within a datpiped report) for fields you need

    I know how to do this easily enough thru a DSN that points to one particular database in its own unqie location, but how to add a new Accpac table to a report that needs to be able to run on multiple companies? Obviously all of Accpac’s out-of-the-box reports will work on multiple companies, so somehow the database location is passed thru the report at runtime.

    Let’s say I want to modify an order confirmation to include optional fields….I need to add OEORDHO to the report. It’s easy enought to add that table by setting up a DSN that points to OEORDHO in one particular database, but how to add that table so that the report still works no matter which company database Accpac is running at at the time the report needs to be printed?

    KenA

    September 20, 2013 at 10:11 pm

    • You don’t need to do anything special. As long as the report is connected using the ODBC driver. We go through the report and any tables that are in our data dictionary we set the database location to the current company. There isn’t anything separate to specify the tables or what to do with them.

      If the report is datapipe, then you need to add the new tables in a sub-report of type ODBC. Since we don’t set ODBC tables for a datapipe type report. But we will for subreports if they are type ODBC.

      smist08

      September 20, 2013 at 10:15 pm

      • Thanks Stephen, great info.

        Since we’re on the subject of customizing Accpac’s reports using Crystal, I have one other quick question, if I may. The question is also related to the issue of adding new tables/fields to an existing Accpac report (without regard to whether the report is datapiped or not):

        What about the scenario where you want to modify a report (for example one of the invoice RPT’s) to add new table/fields to it, but you want to be do this report editing on a different machine (with the Crystal report editor loaded on it) that is NOT on the same network as Accpac…and thus you cannot see the Accpac database? Can you even do this?

        My intent would be that once I complete the report modifications I would put a copy of the RPT back onto the machine/network that is runnng Accpac, and tus I know it will then be able to access the new table/fields…but for now I’d like to edit a copy of that report on a standalone machine, offline from the Accpac database server.

        Is this possible? I tried this with the report offline, and when I bring up Crystal’s “Database Expert” to add a new table/field, I can see a list of the report’s already selected tables (and their fields) on the right-hand side of the Expert, but on the left-hand side it seems to expect me to connect to and browse thru the database to find and select the the new Accpac table I want to add….and if I’m offline, I can’t see those new tables/fields (even though I already know the table and field name I want) because I can’t connect to that database.

        Is this even do-able? If not, not…. but I’ve always been curious to know if the only way to add a new table/field to an Accpac report is to do it within a copy of Crystal that is located on the same machine (or network) as the Accpac programs and data so that I can connect to the database from within Crystal, or if there’s a way to do it “offline.” Thanks!

        KenA

        September 23, 2013 at 2:47 am

      • You need to be able to connect to a database to edit it a report. But you can use a copy of sample data in a SQL Express database as one way to go. You don’t need the production database. Just take a backup of sample data and load it on your remote computer.

        smist08

        September 23, 2013 at 3:14 pm

    • Thanks Steve, using a sample database is a great idea. Of course, it really isn’t so much of an issue nowadays, beacuse everybody has connectivity and with VPN and other remoting methods, most of the time you can get a connection t the production adtabase, but it’s good to know that a report can still be editerd even if you are offline.

      KenA

      September 25, 2013 at 1:54 am

  6. […] 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 Busines…  […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 254 other followers

%d bloggers like this: