Transcript State of The Company

Crystal And Elliott
Edward M. Kwang
President
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 won’t learn this knowledge anywhere with
standard Crystal Report training.
• An Introduction to Database Access from third
party applications
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
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 IQ’s Category
– ODBC offer better performance
– ODBC offer database level security
Two Data Access Methods
• Database Files – Using Btrieve
– Can’t 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
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)
Demo Report Export
• Create the same invoice summary report
by using Report Export
• Add Subtotal by Customer
• Hide Detail
• Show Drill Down Feature
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
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
• 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, can’t take
advantage of key because the formula.
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 in PCC
• 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
• 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.
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.
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 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
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 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.
Demo Security Back Door
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
Questions & Answers