Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘crystal reports

Looking Back on 2013 and Forward to 2014

with 6 comments

Introduction

2013 was a very busy year for all of us on the Sage 300 ERP R&D team. Most of us are located in Richmond, BC, but we have remote team members in Kelowna, BC, Victoria, BC, North Carolina, Chennai, India and Bangalore, India. As we approach New Year 2014 let’s look back on what we’ve been up to this past year.

Happy New Year 2014 replace 2013 concept on the sea beach

Product Update 1

We started the year by releasing the Sage 300 ERP 2012 Product Update 1. This included new features like Colorful Companies and a major new release of Canadian and US Payroll.

As always a lot of work goes into product sustainment. Fixing problems caused by new versions of Windows, strange interactions discovered with other programs and other bugs. Diagnosing and tracking down the root causes of these can be quite demanding work, but is completely necessary to keep our customers happy. Quality is always a high priority and always an ongoing concern.

Sage 300 ERP 2014

We have a core team that works on improving the Sage 300 ERP core product and they spent their time working on the Sage 300 ERP 2014 release. This version has been released to a limited VIP set of customers and will start to be publicized to the full Sage 300 audience in March.

I wrote quite a few articles on the various things in this release including:

Another major improvement in Sage 300 EPR 2014 that I haven’t blogged about yet, is the Crystal Reports Support. With this release we are bundling Service Pack 8 of the Crystal Reports 2011 runtime. This service pack has some major performance improvements over the version of the Crystal Reports Runtime bundled with the 2012 version. Actually someone in our office reverse engineered the Crystal runtime to figure out why it was becoming quite slow in some cases and was able to feed this information to the Crystal Reports development team (that is located nearby in downtown Vancouver) so that we could get this rectified. If you are having trouble with performance on the 2012 version, you can now get the Service Pack 8 from Crystal’s website and install this. With this service pack we now get performance that is consistently better than that experience with the older runtime bundled with 6.0A, but now with all the extra features and supported platforms that are provided as part of the their 2011 version. Generally being in the same city makes working with the Crystal development team easier, plus we have a lot of cross-over since Crystal Reports was originally developed as an add-in reporting tool for Accpac Plus.

With this release we add support for Windows 8.1, Windows Server 2012 R2 and Office 2013. We also have added support to run when Windows DEP is turned on. Generally updating anything to ensure we run well in any modern environment.

Windows 8 and Windows Server 2012 had a bug in their printer drivers that caused many fonts to be converted to bitmaps. This then defeated products like PrintBoss as well as things like Micr checks and anything else that is very sensitive to fonts. This problem was fixed by Microsoft in Windows 8.1 and Server 2012 R2. So if you are having trouble with this, make sure you upgrade these (which is free).

Sage 300 Online

Another major project was the new Sage 300 Online, which we unveiled at Sage Summit. This is a major revamp of our hosted offering. Now we host Sage 300 in the Microsoft Azure cloud and have greatly improved the manner in which we virtualize Sage 300. Authentication is handled by SageID which is also used for our connected services and Sage One. All the management, upgrades and tuning is handle by Sage so that customers don’t need to worry about this.

online

We are managing the site with a new DevOps team that consists of members dedicated to the site along with other members of R&D and IS. This is a new model for running the site, managing loads and keeping everything and everyone up to date.

Sage 300 Online has now started to on-board our first customers. At first we are starting out slowly to ensure everything keeps working fine, but expect to see a major push for this services in a few months.

We are now switching to a continuous delivery model, so expect to see improvement to this service being put on-line frequently.

Sage Connected Services

We have team members helping out with the Sage Connected Services. This year we released: Sage Mobile Service, Sage Mobile Sales and Sage Billing and Payments. We are now working on new versions of these as well as working on building out the Sage Data Cloud platform and bringing out further mobile and web applications.

The cloud part of these applications is developed for the Microsoft Azure PaaS platform. The mobile applications are written as native applications for iOS and in the future Android.

Our core team is responsible for maintaining the Sage 300 connector which transfers data back and forth from an on premise database and the Sage Data Cloud.

Next Year

As we go into 2014 we are building on all these initiatives. The general roadmap is still what I outlined here however as we go into the year, many of the details will be announced. Such as what is in the next version of Sage 300 ERP, what will be in all the Product Updates and what will the next version of Sage 300 Online actually look like.

