Crystal Reports Programmer

Reporting is the process of accessing data, formatting it, and delivering it as information. Crystal Reports (by Business Objects) is a powerful analytical tool and is the enterprise report writer of choice for many corporations. It is multiplatform, powerful, and versatile enough for any requirements.

I am proficient with all the versions of Crystal Reports and more importantly understand tables in relational databases and how to query them accurately. If I can't do the task with the basic Crystal Reports features like grouping and subtotaling, as a skilled programmer, I can always get it done through its inclusion of a C-like programming language called simply Crystal Syntax as well as the widely popular Visual Basic.

Crystal Reports Version XI, with its user defined functions, and event-driven model is capable of the most complex calculations and formatting. I find that there is so much interest in Crystal Reports in the firms around Boston and NH that I work for that I often set aside my old predeliction for Microsoft Access' report writer and suggest the job be done in Crystal Reports.

Projects - Crystal Reports

390 reports created for Citizens Bank Lockbox program. Most were very similar, but there were many that had custom specifications that had to be met. Reports included payments, deposits, and credit card transactions for many well known bank clients like the Celtics, the Boston Herald, and many state governments, municipalities, colleges and public utilities.

30+ disparate reports created for the Learning Management System. This nuclear facility conducts massive amounts of training for all workers, including hundreds of seasonal nuclear workers who are signed on during planned outages. Many reports are necessary to track who needs training and retraining, who is overdue, upcoming courses, and so on. The data is on Oracle 8. Although required to use an older version, I used almost every facet of Crystal to get these reports completed.

I created many compound key joins on tables and views on an Oracle database, using custom formulas, subreports,and parameterized reporting. Incorporated library functions and created many custom functions.

Chip is also the IT Manager and had some difficulties upgrading existing reports from version 8 to 9. I did the debugging/troubleshooting to fix things up for an important series of reports.

Genesys clients are Fortune 500 companies as well as other sites such as state and county governments who have a need for extensive reporting on a wide range of employee activities. The client installations I worked on were in the area of employee education. We built a central report repository, maintained tight user security, used report scheduling, and sought accurate and speedy report processing. As part of developing enterprise reporting solutions, I created reports using Crystal Reports (Ver 8.5) against a very complex MS SQL Server 2000 database.

As part of a tight and highly skilled development team of 5, I helped provide a scalable web-based solution for managing the access and delivery of hundreds of Crystal Reports across the enterprise. These client reports had to be carefully constructed and often rested on SQL joins of up to 20 tables. A very important aspect was speed optimization. The query (view) underlying a particular report was written to take advantage of server-side processing. For example, directing that grouping take place on the server and that execution of the reports be based on data saved with the report, rather than suffering multi-user hits to the server for live data every time. This data was refreshed to the report during off-peak hours.

Another optimization was the use of stored procedures as data sources. Although these take time to set up properly, they can be incredibly powerful, especially when running reports off large databases.

The complexity of the system that I mentioned earlier arises out of their use of 38 byte computer-generated guids which served exclusively as the keys relating 250+ tables. In fact their entire family of data tables is generated automatically by a proprietary program (Vortex) that responds to a developer-designed graphical layout of just the data schema. This leads to some really long fieldnames and some really interesting (i.e. complex) queries relating many tables. Some of the tables were joined for the sole purpose of administering client security rather than contributing data. In spite of the fact that some queries involved 50 or more tables, response on the InterNet was satisfactory.

In the interest of cross-skills enhancement on the development team, I conducted Crystal Reports classes (Basic and Intermediate.) This was fun, but the team got so good, they didn't need a contractor anymore.

I used Crystal Reports 9.0 against data on an Oracle 8 server to develop a complex report on activities and results of participants in New Hampshire's WIA Program. This report tracks what retraining goals the 1600 participants were assigned versus how they did in achieving them. For the sake of the users, it had to be easy to generate. For the sake of federal funding, it had to be accurate and it had to agree with other external analyses. Getting to the accurate part required Bonnie and I to cooperate and communicate frequently.

The client uses a commercial laboratory work throughput program named WinLims which connected to its database on SQL Server 6.5. Using Crystal Reports Ver 5 (later upgraded to 6) I created and maintained many custom lab reports. I connected to the server using ODBC, set up ODBC DSN's (datasources) on PCs at 25 lab analyst workstations stations and deployed the reports. I also developed extensive user as well as technical documentation (like how to create new ODBC connections) and provided manager training in how to make their own changes to these reports, recompile and deploy them on their own. Since the lab did not have their own DBA, I served in that capacity. Since my work in that area had a lot to do with getting familiar with the database in terms of Crystal Reports, I am including a description below.

SQL Server 6.5:
The lab's application. WinLIMS was custom written by an out of state vendor and recent performance degradations after a conversion from Gupta SQLBase to MS SQLServer led the lab manager to ask me to study it as a Crystal Reports Programmer and see if I could make some recommendations. I attempted some reverse engineering using ERWin and plotted the database schema out on a 4 foot wide paper roll on an HP DesignJet 750C. Using the outside software provider as a guide and the MS SQL Analyzer as my main tool, the first thing I reported was that there were no keys of any sort on the tables primary or otherwise. It was not too hard from that point to convince the software provider that any queries against the database would resort to linear table scans to perform joins and this can't be tolerated. They agreed that it was a step that was missed somehow in the upgrade and they took care of it immediately. We reran our baseline queries and got immediate results instead of a lot of finger tapping.

I also recommended that the work I did in ER/Win would serve them well, especially if they carried through on their investment in software and hardware by rerunning the humongous printed schema anytime the provider changed the database structure.