Transcript Implementing Crystal Reports Web Components

Supplementing Aleph Reports Using
The Crystal Reports Web Component Server
Presented by
Designed by
Bob Gerrity
Head of Systems
Boston College Libraries
(617) 552-3155
[email protected]
Kevin Kidd
Systems Librarian
Boston College Libraries
(617) 552-1359
[email protected]
Presentation URL:
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Reporting Issues Boston College Needed to Address
• Limited report customization in canned Aleph reports
• Confusing options/parameters in Aleph custom report generators
(e.g., Acquisitions General Retrieval Form)
• Limited control over formatting of Aleph reports (rep_col.eng)
• Accessing and using Aleph reports can be confusing to some staff
•Multi-step process, using Web Services to submit report request, GUI Task Manager to retrieve
and print output
• Database security concerns: Web Services includes DBMS jobs and
reports that can update data
• Not easy to extract Aleph report data and save it in other formats
• Aleph
reports cannot be viewed online
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Desired Features of Library Reporting Solution
• Ease of use for library staff (i.e. we wanted to avoid having to
train staff to use special software)
• Parameterized reporting
• Ability to print, export and manipulate report data
• Flexible enough to meet differing reporting needs of
various library departments
• Quick access to real-time data
•Ease of design and implementation for Systems Department
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Benefits of Crystal Reports Web Component Server
• Reports are requested using a standard HTML web form and viewed
using a browser plug-in
• Reports can accept multiple parameters
• on-the-fly ad-hoc reporting is possible
• a single report can be used by multiple departments
• systems staff involvement in reporting is minimized
• ODBC connection provides real-time access to data
• Configuration of Web Component server is quick and easy
• All staff members can access reports
• Database integrity: accidental updating of database is impossible
• Reports and user requests are cached to minimize network and server load
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
CR Web Component Server: Things to think about
• Aleph Oracle database design -- indirect and compound primary/foreign
key relationships -- often forces users to resort to costly full-table scans
and string functions when writing SQL queries.
• could potentially become a significant burden on both the
database and the network -- especially if the CR Web has a
large number of users
• Requires a strong understanding of the Aleph Oracle database design,
with staff workflows and with the overall operation of and relationships
among the various Aleph modules.
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Skills Required to Implement CR Web Components
Programming Languages
• Oracle SQL and basic PL/SQL
• HTML
• JavaScript (optional, for form validation)
Software
• Web Server Administration Tools
• Crystal Reports 8
• Oracle Client Administration
- SQL*Plus
- ODBC Configuration
- Net8 Remote Database Access Configuration
• HTML or Text Editor
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Hardware / Software Requirements for
Boston College Crystal Reports Configuration
Hardware
• Client Computer (Running IE5 or Netscape4 Web Browser)
• Web Server Running Microsoft IIS4
•We used existing IBM PC (Pentium III, 192MB RAM, 12GB disk
• Database Server Running Oracle8
•Our ALEPH server: IBM RS6000 (SP Power3 SMP High Node, 6 processors, 8GB memory, 250GB disk)
Software
• Crystal Reports 8 - Developer Edition Installed on Web Server
• Oracle Client installed on Web Server (ODBC and SQL*Plus)
• HTML / Text Editor
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Crystal Web Component Server Architecture
Browser
1. URL containing .RPT file
IIS Server with
CR Web Component
Server installed
3. Formatted report in Report Viewer
1. Web browser sends URL with .RPT file
2. Web server queries
database and runs report
2. Crystal Reports Web Component Server
extension recognizes .RPT file type and
runs report against database
3. Web server sends formatted report back to
browser in Report Viewer
6/1/2001
Oracle Database
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Implementing Crystal Reports Web Components
I. Software Installation / Configuration
A. In Oracle database, create a “reports” user who has permission
to execute stored procedures for both XXX01, XXX50 libraries
B. If one does not already exist, install and configure a web server (IIS4)
C. Install full version of Crystal Reports 8 (recommended) and Crystal
Reports Web Component Server on your web server
D. Install and configure Personal Oracle (SQL*Plus and ODBC connection)
on your web server. Make sure that you can access the Aleph database
through SQL*Plus.
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Implementing Crystal Reports Web Components
II. Report Creation
A. For each parameterized report you wish to make available on the Web:
a. write and test an SQL query
b. create an Oracle package and procedure to accept parameters,
execute the SQL query and return data to the reports server
B. Using Crystal Reports, design a report which executes the stored
procedure you just created
C. Save the Crystal Report file (filename.rpt) to your web server
D. Create an HTML form which accepts the parameters you defined in the
stored procedure and executes the Crystal Report
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Example of an Oracle Package for Crystal Reports
Unpaid Invoices Listed by Vendor & Invoice Creation Date
CREATE OR REPLACE PACKAGE unpaidinvoices_package
AS
TYPE unpaidinvoices_rec IS RECORD
(vendor char(20),
invoice char(15),
createdate char,
status char(1));
TYPE unpaidinvoices_type IS REF CURSOR RETURN unpaidinvoices_rec;
END unpaidinvoices_package;
/
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Example of an Oracle Procedure for Crystal Reports
Unpaid Invoices Listed by Vendor & Invoice Creation Date
CREATE OR REPLACE PROCEDURE unpaidinvoices (
unpaidinvoices_cur IN OUT unpaidinvoices_package.unpaidinvoices_type,
startdate IN number,
enddate IN number
)
AS
BEGIN
OPEN unpaidinvoices_cur FOR
SELECT rtrim(substr(z77_rec_key,1,20)),
rtrim(substr(z77_rec_key,21,15)),
to_date(z77_i_date,'YYYY-MM-DD')),
z77_p_status
FROM bcl50.z77
WHERE z77_i_date BETWEEN startdate AND enddate AND
z77_p_status != 'P'
ORDER BY rtrim(substr(z77_rec_key,1,20)),rtrim(substr(z77_rec_key,21,15));
END unpaidinvoices;
/
http://www2.bc.edu/~gerrityr/NAAUG.ppt
6/1/2001
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
Crystal Reports HTML Submit Form Example
<form method="post" name="rpt" action="Acq/Unpaid_Invoices.rpt" onSubmit="return false">
<input type="hidden" name="prompt0" value="">
From Date: <input type="text" name="prompt1" size="9" maxlength="8" value="">
To Date: <input type="text" name="prompt2" size="9" maxlength="8" value="">
<p>
Important: The date format you must enter is YYYYMMDD
<p>
<b>Step 2:</b>
<p>
Please select the Web Browser you will use to view the report:<br>
<select name="init">
<option value="actx" selected>Internet Explorer
</select>
<p>
<b>Step 3:</b>
<p>
<input type="hidden" name="user0" value=”XXXXX">
<input type="hidden" name="password0" value=”XXXXX">
<input type="submit" value="Get Report" onClick="check_form(this.form);">
<input type="reset" value="Reset Form">
</form>
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt
More Help and Information
Download the Oracle ODBC Client (part of Personal Oracle 8)
http://otn.oracle.com/software/products/8i_personal/content.html***
***Download is free; however, you may be required to register
as a member of the Oracle Technology Network
Download Help on Creating Oracle Packages and Procedures
http://support.crystaldecisions.com
/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf
Contact Kevin!
[email protected]
6/1/2001
http://www2.bc.edu/~gerrityr/NAAUG.ppt