Data Warehouse

Master Data Changes for a Cost Object

[Download]  

For a selected cost object (WBS, Cost Center or Order), shows selected master data values at the end of each month from the inception of the cost object. Master data fields shown are: Supervisor, Addressee, start date, end date, term code, and (WBS) authorized total (in the first pivot report); and costing sheet, F&A key, EB key, lab allocation key, and (WBS) authorized total (in the second pivot). This query can be used to monitor cost object changes as an alternative to receiving 001 forms ("Notice of Cost Object Change") by e-mail.

02/13/2004

Cost Collectors by Supervisor

[Download]

Selects and displays open cost collectors by supervisor.

01/23/2012

Cost Sharing Commitments by DLC

[Download]

Revised 6/23/2010 By master project number, displays all of a DLC's cost sharing commitments made as a condition of the award.

06/23/2010

Current and Pending Support

[Download]

Modified 9/11/2009 to add a flag for ARRA support. Current and Pending Support information from Coeus. User may report by Profit Center and/or by Investigator. Four pivot reports, Active support by Type (Organized Research, Pre- or Post-doctoral support, other), Active Support by PI, Pending support by Type, Pending support by PI, and one detailed Current and Pending Support by PI. NOTE: This document contains three queries, one for Current Support, one for Pending Support, and one to Combine the two. Use the "Process All" command to retrieve all data. The Current and Pending Support queries may also be processed separately.

Maija Ahlquist

09/11/2009

Detail Transaction (DTR)

[Download]

Modified 2/24/2010 to substitute the GL Account Report table for the GL Account Table and to substitute a fiscal period limit for the calendar period limit. For a cost collector or group of cost collectors, this report shows the actual amount spent sorted by GL Account for any given month. It includes: Pivot Report: Actual Amount by GL Account; Detail Report: Document Date, Document Number, Purchase Order Number, Reference Document Number, Description; Document Type, Vendor and Actual Amount sorted by GL Account.

02/24/2010

Final Research Expenses

[Download]

For a profit center or a profit center range, this query shows the fiscal year and cumulative activity for research projects (WBS Elements greater than 5000000). Settlement activity is excluded. Selection is for profit center and fiscal year. Closing periods are not shown because there is no activity on research accounts after period 12.

06/15/1999

Fiscal Year Comparison of Research Expenditures

[Download]

***REVISED 11/19/2001*** This report shows a Department's Research Expenditures by Sponsor for a selected number of Fiscal Years (data is available from FY1997 forward).

Maija Ahlquist

11/19/2001

Fiscal Year Research Expenditures

[Download]

***REVISED 2/19/2002***This report shows a Department's Research Expenditures by Sponsor and GL Account for a selected Fiscal Year.

02/19/2002

Master Project Report

[Download]

For a Profit Center, displays a summary report for open Master Projects. Lower level WBS Elements (aka "children") are displayed under the master project and balance information is computed for each WBS Element as well as for the total project.

07/29/1999

Overrun

[Download]

NOTE: REVISED 3/29/2002 to clarify that overrun with commitments amounts are calculated on non-blanket order commitments. For a profit center or profit center range, this report shows overrun cost collectors and identifies the overrun amount and overrun amount including non-blanket order commitments. Overrun amounts are calculated nightly in the warehouse, and both overrun amount and overrun with non-blanket order commitment amount are loaded with EB, allocation and overhead costs. The report has two views: by supervisor and by cost collector type.

03/29/2002

Primary & Secondary Research Volume, All

[Download]

This version of the "classic Brown Book" fiscal year volume report for Departments will bring back authorized totals and unexpended balances for all cost collectors, whether or not they had fiscal year activity. It is a much longer report than the Primary & Secondary Research Volume, FY only report. There are several tabs including a Summary of primary and secondary volume, Master Data by Supervisor, Detail by Supervisor and Expense Type, Secondary Volume by Master Project Number, and On and Off Campus volume of Supervisor and Expense Type. NOTE: This report will ask you to select your department three times. If you report on only one department, the limit can be changed from variable to fixed after your initial selection. Also, the report can be run for only one Department at a time (secondary volume for one department is primary for another).

Maija Ahlquist

11/19/2001

Primary & Secondary Research Volume, FY only

[Download]

Modified 10/3/2012 to stabilize a calculated item in the Results section.This version of the "classic Brown Book" fiscal year volume report for Departments will bring back fiscal year amounts, including authorized totals and unexpended balances, for those cost collections which had fiscal year activity. If you wish to see unexpended balances for cost collectors which had no fiscal year activity, you should use the Primary & Secondary Research Volume, All report. There are several tabs including a Summary of primary and secondary volume, Master Data by Supervisor, Detail by Supervisor and Expense Type, Secondary Volume by Master Project Number, and On and Off Campus volume of Supervisor and Expense Type. NOTE: This report will ask you to select your department three times. If you report on only one department, the limit can be changed from variable to fixed after your initial selection. Also, the report can be run for only one Department at a time (secondary volume for one department is primary for another).

Maija Ahlquist

10/03/2012

Proposal to Award Statistics

[Download]

Modified 9/11/2009 to add a flag for ARRA support to the query. Provides statistics, by Profit Center, on the number of sponsored research proposals submitted and awarded. Several reports showing both summary and detail information by PI, Sponsor, or Type (Organized Research, Predoctoral Fellowship, Postdoctoral Fellowship, Instruction & Department Research, and Other).

