Getting Started Report Writing Seagates Crystal Reports and

  • Slides: 23
Download presentation
Getting Started: Report Writing Seagate’s Crystal Reports and Cyber. Tools for Medical Libraries Mark

Getting Started: Report Writing Seagate’s Crystal Reports and Cyber. Tools for Medical Libraries Mark Roux Cyber. Tools, Inc. May 17, 1999 * MLA/Chicago see this tutorial at http: //www. cytools. com/crystal © Cyber. Tools Inc. 1999

What We’ll Learn Today Concept of a Relational Database Resources Make Crystal Reports a

What We’ll Learn Today Concept of a Relational Database Resources Make Crystal Reports a Success Table and Field Relationships via LISTAB. HTML Simple Crystal Reports Example: One Table Simple Crystal Reports Example: Two Tables The 10 Demo Reports Some Complexities

A Relational Database Data are represented by tables. A table is similar to a

A Relational Database Data are represented by tables. A table is similar to a file, or a cross tab. It’s two dimensional. Rows are the records. Columns are the fields. The most important column is the Row ID, a. k. a. the key. It should be a value that makes the row unique. No repeating fields or groups of fields. Problem for M(UMPS) & LIS, e. g. MARC tags which can occur an unlimited number of times, & their subfields which can occur an unlimited number of times within a tag. Solution: make them into tables until they are flat. More later.

Example from the Patron File The Patron File: Patron_Name CHASE, VICTORIA C. ALSTON, STEPHEN

Example from the Patron File The Patron File: Patron_Name CHASE, VICTORIA C. ALSTON, STEPHEN P. FELDMAN, CHRISTOPHER J. DURSO, LILIYA Patron_SSN 775 -02 -1599 972 -64 -4681 940 -44 -0508 182 -31 -8360 Patron_Class SGTU 01 SMDRSCI EGUMDC Patron’s Patron_Class points to the Patron_Class Table: Patron_ Class Validation_ Date Ptrn_Class_Name SGTU 01 SMDRSCI EGUMDC STUDENT UNIVERSITY 2001 STUDENT GRADUATE MED. RAD. SCNCS EMPLOYEE GUMDC 06/12/97 02/12/97 08/16/94 Expiration_ Date 05/30/2001 06/30/2000 12/31/97

Suggested Resources 1. Crystal Report’s 32 -bit Crystal Report Designer. Easy to use. 2.

Suggested Resources 1. Crystal Report’s 32 -bit Crystal Report Designer. Easy to use. 2. Cyber. Tools 10 Demo Reports. See RWLIS. EXE, our Microsoft Windows installation kit, and associated rwlis_readme. txt file. 3. Crystal Reports for Dummies by Douglas J. Wolf 4. Seagate Crystal Reports User’s Guide by Gary Carter et al. 5. Email Cyber. Tools (support@cytools. com) with your simple report in English.

How to Make Crystal Reports a Success 1. You need spatial skills, divide and

How to Make Crystal Reports a Success 1. You need spatial skills, divide and conquer skills, & searching skills. 2. Try some simple reports not under deadlines. 3. Study the 10 Cyber. Tools demo reports. 4. Create a local expert. Have that person create most of the reports. 5. Email Cyber. Tools (support@cytools. com) with your simple report in English.

Relationships Via LISTAB. HTML (see hand-out) Format: Table Name List of other Tables that

Relationships Via LISTAB. HTML (see hand-out) Format: Table Name List of other Tables that use this Table List of Fields Field ID Data Type (date, number, text, reference, etc) If Data Type is Reference, the name of Table

LISTAB Example 1 PATRON_CLASS ( Patron Class) Referenced by: * PATRON_CLASS_CLUSTER_CLASSES Fields: * *

LISTAB Example 1 PATRON_CLASS ( Patron Class) Referenced by: * PATRON_CLASS_CLUSTER_CLASSES Fields: * * Expiration_Date >>> datatype PATRON_CLASS >>> datatype is Ptrn_Class_Name >>> datatype Validation_Date >>> datatype is Date Text >>> Row ID is Text is Date --------------------------PATRON_CLASS_CLUSTERS ( Patron Class Clusters) Referenced by: * PATRON_CLASS_CLUSTER_CLASSES Fields: * * * CLUSTER_ID >>> datatype is Number >>> Row ID Cluster_Descript >>> datatype is Text Date_Created >>> datatype is Date Time_Created >>> datatype is Time User_ID >>> datatype is Text

LISTAB Example 2 Fields: PATRON ( Patron Master File) Referenced by: * * *

