RF Grant Expenditure Export Project Level

Description

Purpose

The Research Foundation (RF) developed the customized Grant Expenditure Export report to provide a version of the Grant Sum that could be exported easily to Excel to meet the reporting needs of various sponsors by giving the capability to manipulate the data to meet the sponsor's needs. The RF Grant Expenditure Export Report does not contain header information and only displays the following required columns for sponsor reporting: Expenditure Type, Employee/Supplier Name, Expenditure Item Date, AP Invoice Number, AP Invoice Item Description/Employee Title, Purchase Order Number, Purchase Order Line Description, Check Number, Check Paid Date, and Expended/Refund Amount. It is used to provide the detailed backup documentation required by sponsors for financial and/or invoicing reports.

Frequency

Run this report as needed.

Responsibilities with Access

People with the following responsibilities are able to generate this report:

Setting Report Parameters

The following table describes the required and optional parameters to use to run the report:

Parameters

Description
(click in the field to see the List of Values)

Required Parameters

Project Number

The Oracle project number (Required for Project and Task Level Reports)

Award Number

The Oracle award number (Required for Award and Task Level Reports. Optional for the Project Level Report)

Task Number

The Oracle task number (Required for the Task Level Report)

Starting Period (MON-YYYY)

The month and the year from which the data will be reported

 

Example: MAY-2002

Ending Period

The month and the year from which the data will be reported (e.g. May-2002) To report more than one month, change this date to the last month to be reported (e.g., JUN-2002)

Understanding the Output

The following table describes the report output:

Column Heading

Description of Column

Award Number

This displays only on the Project level as column 1.

Project Number

This displays only on the Award level as column 1.

Task Number

This displays as column 2 on the Award Level and on the Project Level.

Expenditure Type

The expenditure category that was charged.

Employee and/or Supplier Name

The expenditure description of the charge. For Salaries, this column displays the name of the person paid. For Other Than Personal Services (OTPS), this column displays the vendor and/or person paid.

Expenditure Item Date

For OTPS, this column displays the date the Accounts Payable invoice was entered.

AP Invoice Number

The Oracle Accounts Payable invoice number assigned to item for OTPS.

AP Invoice Description and/or Employee Title

For Salaries, this column displays the title of the person paid. For OTPS, this column displays the accounts payable invoice description field from the AP invoice header.

Purchase Order Number

The Oracle Purchase Order number assigned to the item.

Purchase Order Line Description

The description of the item assigned in the Oracle Purchase Order Line Description field.

Check Number

Individual check numbers for each OTPS item.

Check Paid Date

For Salaries, this column displays the payroll period. For OTPS, this column displays the Individual check dates for each item.

Expended/Refund Amount

Amount of the expenditure item. A negative value displays for adjustments and category refunds.

Export a TEXT Report Request into Excel

A variety of Oracle standard reports as well as Research Foundation custom reports can be run from each business area in Oracle. A person's sign-on responsibility determines which reports are accessible. For more information, refer to the Reports/Queries resource area in EPSS and the Run a Report work instruction in most business areas.

A report may be saved as a .txt file and then opened in a spreadsheet application. Also, selected areas of the displayed report may be copied and pasted into an application, such as a spreadsheet. The following table describes the steps to take the report data from Oracle and put it into a spreadsheet:

Step

Action

1

When running the report, ensure that the TEXT version has been selected from the list of reports.

 

Submit the request and wait for the Phase of the Request to read Completed.

2

After the report has completed normally, view the output.

3

Select File > Save As to name the file and save it with the Text File (.txt) extension. Close the report.

4

Switch to the Excel spreadsheet application.

5

To open the file, select File > Open, which opens the Open window. To find the file in the directory in which it was saved, change the Files of type: field to All Files (*.*). Select the desired file and click Open to go to the Text Import Wizard window.

6

In Step 1 of the Text Import Wizard, select Fixed Width and click Next.

7

Step 2 of the Text Import Wizard asks you to Set Field Widths and Column Breaks

Add or remove the appropriate breaks, and click Next.


Note: The line breaks should be at the following:
Task Level 40, 185, 200, 240, 335, 365, 689, 700, 730
Project Level 7, 47, 185, 198, 236, 385, 403, 687, 697, 735
Award Level - 10, 25, 70, 225, 240, 280, 419, 435, 734, 750, 790

8

In Step 3 of the Text Import Wizard, select Finish.

9

You have successfully exported the data into Excel.

You can now adjust and manipulate the data. Some column widths may need to be increased for the data to be fully displayed.

Select File > Save As to name the file and save it with the spreadsheet extension.

Helpful Tips

Effective Date: 3/14/2007 8:43:57 AM