Besides product changes, we will be having a lot of process changes. Now that Sage 300 Online is launched, we will be living in a continuous delivery, DevOps and cloud first world. Seamless invisible upgrades will be the norm, features can be deployed to all our cloud customers instantly and frequently. Ensuring that all these new processes run smoothly will be one of our major challenges in 2014.

Summary

Sage 300 ERP is an important strategic product for Sage. There is a lot of exciting development going on. We accomplished a lot in 2013 and look forward to even more in 2014. I am really looking forward to attending our various conferences this year to show off all the exciting work that is currently going on behind the scenes.

Advertisements

Written by smist08

December 28, 2013 at 6:33 pm

Customization: A Two-Edged Sword

with 2 comments

Introduction

When implementing a mid-market ERP system, it’s often suggested that the base ERP should provide 80% of the needed functionality and then the other 20% is provided by customization. The rationale behind this is that although GAAP (Generally Accepted Accounting Principles) is standard, each business is unique and has unique requirements in addition to GAAP, especially in modules outside of the core financials.

All Sage ERP products offer quite high levels of customization ability. Generally we use this as a competitive advantage to make sales, since we can more closely match a customer’s requirements. However some customizations can lead to quite high costs and problems down the road (perhaps a version or two later).

As more and more customers are looking to move to the Cloud, we need to rethink customization and what we are trying to achieve. This blog post looks at some of the problems with Customization and some things to consider when recommending these. These apply to both hosting current ERP’s in the Cloud as well as to how we have to think about Connected Services and Features as a Service (FaaS) in the Cloud.

Problems

This is a rather unfair list of many of the problems we typically see with customizations:

  1. Cannot upgrade to a new version of the software because it means doing the customizations all over again.
  2. An initial product implementation fails because the costs and delivery of the customization go over budget and behind schedule.
  3. Some customizations cause system stability issues. For instance adding SQL triggers to the database. Then if the trigger fails, the transaction that invoked it fails with a really hard to diagnose error.
  4. Some customizations cause performance load problems. Perhaps a custom report or inquiry causes a SQL query to run that takes hours to finish, slowing everyone else in the meantime.
  5. In a hosted version everyone runs the same programs, so some customizations can prevent you moving to the cloud.
  6. Extra testing is required for every product update or hotfix to ensure they don’t interfere with customization. This restricts the use of things like automatic updates.
  7. Getting support and diagnosing problems. Customer support has a hard time diagnosing problem in and around customizations because they don’t know what the customizations are meant to do and what they might affect.

Moving to the Cloud

As we look to move more customers to the cloud, we have to deal with all these customization issues. Some level of customization is necessary, so the question to some degree becomes how much? In the cloud we want everyone sharing the same set of programs, so no customized DLLs or EXEs. In the cloud we want everyone to immediately receive hotfixes and product updates, so customizations must be upgrade safe. Further when a new major version comes out, we want everyone in the cloud moved to this new version in a quick, transparent and automated fashion. Hence any customizations allowed in the cloud can’t prevent data activations and can’t require a lot of special testing for each new version.

Causes

So what causes many of the problems above? A lot of customization can be a sign of an initial incorrect product choice resulting in trying to fit a square peg into a round hole. It can also indicate an overly eager to please sales team promising the product will do many things that it normally doesn’t. Plus many companies main line of business is developing customizations, so providing these is a major source of revenue.

A big cause of problems with upgrading customization is the result of database schema changes in the core ERP. This is usually the result of adding new features or streamlining functionality for performance and scalability reasons.

Often business logic changes can have unforeseen effects on customizations, even though the database schema doesn’t change, perhaps a customization relies on something in the way the business logic used to work.

Solutions

As solutions move to the cloud the nature and scope of customizations is changing. This affects both vendors like Sage, in how we need to make sure all customizations can be automatically updated and it affects customization consultants now that you can’t install EXEs or DLLs into the ERP or CRM system itself. So what do we do, since there are still vital business needs that need to be addressed?