LISTAB Example 2 Fields: PATRON ( Patron Master File) Referenced by: * * * * CIRC_TRANSACTIONS DDS_LOAN_INVOICE DEPARTMENTS DIVISIONS DOCUMENT_REQUEST INSTRUCTORS ITEMS PATRON_CLAIMED_RETURNED PATRON_CONDITIONAL_CHECKINS PATRON_FINES PATRON_HOLDS PATRON_LOANS PATRON_LOST_ITEMS SCHOOL * * * * * * Alert >>> datatype is Yes/No Comment >>> datatype is Text Dummy_Field >>> datatype is Text Expiration_Date >>> datatype is Date Home_Address >>> datatype is Text Home_City >>> datatype is Text Home_Telephone >>> datatype is Text Home_Zip_Code >>> datatype is Text Local_Address >>> datatype is Text Local_City >>> datatype is Text Local_Telephone >>> datatype is Text Local_Zip_Code >>> datatype is Text PATRON_CLASS references PATRON_CLASS PATRON_ID >>> datatype is Number >>> Row ID Patron_Barcode >>> datatype is Text Patron_Name >>> datatype is Name Patron_Name_Ext >>> datatype is Text Patron_Name_Norm >>> datatype is Name Patron_SSN >>> datatype is Text Patron_Suffix >>> datatype is Text Patron_Title >>> datatype is Text Patron_VIP >>> datatype is Text Validation_Date >>> datatype is Date

How to Make a Report 1. Write in English what you want to do:

How to Make a Report 1. Write in English what you want to do: “Find all Patrons with the home state of Texas. Sort by Patron Name. Show only the name. ” 2. Press the Task Bar’s Start push button. 3. Select Programs. 4. Select Seagate Crystal Reports. 5. Select 32 -bit Crystal Report Designer. 6. From the Welcome child window, select New Report.

Make a Report, cont. (7 -10) 7. From the Report Gallery window, from group

Make a Report, cont. (7 -10) 7. From the Report Gallery window, from group Choose an Expert, select Standard. 8. You’re now in Create Report Expert, a window with 8 control tabs. In the first control tab, Data, select the big push button SQL/ODBC. 9. A grandchild window (child to step 8 Create Report Expert) Log On Server appears. Click on ODBC - LIS twice, or click on it once and then click on Ok. This window will close. 10. A new grandchild window appears called Choose SQL Table. Find Patron, click on it once and then click on the push button Add found on the right. Or, double click on Patron. In either case, notice that Patron now appears in the text box within Create Report Expert.

Make a Report, cont. (11 -15) 11. On window Choose SQL Table, press Done.

Make a Report, cont. (11 -15) 11. On window Choose SQL Table, press Done. This window will close. 12. Back on Create Report Expert, click on the push button Next>>. 13. On the control panel Fields, under Database Fields, click once on Patron_Name, and then click the push button Add. Notice that Patron_Name appears in the text box under Report Fields, but with its Table’s name as a prefix. 14. Repeat the above step with Database Fields’ value Patron_Name. 15. Press the push button Next>> to proceed to panel Sort.

Make a Report, cont. (16 -19) 16. In the text box under Report Fields,

Make a Report, cont. (16 -19) 16. In the text box under Report Fields, click once on PATRON. Patron_Name, and then click on Add->. Notice it appears in the text box under Sort Field. 17. Click once on the control (notebook) tab Select. 18. Under Report Fields, select Home_City, and press push button Add. Notice PATRON. Home_City appears in the text box under Select Fields, and notice that two new fields appear. 19. Change the second new field from “any value” to “like”. Notice the window added a help key, two text fields, and an inactive push button Del.

Make a Report, cont. (20 -23) 20. If you click on the table list

Make a Report, cont. (20 -23) 20. If you click on the table list button belonging to the field to the right of “like”, it will show you actual values from the db. 21. In the field to the right of “like”, enter “*, TX” but without the quotes. 22. On the bottom of the window, press push button Preview Report. 23. To add the field Home_City to the report, click on the Menu Bar item Insert, select Database field. Drag the resulting child window Insert Fields off of the report, and click on Home_City, Click on the push button Insert. NOW CLICK ON THE REPORT WHERE YOU WANT THE FIELD. Watch the data change. On child window Insert Fields, click on Close.

Make a Report, cont. (24 -25) 24. To change from Texas to Florida, click

Make a Report, cont. (24 -25) 24. To change from Texas to Florida, click on the menu bar item Report, and select Report Expert. You should know what to do from here. If not, ask us. 25. SAVE YOUR REPORT WITH A SELF DOCUMENTING NAME. CREATE AN HTML FILE WITH THE SAME NAME AND DOCUMENT THE REPORT! PLACE THE HTML PAGE IN AN INTRANET DIRECTORY WITH THE REPORT.

A More Complex Report “Find all patrons whose Patron Class Expiration is after 1999”

A More Complex Report “Find all patrons whose Patron Class Expiration is after 1999” A. Follow steps 2 -10 from Part 1, but add table PATRON_CLASS. When you press the grandchild’s Done, you automatically move to the control panel Links. B. Notice how Crystal Reports automatically links similar names, but 2 to the 3 links are bad. We know that they are bad by comparing them to LISTAB. HTML. C. Focus on link Expiration_Date, & click on the push button Delete. Repeat for the Validation_Date Link. Only the PATRON_CLASS link should remain.

