Making Crystal Reports, ASP and Coldfusion Play Nice

1. Making Crystal Reports, ASP and Coldfusion Play Nice Presented by Debbie Stoeffler distoer1@bellsouth.net

2. Demonstrate how to use CFMX, ASP, MSSQL and Crystal Reports to display a crystal report in the viewer based on dynamic data. Overview

3. What is a Crystal Report Brief History of Crystal Reports What can you do with Crystal Reports Licensing for Web Development with ASP Why should you use a Crystal Report Why I used Crystal Reports Recipe (hardware/software configuration) Basic flow of the code Code demos Tips for Crystal Reports Reporting in the future Resources Agenda

4. An indescribable evil foisted on programmers under the guise of "easy reporting". http://c2.com/cgi/wiki?CrystalReports What is Crystal Reports?

5. A report design tool that allows you to create reports capable of retrieving and formatting a result set from a database or other data source. Contains its own formula language for creating calculations Contains features for turning raw data into presentation quality reports, with graphs, charts, running totals, and so on. Professional Crystal Reports for Visual Studio .NET, 2nd Edition, by David McAmis What is Crystal Reports?

6. 1992 Crystal Reports arrived as the world's first Microsoft Windows report writer 1993 Microsoft bundled Crystal Reports in Microsoft Visual Basic 1996 Crystal Enterprise was launched as the industry's first web-based enterprise reporting platform 2002 Microsoft bundled Crystal Reports with Visual Studio .NET 2003 Business Objects acquired Crystal Decisions (f.k.a. Seagate Software) in December 2003 http://www.businessobjects.com/company/innovation.asp Brief History of Crystal Reports

7. Demo D&BAM reports What can you do with Crystal Reports

8. Depends on the version you are using Version 8.0 Developer had the WCS (web component server) included in the developer edition The WCS in version 8 did not technically enforce the 5 concurrent user license restrictions. In Version 8.5 the WCS is replaced by Crystal Enterprise (CE) Standard edition. CE in Version 8.5 DOES monitor the five free concurrent user licenses. As of Feb 2003, this version no longer offers the five free user licenses Version 9.0 – Professional, Developer, and Advanced editions come with CE RAS (Crystal Enterprise Report Application Server) which contains five concurrent user development and test licenses. Version 10.0 – Professional comes with Crystal Enterprise Express which contains the five concurrent user development and test licenses. Version 10.0 – Developer comes with CE Professional which contains the five concurrent user development and test licenses. Version 10.0 – Advanced comes with CE Professional and the five concurrent user licenses. Licensing for Web Development with ASP

9. Utilize features like formula fields, running totals, graphs, and so on to make your report as complex as you require. Not limited to reports, but can make labels, utilize bar codes, pictures, graphics, etc. Reports are portable between code bases (i.e., take reports for Coldfusion and use in .NET without modification to the actual report. Why should you use a Crystal Report

10. User requirements lent themselves to this format: Search report Drill down Export reports to excel, word, pdf Reports need to look a specific way for submission to Commissioner Reliable printing Tons of parameters users wanted ability to change. I was familiar with Crystal Reports from using Visual Basic. It was faster for me than building an HTML solution I wanted more control over the report Company already had the Crystal Reports software I wanted to learn more about Crystal Reports I used the ASP method of rendering the report because the URL method was restrictive. CFID and CFTOKEN kept dropping off if I passed to many parameters. Why I used Crystal Reports

11. I use the following in production: Windows 2000 Adv server IIS Crystal Reports Developer 8.5 If using CR 10 Developer, need to install CE Prof Coldfusion MX 6.1 (started with CF 5.0) SQL Server 2000 sp3 I have approx 200 users on this app. Dell PE 8450 (6 processors 4GB RAM) Recipe (hardware/software configuration)

12. User is presented with report options User chooses then submits report options Option choices are sent thru CF to ASP ASP sends the user parameters to the Crystal Report by calling the Crystal Report ActiveX viewer CRViewer activates and displays the report. Basic flow of the code (code demos)

13. You will need these files to render the Crystal Reports: AlwaysRequiredSteps.asp Creates the application object Creates the report object Opens the report MoreRequiredSteps.asp Retrieve the records Create the page engine SmartViewActiveX.asp Displays the report using the “smart” viewer I’ve edited these files (which you can get from www.businessobjects.com) to make them work with Coldfusion code and to demo subreport parameters. These edited files will be available from www.acfug.org ASP Files needed

14. Stored procedures for reports Simple Crystal Report Report with SubReport Code Demo

15. Make sure to fill out the report summary “Title” attribute or the report won’t export correctly to excel. Do not “save data with report” Use a stored procedure to provide data to the report. Ensure all data needed is provided by SP. Tips for Crystal Reports

16. Blackstone feature sneak peaks http://www.macromedia.com/software/coldfusion/blackstone/features/reporting/ Integration of a report builder to make a CFR (coldfusion report) similar to that in .NET using crystal reports. Uses<cfreport>. http://www.macromedia.com/devnet/mx/coldfusion/articles/blackstone.html Uses a new tag called <cfdocument> to create PDF or flashpaper reports. Reporting in the Future

17. http://www.businessobjects.com/products/dev_zone Crystal Reports 10: The Complete Reference , Peck, George, McGraw-Hill/Osborne, 2004 Resources