Tag Archive: SQL Server Reporting Services


iStock_000007871357XSmallYour firm has just completed its implementation of APX. All systems are go including a small collection of SSRS reports, which meet some but probably not all of your firm’s requirements. You have new reporting capabilities, and now the question is “will your firm ever use these new features?” SSRS is also known as Microsoft Reporting Services, which sounds a little less complicated. No matter what the name is, SSRS is a beast – the following issues will challenge your firm’s ability to embrace and leverage SSRS technology for the foreseeable future.

1. TOOL INSTALLATION – SSRS tools like Report Builder and Business Intelligence Development Studio (BIDS) will not be installed on most of your PCs. They are kept at an arms-length from most users, and rightfully so. Though SQL Server Manager and SSRS reporting tools can be accessed on the database server, it currently isn’t Advent Software’s policy to install the applications that give users access to these tools on all users’ PCs. Assuming you have someone at your office with relevant report-writing experience, getting their system configured to make SSRS reports and/or modifications is special request. I have worked with many APX users. By default, most of them do not have access to the tools, so they could not use them or even see them. Some firms using APX 3.x do not even have access to SSRS reports because they have not been installed.

2. ENVIRONMENTAL COMPLEXITY – Once the tools have been installed, the collection of SSRS reports is open to users’ review and modification, but the infrastructure and understanding it requires is cumbersome. Most APX users do not have the skills necessary to create SSRS reports, and very few of those who do are interested in doing it. For those unfamiliar with SSRS and other similar report-writing tools, seemingly simple reporting modifications can be a pain if datasets aren’t designed with your specific reporting needs in mind. Those writing reports need to make frequent backups. Occasionally, reports can become corrupted and cause their writers to lose hours of work.

3. TIME – Compared to creating compound reports and building reports using Advent Report Writer Pro, developing reports using SSRS and other similar report writers like Crystal takes much more time. This is the norm, but not the rule. There are specific report-writing tasks that SSRS is more efficient at performing, but overall report-writing with SSRS is exponentially more complex than using Advent’s standard report-writing tools. This is due to the fact that SSRS development and modifications are the domain of Business Intelligence (BI) professionals and other system integrators who do it for a living. Report Writer Pro and compound reporting were developed by Advent to be used by investment operations end-users with limited technical know-how. SSRS was created by Microsoft, and is not designed with these same users in mind. Some simple SSRS reports take minutes to create, but it is much more likely for users to spend hours, weeks or even months working on reports.

4. COST – Since your firm is unlikely to have BI report developer resources internally, you will need to hire outside resources to develop your reports. That sounds familiar, right? Assuming that you, like many Advent APX clients, spend somewhere around 100k to 200k annually on APX, you can expect to pay at least another 15k to 30k annually to get the reports you want and keep them maintained by qualified third-party resources. You may be able to get the work done cheaper, but anyone delivering reporting services on a platform this complex at a significantly lower price will not be in business for long.

5. AVAILABILITY OF QUALIFIED RESOURCES – Since SSRS is still relatively new to Advent users, there are very few BI resources available with specific experience working for APX users. The learning curve is steep. Significant integration and reporting work needs to be done for individual firms to fully embrace SSRS as their reporting platform, and short-term that leads to a smaller pool of available resources to do the work.

RUNNING WITH SSRS
Due to the complexity inherent in combining various data elements via SSRS and workflow automation, some APX users may still be better off using the REPLANG and compound report functionality first introduced in Axys. Standardizing your firm’s reports using SSRS on Advent’s APX platform could be tough. For many users, standardizing will mean trying to make standard (REPLANG) reports look like they were created in SSRS, or worse, completely reengineering those reports in SSRS.

Advent deserves credit for implementing SSRS. It is a progressive move aimed at satisfying the enterprise users for which APX was designed, but some firms using APX should ask themselves whether they truly are an “enterprise” before they start implementing tools designed for enterprises. (In the near future, I will be blogging on the issue of firm identity and the role it plays in the success or failure of technology implementations.)

Long-term, there is good news for many APX users. Though creating reports can be very complex, the format of SSRS reports is extremely portable, which should eventually lead to more report sharing among APX users. Unfortunately, while this may be good news for APX users, BI developers and integrators like ISITC have to be more concerned with the portability of their end product.

One could literally spend hundreds of hours developing a report and have someone walk away with it. In other stickier environments, reports might be developed at a discount, but an integrator’s sunk costs could easily be recouped through a nearly guaranteed long-term maintenance agreement. Given concerns regarding portability, you should expect to pay a premium to have SSRS reports developed for your firm.

Firms making a significant investment to develop distinctive reports in APX now should be equally concerned with maintaining those reports in the future. Advent and third parties that create reporting solutions regularly make updates to address bugs and/or add functionality to reports. Though APX users may not realize it, this environment is still fairly sticky. Those unfamiliar with specific reports can easily perform the simplest modifications, but firms will do well to retain those that write their SSRS reports to address more complex modifications in the future.

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 details, please visit isitc.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

Whether you are an investment professional or an individual investor, you want to be on the respective sending or receiving end of meaningful statements that effectively convey what has transpired on a quarterly basis. These reports may represent clients and investors’ only communication of the quarter. As such, the importance of mainstream client reports cannot be underestimated. Advisors do not necessarily need a state-of-the-art reporting package, but they also cannot afford to have their reports look dated.

A couple of years ago, I was approached by an investment firm looking to overhaul their client statements. The impetus was the departure of a client, who was “nice” enough to drop off a sample of their new investment advisor’s reporting package. I was presented with the package and asked how much it would cost to do something like this for them.

I went through the lengthy presentation-quality package in detail. It was clearly something that was produced through a custom report-writing engine or a third-party, and not a standard report package by any means. The reports included comprehensive summaries of holdings, allocation, fixed income, equity, and performance. All of the reports contained detailed graphs and charts.

We came up with a number – roughly 50k – and we were not surprised when the prospective client did a double take and put the project out for other bids. Our bid was for a system that worked with the underlying Advent data, but stood on its own. In order to produce the high-quality output they wanted, we needed to get the data out of Axys and into a format where we would be able to use report-writing tools like Crystal Reports and SQL Server Reporting Services (SSRS). That would enable us to create reports of the same caliber. We had done it successfully for other clients and knew what was required.

However, our prospective client made some internal concessions on the presentation-quality aspect of the reports they wanted versus that original package, and eventually agreed to have a less expensive, but well-known competitor do the work as custom compound reports. Shortly after the job began, they started hearing a two letter word few advisors like — NO. It was the answer to whether they could have certain reports in landscape or portrait. And it was the answer to whether certain report components could appear on the same page.

With that experience in mind, the client called us back and asked us if we could do the job using compound reports. We agreed and said YES to all of their requests. We delivered their new reporting package, which consisted of the same types of portfolio summaries they had originally shown us. The reports are generated through the use of compound Axys reports and assembled via Encore, our PDF report packaging application.

The client reports that you send out every quarter are a representation of who your firm is and what you do. Good quarterly reports are an opportunity for your firm to maintain or improve your clients’ opinion of your service. Conversely, substandard quarterly reports are a liability that speaks to your clients every quarter. What do your quarterly reports say about your firm?

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.

There are plenty of lackluster reporting packages that get sent to investors, but every once in a while, you may see a reporting package that is truly impressive in terms of content and presentation. When you do see something that looks that good, it is only natural to wonder how it was done and at what cost. If the package is exceptional, then chances are somebody spent mucho dinero to make it so.

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.To see more samples click on these links: portrait #1, portrait #2landscape #1 and landscape #2.

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.Click on the following link to see more samples: landscape

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.