First off, we as a vendor have to be much more disciplined in how we change our applications from version to version. Long gone are the days when we could just make any change we wanted and then throw it over the fence to the Business Partners to figure out how to accommodate in the whole eco-system. This made the cost of upgrading quite expensive and detracted from the overall customer experience because generally upgrades introduced all sorts of problems that then needed to be stamped out. As we begin to automatically update people, both in the cloud and on-premise we have to ensure the whole process is automatic and seamless. We have to:

  • Maintain application API compatibility so customizations and ISVs continue to work.
  • Limit schema changes so that we don’t break customizations or ISVs. Mostly just add fields or tables and even if fields are no longer really used, leave them in for compatibility.
  • Provide tools to automatically convert anything beyond the database that is affected like Crystal Reports or VBA macros, so no additional work is required.
  • Ensure that we can upgrade independent of database state, i.e. don’t require that all batches be posted or such things.
  • Work closely with ISVs and Business Partners through alpha, beta and early adopter programs to ensure the entire ecosystem won’t be disrupted by new features and versions.
  • More tightly control the customizations that are allowed in the cloud. Even for custom reports and VBA macros, have rules and monitoring procedures so that badly behaved ones can be found and removed (or fixed).

When we are running our application in the cloud, how do we perform common integration type customizations? A great many of our clients create programs to feed data into and out of an ERP to integrate to things like perhaps a custom subscription billing service, a company shopping web site or one of any number of things. Since you can’t install EXEs or DLLs and even if you could do this via a VBA macros, chances are external communications would be blocked by a firewall, so what do you do? For these cases you have to switch to using Web Services. Which in the Sage world means SData. With SData you will be able to create Cloud to On-Premise integrations or Cloud to Cloud integrations. Basically you are removing the requirement that the two applications being integrated are on the same LAN.

Summary

Changing the thinking on customization will take time, but the industry and customer expectations are changing. Sage and its Business Partners need to be more disciplined in how we all customize to ensure we don’t create longer term problems for customers. In the Cloud world these problems show up much more quickly than in the on-premise world. We have to re-evaluate the technologies we are using and re-evaluate how our common customization design patterns are changing.

I tend to think once we get through some transitional pain, that we will come out the other side with a more powerful and more sustainable customization model that will lead to a better customer experience.

Written by smist08

September 29, 2012 at 6:20 pm

Reporting Via Macros

with 71 comments

Introduction

With our Sage 300 ERP 2012 release we updated our Crystal Reports runtime to the newest Crystal 2011 runtime (SP3 actually). The intent is to move to a fully supported version of Crystal Reports, so as they adapt to things like Windows 8 and Windows 2012 Server, we know we are fully supported and can get updates for any problems that show up. Plus it means that people customizing reports can take advantage of any of the new features there.

For reports you can print to preview, print to file or print directly to a printer. Then we have various options for printing from various web contexts like Quotes to Orders. You can drive reports from our regular forms, or you can write VBA macros that automate the reporting process.

This blog post is really for people that are controlling printing reports programmatically and are more affected by the changes in the Crystal runtime and more specifically changes in the Crystal Reports API.

Headache for Customizers

Our intent was that people performing customizations will use our API to drive Crystal Reports. Then your programs are upgrade safe since we maintain compatibility of our COM API. However it turns out that quite a few people have automated the report process by writing to the Crystal COM API directly.

This then leads to a problem because Crystal dropped support for their COM API. Not only did they drop support for it, but they removed it completely from the product. Hence anyone that is writing directly to the Crystal COM API will be broken by the Sage 300 ERP 2012 release. At least for new installs. If you had an older version and don’t un-install it, then you can still use the older version of the Crystal runtime (since it will be still there), but that isn’t a good long term solution as people upgrade computers and go to newer operating systems like Windows 8.

Crystal Reports now only supports a .Net Interface and a Java interface. For this version we had to change our internal interface to Crystal from COM to .Net. (The newer Web portal parts use the Java interface and so were ok).

Printing without User Intervention

It appears that one of the common reasons to go to the Crystal API directly is to print to file without any manual intervention. Often if you choose File as a print destination then we prompt you for the format and then prompt you for the file name to save to. People want to set these programmatically. Our API does have the ability to do this in a couple of situations.

Below is a macro I recorded to print O/E Quotes. I deleted any extra code, like the error handler to make it a bit more compact. Then I edited the destination and format to change the print destination to PD_FILE and the format to PF_PDF.

