Extracting Information from Research Reports

Extracting Information from Research Reports

First generate a list of grants which are funded by the sponsor you are interested in.

·  Open transaction S_ALN_01000079

·  Input your dept code eg HAR in the Grant Group field. (You can use the variant button to exclude closed accounts if needed)

·  Once the report has run, from the menu at the top of the screen select List>Export>Spreadsheet>All Available Formats>Microsoft Excel(in Existing XXL format)

·  Export the information as a table

·  Use a filter to limit the list to the sponsor(s) you are interested in

·  Select all of the grant numbers and use Ctrl+C to copy the grants to the clipboard

Once you have generated a list of grants you can use a Budget vs Actual report to extract the financial information you need

·  Open transaction Y_DR3_07000007

·  Input the sponsored class you want in the Sponsored Class Or value(s) field eg RESEARCH INCOME

·  Click on the Grant Or value(s) multiple selection button and then click on the clipboard button to copy your list of grants from the clipboard

·  Click on the execute button and then again on the next screen to run the report

·  Once the report has run, double click on the amount in the available budget column

·  Once the line item report has run, click on the select layout button and select the EXPORT layout

·  Export the report to Excel as described above

·  Filter the report to show all records where the ‘R’ value is 0

·  Replace all 0’s with the word ‘Actuals’

·  Repeat the process and replace all 1’s with the word ‘Budget’

·  Select Insert>Pivot Table, highlight the data (including the headings), select the new worksheet option and then click ok.

·  Drag the following fields into the report areas as shown below

Row Labels - Rcvr Grnt

Column Labels - R

∑ Values - Sum of Co.cd.curr

·  Change the order of the columns and hide the grand total column by right clicking on the column header and selecting the appropriate option.

·  You can then calculate the remaining budget by subtracting the actuals from the budget eg =C5-B5. (Make sure your formula does not reference the pivot table otherwise you will not be able to drag the formula down to other rows)

IMPORTANT

Please remember to save your spreadsheet before returning to your uBASE session otherwise you will lose your data.

M:\fi_share\Financial Control Group - Research Finance\Research Management Info\Notes and Procedures\Extracting Information from Research Reports.docx