Finding awards based on an associated Sponsor Award Number.

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...Let’s start out simple though. Let’s 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 don’t 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 sponsor’s 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 you’ve finished viewing a definition. Click the close button when finished viewing definition

7. The results to our query were dependent on the default query’s 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. Let’s 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. Let’s 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 weren’t 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.We’ll 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 aren’t 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.That’s 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 I’ve got all that stuff out of the way let’s 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. Let’s 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, doesn’t 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, let’s 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. That’s 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 cell’s 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 let’s 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. It’s 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 let’s 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@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.