1 / 71

Exporting Data and Creating Financial Reports with Excel and Crystal

Exporting Data and Creating Financial Reports with Excel and Crystal . By Peter Schmidt. Set up an ODBC connection. Setup ODBC. Select menu:. Start Settings Control Panel. Setup ODBC. Select folder:. Administrative Tools (double click). Setup ODBC. Select Program:.

kentaro
Download Presentation

Exporting Data and Creating Financial Reports with Excel and Crystal

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exporting Data and Creating Financial Reports with Excel and Crystal By Peter Schmidt

  2. Set up an ODBC connection

  3. Setup ODBC Select menu: • Start • Settings • Control Panel

  4. Setup ODBC Select folder: • Administrative Tools (double click)

  5. Setup ODBC Select Program: • Data Sources (ODBC) (double click)

  6. Setup ODBC Select : • System DSN • Add

  7. Setup ODBC From here on, the steps for SQL Server are different than Informix.

  8. Setup ODBC For SQL Server The following steps are just for SQL Server databases.

  9. Setup ODBC For SQL Server Select : • Select SQL Server driver • Click Finish

  10. Name (any name you want) • Description (optional) • Your SQL Server server-name • Next > Setup ODBC For SQL Server Enter :

  11. With SQL Server authentication using a login ID and password… • Connect to obtain default settings • Login ID & password – Next > Setup ODBC For SQL Server Enter :

  12. Change the default database to: (Pick your desired database) • Leave everything else unchanged • Next Setup ODBC For SQL Server Enter :

  13. Setup ODBC For SQL Server • Leave everything else unchanged • Finish

  14. Setup ODBC For SQL Server • Test Data Source • Click OK on test results • Click OK on Test Data Source

  15. Setup ODBC For SQL Server • Should see your new System DSN (DSN = data set name) • Click – OK – to finish

  16. Setup ODBC For Informix The following steps are just for Informix databases.

  17. Setup ODBC For Informix Select : • Select a driver • Finish

  18. Setup ODBC For Informix • Name (any name you want) • Description (optional) • OK Enter :

  19. Informix server name (instance name) • Server Host Name (box name) • tcp/ip service name, default is 1575 • Protocol – onsoctcp • Database – son_db • Click – Apply & Test Connection • Click OK - OK Setup ODBC For Informix Enter :

  20. Extract data using Excel

  21. Open a new worksheet in Excel

  22. Select options: • Data • Get External Data • New Database Query

  23. Pick your ODBC data source

  24. Microsoft Query should automatically start. If this windows pops up, you can use it to create your SQL or just “close” it. Note the Microsoft Query window that is displayed.

  25. Copy and paste in a pre-written SQL statement. Click the SQL button to copy and paste in a pre-written SQL statement. Click OK when ready.

  26. Sql used for this demo SELECT mbillaty, tkfirst, tklast, mmatter, clname1, mdesc1, mloc, mhper, mhdobidb, mhdocodc, mhcrdc FROM son_db.dbo.matths, son_db.dbo.timekeep ttk, son_db.dbo.matter, son_db.dbo.client, son_db.dbo.periodt WHERE mhmatter = matter.mmatter and mclient = client.clnum and mbillaty = tkinit and mhper = pe and pebedt between '01/01/2004' and '12/31/2004'

  27. Your data should be displayed.

  28. Send the data to your spreadsheet. Click on the “Return Data” button.

  29. Place your data on your spreadsheet (usually in it’s own ‘tab’)

  30. Your data is placed into your spreadsheet. If desired, rename the ‘tab’ to ‘data’.

  31. If desired, copy to a new ‘tab’ and create a page heading and some better column headings.

  32. Start a Pivot Table. • Data • PivotTable and PivotChart Report

  33. Microsoft Excel List or database • Pivot Table • Next >

  34. Indicate the data to be included in the pivot table. Be sure to include the column headings with the data.

  35. Indicate where to place the new pivot table. You can place it on a new or the existing worksheet.

  36. Build your new pivot table. Build your pivot table by dropping and dragging fields from the list onto the template.

  37. Usually, make sure you are doing a “sum” and not a “count”.

  38. Create a “PivotChart” (graph) • PivotTable • PivotChart

  39. Presto!

  40. If desired, convert to a “pie” chart. • Right-click the chart • Select “Chart Type”

  41. Select your desired chart type.

  42. Presto - Changeo!

  43. Extract data using Crystal Reports

  44. Start Crystal Reports

  45. File • Printer Setup • Orientation - Landscape For additional width, change to Landscape printing.

  46. Database • Database Expert • Create New Connection • ODBC (RDO) Configure your DB connection

  47. Select your ODBC database desired – Next>

  48. Supply your password (or select “Trusted Connection”) Click “Finish”

  49. Pick the tables you want to use in your query. Click on “>” to put tables into the “Selected” column

  50. Click on the “Links” tab and configure your table joins. See next page for a list of joins needed for this demo.

More Related