DATA WAREHOUSE

1
DATA WAREHOUSE QueryLink TUTORIAL Advanced Topics
QueryLink is used to retrieve Campus data for
Departmental use. The purpose of the tutorial
is to acquaint you with some of QueryLinks more
advanced features such as charting and
downloading of data.
Prepared by ACTs Data Warehouse team 03/20/03
2
TABLE OF CONTENTS

• Query Options
• Balances
• Send Data To
• Excel
• Excel Pivot Tables
• Chart Wizard

3
Query Options Balances
This section of the Tutorial presents steps you
can follow to help with the use of the Balances
Wizard.
4
Click on the Check or Payment/Invoice Model to
select it.
5
The login screen will appear next.
Enter your User Id and Password.
6
Change the query to have the following
configuration
7
Choose the Balances Query Option
8
Objective is to create a new column titled
Total_Minus_Tax that will be calculated as
Approved_Invoiced_Amount Additional_Charge_amoun
t Discount_Amount.
9
Step 1 was accomplished by a combination of
clicking the field name, clicking the add button,
followed by the button, followed by clicking
the next field name.
10
The hard part is now done. Complete Step 2 by
giving the field a name then follow by clicking
on the Add New Column button in Step 3.
Everything should now look like this page.
Follow by clicking on the OK button at the bottom
of the page.
11
Run the query by clicking on the submit button
12
You new column now appears
13
Send Data To
This section of the Tutorial will present how to
download data to Excel.
14
If you would like to save the results of your
query on your desktop computer select the output
setting for an EXCEL file. Click Submit.
15
A dialog box will appear. Choose Save File.
16
The next dialog box to appear has three selection
areas Save in where you select a storage
location, File name where you name the file with
an EXCEL suffix (.xls), and Save as Type (allow
All Files as the default to remain). Click Save.
The results set of your Query is Saved in EXCEL.
17
From EXCEL To retrieve your new file, click Open
in the File Option Drop Down Menu.
18
A dialog box will assist you in locating your
data file. Highlight the file and click Open.
19
The EXCEL Text Import Wizard checks file
formatting whenever a new file is downloaded. All
Query Models format data to be compatible with
EXCEL, therefore no formatting changes are
necessary by the Wizard.
20
This is the results set of your Query which was
downloaded to EXCEL and retrievable as an EXCEL
file.
21
Send Data To
This section of the Tutorial will present how to
download data to an Excel Pivot Table.
22
Our example will use the Operating Ledger Monthly
Summary Query
23
Change the query to have the following
configuration
24
If you would like to save the results of your
query on your desktop computer select the output
setting for an EXCEL Pivot Table. Click Submit.
25
(No Transcript)
26
If you would like to see the budget, financial
and encumbrance amounts for just December, use
the drop down arrow and select December, 2001
followed by the OK button.
27
(No Transcript)
28
These results show totals by index for the month
of December. This was accomplished by pointing
and dragging the index gray tab and dropping in
over the total gray tab.
29
Try double clicking on an amount column.
30
The underlying detail will appear.
31
To produce a chart of the resulting data, use
View, Toolbars and Pivot Table.
Then select the Chart Wizard icon from the
toolbar.
32
(No Transcript)
33
Send Data To
This section of the Tutorial will present how to
create a chart using the Chart Wizard.
34
Our example will use the Campus Student
Information Query
35
Change the query to have the following
configuration.
36
If you would like to save the results of your
query as a chart. Click Submit.
37
Select Bar Chart, College and provide Student
Enrollment By College as a title then click the
submit button.
38
(No Transcript)
39
You have completed the QueryLink Advanced
Tutorial. If you have any further questions,
please contact the ACT Help Desk _at_ x41853 or
x43187
CONGRATULATIONS