A More Complex Report, cont. D. If no links were present, you can drag

A More Complex Report, cont. D. If no links were present, you can drag them into place. EXAMPLE. E. Press push button Next>> to Fields. Select PATRON. Patron_Name, PATRON_CLASS, and PATRON_CLASS. Expiration_Date. F. Next>> to Sort, Sort by PATRON_CLASS and then by Patron_Name. G. Click on control tab Select, select field Expiration_Date, and enter “Date (1999, 12, 31)” without the quotes. Really. H. Press push button Preview Report.

Ten Demo Reports: BCirc. By. Year. rpt & booksubj. qry The following text is

Ten Demo Reports: BCirc. By. Year. rpt & booksubj. qry The following text is from the rwlis. exe installation kit’s rwlis_readme. txt file. BCirc. By. Year. rpt This report joins CIRC_TRANSACTION_DATE, CIRC_TRANSACTIONS, ITEMS, and BOOKS to provide a count of books checked out over a time period. It represents an example of a "parameter query", as it prompts for a start date and an end date before running. booksubj. qry This is a query defined with the Crystal Reports Query Designer. The query is used by the report top 10 sub, and is an example of basing a report on a predefined query. The query is a join between the BOOKS, MARC_BIB_TAGS, and MARC_BIB_TAG_SUBFIELDS tables. It joins information in BOOKS with subject information from the MARC record.

Demo Reports: bookswithoutitems. rpt & bookutlbyclus. rpt Bookswithoutitems. rpt This report shows records from

Demo Reports: bookswithoutitems. rpt & bookutlbyclus. rpt Bookswithoutitems. rpt This report shows records from BOOKS that don't have any linked items in ITEMS. There didn't seem to be any way to form this question using the Report Expert, so SQL was entered directly: SELECT BOOKS. BIBLIO_ID, BOOKS. Call_Number, BOOKS. Title FROM BOOKS WHERE BOOKS. BIBLIO_ID NOT IN (SELECT BIBLIO_ID FROM BOOKS_ITEM) ORDER BY BOOKS. Call_Number ASC bookutlbyclus. rpt This report joins CIRC_TRANSACTION_DATE, CIRC_TRANSACTIONS, ITEMS, PATRON_CLASS, PATRON_CLASS_CLUSTERS and PATRON_CLASS_CLUSTER_CLASSES to provide counts for utilization by different PATRON_CLASS_CLUSTERS over a date range.

Demo Reports: finesdrill. rpt & INVn. LIN. rpt finesdrill. rpt This report demonstrates both

Demo Reports: finesdrill. rpt & INVn. LIN. rpt finesdrill. rpt This report demonstrates both the Top N feature and the drill-down feature of Crystal Reports. It joins PATRON with PATRON_FINES and produces a report that shows the highest five offenders and the amount owed with the rest grouped with "Other". If you double-click on any of the six summary lines a detailed drill-down report is displayed with individual items. INVn. LIN. rpt This report joins SERIALS_INVOICE, VENDOR, and SERIALS_INVOICE_LINE and show invoice line data grouped by vendor, invoice date, and invoice number; it demonstrates multiple grouping levels.

Demo Reports: Item. Type. Couts. rpt & Rev. Temps. rpt Item. Type. Counts. rpt

Demo Reports: Item. Type. Couts. rpt & Rev. Temps. rpt Item. Type. Counts. rpt This demonstrates a cross-tab report and also the Crystal Reports graphing function. It joins CIRC_TRANSACTIONS with ITEMS and generates circulation counts of Item types versus month. Files Item. Type. Counts. exe and Item. Type. Counts. crf are the same report, but the former is the compiled version, meaning it may be run apart from Crystal Reports as a stand-alone program. Therefore, you can click on the exe or a short cut pointing at it and the report will run. Rev. Temps. rpt This report provides a dump of the TEMPORARY_BOOKS table, sorted backwards in time, grouped by week.

Demo Reports: SCirc. By. Year. rpt & top 10 sub. rpt SCirc. By. Year.

Demo Reports: SCirc. By. Year. rpt & top 10 sub. rpt SCirc. By. Year. rpt This report is the same as BCirc. By. Year except that it selects the ITEMS. BARCODE_TYPE for serials instead of books. One report would do here by making the wired-in type a parameter. top 10 sub. rpt This report is based on the booksubj query. It demonstrates the TOP N feature by showing the top 10 subjects in BOOKS ans determined by the MARC tag 650. Note that multiple reports could be based on this one query.

Complexities 1. The MARC bib is very complex, and is actually many tables. When

Complexities 1. The MARC bib is very complex, and is actually many tables. When searching the MARC bib, use the title found in BOOKS. 2. Crystal Report Date Format: Date (YYYY, MM, DD) 3. Not everything is mapped. Talk to us.