Making Crystal Reports, ASP and Coldfusion Play Nice

1
Making Crystal Reports, ASP and Coldfusion Play
Nice
Presented by
Debbie Stoeffler
distoer1_at_bellsouth.net
2
Overview

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

3
Agenda

• 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

4
What is Crystal Reports?

• An indescribable evil foisted on programmers
  under the guise of "easy reporting".
• http//c2.com/cgi/wiki?CrystalReports

5
What is Crystal Reports?

• 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

6
Brief History of Crystal Reports

• 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

7
What can you do with Crystal Reports

• Demo DBAM reports

8
Licensing for Web Development with ASP

• 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.

9
Why should you use a Crystal Report

• 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.

10
Why I used Crystal Reports

• 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.

11
Recipe (hardware/software configuration)

• 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)

12
Basic flow of the code (code demos)

• 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.

13
ASP Files needed

• 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
• Ive 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

14
Code Demo

• Stored procedures for reports
• Simple Crystal Report
• Report with SubReport

15
Tips for Crystal Reports

• Make sure to fill out the report summary Title
  attribute or the report wont 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.

16
Reporting in the Future

• Blackstone feature sneak peaks
• http//www.macromedia.com/software/coldfusion/blac
  kstone/features/reporting/
• Integration of a report builder to make a CFR
  (coldfusion report) similar to that in .NET using
  crystal reports. Usesltcfreportgt.
• http//www.macromedia.com/devnet/mx/coldfusion/art
  icles/blackstone.html
• Uses a new tag called ltcfdocumentgt to create PDF
  or flashpaper reports.

17
Resources

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