We want to use the Award Data for Department queries for various reasons. 1. We can find information about a particular award we are working with. 2. Easily find new federal or private funding sponsors for future new awards. 3. Create a report on all

1
We want to use the Award Data for Department
queries for various reasons.1. We can find
information about a particular award we are
working with.2. Easily find new federal or
private funding sponsors for future new
awards.3. Create a report on all the funded
awards of a department.4. See what Principal
Investigator Joe Shome is working on nowadays.5.
Etc...Lets start out simple though. Lets
assume we know what the sponsor award number for
an award is. This will make it very simple to
find the award.
Award Data For Departments Summary and Detail
QueryLink Queries
Finding awards based on an associated Sponsor
Award Number.
2
Start by using your web browser to surf to
http//financiallink.ucsd.eduClick on the
Queries link on the left navigation section.A
more direct approach of getting to the Award Data
Queries for Departments is by surfing to
http//blink.ucsd.edu/go/financialqueries
Navigate to http//financiallink.ucsd.edu/
Click on Queries in left navigation section
3
There are two excellent Microsoft PowerPoint
tutorials about queries on this page. Use them
to review how to work with QueryLink queries.
The advanced tutorial will show you the positive
potentials of using QueryLink queries.Click on
Award Data for Departments - Summary
Note the Basic and Advanced query tutorials
Click on Award Data For Departments - Summary
Query
4
You will need FinancialLink access to use either
of the two Award Data for Department
queries.You can get access by contacting the
DSA for you department or calling the ACT help
desk (x41853).If you have not registered
already, simply registering will not grant you
access to these queries. Otherwise all you may
have to do is click on the Forgot Your Password
link to retrieve your forgotten password if you
dont know it already.Be patient The query may
take a while to appear after you click the
Sign-In button.
Enter you UCSD Single-SignOn username and
password and Click the Sing In button.
5
Click on the Sponsors tab.In the sponsors tab
there are many things you can query for.We want
to find awards based on an associated Sponsor
Award Number.Enter the full sponsor award
number (N68335-03-C-0040) in the corresponding
box in the upper right.Click on the Sponsor
Award Number link to view the definition of what
a Sponsor Award Number is.
Click on Sponsors Tab
Enter the exact Sponsor Award Number here
Note Clicking on any of the links will show you
the definition of what you clicked on.
Click submit to view your results
6
Sponsor Award Number DefinitionThe unique
identifying number associated with an award
(generally assigned by the funding agency).Click
the Close button when youve finished viewing a
definition.
Click the close button when finished viewing
definition
7
The results to our query were dependent on the
default querys selected columns. There was only
one award containing sponsor award
N68335-03-C-0040.The Project Period (Project
End Date - Project Start Date) of this award
looks unusual because the awarded amount exceeds
one year and is roughly 15 months.Perhaps the
detail query can tell us why...
Note what you queried on is described on this line
Project Start and End dates look weird. Lets
take a look at the exact same query using the
Award Data for Departments Detail query to see why
1 record returned in this query
8
Lets do the same query but use the Award Data
for Departments - Detail query.Go back to the
FinancialLink start page and click on the Detail
query link.
Click on Award Data For Departments - Detail
link to get to the query
9
We are now in the Award Data for Departments -
Detail query. The only way we can tell is by
reading the title because the Detail query and
the Summary query have the same amount of tabs
and look virtually the same.There are extra
fields here that werent present in the Summary
query that we can query on and view in our final
results like the Sponsor Type field on the
Sponsors tab.Well enter the same information
we entered into the Summary query and look at the
results.
We are in the detailed Query
Click on Sponsors Tab
Enter the exact same Sponsor Award Number here as
you did in the summary query
Note The Detail Query contains the same number
of tabs (10) but contains extra information to
query on like Sponsor Type on the Sponsors tab.
Click submit to view your results
10
Even though the original Project Period was
roughly 9 months when the award started and had a
Project End Date of February 14, 2004, an
amendment was made to the award during the awards
active budget period and a no cost extension
added a 8 month budget period onto the end of the
award resulting in an extension of the Project
Period.Well, why are there 2 queries if we can
get the same information from the detail query we
get from the summary query?There is a lot of
Detail in these awards that causes multiple
rows to be returned back that just arent needed
when we do our querying.Lets demonstrate in the
following example
Clicking on column headers will sort the rows
corresponding to what was clicked instead of
showing you the definition of what was clicked
Same award but Budget Start and End Dates (off
screen) show the Project End Date was extended
from 2/14/2004 to 10/29/2004 due to a No Cost
Extension we could not view in the summary query
Data is formatted differently because more than
one row was returned in this query
11
Return to the Detail Query and go into the Admin
People tab.Select the checkboxes next to Admin
Name and Admin Role so that we can view these
columns in our final results.Click the submit
button. You might want to order the columns
so that Full UCSD Award Number and Admin Name
columns are side by side so comparing the results
will be easier.
Click on Admin People Tab
Select these two checkboxes to display the Admin
Name and Admin Role columns in your final
results
Click submit to see you results
12
13 rows are returned because for every detailed
record there can be numerous admin people
assigned to it.Thats a lot of information we
might not necessarily need.Let see what happens
in the Summary Query.
13 rows are returned because for every detailed
record there can be numerous admin people
assigned to it
Lets see what happens in the Summary Query...
13
Do the exact same entry as in the Award Data for
Departments - Detail query.Make sure you
entered the Sponsor Award Number in the Sponsor
Tab.
Click on Admin People Tab
Select these two checkboxes to display the Admin
Name and Admin Role columns in your final
results
Click submit to see you results
14
There are only 4 rows returned because the
elimination of duplicate Admin Roles within a
single award are one of the many optimizations
done to eliminate duplicate rows from showing up
in your summary query.Now that Ive got all
that stuff out of the way lets get into
something that might be a tiny bit more
interesting.
Four rows for the four Admin roles within this
entire award
15
I previously mentioned in this packet that these
queries can be used as a research tool to for
finding new sponsors for awards and can help
create PI reports.Using the features of
QueryLink and Excel we can create very useful
reports for departments using the Award Data for
Department queries.We are going to dive head
first into the chaotic sea of award data and
create our very own pool of perfection. Lets
use the detail query to retrieve all awards
funded through the BIOENGINEERING department.
Award Data For Departments Summary and Detail
QueryLink Queries
Tapping into the full potential of the Award Data
Queries using Microsoft Excel
16
To begin, we would obviously select
BIOENGINEERING as the Department Name to limit
our search to bioengineering awards but we must
also select Yes for Lead Department. This will
ensure the BIOENGINEERING department administered
the award.Note Just because we selected Yes
in the Lead Department limit column and
BIOENGINEERING as the Department Name, doesnt
mean we have to check the checkboxes to see them
in the final result. We already know that all
awards returned back will be awards
BIOENGINEERING was assigned to be the lead unit.
Make sure you are in the Departments Tab
Select Yes for Lead Department and
BIOENGINEERING as the Department Name to limit
our award search to awards the BIOENGINEERING
department administered
17
We will also pull the PI from the award data we
retrieve so that we can use it to create PI
reports later.Instead of viewing the query
results in a browser, lets send it to an Excel
pivot table so we can manipulate the data for
analysis.Note The Microsoft Excel pivot table
feature is only in 2000, XP, and 2003 versions of
Excel.Note You can further limit your results
to a particular fiscal year of awards by going to
the Money and Dates tab and entering the
appropriate values in the Budget Start Date and
Budget End Date fields.
Click on the Research Tab and check the box next
to Researcher Name to view it in the query results
We select PI here so that in our results CO-PI
will not produce duplicate budget periods for the
same award
Click on the drop down menu arrows and select
Excel 2000/XP Pivot Table
Click Submit button when you are finished
18
Now we have to set up of the pivot table.The
pivot table is an excellent tool if you know how
to use it.Start by dragging the Budget Direct
Cost column and Budget Indirect Cost into the
Data area of the pivot table one at a time.
Drag Budget Direct Cost and Budget Indirect
Cost into the DATA area of the pivot table
Data area of pivot table
19
We need to show useful information in the pivot
table. The default summary information is the
number of rows of Direct and Indirect Costs there
are. Thats not too useful.We start out but
formatting the data fields into currency amounts.
Budget Direct Costs and Budget Indirect Costs
are after all money fields.
When the Format Cells window appears, select
Currency as the format and click the OK button
The default summary of the columns is the total
number of all the rows, thus the Count
Hi-light both cells in the data area, right click
on them and select Format Cells in the popup menu
20
Likewise, we need to change the field setting of
both data cells to calculate the sum of Budget
Direct Cost and Budget Indirect Cost for the
awardsYou can only change one cells field
setting at a time.
When the PivotTable Field window appears, select
Sum in the Summarize by list of choices and
then click the OK button.
One at a time, right click on a cell in the data
field and choose Field Settings in the popup
window
After setting the field settings to Sum, the Sum
of Budget Direct Cost for all the awards is
displayed
Change the name of the column here if you like
21
We are done with setting up the pivot
table.What this shows us now is how much direct
cost and indirect cost went into funding all
awards administered by the BIOENGINEERING
department.Now lets see which sponsor
contributed most to the awards the BIOENGINEERING
department was responsible for.Doing this would
obviously be useful in determining where a
principal investigator, that is part of the
BIOENGINEERING department, should divest his time
into what sponsor he should get his funding from.
Drag the Local Sponsor Name column into the
Total cell of the pivot table to breakdown the
Budget Direct Cost and Budget Indirect Cost
by sponsor
22
This is a break down of what sponsor contributed
the most to awards the BIOENGINEERING department
administered.Use the features of excel to
create a handy graphical representation of it.
Right click anywhere in the data area of the
pivot table and choose Pivot Chart in the popup
menu to get a graphical representation of the data
23
Its very easy to see by looking at this chart
that if you are a BIOENGINEERING principal
investigator, you should be looking into getting
funding for your award from the NIH HEART LUNG
INSTITUTE.Now lets go back to our pivot table
and see which awards were funded by which sponsor
and for how many budget periods.
The Direct and Indirect cost for all the
BIOENGEENING awards are clearly broken down for
analysis
24
To further utilize this award data we are going
to take a look at which awards were funded by
which sponsor.The pivot table can be tricky.
Complete this next step carefully otherwise your
data will look like junk.When you drag the
Full UCSD Award Number column down between the
Local Sponsor Name column and the data area of
the pivot table you will see a faint I symbol
appearing between them.
Drag the Full UCSD Award Number column between
the Local Sponsor Name column and the data
area. Note the I symbol for correct placement
25
Now we can see which awards were funded by each
specific sponsor.We can further use the pivot
table to create a tiny PI report. We can find
which researcher was responsible for which awards
by selecting a researcher from the Researcher
Name column. Double clicking in the data area
will show you all the data behind the scenes
about that Researcher too!In conclusion, these
queries are here for you use to create reports
and make your research a little easier. Use the
Award Data for Department queries to your
advantage.Anthony Slay aslay_at_ucsd.edu
Now getting a PI report is a simple as clicking
on the Researcher Name drop down list box and
selecting a PI
After selecting a PI, double click on specific
lines in the data area of the pivot table to see
the data associated with that PI for a specific
sponsor name or UCSD award number
Click on last Total line to see all data
associated with the PI for all associated
sponsors.