Transcript State of The Company

Crystal And Elliott
Edward M. Kwang
President
Crystal Version
• Standard - $145
• Professional - $350
• Developer - $450
Professional vs. Standard
• No real advantage as far as Elliott
concern
– Professional allow access to Microsoft SQL
or Oracle database natively
Developer vs. Standard
• Developer allow Crystal Reports to
interact with VB
• In only few circumstance, you need to
use VB to produce the Crystal Report
with Elliott
– I.e. Print Item Image (from link) on The
Crystal Report
Demo
• Using Visual Basic to Print A Crystal
Report for Item with Image
Two Data Access Methods
• Database File – Using Btrieve
– Can’t Enforce Security
– No View - Need to Join Table Manually
– May be slower
• ODBC – Using SQL
– Can Enforce Security
– With Views – Ease of Use
– Can be a lot faster than Database File Method
Demo
• 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: 20020101 - 20020131
• Using Crystal with ODBC
– Choose CPINVLIN_VIEW
• Summary
– Using View Is Easy
– Using View Produce Better Performance
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
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.
Date Field Issue
• All Date Fields in Elliott are defined as 8-digit
Numeric Fields.
– The display doesn’t 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
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?
Demo Date Handling
• Create a Crystal Report like Invoice Summary
Edit List from CPINVHDR. DSN PrimaData
• Select Inv_Date range 20020101 to 20020107.
• Create InvDate Formula by using
NumberToDate Function
• Place InvDate on where Inv_Date use to be
• Run the Report
• Change Selection Criteria base on InvDate
• What Happen? NumberToDate function not
available on server side, so all data has to be
processed on the client side.
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
Demo CPHSTTRX_VIEW
• The favorite Sales Analysis Data Source
– No Formula To Worry
• Link CPHSTTRX to
– ARCUSFIL
– CPINVHDR
– IMITMFIL
Demo APOPNFIL_CHK_VIEW
• The difference between using this view
vs. APOPNFIL_CHK table.
– Vendor Info Available
– No Need to filter out Voucher Record
Use Excel
• 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.
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.
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.
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.
Demo
• Show SQL Data Manager
– Update a record
– Delete a record
– Mass Update
• Enable Security in PCC
• Review \ELLIOTT7\DDF40\GRANT.SQL
• Run GRANT.SQL
• Access Password Protected Data in PCC
• Access from Crystal
• Access from Excel
Considerations for Views
• Rights is granted to a table and can’t 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, don’t use
views. (Performance Consideration)
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 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.
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