metamulty.com

Crystal Reports Design As Combination of Programming and Arts

Good report is often based on SQL View or Stored Procedure. And professional design often involves computer graphics in the form of logos, database driven posters, etc. Here you have to be comfortable with such things as Photoshop, Illustrator and maybe even Flash file creation software. Plus often CR is pulling its records from Corporate ERP database, meaning that programmer should have good working knowledge of accounting database architecture and be familiar with such popular packages as Microsoft Dynamics, SAP Business One maybe old ones on Pervasive SQL platform such as Platinum, Great Plains DOS version. Let’s review Crystal design recommendations:

1. SQL as querying technology. Report is in essence layout where SQL select statement result set is displayed. Typically you have set of standard reports in Accounting, MRP, ERP or CRM packages. If you are appealing to Crystal designer chances are high that you need something complex and this is outside of the scope of these embedded reports in your software. You do not do complex report in Wizard. Instead you rather create SQL query in the form of View or even better SQL Stored Procedure. This way allows you to test resulting set of records prior to design in reporting tool. It is one of the most popular issues where designer with artistic skills and not familiar with Structured Query Language tries to entrust table linking to Wizard. Such reports typically are very nice in their appearance but they produce such annoying things as duplicate rows, incorrect summaries and have unnecessary body or group level conditions and calculations in scripts. If you decide to dedicate yourself to professional design you got to be good SQL developer as well. Let’s review the advantages of SQL View and Stored Procedure

2. Views and Stored Procedures. SQL querying language is a way more advanced in comparison to design tools. It is using so-called aggregation which gives SQL processing power far beyond the limits of cursor approach often deployed in report writers. Let’s take a look now at Microsoft SQL Server Stored Procedure. This construction allows you to create temporary tables and even SQL cursor and by doing so it is in our opinion unlimited in programming. Whatever is theoretically or better say mathematically possible could be implemented in Stored Procedure. Crystal automatically recognizes its parameters and exposes them in the report

3. Graphics or what makes report charming. The layout itself allows you to incorporate the majority of graphical formats, including such popular as JPG, BMP, GIF (where you can include animation, popular in clip art), PNG. Logo is typically produced in Adobe Illustrator where it could be done as vector graphics. The advantage of vector graphics is the ability to be scaled up and down without logo picture quality degradation. If you are dealing with photos better instead of Illustrator you typically work in Adobe Photoshop

4. ODBC connection to the database. Such databases as Btriever/Pervasive SQL, Ctree/Faircom, Text in CSV or tab delimited format, Access, MySQL/PHP are typically connected via ODBC DSN. It is still possible to deploy SQL View or Stored procedure where you do cross platform query via Open Row Set or Linked Server construction

5. Working with accounting database. Microsoft Dynamics in its current versions is based in Microsoft SQL Server 2008 and 2005. Older versions of Great Plains Dynamics were also available on Pervasive SQL 2000/Btrieve and Ctree. Great Plains Accounting 9.5 was Btrieve application. SAP B1 is typically hosted in SQL Server. In order to connect to Pervasive database you have to have so-called Data Definition Files

6. Competition. Microsoft is now encouraging its customers to switch to SQL Server Reporting Services often abbreviated as SSRS. Its interface is web based and the report itself could be tweaked in Microsoft Visual Studio. Report design in our opinion is programmer oriented. We like the way as SSRS is exposed in Microsoft Dynamics CRM. It is possible to do MS Access reports with data pulling sets from its own database plus you can use Access with ODBC connection to all kinds of ODBC compliant data platforms. Obviously Excel is almighty and open to reporting.