Crystal And Elliott

1
Crystal And Elliott

• Edward M. Kwang
• President

2
Objective

• A brief demo of Crystal Report to entice you
• People spend thousand of dollars to attend
  Crystal Report trainings
• We do not intend to make you a Crystal Report
  expert with this session
• How Elliott Crystal Reports can integrate
  together
• You wont learn this knowledge anywhere with
  standard Crystal Report training.
• An Introduction to Database Access from third
  party applications

3
Crystal Reports Elliott

• Netcellent has chosen Crystal Reports to replace
  aging IQ Report Writer
• Windows vs. DOS
• Much More Powerful
• Industrial Standard Tool
• Difficulty with using Crystal in the past
• User need to manually join tables
• IQ has Category which act like pre-joined tables
• Introduce a new set of DDF in V7 to resolve this
  problem

4
A New Set of DDF

• A new set of DDF is in Elliott V7
• Improvement in the New DDF
• Easier to use because of VIEW
• VIEW is similar to IQs Category
• ODBC offer better performance
• ODBC offer database level security

5
Two Data Access Methods

• Database Files Using Btrieve
• Cant Enforce Security
• No View - Need to Join Table Manually
• May be slower
• Need Crystal Professional Version (V9)
• ODBC Using SQL (Client Server)
• Can Enforce Security
• With Views Ease of Use
• Can be a lot faster than Database File Method

6
Demo a Simple Crystal Report

• Using ODBC with DSN Mdata96 The Suggested
  Method
• Use the Blank Report Method
• Create a Crystal Report like Invoice Summary Edit
  List from CPINVHDR.
• Select Inv_Date range 20020101 to 20020107
• Create Grand Total
• Notice the Inv_Date and Inv_No Display are not
  desirable.
• Format Inv_Date Inv_No with No Decimal
• Save it on the Desktop (With or Without Data)

7
Demo Report Export

• Create the same invoice summary report by using
  Report Export
• Add Subtotal by Customer
• Hide Detail
• Show Drill Down Feature

8
Demo Cross Tab Report

• Using Crystal with Database Files
• Manually Join, CPINVLIN, CPINVHDR, IMITMFIL
• Choose m\macola\data\file.ddf
• Report Export, Cross Tab, Inv_slm_no_1,
  item_prod_cat, Ext_amt
• Range Line Item Inv Date 20020101 - 20020131
• Using Crystal with ODBC
• Choose CPINVLIN_VIEW
• Summary
• Using View Is Easy
• Using ODBC Produce Better Performance

9
Performance Client Server

• ODBC can be significantly faster than Database
  File method. This depend on multiple factors
• Server and Network Speed
• Is The Key Used for Selection Criteria?
• ODBC is faster if selection criteria is not a key
• Size of the selected dataset of the whole file
• ODBC is faster if the the selected dataset is
  small
• Data File Join
• If multiple table need to join, using View with
  ODBC is faster because this force the join on the
  server side

10
Numeric Field Issue

• When you use ODBC, the Numeric Field is displayed
  with Comma and Decimal. Field like INV_NO,
  ORD_NO does not look good on the report.
• Solution Right click on the field and choose
  Format Field and change the style.

11
Date Field Issue

• All Date Fields in Elliott are defined as 8-digit
  Numeric Fields.
• The display doesnt look good
• ODBC is even worse (comma and decimal were used)
• Download U2LTDATE.DLL from ELLIOTT.COM support
  area. Copy to Crystal Report Directory
• Use NumberToDate Custom Function

12
Use NumberToDate Function

• Create a formula field, say InvDate as
• NumberToDate(INV_DATE)
• Place InvDate on your report
• Use InvDate as Selection Criteria may slow down
  report. Use INV_DATE instead. Why?

13
Demo Date Handling

• Use Invoice Summary Report Created Previously.
• Create InvDate Formula by using NumberToDate
  Function
• Place InvDate on where Inv_Date use to be
• Run the Report
• Handle Numeric Field Display Issue
• Change Selection Criteria base on InvDate
  1/1/2002 1/7/2002
• What Happen? NumberToDate function is not
  available on server side, so all data has to be
  processed on the client side. Also, cant take
  advantage of key because the formula.

14
Using Views

• Views Not Available Through Database File method
• Views Is Only Available Through ODBC method
• Using View Prevent Manually Join The Table
• Using View Ensure the Joining of Tables Are On
  The Server Side Better Performance

15
Demo CPHSTTRX_VIEW in PCC

• The favorite Sales Analysis Data Source
• No Formula To Worry
• Link CPHSTTRX to
• ARCUSFIL
• CPINVHDR
• IMITMFIL

16
Demo APOPNFIL_CHK_VIEW

• The difference between using this view vs.
  APOPNFIL_CHK table.
• Vendor Info Available
• No Need to filter out Voucher Record
• Review ELI7VIEW.SQL You can Create Your Own
  View
• Save Your Own View in a separate .SQL file
• Run your .SQL file after you install an update
  DDF from Netcellent.

17
Demo Using Excel with ODBC

• From Microsoft Excel Menu, choose Data, Import
  External Data, New Database Query.
• If you have setup the ODBC DSN, then you should
  see it in the list. Choose it and then choose
  the tables.
• This feature (Microsoft Query) by default is not
  installed with Microsoft Office 2000.
• Suggest to use the Complete instead of
  Typical installation option when installing
  Microsoft Office.

18
Enable Security with PSQL 2000

• Use Pervasive Control Center, Identify the
  server, expand the database folder, then
  highlight the database and right click.
• Choose Properties.
• Choose Security Tab
• Enter the password.
• Once security is created. A user Master is
  created with the password you just type in.

19
Create User and Group

• User Master has all the rights, you might want
  to create Users and Group that has limited rights
  only.
• Expand the Database node and highlight Users node
• Right click on the right hand pane to create
  Users or Groups
• Use the GRANT statement to tailor security.
• Use \elliott7\ddf40\grant.sql as an base to
  tailor you needs.
• Save your scripts because you will need to use it
  again when we give you a new set of DDF.

20
Grant Access to User and Group

• Setup Users and Groups
• Setup User Belong to Group
• Each user can only belong to one group
• If a user need to belong to multiple group, you
  will assign this user to one group first, then
  grant right for other tables individually to that
  user.
• Setup Tables to Be Access by Users and Groups
• Determine the Select, Update and Delete rights
  for each Table.

21
Demo Security in ODBC

• Show SQL Data Manager (Mdata96)
• Update a record
• Delete a record
• Mass Update
• Enable Security in PCC (Chk DDF in Data_96)
• Review \ELLIOTT7\DDF40\GRANT.SQL
• Run GRANT.SQL (Rename customize it first)
• Access Password Protected Data in PCC
• Access from Crystal
• Access from Excel

22
Considerations for Views

• Rights is granted to a table and cant be granted
  to a view.
• User must at least have the SELECT right on all
  the tables in the view to Select that view,
  otherwise will receive error message.
• If you only need data from one table, dont use
  views. (Performance Consideration)

23
Security Back Door

• User can remove the DDF files and replace with a
  new set of DDF that does not have security turned
  on.
• If you put DDF in the DATA directory, then by
  default user have full access right to the DDF
  file.
• Separate DDF40 directory from DATA directory.
  Apply security to DDF40 directory. Do Not Apply
  Security to DDF directory.
• Create Separate DDF directory if the user right
  need to be different by company.

24
Demo Security Back Door
25
Conclusion

• Using ODBC Instead of Database File
• Performance Improvement
• Ease of Use with Views
• Security
• Put DDF in a different directory from DATA
• Impose Netware or NTFS security on DDF

26
Questions Answers