Transcript Document

Exporting Data
and
Creating Financial Reports
with Excel and Crystal
By
Peter Schmidt
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 1
Set up an ODBC connection
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 2
Setup
ODBC
Select menu:
 Start
 Settings
 Control Panel
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 3
Setup
ODBC
Select folder:
 Administrative Tools
(double click)
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 4
Setup
ODBC
Select Program:
 Data Sources (ODBC)
(double click)
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 5
Setup
ODBC
Select :
 System DSN
 Add
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 6
Setup
ODBC
From here on, the steps for SQL Server
are different than Informix.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 7
Setup ODBC
For
SQL Server
The following steps are just for SQL Server databases.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 8
Setup
ODBC
For
SQL Server
Select :
 Select SQL Server driver
 Click Finish
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 9
Setup
ODBC
For
SQL Server
Enter :
 Name (any name you want)
 Description (optional)
 Your SQL Server server-name
 Next >
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 10
Setup
ODBC
For
SQL Server
Enter :
 With SQL Server authentication using a
login ID and password…
 Connect to obtain default settings
 Login ID & password – Next >
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 11
Setup
ODBC
For
SQL Server
Enter :
 Change the default database to:
(Pick your desired database)
 Leave everything else unchanged
 Next
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 12
 Leave everything else unchanged
Setup
ODBC
For
SQL Server
 Finish
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 13
Setup
ODBC
For
SQL Server
 Test Data Source
 Click OK on test results
 Click OK on Test Data Source
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 14
Setup
ODBC
For
SQL Server
 Should see your new System DSN
(DSN = data set name)
 Click – OK – to finish
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 15
Setup ODBC
For
Informix
The following steps are just for Informix databases.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 16
Setup
ODBC
For
Informix
Select :
 Select a driver
 Finish
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 17
Setup
ODBC
For
Informix
 Name (any name you want)
Enter :
 Description (optional)
 OK
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 18
Setup
ODBC
For
Informix
 Informix server name (instance name)
Enter :





Server Host Name (box name)
tcp/ip service name, default is 1575
Protocol – onsoctcp
Database – son_db
Click – Apply & Test Connection
 Click OK - OK
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 19
Extract data using Excel
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 20
Open a new worksheet in Excel
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 21
Select options:
 Data
 Get External Data
 New Database Query
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 22
Pick your ODBC data source
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 23
Microsoft Query should automatically start.
Note the
Microsoft Query
window that is
displayed.
Exporting Data and Creating Financial Reports
with Excel and Crystal
If this windows
pops up, you
can use it to
create your SQL
or just “close”
it.
Slide 24
Copy and paste in a pre-written SQL statement.
Click the SQL
button to copy
and paste in a
pre-written SQL
statement.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Click OK when
ready.
Slide 25
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'
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 26
Your data should be displayed.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 27
Send the data to your spreadsheet.
Click on the
“Return Data”
button.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 28
Place your data on your spreadsheet
(usually in it’s own ‘tab’)
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 29
Your data is placed into your spreadsheet.
If desired,
rename the ‘tab’
to ‘data’.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 30
If desired, copy to a new ‘tab’ and create
a page heading and some better column headings.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 31
Start a Pivot Table.
Exporting Data and Creating Financial Reports
with Excel and Crystal
 Data
 PivotTable and PivotChart Report
Slide 32
 Microsoft Excel List or database
 Pivot Table
 Next >
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 33
Indicate the data to be included in the pivot table.
Be sure to include the column headings with the data.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 34
Indicate where to place the new pivot table.
You can place it on a
new or the existing
worksheet.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 35
Build your new pivot table.
Build your pivot table by dropping and dragging
fields from the list onto the template.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 36
Usually, make sure you are doing a “sum” and not a “count”.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 37
Create a “PivotChart” (graph)
Exporting Data and Creating Financial Reports
with Excel and Crystal
 PivotTable
 PivotChart
Slide 38
Presto!
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 39
If desired, convert to a “pie” chart.
Exporting Data and Creating Financial Reports
with Excel and Crystal
 Right-click the chart
 Select “Chart Type”
Slide 40
Select your desired chart type.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 41
Presto - Changeo!
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 42
Extract data using Crystal Reports
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 43
Start Crystal Reports
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 44
For additional width, change to
Landscape printing.
Exporting Data and Creating Financial Reports
with Excel and Crystal
 File
 Printer Setup
 Orientation - Landscape
Slide 45
 Database
Configure your DB
connection
Exporting Data and Creating Financial Reports
with Excel and Crystal
 Database Expert
 Create New Connection
 ODBC (RDO)
Slide 46
Select your ODBC database desired – Next>
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 47
Supply your password
(or select “Trusted Connection”)
Click “Finish”
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 48
Pick the tables you want to use in your query.
Click on “>” to put tables into the “Selected” column
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 49
Click on the “Links” tab and configure your table joins.
See next page for a list
of joins needed for
this demo.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 50
For this demo, set up the following links.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 51
Bring up the “field explorer”
window
Exporting Data and Creating Financial Reports
with Excel and Crystal
 View
 Field Explorer
Slide 52
Expand the “Database Fields” section
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 53
Drag & drop desired fields into the “Details” section.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 54
Adjust fields widths if necessary to accommodate your needs.
Click “Preview” to see the 1st cut of your report.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 55
See the first cut of your report.
The “Preview”
button moves
over next to
“Design” tab.
Click on the
design tab to
finish formatting
your report.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 56
Drag the bar under the columns headings down to give your
page headings section more height.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 57
Make your page
headings taller.
 Right-click under Page Header section
 Select all section objects.
 Drag your page headings borders up and down.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 58
Edit and clean up your column headings as desired.
(Enter better descriptions, adjust widths, make bold, etc.)
Hint: Use <Ctrl>-click
to move objects
together as a group.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 59
Click “Preview” as desired to see how you are doing.
Hint: Change percentage
to make it fit on your
screen better.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 60
Use text boxes to add report titles.
Hint: Format titles as
desired. Add color, different
font, larger font, etc.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 61
Use special fields under Field Explorer
to add other page headings like run-date and page number.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 62
Use Parameter Fields under Field Explorer to prompt for input
values when you start your report.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 63
Use Formula Workshop to establish relationship between
parameter fields and data in the database.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 64
Use Refresh Report Data to get re-prompted for input fields.
Select Prompt for new
parameter values to get
re-prompted for input.
Hint: Press <F5> to refresh
report data and re-prompt.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 65
Use Group Expert to prepare for sub-totals.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 66
Use Insert Summary to add sub-totals.
Hint: Make sure
you have it set to
sum.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 67
Do almost the same to get final totals.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 68
Add some underlines if desired with Format Objects.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 69
Preview and admire your new report.
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 70
Exporting Data
and
Creating Financial Reports
with Excel and Crystal
Questions ?
Peter Schmidt
www.prstech.com
Exporting Data and Creating Financial Reports
with Excel and Crystal
Slide 71