Maija Ahlquist

09/11/2009

Research Expenditures by Sponsor

[Download]

Modified 9/8/2005 to update for a GL Account category name change.This is the classic Brown Book Section V report. This report can be run from fiscal year 1999 forward. It will not return accurate data for earlier fiscal years. There are two report tabs. One shows Master Data detail with FY amount, Authorized Total and Unexpended Balance for all cost collectors that had fiscal year activity. The other shows fiscal year activity by expense type (Salaries, EB, Operating, F&A), sorted by Sponsor, Agreement Type, and On/Off Campus.

Maija Ahlquist

09/08/2005

Research Expense by Sponsor & Agreement Type Curr

[Download]

This report is identical to the Research Expense by Sponsor & Agreement Type History but can be used for the current fiscal year. It shows a Department's fiscal year research expenditures by Sponsor category, broken down by type (Grant, Contract). There are four tabs: (1) Department Summary by DIRECT sponsor, (2) Research Volume by Supervisor and DIRECT Sponsor, (3) Department Summary by PRIMARY Sponsor, (4) Research volume by Supervisor and PRIMARY Sponsor. This is similar to the Brown Book Section VI and VII reports.

Maija Ahlquist

10/12/2006

Research Expense by Sponsor & Agreement Type Hist

[Download]

REVISED 10/12/2006 to include reports by Primary Sponsor as well as Direct Sponsor This report is identical to the Research Expense by Sponsor & Agreement Type Current but should be used for closed fiscal years. It shows a Department's fiscal year research expenditures by Sponsor category, broken down by type (Grant, Contract). There are four tabs: (1) Department Summary by DIRECT sponsor, (2) Research Volume by Supervisor and DIRECT Sponsor, (3) Department Summary by PRIMARY Sponsor, (4) Research volume by Supervisor and PRIMARY Sponsor. This is similar to the Brown Book Section VI and VII reports.

Maija Ahlquist

10/12/2006

Research F&A Changes in Master Data

[Download]

For a DLC or DLCs, shows changes in F&A-related Master Data for Projects (Costing Sheet Procedure, Lab Allocation, EB, F&A) from one calendar period to another. There are two report tabs, both sorted by DLC and Project WBS. One report shows all changes between the two calendar periods specified, the other shows Rate History. Please see "F&A Underrecovery on Research WBS Elements" at http://web.mit.edu/sapr3/docs/webdocs/finop/ur/foURprocess.html for further documentation on the Research F&A Underrecovery reports.

Maija Ahlquist

07/13/2004

Research F&A Underrecovery - Coeus IDC Info

[Download]

**NEW VERSION 9/5/2012 to adjust for fiscal year.** For a DLC, displays Coeus IDC data for all research WBS elements that may incur F&A U/R (excluding training grants and Federal "Fixed Rate" competitive segments). This report will assist administrators in determining the source of funding for F&A U/R. Please see "F&A Underrecovery on Research WBS Elements" at https://vpf.mit.edu/site/content/view/full/16194 for further documentation on the Research F&A Underrecovery reports.

Maija Ahlquist

09/05/2012

Research F&A Underrecovery Cost Center

[Download]

For a DLC's U/R Cost Center, the FYTD DTR entries are sorted and subtotaled by WBS. This report will assist administrators in determining the F&A U/R amount to be funded. Please see "F&A Underrecovery on Research WBS Elements" at https://vpf.mit.edu/site/content/view/full/16194 for further documentation on the Research F&A Underrecovery reports.

Maija Ahlquist

09/29/2000

Research F&A Underrecovery JV

[Download]

For a selected month, the DW JV template will produce a partially completed JV to be downloaded, reviewed for accuracy, completed, and uploaded to SAP by the DLC administrator. The JV template includes entries to clear the U/R cost center's current month postings to cost elements 600316 (Adj F&A On to Spon Base/Rate) and 600317 (Adj F&A Off to Spon Base/Rate). Please see "F&A Underrecovery on Research WBS Elements" at https://vpf.mit.edu/site/content/view/full/16194 for instructions on use of DW JV template.

Maija Ahlquist

08/08/2000

Research Volume FYTD Report

[Download]

For a profit center or a profit center range, this report shows FYTD volume, cumulative expenses, and unexpended balance for all WBS elements. Limiting criteria are Profit Center, type of research project (Research, fund-sponsored or other) and fiscal period. Includes three reports, sorted by supervisor; supervisor and sponsor; and sponsor and supervisor; and a summary pivot report.

12/02/1999

WBS Elements That Expire Within 120 Days

[Download]

Shows the available total, expenditures to date, total commitment amount, and available balance for WBS Elements that have an expiration date of less than 120 days from the present.

07/13/1999

WBS Elements That Expire Within 30 Days

[Download]

Shows the available total, expenditures to date, total commitment amount, and available balance for WBS Elements that have an expiration date of less than 30 days from the present.

03/23/2000

WBS Hierarchy

[Download]

Displays FYTD and Cumulative activity for a multi-level WBS Hierarchy project. There are several detail reports, which summarize activity by levels 2, 3, 4 and 5, and also a pivot report which outlines the WBS Hierarchy structure for that project.

09/23/1999