Sub MainSub()
Dim temp As Boolean
 Dim rpt As AccpacCOMAPI.AccpacReport
 Set rpt = ReportSelect("OEQUOT01[OEQUOT01.RPT]", "      ", "      ")
 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
 Set rptPrintSetup = GetPrintSetup("      ", "      ")
 rptPrintSetup.DeviceName = "HP LaserJet P3010 Series UPD PS"
 rptPrintSetup.OutputName = "WSD-ad0e8bc6-396c-4e50-84c7-fab17beaf18a.006a"
 rptPrintSetup.Orientation = 1
 rptPrintSetup.PaperSize = 1
 rptPrintSetup.PaperSource = 15
 rpt.PrinterSetup rptPrintSetup
 rpt.SetParam "PRINTED", "0"              ' Report parameter: 4
 rpt.SetParam "QTYDEC", "0"               ' Report parameter: 5
 rpt.SetParam "SORTFROM", " "             ' Report parameter: 2
 rpt.SetParam "SORTTO", "ZZZZZZZZZZZZZZZZZZZZZZ"   ' Report parameter: 3
 rpt.SetParam "SWDELMETHOD", "3"          ' Report parameter: 6
 rpt.SetParam "PRINTKIT", "0"             ' Report parameter: 7
 rpt.SetParam "PRINTBOM", "0"             ' Report parameter: 8
 rpt.SetParam "@SELECTION_CRITERIA", "(({OEORDH.ORDNUMBER} >= """") AND 
     ({OEORDH.ORDNUMBER} <= ""ZZZZZZZZZZZZZZZZZZZZZZ"")) AND (({OEORDH.COMPLETE} = 1) OR 
     ({OEORDH.COMPLETE} = 2)) AND ({OEORDH.TYPE} = 4) AND (({OEORDH.PRINTSTAT} = 1) OR 
     ({OEORDH.PRINTSTAT} = 0) OR ({OEORDH.PRINTSTAT} = -1))"   ' Report parameter: 0
 rpt.NumOfCopies = 1
 rpt.Destination = PD_FILE
 rpt.Format = PF_PDF
 rpt.PrintDir = "c:\temp\quote.pdf"
 rpt.PrintReport
End Sub

 

Basically this technique will silently export to PDF files. The Format member also accepts PF_RTF which will export silently in RTF format. The file they export to is specified in the PrintDir property. If this is a folder, the filename will be the same as the report name, if it’s a filename, it will be that (make sure you get the extension right).

You can also export silently to HTML format by setting the Destination to PD_HTML. For HTML if there is one file then the PrintDir specifies the filename, but sometimes HTML required multiple files, in which case it will use the PrintDir as a folder name.

These are a few ways you can print reports to a file silently without user intervention.

Secret Parameters

In the recorded macro above, you might notice the strange parameter “@SELECTION_CRITERIA”. If you look in the report, there is no such report parameter. Basically our API lets you set report parameters and then print the report. However there are a few other things you might want to do with the Crystal Reports API. Below is a list of these special parameters that might help you get a grip on some more aspects of the Crystal Reports API:

“@SELECTION_CRITERIA”: PESetSelectionFormula (job, asParamValue). This parameter is changed into the API to set the Selection Criteria in the report.

“@SELECTION_CRITERIA_xxxx”:  where  xxxx is the name assigned to the subreport when it was first created. This call will be translated into

job = PEOpenSubreport( parentJob, xxxx)
PESetSelectionFormula (job, asParamValue)

To set the selection criteria in the designated subreport.

“@SELECTION_ADDCRITERIA”: will add the parameter specified to the selection criteria that exists inside the report.

“@TABLENAME”: The parameter value must be in the form:

“table” “name”

Each instance of “table” will be switched to “name” before each PESetNthTableLocation call. This is done for both the main report and all subreports. You must put the table and name in quotes or the parameter will be rejected. Table names are not treated as case sensitive.

“EMAILSENDTO”:   The call will be set the email address when you are using PD_EMAIL.  This will suppress the popping up of the address book dialog.

“EMAILSUBJECT”: The call will be set the email subject when you are using PD_EMAIL.

“EMAILTEXT”: This parameter will be set the email body when you are using PD_EMAIL.

“EMAILPROFILE”:  This parameter will be set the email profile when you are using PD_EMAIL.

“EMAILPROFILEPWD”: This parameter will be set the email password when you are using PD_EMAIL. This password will be used to sign-in to MAPI.

The following are some parameters that if you create them in your report, then the system will automatically set them without you having to do any programming.

“CMPNAME”: The company name.

“ACCPACUSERID”: The Sage 300 User ID.

“ACCPACSESSIONDATE”: The session date from signon.

“ALIGNMENT”: The current alignment option.

“REGIONALFMT” : The current regional format.

Summary

We went through this Crystal API pain once before when Crystal dropped their DLL interface (crpe32.dll). At that point we had to change all API access over to COM. At that point we had similar issues due to various people using the DLL interface and now having to re-code for the COM interface. At that point we did add some functionality to our API, namely we added the TABLENAME special parameter, so that a number of people could start using our API.

Hopefully most people can switch from using the Crystal COM API directly to using the Sage 300 ERP API, however if something is missing, please comment on this blog, so we can consider expanding our API in the future. I’ve already gotten a couple of requests to add silently exporting to Excel format in addition to RTF and PDF. Keep in mind that like we have gone from DLL to COM to .Net, chances are in a few versions, the Crystal API will change again (perhaps to a REST web services API) and we will have to go through this again.

Written by smist08

September 15, 2012 at 7:50 pm

ERP and Crystal Reports

with 3 comments

Introduction

I put a questionnaire on how people liked Crystal Reports up on the “Sage Partners, Employees & Alumni Networking Group” group on LinkedIn and received a lot of 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 the various challenges faced.

History

Accpac (now Sage 300 ERP) was originally developed by the Basic Software Group in the eighties in Vancouver. They started a project to have a new WYSIWYG report writer as part of the product rather than the codes based approach being used. This project was cancelled and some of the people involved quit and started their own company to develop Crystal Reports (then Quick Reports). This was originally developed as an add-in product for the MS-DOS based Accpac Plus. Then they generalized the product to dynamically look at any database and that became the Crystal Reports of today. The original founders then struck it rich when they sold their company to Seagate in 1994, which was trying to branch out from hard disks to software. Eventually Seagate gave up on this and Crystal went private and independent as Crystal Decisions. Shortly after in 2003 it was sold again to Business Objects. Then in 2008 Business Objects was bought by SAP where it lives today.

All of Sage 100, 300, 500 and X3 ERP use Crystal Reports as their main reporting engine. Sage HRMS and Sage CRM also use Crystal. We all adopted Crystal long ago at some stage or another. Sage 300 ERP adopted Crystal when we switched from using CARET (the Computer Associates report writer) back at version 3.0A.

Most products started integrating with Crystal in the 16-Bit world of Windows 3.1. We all integrated via the DLL interface that was to the DLL: crpe.dll. This interface let us print reports to a printer, preview or file. It let us set parameters that were passed from application’s entry forms to Crystal for things like to/from ranges. The Crystal runtime that included crpe.dll was installed to a directory c:\windows\crystal. This meant that any product that installed the Crystal runtime would usually overwrite what was already there, meaning the last product using Crystal to be installed would usually work, whereas most other products using Crystal would then stop working. This was the famous DLL hell.

In these days we had to use native drivers for accessing the database, Sage ERP 300 shipped two versions of its reports, one set using the native Btrieve driver and the other using ODBC for SQL Server. This tended to be a maintenance headache. Having multiple reports for different databases, or even different page sizes like letter vs. A4 vs. legal. Having different reports depending whether G/L is used or not. Over the years as Crystal functionality has improved and ODBC drivers have improved, we’ve been able to reduce the number of report versions. We now ship one version of reports using ODBC that is just configured to look at the correct database regardless of type.

Then we all started to move to 32 bit Windows with the advent of Windows 95. Crystal produced a 32-bit version and the new interface DLL was crpe32.dll. This was a close match to the 16-bit version and programs could work with Crystal in the 32 bit world very similarly to how they did in the 16 bit world. However DLL hell still remained. Plus we started to see the appearance of Terminal Server and Citrix. To keep users separated on TS, anything that a user installs to the Windows directory actually goes to a local Windows directory. The problem now is that the Crystal directory would go to the local user’s private windows directory and hence Crystal would only work for the user that installed the product and not for any other users on the Terminal Server. This then led to a manual procedure of copying the Crystal subdirectory either to the main Windows directory or each user’s local Windows directory.

With Crystal Reports 9, Crystal moved to eliminate the DLL hell problem. They dropped support for calling the crpe32.dll directly and moved the Crystal runtimes file over under Program Files under a version labeled directory. This allowed multiple version of the Crystal runtime to be installed at the same time and fixed the problems with Terminal Server and Citrix. However applications that used Crystal had to switch from using the crpe32.dll to using Crystal’s COM interface.

Crystal X and XI then followed and these worked very similarly to Crystal 9. Another nice thing was at this point Crystal had adopted an extensible file format, so the file format stopped changing between versions making upgrades easier.

Crystal Reports 2008 then dropped their COM API and now only supports integration via .Net and Java interfaces. This presented a number of problems, namely since these interfaces were quite different than the ones that went before them. Plus Crystal Reports was a fairly major upgrade to XI and seemed to introduce quite a few bugs. At this point ERP packages integrating to Crystal either had trouble with the new interfaces or had trouble with existing reports not running properly. This led to a bit of a low adoption of CR 2008. However the file format remained the same, so you could use CR 2008 to edit reports that would later be rendered in the ERP package via an earlier Crystal runtime, and again co-existence of multiple versions isn’t a problem due to the side-by-side DLLs.

Crystal has now released Crystal Reports 2011 and this fixes most of the problem in CR 2008. So we should start to see ERP packages moving forwards again. This version still only supports .Net and Java interfaces but has fixed a number of bugs and deficiencies that were causing adoption problems.

Challenges

For setting parameters and running reports, most ERP packages can do this fairly handily. However when you start to push the integration, what are some of the problems you can run into?

Sage ERP 500 (previously MAS 500) tries to simplify report customization by actually generating reports based on a higher level report designer built into the Sage ERP 500 product. This then eliminates a lot of the challenges in customizing reports inside the Crystal Designer. However this means that Sage ERP 500 uses the Crystal Design API and this API changes a lot from version to version making upgrades much harder. Sage ERP 500 also controls access to its database via a customer ODBC driver that then adds the Sage ERP 500 security model to the generic ODBC access.

Sage ERP 300 (previously Accpac) has a concept called “datapipes”. These were originally created for CARET as a performance enhancement and then taken over to Crystal. The concept is that you use these in place of ODBC to connect to the database and then these datapipes use the Sage 300 ERP database ERP to access the database. This allows the datapipes to use special knowledge of the Sage 300 ERP database schema to provide faster access. We use them to “flatten” out subreports, since accessing subreports in Crystal can be slow. We also use these for controlling access to things like G/L accounts with our G/L security module so users don’t see data they aren’t entitled to.

Reports Server

Using the Crystal Reports runtime from an ERP package is fairly straight forwards and easy, it can be installed with the ERP package and usually works quite well, so the user doesn’t have any extra hardware, installation or management problems. If you need to customize reports you need to buy and install Crystal Reports, but to just run reports there is no extra overhead. However much new development by Crystal is requiring the Crystal Reports Server.

The Crystal Reports Server is a central server that manages the printing and distribution of reports. You can use it to schedule when reports run and who gets the results. Most new Crystal development is now requiring a Reports Server to operate. For instance if you want to use the new Crystal Data Universe to make creating reports easier then you need a Reports Server to hold these. Similarly several new technologies like Crystal’s dash-boarding technology require the Crystal Server.

For us ERP vendors, we now have to evaluate the ROI for our customers. Is requiring this new server worth it? Is it worth the hardware, setup and licensing costs? Long term, is this tying the ERP package too closely to Crystal making adopting other technologies harder? At this point I don’t have a clear answer to these questions, but we are working with the new things in Reports Server to see their costs and benefits.

Summary

Crystal Reports has a long and interesting history and has become the de facto standard in report writers across all ERP packages. It is very full functioned and works with any database server. It takes a bit to learn how to use it effectively and is a very powerful tool for Business Partners that use it. The main complaint about Crystal is that it’s hard for end users to customize their own reports, due to some of its complexities.

Written by smist08

January 21, 2012 at 4:32 pm

Customizing Crystal Reports for Sage ERP Accpac

with 20 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.

Written by smist08

May 21, 2011 at 10:24 pm

Posted in sage 300

Tagged with , ,

Drilling Down from Crystal Reports in Sage ERP Accpac 6

with 7 comments

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.

Written by smist08

June 4, 2010 at 10:46 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 , ,