Back in the 80s when I started using The Professional Portfolio, the pre-Windows version of what is now known as Axys, there were relatively few easy ways to enhance reporting. Like a lot of things back then, we had to do it the hard way. There were then, as now, limits to what could be done within Advent’s infrastructure. When we came up against something that could not be done, or could not without Herculean efforts, we stepped outside of the environment and utilized programming tools to get the job done.
One of the first major projects I worked on was a client billing system that generated management fee summaries for custodians, and invoices for clients; it had many of the same features that have only recently become available to Axys users. That program was written in QuickBasic almost 20 years ago. In order to produce presentation quality invoices, specific HPGL2 (“printer language”) code needed to be written to format text and shade certain areas. The results were first rate but the effort necessary was daunting.
In the 90s, we started using Visual Basic (VB) to create one-page account summaries for our clients. By 1995 we were using VB and Crystal Reports to create reporting systems that were way ahead of their time, but the work behind the scenes was still considerable. Over the years, report writing products have evolved, becoming more robust and making it simpler than ever to produce sophisticated reports. There are two fundamental ways you can produce better reports in-house: within the Axys/APX environment or outside of the Axys/APX environment
Staying within has the potential benefits of keeping initial implementation costs down, facilitating standard Axys automation techniques, and lowering future maintenance costs. Cost may be kept in check by nature of the fact that the Axys/APX system’s rigid infrastructure puts the kibosh on what can be done. This approach is absolutely fine for firms that have simple reporting requirements and are willing to accept the limits imposed by this choice.
Here are some report enhancing tips for those staying within the Axys/APX envelope:
1. Utilize Axys master pages and consistent reporting styles to create continuity across reports.
2. The macro based compound reporting feature need not be used to create reports that look like they were “shrinky-dinked” to fit on the same page. Perseverance, patience and creativity usually payoff when designing compound reports.
3. Axys formatting has a certain look and feel that is dependent on report writing syntax. Most of the time you want Axys formatting, but if you don’t want standard formatting you can work your way around it by showing an Excel worksheet instead of a graph and formatting the Excel sheet with Excel macros.
4. Advent’s Excel data link can be further leveraged by utilizing Excel graph add-ins to give your reports a presentation quality that exceeds standard faire Excel graphs.
5. Battle-tested integrators should know that the limits of Excel integration can be stretched to allow the insertion of related data that may not be in Axys or the APX database.
6. It is sometimes okay to tell REPLANG a white lie about whether the report is really a one page report to get all the reports you want to fit on the page.
7. APX features “public views” which allow technology savvy users to create reports from the APX database. However, the public views are limited and do not give these same users access to all of the data they would like.
Staying within the confines of Axys/APX comes with a cost. The compound reporting feature can be finicky. Some things that should work do not work as expected. The resulting trial and error testing can be frustrating and time-consuming. In some cases, you may eventually find that you cannot do something that you should be able to do, forcing you to figure out an alternative way to reach your goal. These mindbending setbacks can delay or derail a reporting project.
This is a sample of what we have done working within the Axys/APX environment to create custom reports using compound report macros.
Stepping outside of the Axys/APX environment by extracting the data and placing it in a database opens up a new world of possibilities, but it should only be done if complex reporting requirements demand it.
The key perceived benefits of this approach can be best described in one word – flexibility. As a direct result of having data accessible in a standard database rather than a proprietary data format, you gain the ability to use report writing tools like Crystal Reports or SQL Server Reporting Services (SSRS) to produce complex reports efficiently, thereby placing your report developers in a more mainstream development environment where solutions to problems encountered are more readily available. This provides a foundation that empowers consultants/staff to act without necessarily requiring a subject matter expert to facilitate progress.
Some guidelines for creating a successful report production environment outside of Axys/APX follow:
1. Extract all the data you need from Axys/APX. You should have a subject matter expert architect and assist with the development of this feature or buy a product that performs the extract for you.
2. Design a well organized data structure.
3. Import data and perform data quality checks up at the earliest point in your report generation process.
4. Create standard report templates similar to Axys master pages and report styles functionality.
5. Take the time to mockup/document the possible permutations of report packages and determine what the process to generate your reports will look like. Seemingly, little details like page numbers, PDF merging, letterhead, disclosures can be a thorn in your side if you do not plan adequately for them in advance.
This is a sample of what we have done by stepping outside of the Axys/APX environment using Crystal Reports to create custom reports.
Beware; this approach is not perfect either. The newfound, godlike ability to make any report cuts both ways. Because you can do almost anything, you will likely spend more time designing reports than you would have. Popular report writing programs have idiosyncrasies, too. As you develop your reports, you will no doubt be disappointed to learn that your chosen solution set has limitations of its own. Report development in this environment typically takes longer, but has more predictable results and corresponding timelines. Using this method also requires that you retain reliable and responsive resources with the ability to maintain and enhance your system as needs change over time.
The labor required to produce reports outside of Axys/APX is a multiple of what it costs to do so within Axys/APX, but the resulting reporting packages should be easily identified as high-end boutique productions, not standard client reporting packages. For companies that consider their reporting systems a top priority and are willing to allocate the necessary resources to produce uniquely engaging reporting packages, going outside the Axys/APX envelope makes sense.
This article was originally published in the Advent Users Group newsletter in 2008.
About the Author:
Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide. For more information, please visit isitc.com , contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.