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 won’t 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 IQ’s Category • ODBC offer better performance • ODBC offer database level security

5. 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

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 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

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, can’t 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 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)

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