Getting
Started With Crystal Reports 8.5
To
quote Mr. T, I pity the fool...the fool that gets stuck having to build
a new, parameter-driven Crystal Reports 8.5 web report without having
had any formal training. If you poke around the web, you can find lots
of tutorials that will cheerfully walk you through the steps required
to build a report using Crystal's included report wizard. But assuming
you're not a total idiot, and you know what the 'Next' button is for,
you don't really need a tutorial to run ANY wizard, including Crystal's
report wizard. Hence, I found those cheerful tutorials to be cheerfully
useless to me when I learned that my formally-trained Crystal Reports
staffer would be leaving the company and I needed to get up to speed
on Crystal Reports 8.5 - fast!
That
wizard is just great if all you need to do is build a simple report
that draws data from one table based on fixed parameters that can be
permanently hard-coded into the report definition. And how often does
that happen in real life? If you need to accomplish the much more typical
task of running a report based on a complex parameter query, for which
users provide parameters via a form on an ASP page, you need that wizard
like a fish needs a bicycle. And good luck finding any help on the web.
So herewith, I present the super-secret knowledge of how to do it, as
passed on to me by that kindly Crystal Reports staffer on his way out.
Crystal Reports 8.5 Web Reporting newbies, bookmark this page!
This
tutorial assumes you already know how to code in ASP and your Crystal
and IIS servers are already set up and fully functional.
Overview
Creating the Data Definition File
Creating
the Report
Creating the ASP Input Page on the Application Side
"Pushing" the Data to the Crystal Server
Side
Running the Report
Overview
This
page is geared toward the person who has inherited responsibility for
Crystal Reports 8.5 maintenance and creation of new Crystal Reports
8.5 web reports, but has had no formal training.
Where
I work, we maintain our application code on an IIS server (let's call
it AppServer here), our database on a separate SQL server (let's call
it SQLServer here), and our Crystal Reports server components on a third
server (let's call it CrystalServer here).
Creating
a new, parameter-driven Crystal Report for the web requires the creation
of four things: a data definition file, which is disposable and can
be saved anywhere; the report itself, on CrystalServer; the ASP page
used to gather parameter input, on AppServer, and the ASP page which
will push the result set to the Crystal Report, also on CrystalServer.
Two add-ins are needed to actually run the report: the Crystal Report
Engine is used to merge the result set with your pre-defined report,
and the Active X Smart Viewer is used to present the end-user with the
finished report in a Crystal GUI that offers them various paging, sorting
and printing/output options.
So
basically, creating a new, parameter-driven Crystal 8.5 web report consists
of the following steps:
1.
Create a data definition file to store the field definitions needed
for your report, save it anywhere
2. Create a report in the Crystal Reports program, save it to CrystalServer
3. Create an ASP parameter-gathering page, save it to AppServer
4. Create an ASP page to capture the parameters, fetch the result set,
create an instance of the Report Engine and launch the Smart Viewer,
save it to CrystalServer
Creating
the Data Definition File
Open
the Crystal Reports application. Select 'As A Blank Report' under the
heading of 'Create A New Crystal Reports Document'.
In
the Data Explorer pop-up dialog, expand the folder named 'More Data
Sources' and then expand the sub-folder named 'Active Data'.
Double-click
on 'Active Data [field definitions only]' from the listed sources to
select it. In the Select Data Source pop-up dialog, 'Data Definition'
will be pre-selected; click the 'New' button under 'Data Definition'.
The
Database Definition Tool dialog will pop up, prompting you to enter
your field names, specify data types and (optionally) provide sample
data. For each field you intend to include on your report, enter a field
name and select the correct data type from the provided drop-down box.
You
can leave the 'Sample Data' field blank, or enter dummy data of the
same type and length as the data that will ultimately appear in the
field on the finished report. If you do include a Sample Data entry,
that entry will appear in the field when you place it on your report
as you work in design mode. Otherwise, the field will only contain the
field name. Click the 'Add' button to add each field. Note that after
you click 'Add', your field is added to the grid in the lower part of
the window. When you're done adding fields, close the dialog by clicking
on the Close control in the upper right-hand corner of the dialog (the
'X').
When
prompted to save your file, click 'Yes'.
In
the 'Save' dialog, specify a file location and leave the default file
type as it is. Yes, I am aware the name of the file type ('Field Defintion
File') is misspelled, but it doesn't matter. It also doesn't matter
where you save this file, because you'll end up deleting it later. Just
make sure you remember where you put it, because you'll need to use
it in the next steps.
After
you have saved the file, you will be returned to the 'Select Data Source'
dialog. Click 'OK' to close it.
Note
that your newly created Data Definition file is now listed in the Data
Explorer dialog. Click the 'Add' button to add this data source to your
new Crystal Reports file.
Note
that after you do so, a small, green checkmark will appear over the
file icon in the Data Explorer dialog. This confirms that the new data
source has been made available to your report. Click the 'Close' button
to proceed to the report design phase.
Creating
the Report
After
you've saved your Data Definition file, you will be presented with the
Crystal Reports report designer interface, with the Data Sources window
floating over it. Expand the Database Fields item, then expand the Data
Definition file item you just created, to view the fields you've made
available to your report.
Notice
that the report designer interface shows five distinct report sections.
The Report Header section will print only at the top of the first page
of your report. The Page Header section will print at the top of every
page of the report, including the first page; this is the section where
you will place your column headings for the report. The Details section
is where the actual data fields go. The Report Footer section will print
only at the bottom of the last page of the report, and is typically
used for running report totals. The Page Footer section will appear
at the bottom of every page of the report, including the last page;
this is the section where page numbers, database name, application name,
date and so on are usually placed.
To
add a field from your Data Definition file to your report, just drag
it from the Field Explorer window to the Details section of your report.
As you do so, the field names will automatically be added to the Page
Header section of the report and a green checkmark will appear over
the field name icon in the Field Explorer window.
NOTE:
YOU MUST ADD YOUR FIELDS IN THE SAME ORDER IN WHICH THEY WILL BE RETRIEVED
BY THE RECORDSET THAT WILL BE USED TO POPULATE THE REPORT WHEN IT IS
RUN.
In
other words, if the query you intend to run on the application side
is "SELECT SSN, LastName, FirstName FROM dbo.tblEmployees, ORDER
BY LastName, FirstName", you must add the fields to your report
in the same order as they will be sorted in the result set: LastName,
then FirstName, then SSN.
As
you add your fields, the report constructs a kind of numbered array
behind the scenes, and this is the tool it will use to place your actual
data in the report when the report is run. It 'assumes' that the first
field you add to your report is the first field in your query result
set. The report engine does not care if you've used actual database
field names in your Data Definition file. The report engine is as dumb
as a box of hammers and will do nothing more than deposit each field
into the report in the order it finds them, regardless of whether or
not the field names match.
Location
doesn't matter to the report engine, it does not read your fields from
left to right on the report. Even if you place SSN in the far right-hand
portion of the Details section first, then place FirstName just to the
left of it next, then place LastName at the far left last, the report
engine thinks that SSN is the first field in your result set, FirstName
is the second field in your result set, and LastName is the third field
in your result set. You'll end up with SSNs printed in the LastName
column, FirstName in the FirstName column, and LastName in the SSN column.
And if your data types don't match as a result of the mixup, you won't
get anything but an error when you try to run the report.
If
you specified sample data in your Data Definition file, you will see
that data in the fields on your report, instead of the field names as
I have in my example.
If
you want to specify different text for your column headings, just click
on the heading you want to change and hit the Delete key on your keyboard.
The field will remain in place. Then select 'Insert - Text Object' from
the menu bar and a text field will appear next to your cursor; drag
the field to the desired location and type in the desired text.
After
you're done adding fields to the report, open the File menu and make
sure the 'Save Data With Report' item is unchecked. In the example below,
it hasn't been unchecked yet. Saving data with this report is unnecessary
because there is no data in the report. It might be a useful option
if you were using a fixed data source instead of a data definition file.
Save
your finished report to the same server where your Crystal Reports components
reside as a file of type 'report', with the file extension of .rpt.
Creating
the ASP Input Page On The Application Side
Create
an ASP page with a form and input fields for the users to enter their
report parameters. Consider the example below:
Since
this is a Crystal Reports tutorial and not an ASP tutorial, I will assume
you already know how to build this kind of a page and won't go into
any more detail about it. The important thing to note about it is that
you must include the following text as the onClick event of the submit
button used to run the Crystal Report:
onClick="document.frm[form name].action='http://[Crystal server name]/[path
to your asp report results page]';"
This
onClick sends the user to the ASP page where you will gather and process
all the parameters entered by the user in your parameter entry form.
Note that while your parameter-gathering ASP page can reside on the
IIS server along with every other page of your application code, the
ASP page where the parameters are gathered and processed must reside
on the Crystal server. The reason why will become clearer in the next
section of this tutorial.
"Pushing"
the Data To the Crystal Server Side
Build
the ASP page which will gather and process the parameters entered by
the user, and save it to the Crystal server. DO NOT save it on the IIS
server along with the rest of your application code.
The
basic structure of this ASP page will be as follows:
[Block(s)
of ASP code used to retrieve recordset(s) from the database. Since the
records will be retrieved based on parameters entered by the user, the
Request method will be used to pull the user's entries from the referring
ASP page.]
[Blocks
of code needed to open the Crystal Reports report object, create the
Page on Demand object, and to "push" the recordset to the
report object. Code to kill the session if any of these fail to initialize.]
['Include'
file directives to create an instance of the SmartViewerActiveX object
if the user is browsing in Internet Explorer, or SmartViewerJAVA if
the user is browsing in Netscape.]
Rather
than go into a detailed explanation of each general section of this
page, I've included the source code for the ASP page used to gather
and process parameters entered in the sample screen shot shown in the
previous section. You can view a text version of the heavily-commented
code here. You will
see many places in this source code where various Crystal server files
and components are referenced, and you will also notice that Crystal
Reports engine has its own database security account; this is why the
page must reside on the Crystal server, and not with the rest of the
application code on the IIS server.
A
cleanup routine is called by this page when the report session fails
for any reason. A text copy of the source of that file can be viewed
here. This page should already
exist on your server if the previous Crystal developer was in the habit
of cleanly shutting down failed sessions.
Notice
that the Seagate Smart Viewer object is launched to create an instance
of the Smart Viewer GUI, which provides the end user with a clean, easy
to use interface for viewing, exporting, and printing the report. The
Smart Viewer GUI can also provide other functionality, such as the capability
to re-sort the report after it loads and to drill down or collapse in
on detail views of the report. These options are set in the Smart Viewer
include files themselves, which are customizable by the developer.
There
are different versions of the Smart Viewer for different browser versions.
In the example I'm working with here, the only two browsers supported
are Netscape 4.x+ and Internet Explorer 5.x+. You can view a text version
of the source code for the SmartViewerActiveX.asp file (for IE) here,
and a text version of the source code for the SmartViewerJAVA.asp file
here. One wrong
keystroke in either of these files will cause your report to fail, so
don't try to edit them unless you're sure you know what you're doing.
There
are many options and specifics for how the Crystal Reports report engine
will process your report. All of those options are specified by the
rptserver.asp file, which should already reside on your Crystal server
and is referenced by the source of the Smart Viewer files. You can view
a text version of the source code for rptserver.asp here.
As with the Smart Viewer files, one wrong keystroke in this source can
cause your report to fail, so do not edit it unless you are absolutely
sure you know what you're doing.
Running
the Report
When
you're all done following the preceding steps, you should have all of
the following ready to go:
1.
An application-side ASP page with a form on it to gather user-specified
report parameters, and a button on it to pass the parameters to your
Crystal-side ASP page.
2. A Crystal-side ASP page to gather and process the parameters, open
the Crystal report engine, 'push' your recordset to the report, and
create an instance of the Smart Viewer.
3. A Crystal-side copy of each Smart Viewer source code file appropriate
for your supported browser(s).
4. A Crystal-side copy of the rptserver.asp source code file to specify
all the report engine options.
5. A Crystal-side copy of the cleanup.asp source code file to cleanly
abort a failed session.
To
run the report, login to your application and go to the application-side
page with the parameter form. Enter your desired parameters, then click
the button to run your Crystal report. You will be passed to the Crystal-side
ASP page, which will very quickly retrieve your recordset, open the
Crystal report engine, push your data to your report, create an instance
of the appropriate Smart Viewer and open your Crystal report file with
the data in it.
There,
now wasn't that easy? Okay, maybe not. But wasn't it easier than trying
to figure out all this stuff by yourself? =)
|
