Reporting: Enhancements and Updates

Where are financial reporting enhancements announced?

In addition to posting information here, the latest financial reporting enhancements are posted on the EFS message screen, as are the other enhancements made in relation to the financial system.

Data Warehouse - DWEF database - Improvements

DWEF is the database that stores information from the financial system (EFS, PeopleSoft Financials). Enhancements are being made to this tool. Read more about the enhancements made to DWEF here.

Got an idea or suggestion?

Fill out this quick form! This form is for Financial reporting ideas, needs, or suggestions. The business process owners review items regularly.

Older enhancements and updates are available here.

Quick Links

Financial Reporting Home
About
Initiatives
News
Release Notes
Reporting Tools
Events
Job Aids and Training Materials


Have an idea or suggestion for financial reporting?

Tell us by filling out this form to provide your ideas, needs, or suggestions. Business process owners review submissions regularly. This link is only for feedback related to Financial Reporting within the University of Minnesota.

April 2014 Update

Enhancements to existing UM Report, Position Budget Status vs. Actuals

Two column labels on the report have been modified to better represent the data rendered in the associated columns on the report.

  • The first column label is now Employee ID (EID) or Journal (JE) or Position EmplID (PEID). Previously the column label was Employee ID and there are values rendered in the column that are not employee IDs. The column also includes journal IDs and “dummy” Position Budgeting EmplIDs.

  • The second column label is now Employee Name or Other Description. Previously the column label was Employee Name and there are values rendered in the column that are not the name of an employee. The column may also include descriptions entered on journals as well as position names (e.g., New Incumbent) set up in Position Budgeting.

The Group by functionality has been slightly modified, too. When the rendered report data is grouped by Employee, the Group by Employee group header and group total descriptions have an acronym included in the descriptors to identify the source.

  • EID indicates the group is for a valid Emplid.
  • JE indicates a journal processed in EFS. 
  • PEID indicates the group is for a Position Budgeting “dummy” position Emplid.

March 2014 Update

Enhancements to existing UM Report, Actual Account Summary by Accounting Period for Current Non-Sponsored Funds

The name of the report has been changed to the Actual Account Summary by Accounting Period for Current Funds and is located in both the Sponsored and Non-Sponsored UM Report categories.

A new prompt has been added to the report to allow for the inclusion of sponsored activity on the report.

  • Selecting the Both Sponsored and Non-Sponsored Data prompt includes both types of data on the report.
  • Selecting the Sponsored Data Only prompt renders only sponsored data on the report.
  • Selecting the Non-Sponsored Data Only prompt renders only non-sponsored data on the report.

Another group by level has been added on the report. The new Group report by level is Trans Type (transaction type). Applying the Trans Type group level on the report will separate the sponsored data from the non-sponsored data.

For the sponsored data, the same logic used for inclusion of sponsored data on the Sponsored Project Detail and Sponsored Award Overview reports (detail sections of the reports) is used to filter the sponsored data on this modified report.

February 2014 Update

Enhancement to existing UM Report, Budget Prep Status by ChartField String

A link to Include Excluded Budget Prep Rows has been added to the header of the report.

The default report no longer includes budget prep rows flagged for exclusion from the EFS automated system budget set up, performed as part of the budget prep process. The report header now identifies that the default report includes NO excluded rows.

Selecting the Include Excluded Budget Prep Rows report header link includes excluded rows on the report.

If excluded rows are included on the report, the link Exclude Excluded Budget Prep Rows (changed link name) will “re-exclude” the budget prep rows flagged for exclusion from the report.

A new column Excl has been added to the report to identify whether a row of data is included (Excl = N) or flagged for exclusion (Excl = Y) from the EFS automated system budget set up.

The Group report by: “Excluded” will group the report data by whether or not report rows are identified for inclusion or exclusion.

Enhancement to existing UM Report, Account Budget Status for Current Non-Sponsored Funds

The links for drill down reports have been modified.

The Account column values are now links that drill down to the Transaction Detail Report. Selected budget account links allow viewing of transactions that roll up to the budget account selected. Selected "actuals" account links allow viewing of transactions for that specific account.

The column C Actuals Resources/Expenses amounts associated to payroll-related accounts are links to the Payroll Detail report. Budget account amounts in column C render data for all associated salary and fringe actuals that roll up to the selected budget account. Actuals account amounts in column C render data for the selected actuals account and its associated salary or fringe actuals.

September 2013 Update

new UM Reports related to Asset Management

Four new reports have been created in UM Reports as a result of recent changes to the captial assets business processes. They can be found in UM Reports under the report category "Enterprise Financials" and Sub-Category "Asset Management".

A wealth of additional information about each of these new reports is available in the companion "HelpDocs" in UM Reports. Click on this icon in the upper right corner, once you have run a report:

UM Reports help icon

Asset Management Capitalization report

This report shows asset IDs that have been capitalized based on the 'In Service' fiscal year and accounting period of an asset.

Asset Management Depreciation Expense report

This report shows summarized asset depreciation expense.

Asset Management Depreciation Schedule report

This report shows summarized asset scheduled depreciation expense.

Asset Management Disposal report

The report renders asset IDs that have been disposed (retired) based on the 'Retirement Date' of an asset.

June 2013 Update

Update on the Financial Reporting improvement effort begun in December 2010

It has been some time since the community received an update about the Financial Reporting improvements being made at the University. Rest assured that making improvements to reporting at the University is not and probably never will be called ‘complete’. For the immediate future, reporting improvements goals are embedded in the work of the Enterprise Systems Upgrade Program (ESUP). ESUP has an entire work stream devoted to reporting. If you have ideas or needs for financial reporting moving forward, Sue Paulson, Sherrie Kutzler, and Julie Tonneson, the Business Process Owners want to hear from you. A link to a new and more efficient way to capture ideas or needs can be found on the Controller’s Office financial reporting page (http://finsys.umn.edu/reporting/). The ideas and needs collected are reviewed frequently by the BPOs and vetted through existing user groups: the Reporting Work Group (RWG) and the Reporting Advisory Committee (RAC). When ESUP is complete, there will be an ongoing process to ensure financial reporting needs are met based on user feedback, emerging needs, and emerging functionality of the tools.

Highlights

New reports added to UM Reports during fiscal year 2013:

  • Accounts Receivable Activity by Invoice
  • Sponsored Projects in Deficit
  • Accounts Receivable Status by Chart String
  • Position Budget Status vs. Actuals
  • Activity by Account
  • Chart String Exceptions
  • Encumbrance Summary
  • Encumbrance Detail
  • Transfer Detail

 Activities in progress:

  • Multi-year Nonsponsored Funds Fiscal Year report
  • Monthly Payroll by Period Summary report
  • Sponsored Actuals by Accounting Period and Fiscal Year report
  • Asset Management detail reports
  • And more!

UM Analytics Deployment

There are two views of information available in this tool: BI Publisher (fancy) reports and more simple charts/graphs displayed on a dashboard. Building BI Publisher reports consumes a great deal of resources and can be a complex process. Therefore, the Reporting Work Group evaluated all of the Financial Reports in UM Reports and has been designing the foundation of what will flow to UM Analytics, how it will be presented, and the order in which those transitions will take place.

Release 1 targets data analysts—“it’s just the data” that is being released.
Testing takes place during July2013 for this phase. Testers are data analyst participants from the Business Intelligence (BI) pilot, also known as the “formal” BI pilot group, and it will be their job to validate the accuracy of the data, evaluate the presentation of the data, and participate in performance testing.

Subsequent releases will target the release of standard reports and dashboards based largely on usage. Heavily used reports will be rebuilt in UM Analytics first.

Three types of dashboards are planned:

  • Oversight/metrics
  • PI-specific financial
  • Accountant/departmental administrator

The vision for the dashboards is to include simple charts/graphs that display important financial priorities for the specific target audience and provide links to the BI Publisher version of many of the UMReports. We’re collectively very good at building historical reports, and those currently available in UM Reports are of value and provide relevant information. Historical or “look back” reporting only meets specific needs. To meet “look ahead” or forecasting needs, the initiative will explore the ability to use UMAnalytics and forecasting functionality and how to incorporate into financial reports.

Thank you

A large number of people from across the University system have contributed ideas and time to the activities of the financial reporting improvement efforts thus far. These people serve on the Reporting Work Group or on the Reporting Advisory Committee, and they deserve special recognition for their contributions.

  • Abel Ponce de Leon, Associate Dean, College of Food, Agricultural and Natural Resource Sciences
  • Bill Tolman, Chair, Chemistry, College of Science & Engineering
  • Lisa Warren, Office of the Vice President for Research
  • Andrea Backes, College of Biological Sciences
  • Dan Dwyer, A L R T Administrative Center, Lab Medicine & Pathology
  • Evryl La Chapelle, Academic Affairs/Provost
  • Kathy Smith, Lab Medicine/Pathology
  • Liz Hinske, Center for Drug Design
  • Pandora Aaron, CLA Fiscal Administration
  • Terri Ritz, Genetics, Cell Biology, and Development
  • Mary Allen, UMD Office of Academic Affairs
  • Julie Tonneson, Budget & Finance
  • Sue Paulson, Sponsored Financial Reporting, Controller’s Office
  • Sherrie Kutzler, Accounting Services, Controller’s Office
  • Dan Hemauer, Module Support, Controller’s Office
  • Ruth Dodson, Office of Information Technology
  • Steve Gillard, Office of Planning and Analysis
  • Bill Kenney, Module Support, Controller’s Office
  • Scott Tisinger, CFANS

May 2013

NEW UM REPORT

Accounts Receivable Activity by Invoice

This report shows all activity for a given accounts receivable item or group of items. This report is a standalone report as well as a drill down report from the Accounts Receivable by Chart String report using the invoice value as the link. The report shows payment activity and maintenance activity, such as write-offs, matches, and any unposting. For each line of activity, the following data is rendered on the report: Activity (this will be the entry type description), Activity Date, and the amount of the activity. Where possible, the Deposit ID, Payment ID, Pay Amount, and Deposit Date are shown for payment activity. See the report’s Help Doc for more information.

EXISTING UM REPORT

Payroll Detail (not PI)

EmplID has been added to the Payroll Detail report. A link has been added in the header of the report to include Plant & Agency Funds data on the report. One may drill down to this report from the Account Budget Status for Current Non-Sponsored Funds report. If Plant & Agency Funds have been included or Group Report by levels have been applied to the Account Budget Status for Current Non-Sponsored Funds report, these values are passed as prompts to the Payroll Detail so the detail report includes the same data and is grouped at the same level.

EXISTING UM REPORT

Transaction Detail (not PI)

One may drill down to this report from the Account Budget Status for Current Non-Sponsored Funds report. If Group Report by levels have been applied to the Account Budget Status for Current Non-Sponsored Funds report, these values are passed as prompts to the Transaction Detail so the detail report data is grouped at the same level.

March 2013

NEW UM REPORT

Sponsored Projects in Deficit

This report provides summary level data for Sponsored Projects that are in deficit as of the report prompt date or system date. Overdraft calculations only take into consideration expenses; they do not include requisition pre-encumbrance or encumbrance amounts.

This new report is very similar to the Sponsored Awards in Deficit report, except the data is rendered at the project level rather than the award (contract) level.  There may be projects in deficit rendered on this report where the associated award (contract) is not in deficit.  This new report should not be used to identify sponsored awards in deficit.

Projects associated to proposals authorized for pre-award spending are rendered on this report if the project is in deficit.  They are identified by “PRE-AWARD” in the Rate/Amount Base column of the report.

You can enter or select an Accounting Date Through, enter or select an RRC, enter or select a ZDeptID, enter multiple or select a single DeptID , and enter PI (Principal Investigator) EMPLID or enter COI (Co-Investigator) EMPLID. The report will display the sponsored project information for the prompt criteria entered.

See the report’s Help Doc for more information.

February 2013

NEW UM REPORTS

Accounts Receivable Status by Chart String

This report shows invoices broken down at the chartstring level showing the following data elements: invoice number, invoice date, total amount of invoice, amount invoiced for the specific chartstring, and total collected for the specific chartstring. In addition, any outstanding amount will be placed in its proper aging bucket (Current, 31-60, 61-90, 91-120, 120+).

The reporting user will be asked to enter a ‘from date’ and ‘to date’ which will be used to filter out invoices that are returned.  The data may be rendered for Non-Sponsored data onlySponsored data only, or a combination of Both.  ChartField prompts (fund, program, project, etc.) and RRC, ZDeptID, and Deptid are available as methods for requesting data. See the report’s help document for more information.

Position Budget Status vs Actuals

This report provides a detail list of position budget vs. actual expenses at the employee and chartfield detail level. The financial detail includes budget for salary and fringe, actuals, encumbrances, the remaining budget amount, and percentage of budget left. It can be run by RRC, zDeptID, DeptID, a combination of Chartfield values, and by single Employee ID. It can be used to monitor position budgeting distribution data throughout a selected budget year.

EXISTING UM REPORTS

Sponsored Award Overview

This report has been modified to provide a drill down to the new Accounts Receivable Status by Chart String report.  A new hyperlink is now available in the upper right hand corner under the Open AR Amount ITD balance.  The drill down reports shows detail for the sponsored projects associated to the Award (Contract Number) from the header of the Sponsored Award Overview report.    

Note: The AR data on Sponsored Award Overview report is inception to date and is not filtered by the accounting date entered on the Sponsored Award Overview prompts. The data rendered on this drill down report is also inception-to-date through the report run date (invoice date from EFS "Go-Live" through current date). AR processed on legacy systems (CUFS, JDE) may not be included on this report. As a result, the Invoiced Amount ITD from the Sponsored Award Overview may not always tie to this report.

December 2012

New UM Reports

Activity by Account

This report provides summarized activity for selected accounts that has posted to the general ledger through a selected accounting period for a fiscal year.

The data may be rendered for non-sponsored data onlysponsored data only, or a combination of both

Chart field prompts (e.g., fund, program, project) as well as selecting an RRC, ZDeptID, and Deptid are available as methods for requesting data.

The account prompt is a required prompt and supports both budget and actual accounts. If a budget account is entered, the amount rendered in the Budget Account Amount column is the summarized amount for all actuals accounts that roll up to the budget level account. If actuals accounts are entered in the prompt, the Actuals Account Amount is the summarized amount for the actuals account(s).

See the report’s Help Doc for more information.

Chart String Exceptions

This report identifies posted general ledger and/or commitment control chart string activity where no budget is set up at the Fund, DeptID, Program, Project, ChartField1, ChartField2, ChartField3, and Budget Reference (CS = cost share) level for a selected fiscal year. The report is designed to help academic units and central in identifying fiscal activity that wasn't planned (budgeted). 

The default report returns data for non-sponsored fiscal activity that is validated as an exception (no budget exists) and no resource transfers have been processed for the extended chart string. These exceptions are considered OPEN exceptions.  The OPEN exceptions are the default for the rendered report.

If the Integrated Chart String Exceptions check box is selected in the report prompts, the report renders exceptions that were OPEN and a budget has been set up and/or resource transfers have been posted for the extended chart string after the initial exception activity was posted to the general ledger or commitment control. This report identifies chart strings that at one time were exceptions and have since been "integrated" as valid chart strings.

The Include Carry Forward as Exceptions prompt includes cash carry forward balances on the report where a budget (any account) does not exist in the following fiscal year for the extended chart string.

The Include Zero Balance Exceptions prompt includes exceptions on the report where the chart string balance is $0.00.

The Include Program UM003 (IMG F&A Revenue) Exceptions includes exceptions for ICR program UM003 on the report.

By default, Carry Forward as ExceptionsZero Balance Exceptions, and Program UM003 (IMG F&A Revenue) Exceptions are not included on the report.

The Balance column on the report links to the Transaction Detail (UM Report ID 526) which renders the transaction data for the exception chart string (OPEN or Integrated).

See the report’s Help Doc for more information.

Existing UM Reports

Submitted Proposal Processing Statistics

The Submitted Proposal Processing Statistics Report rendered proposal data validated by report prompts where the current status of a proposal is submitted.  The report has been modified to include any proposal that was submitted whether or not submitted is the current proposal status (for example, the current status could be declined, discontinued, etc.).  As a result, most reports will include more submitted proposals and the awarded proposal percentage compared to total submitted proposals will be smaller.

November 2012

Modifications to existing UM Reports

Sponsored Award Summary
Sponsored Award Overview
Sponsored Project Summary
Sponsored Project Detail

A new prompt (check box) has been added on the UM Reports that render sponsored award, pre-award proposal and project data.  The new prompt allows users to "Exclude Encumbered Facilities and Admin (F&A)" from the reports.

 The default is that encumbered F&A is included on the reports.  Selecting the prompt excludes the encumbered F&A calculation from the report.  Inclusion or exclusion of encumbered F&A is identified in the report header and also by a foot note on the report.

The inclusion or exclusion of encumbered F&A is passed to drill through reports within this suite of UM Reports.  For example, if encumbered F&A is included (or excluded) on the Sponsored Award Summary, the encumbered F&A is also included (or excluded) when drilling through to the Sponsored Award Overview.

Inclusion/exclusion of encumbered F&A is not passed as a filter to the Payroll Detail report because the calculated value is not relevant to payroll data, and is not included on the Transaction Detail report because the amount is a calculated value generated at report run time and is not an actual transaction.  Links to the Payroll Detail and/or the Transaction Detail from the Sponsored Award Overview and Sponsored Project Detail reports are not affected by the addition of the new prompt.

Sponsored Award Overview

Sponsored Award Overview - The prompt order for the report has been changed. The date and encumbered F&A
exclusion prompt is now the first prompt. This change was made so the report may be run multiple times without being re-prompted for the date and encumbered F&A exclusion on each report run.

Transfer Detail

Transfer Detail - Operator ID has been added to the Group Report by: prompt lists so the report data may be grouped at that level.

October 2012

DWEF (data warehouse) - new tables

The following tables have been added to DWEF.

FS_PS_PO_CHNG_SHIP – Purchase Order Shipping Change
FS_PS_GM_PROP_VNDR – Grants Proposal Vendors List
FS_PS_GM_PROP_VNDR_DE – Grants Proposal Vendor Deliver
FS_PS_GM_PROP_VNDR_TE – Grants Proposal Vendor Terms
FS_PS_GM_VNDR_BUD – Grants Proposal Vendor Budget Item
FS_PS_GM_AWD_PRJ_VNDR – Award Subrecipients
FS_PS_FM_AWD_VNDR_BUD – Award Subrecipients Budget
FS_PS_TEAM_MEMBERS – Sales Team Members
FS_PS_MEMBER_PERSON – Support Team Member Table

September 2012

Three new UM Reports are included in the September release.

  • Encumbrance Summary - This report provides encumbrance balances for the specific set of chart field values entered on the prompts, or for all chart field values (chart strings) for the RRC, ZDeptID(s) and DeptID(s) selected. The encumbrance data is reported at the full chart string, transaction, transaction line and distribution line level through the accounting date entered or fiscal year/accounting period selected on the report prompts. Only encumbrance distribution lines with a balance other than $0.00 are rendered on the report. This report is available in the UM Report Category – Enterprise Financials, SubCategory(ies) – Non-Sponsored and Sponsored.

  • Encumbrance Detail - This report provides encumbrance detail for specific encumbrance transaction values entered on the prompts. The encumbrance data is reported at the full chart string, transaction, transaction line, distribution line and sequence number level through the accounting date entered or fiscal year/accounting period selected on the report prompts. This report renders data for all distribution lines associated to the transaction regardless of the distribution line balance. This report is available in the UM Report Category – Enterprise Financials, SubCategory(ies) – Non-Sponsored and Sponsored.

  • Transfer Detail - This report provides transaction detail for Transfer Journal Entries for a selected accounting period/fiscal year range or accounting date from/to range. Users may select a specific chartstring and Account Group (corresponding Transfer IN and Transfer OUT accounts). The report renders transfers in and transfers out in separate columns with totals and a net balance. This report is available in the UM Report Category - Enterprise Financials, SubCategory(ies) - Non-Sponsored Reports & Sponsored Reports.
Existing UM Reports (modifications)

PI Reports

Sponsored/Non Sponsored Summary – Overall 
Sponsored Project Summary by Budget Period 
Sponsored Project Activity by Budget Period 
Sponsored Project Activity by Project Period

A new prompt (check box) has been added on the PI Reports that render sponsored project data. The new prompt allows users to "Exclude Encumbered Facilities and Admin (F&A)" from the reports.

The default is that encumbered F&A is included on the reports. Selecting the prompt excludes the encumbered F&A calculation from the report. If selected, encumbered F&A is also not included on any of the drill downs available from the source report. Inclusion or exclusion of encumbered F&A is identified in the report header and also by a foot note on the report.

This prompt is not available on the Payroll Detail PI Report because the calculated value is not relevant for payroll data, and is not included on the Transaction Detail PI Report because the amount is a calculated value generated at report run time and is not an actual transaction.

This new functionality to "Exclude Encumbered Facilities and Admin (F&A)" is currently scheduled over the next two months for the following Sponsored reports:

Sponsored Award Summary
Sponsored Award Overview
Sponsored Project Summary
Sponsored Project Detail
Sponsored Fixed Price Balances

UM PUF Endowment Report

The PUF endowment report provides both University and Foundation market values, available balances and quarterly income distribution for PUF endowments based on information from the University of Minnesota Foundation and the Minnesota Medical Foundation. The report has been modified to include an endowment description.

UM Reports modifications (August 2012)

Modification to ‘Submitted Proposal Processing Statistics Report’

Report Prompt Changes:

  • Change the “Enter a Sponsor ID” prompt to a “Select a Sponsor ID below:” and correlate prompt list on the “Enter a value” prompt path.
  • Remove the “Enter a PI/COI/CPI radio button and prompt from the “Select a value” prompt path.

Report Header Changes:

  • Center the values returned in the “Award Processed ****” column.

Modification to ‘Sponsored Award Overview’

Report Output / Download Changes:

  • Apply the XLS download “preferred” resolution, all header and footnotes are rendered in separate cells in the download file.

Modification to ‘Payroll Detail’

Report Header Changes:

  • Add F&A % and F&A Distribution information to the header of the report if run for sponsored projects.
  • (BLANK) is being returned in the header of the Payroll Detail report as if it is a valid chart value.  It is a stored value on the PS_UM_CF_GROUP_FLD table, but is used only to indicate the chartfield should be filtered with '   ' when running reports.  The value should be replaced with '  ' in the header.

Report Output / Download Changes:

  • Apply the XLS download “preferred” resolution, all header and footnotes are rendered in separate cells in the download file.

Modification to ‘Transaction Detail’

Report Prompt Changes:

  • (BLANK) is being returned in the header of the Transaction Detail report as if it is a valid chart value.  It is a stored value on the PS_UM_CF_GROUP_FLD table, but is used only to indicate the chartfield should be filtered with '   ' when running reports.  The value should be replaced with '  ' in the header.

Report Output / Download Changes:

  • Apply the XLS download “preferred” resolution, all header and footnotes are rendered in separate cells in the download file.

UM Reports modifications (June 2012)

New ‘Payroll Detail’ report

This report shows payroll detail for sponsored and non-sponsored activity. This new report mimics the Payroll Detail report that currently exists in the PI Suite of reports in UM Reports. The main difference is that this report is not driven by Internet ID (X.500). Instead one may enter dates and distinct chartfields as parameters. This report:

  • Tracks salary and fringe spending as well as current encumbrance values for a specific set of chartstrings. Information is shown at the employee level of detail.
  • Can be used to calculate burn rates, prepare forecasts, and to get a point in time fiscal status(es) for both non-sponsored and sponsored payroll activity.
  • In addition to this being a stand-alone report, one can link to this report from salary and fringe budget level rows on both the Account Budget Status for Current Non-Sponsored Funds and Budget Account versus Actual Activity – Current Non-Sponsored Funds reports.

New ‘DeptID Approvers’ report

This report identifies approvers by DeptID. The report can be run at multiple levels (RRC, ZDeptID and DeptID), by transaction type (general ledger, expenses, requisitions, vouchers) and approver type (primary, alternate, or certified approver). The report is not tied to specific transactions but does provide a list of the current approvers associated with the DeptID and DeptID attribute.

Modifications to ‘Sponsored Flexible Period Expense Summary’ report

Report prompt changes made:

  • Added “Select From Month/Year” prompt option.
  • Added “Select Through Month/Year” prompt option.
  • Added “Select Internet ID” prompt path.

Report header changes made: Added mouse-over text for all report detail columns.

Modifications to ‘Sponsored Fixed Price Balances’ report

Report prompt changes made: Added “Select Through Month/Year” prompt option.Add “Select a Project ID” prompt option for item 1 (modified PI Report prompt)

Report header changes made:

  • Added “smart headers” for report detail columns.
  • Combined pre-award and awarded project into one report.

Report body changes made:

  • Calculate encumbered F&A at report run time (inception to date through run date).
  • Added mouse-over text for all report detail columns.

Modifications to ‘Sponsored Awards in Deficit’ report

Report prompt changes made: Added “Select Through Month/Year” prompt option for all prompt paths.

Report header changes made:

  • Added mouse-over text for all report detail columns.
  • Added re-sort function to Overdraft *** Amount as of MM/DD/YYYY column.

UM Reports modifications (May 2012)

'Budget Account versus Actual Activity - Current Non-Sponsored Funds' modifications

Report Prompt Change: ChartField prompts were re-ordered to be consistent with entry.

Report Header Changes:

  • The report header will show the Program prompt as “(blank)” when a Program was not specified in the selection criteria.
  • The report header will now display multiple functions, if multiple functions were specified in the selection criteria.
  • Can now sort by the Account and Account Description columns.
  • Added report header “smart columns” that display how amounts are calculated (for example, A, B, C, D=B+C, E=A-D).
  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.

Report Body Changes:

  • Expenditures and Encumbrances are displayed as positive numbers instead of negative numbers.
  • The "Actuals" columns are highlighted.
  • A clickable report detail link is now available on the Account value. The link takes one to the latest version of the Transaction Detail report.
  • Added a report detail link in the Expenditures column that takes one to the Payroll Detail report.

'Sponsored Project Summary' modifications

Report Prompt Change: Added a new prompt ‘Select Through Period’.

Report Header Changes:

  • Added report header “smart columns” that display how amounts are calculated (for example, A, B, C, D=B+C, E=A-D).
  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.
  • Include projects associated to pre-award proposals on the report.    

Report Body Changes:

  • Each time the report is run it will calculate encumbered F&A (inception to date through run date).

'Budget Prep Status by Chartfield String' modifications

Report Prompt Change:

  • Elimination of Fiscal Year and Accounting Period prompt in favor of single ‘Budget Year’ prompt.

Report Header Changes:

  • Fixed issues with downloading to Excel and CSV file formats (WHAT ISSUES?!)
  • Added report header column sorting ability for columns: DeptID, Account, Fund, Program, Project ID, Chartfield 1, Chartfield 2, Fin EmplID.
  • Changed report heading order to match prompt page field order.
  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.

Report Body Change: Highlighted the "Budgeted Amount" column.

'Sponsored Award Summary' modifications

Report Prompt Changes:

  • Added “Select Through Period” prompt option.
  •  Added a new prompt for Contract Status.

Report Header Changes:

  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.
  •  Merged the ‘Sponsored Aged Pre-Award Summary’ and the ‘Sponsored Award Summary’ into one report.    

Report Body Changes:

  • Added a column in the report detail for Contract Status and Pre-Award Days Aged as of MM/DD/YYYY.
  •  Each time the report is run it will calculate encumbered F&A (inception to date through run date).
  • Changed report calculation from percent spent (% SPENT) to percent remaining (% LEFT), including a column header change in the report detail.

'Sponsored Project Detail' modifications

Report Prompt Changes:

  • Added “Select an Internet ID” prompt path.
  • Added “Select a Project ID” prompt option for item 1 (modified PI Report prompt).
  • Added “Select Through Month/Year” prompt option for all prompt paths.

Report Header Changes:

  • Added re-sorting on all report columns.
  •  Added report header “smart columns” that display how amounts are calculated (for example, A, B, C, D=B+C, E=A-D).
  • Changed header so that it links to the new transaction detail report prompt page.
  • Combined pre-award and awarded project into one report.

Report Body Changes:

  • Added links to the Payroll Detail report – two links (PI Report).
  •  Add links to the Transaction Detail report – three links.
  • Each time the report is run it will calculate encumbered F&A (inception to date through run date).
  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.

'Sponsored Award Overview' modifications

Report Prompt Changes:

  • Added “Select an Internet ID” prompt path.
  • Added “Select an Award” prompt option for item 1 (modified PI Report prompt).
  • Added “Select Through Month/Year” prompt option for all prompt paths.

Report Header Changes:

  • Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.
  •  Incorporated ‘Sponsored Pre-Award Overview’ report with the ‘Sponsored Award Overview’ report.

Report Body Changes:

  • Added links to the Payroll Detail report – two links (PI Report).
  • Added links to the Transaction Detail report – four links (PI Report).
  • Each time the report is run it will calculate encumbered F&A (inception to date through run date).
  • Changed report calculation from percent spent (% SPENT) to percent remaining (% LEFT), including a column header change in the report detail.

'Salary Cost Transfers' modifications

Report Prompt Change: When running the report for date prompts only, the edit that validates the elapsed time between from and through journal dates has been corrected.

Report Header Change: Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.

'Non-Salary Cost Transfers' modifications

Report Prompt Change: When running the report for date prompts only, the edit that validates the elapsed time between from and through journal dates has been corrected.

Report Header Changes: Added mouse over text to header columns. Hover the mouse over the heading and descriptive text will appear.

New transaction detail report in UM Reports (April 2012)

There is a new transaction detail report in UM Reports that combines the three old detail reports: Sponsored Transaction Detail – Expense report; Pre-Award Transaction Detail report; Transaction Detail for Current Nonsponsored Funds.

If you attempt to access one of the three old detail reports, via a customized “home” page, a bookmark, or the UM Reports menus, you will see a message at the top of the parameter screen, “Prompt Selection for UNSUPPORTED/INACTIVE - Transaction Detail for Current Non-Sponsored Funds Report”. The new detail report can be found as shown in the picture below.

The report title appears under two subcategories: sponsored and non-sponsored. It’s the same report, just two paths to get to it. Pick one, and you’re off and reporting!

UM Reports search results showing Transaction Detail reports

Don’t forget to remove any old bookmarks/favorites to the reports that were replaced. If you want to add this new report to your UM Reports “home page”, click the check box to the left (the column with the little check boxes shown above), and then click the hyperlink “Include” at the bottom of the report listing.

Financial reporting update April 2012

The Reporting Work Group has been very busy the last few months. Based on the enthusiastic response to the PI reports released this past fall, we’ve applied much of the same functionality to the existing UM Reports (excluding the ChartField string mapping requirements) and made other enhancements. The improvements will be visible in certain reports starting in April with the remainder of the reports impacted in May and June.

  • Expenses and encumbrances are now shown as positive numbers on every report.
  • Any summary report that can be linked to a detailed report is now linked, so people can click through to detail from the summary – including links to the payroll detail report.
  • All reports will now have the “mouse over text” feature, so that when the mouse is hovered over a column header, a pop-up description appears.
  • Column headers now display the calculations for what is displayed.
  • The transaction detail report has been redesigned to incorporate the improvements in format and information implemented in the PI version of this report.
  • For a select number of sponsored reports, the functionality that allows one to link an Internet ID to a set of ChartField strings is available (see details below).
  • All of the sponsored reports now have the ability to use the “through month/year” prompts.
  • Encumbered F&A is now out there on all of the sponsored reports.
  • At the request of those working with sponsored projects, pre-award information is now incorporated into all of the sponsored reports and the separate reports for pre-award activity have been retired.
  • Got Plant & Agency Funds? The Account Budget Status for Current Non-Sponsored Funds can now be run just for that activity.

Where applicable:

  • The actuals column is highlighted for more visibility—the actuals amounts stand out from the rest of the information.
  • The ChartField order in report headers has been fixed to match the logical order of fields most commonly used: Fund, DeptID, Program/Project, CF1, CF2, FinEmplID.
  • The ChartField order was also fixed on certain report prompts so that they’re in the same order: Fund, DeptID, Program/Project, CF1, CF2, FinEmplID.
  • Column headers have been revised and improved to add clarity.
  • The ability to sort the report output by each of the relevant column headers has been added.

Performance improvements

Most of the people using UM Reports since go-live of EFS have commented about slow performance of the reports. A lot of work has been done “under the hood” of UM Reports to improve performance of the reports. Representative improvements, based on most commonly used run prompts and parameters:

Average UM Report run times, showing improvements

Run times (averages) ==>

Report

As initially rolled out After fall of 2011 improvement effort After Exadata upgrade March 2012
Account Budget Status for Current Non-Sponsored Funds 2 to 3 minutes 18 to 39 seconds 10 to 20 seconds
ChartField String Budget Status for Current Non Sponsored Funds 2 to 3 minutes 30 to 60 seconds 8 to 25 seconds
Sponsored Award Overview 2 to 3 minutes 50 to 60 seconds 10 to 20 seconds
Sponsored Project Summary 20 to 30 minutes 2 to 3 minutes 20 seconds or less
Sponsored Fixed Price Balances 16 to 23 minutes 7 to 10 minutes 5 seconds or less

Please note that running a report for one ChartField string will take significantly less time than running it for an entire RRC or campus.

Enhancements on specific reports

The following enhancements will be rolled out over the course of the next three months (April, May, and June 2012). Some of these items may not be available until the June 2012 release.

Sponsored Award Summary

  • Added "Select through Month/Year" prompt option. Last day of month selected is fed as date prompt.
  • Added a new prompt for Contract Status.
  • A new column for Contract Status has been added to the report.
  • Encumbered F&A is now being calculated for the report.
  • Mouse over text has been added providing definitions for all report column headers.
  • Pre-Award Proposal Spending is now incorporated into the Sponsored Award Summary. The Sponsored Aged Pre-Award Summary report is going away. Pre-award proposals are identified by a contract status of PRE-AWARD.
  • The column for pre-award proposal aging which existed on the Sponsored Aged Pre-Award Summary report has been added.

Sponsored Award Overview

  • Added the optional "Select an Internet ID" prompt – X.500 mapping in use for PI Reports is being used to map to Sponsored Awards for this report. User will be able to select an award associated to X.500 values already mapped to projects.
  • Added "Select through Month/Year" prompt option. Last day of month selected is fed as date prompt.
  • Links to the PI Report Payroll Detail report have been added.
  • Links to the PI Report Transaction Detail report have been added from the Sponsored Award Overview.
  • Encumbered F&A is now being calculated for the report.
  • Mouse over text has been added providing definitions for all report column headers.
  • Proposals set up for pre-award spending are now incorporated into the Sponsored Award Overview report. The report header identifies if the report data is for a pre-award proposal, or a sponsored award. The Sponsored Pre-Award Overview is going away.

Sponsored Project Detail

  • Added "Select an Internet ID" prompt - X.500 mapping in use for PI Reports is being used to map to Sponsored Projects for this report.
  • Added "Select through Month/Year" prompt option. Last day of month selected is fed as date prompt.
  • Links to the PI Report Payroll Detail report have been added.
  • Links to the "new" Transaction Detail report will be added. (Why isn't this linked to the "PI Transaction Detail" report? It didn't allow for cost share information. The new version of the Transaction Detail report that will have cost share included.)
  • Encumbered F&A is now being calculated for the report.
  • Mouse over text has been added providing definitions for all report column headers.
  • Ability to re-sort data is added on all report columns.
  • Projects associated to pre-award proposals are now rendered on this report. Pre-Award status is identified in the report header. The Sponsored Pre-Award Project Detail report is going away.

Sponsored Project Summary

  • Added "Select through Month/Year" prompt option. Last day of month selected is fed as date prompt.
  • Encumbered F&A is now being calculated for the report.
  • Mouse over text has been added providing definitions for all report column headers.
  • Projects associated to pre-award proposals are now rendered on this report. They are identified by a Rate/Amount Base of PRE-AWARD.

Account Budget Status for Current Non-Sponsored Funds

  • Added mouse over text.
  • Added report header smart columns (A, B, C, D=B+C, E=A-D).
  • Changed report heading to be like PI Reports.
  • Changed Multiple ChartField string Values input order.
  • Added report detail link for column “Account” to report Transaction Detail Replacement.
  • Added report detail link for column “Actuals Resources/Expenses” to report Transaction Detail Replacement.
  • Added report detail link for column “Budget Account” to report Transaction Detail Replacement.
  • Added requirement for displaying expenses and encumbrances as positive instead of negative.
  • Highlighted the "actuals" columns.
  • Reported should allow user to run report for Plant and Agency Funds.
  • Report Header corrected to show prompt for Program (blank).
  • Report Header corrected to show function prompt.

ChartField String Budget Status for Current Non-Sponsored Funds

  • Added mouse over text.
  • Added report header smart columns (A, B, C, D, E, F=A+B-C+D+E, G, H=F-G).
  • Changed report heading to be like PI Reports.
  • Changed Multiple Chart String Values input order and rename CSProject to Project.
  • Added requirement for displaying expenses and encumbrances as positive instead of negative.
  • Highlighted the "actuals" columns.
  • Report Header corrected to show prompt for Program (blank).
  • Report Header corrected to show function prompt.
  • Changed header ChartField order to be Fund Code, DeptID, Program Code, Project, CF1, CF2, FinEmplid, CS.

Actual Account Summary by Accounting Period for Current Non-Sponsored Funds

  • Added mouse over text.
  • Changed report detail link for column “Account” to report Transaction Detail Replacement.
  • Changed Multiple Chart String Values input order and CS Project to Project.
  • Report Header corrected to show prompt for Program (blank).
  • Report Header corrected to show function prompt.
  • Display “Expenditures” as positive instead of negative.

Financial reporting updates 12/20/2011 (updated 3/26/2012)

There are three very important reporting-related items included in this message:

  • Reminder about UM Reports enhancements and possible data differences
  • Sunsetting of the financial.reports.umn.edu website (old Financial Reports on the Web)
  • Sunsetting of financial reports in PeopleSoft

Reminder item about UM Reports enhancements in progress

As has been previously communicated, performance enhancements are being made to the financial UM Reports.

Please note that as sponsored financial report enhancements are rolled out within the next couple of months, you may note some data differences between the reports, particularly when accessing reports via links between reports. This is not a data issue. This is due to a timing issue between the old sponsored reports using accounting dates from PS_PROJ_RESOURCE and the enhanced reports using the ledger dates from the new reporting tables (the PS_PROJ_RESOURCE table is loaded the day after activity hits the general ledger).

When all of the performance enhancements have been made, this data difference will be eliminated.

Financial Reports on the Web (legacy website) updated March 26, 2012

One of the final pieces of the legacy financial system to be decommissioned is the old financial reporting web site, http://financial.reports.umn.edu. This site allowed departments to run reports on legacy CUFS financial data. Many of these reports have not been run in years, while others are still run on occasion (primarily for troubleshooting issues related to activity that took place in FY2008 or earlier).

To find information on how to recreate the most frequently used legacy queries/reports: http://www.finsys.umn.edu/legacy.html#finreportsite

The DWFS database of legacy CUFS financial data in the data warehouse will continue to be available to departments and colleges.

For help with legacy financial system information, reporting, or queries, or help getting access to the legacy system financial data stored in DWFS, please contact the University Financial Helpline.

The site will be shut down April 30, 2012.

Sponsored Reporting Update – reports from PeopleSoft Financials (EFS) reporting (FSUMRPT) and production (FSUMPRD) instances

There are currently two main tools for people to use to generate financial reports for sponsored activity:  UM Reports and reports in EFS itself (both the production and reporting instances). The UM Reports versions of sponsored reports are run far more frequently than the corresponding versions of the reports in EFS production or reporting instances. Vice Presidents Mulcahy and Pfutzenreuter asked the reporting team to analyze both suites of sponsored reports to determine where the University’s limited reporting support and development resources should be focused.

Based on use statistics and feedback from faculty and sponsored research support and administrative staff, the recommendation has been made to continue to maintain and improve the UM Reports tool until such time as the UM Analytics (business intelligence) tool is ready to meet most or all of the University’s required financial reporting needs.

Therefore, as of January 29, 2012 the reports listed below in EFS production and reporting instances will no longer be available in EFS, use UM Reports instead. The January 29 date is dependent on a modification being moved to UM Reports to improve performance for certain activities. If that modification is delayed, the January 29 date may be pushed out a short time.

Report that will be deactivated and navigation Use this report on UM Reports instead
Sponsored Awards in Deficit Grants > Interactive Reports > UM Sponsored Awards In Deficit Sponsored Awards in Deficit
Sponsored Flexible Period Expense Summary Grants > Interactive Reports > UM Flexible Period Expense Summary Sponsored Flexible Period Expense Summary
Sponsored Pre-Award Project Detail Grants > Interactive Reports > UM Spon PreAward Project Dtl Sponsored Pre-Award Project Detail
Submitted Proposal Processing Statistics Grants > Interactive Reports > UM Spon Prop Proc Report Submitted Proposal Processing Statistics
Sponsored Pre-Award Overview Grants > Interactive Reports > UM SponPreAward Overview Sponsored Pre-Award Overview
UM Spon Pre-Award Summary Grants > Interactive Reports > UM Sponsored PreAward Summary Sponsored Aged Pre-Award Summary
UM Award Processing Statistics Grants > Interactive Reports > UM Award Processing Statistics Award Processing Statistics
F&A Recovery Report Grants > Interactive Reports > F&A Recovery report F&A Recovery
Sponsored Project Summary Grants > Interactive Reports > UM Sponsored Project Summary Sponsored Project Summary
Sponsored Award Summary Grants > Interactive Reports > UM Sponsored Award Summary Sponsored Award Summary
Sponsored Project Detail Grants > Interactive Reports > UM Spon Project Detail Sponsored Project Detail
Sponsored Award Overview Grants > Interactive Reports > UM Spon Award Overview Sponsored Award Overview
Sponsored Fixed Price Balances Grants > Interactive Reports > UM Spon Fixed Price Balances Sponsored Fixed Price Balances
Sponsored Pre-Award Transaction Detail – Expense Grants > Interactive Reports > UM Spon PreAward Trans Dtl Exp Transaction Detail (new report combining sponsored/non-sponsored/pre-award transactions)

The above message was sent by the Financial Reporting Work Group and the Controller's Office of the University of Minnesota to the GMUN post award group, certified approvers, RRC managers, EFS cluster directors, IDEA-L, various Controller's Office listservs, and posted on the EFS message screen.

September 2011, New PI Reports Available

New Reports Available! PI Reporting Improvements: We are excited to announce new financial reports for faculty and principal investigators!

Quick start

Step 1: Configure a list of ChartField strings for which you (or your faculty) want to generate reports.
  • Each faculty member that is assigned the role of PI on a project will have these projects in a pre-configured list and may begin using the reports immediately.
  • Any desired non-sponsored strings or projects where the individual is the Co-I or CPI will need be defined to allow the faculty member to report on those projects or programs.
  • Need PeopleSoft Financials (EFS) production access in order to add or adjust the ChartField string mapping. A link to instructions on how to set up the maps can be found here. (pdf) Updates made to the mapping will be available for use the following day.

Step 2: Log in to UM Reports.

  • Click <Search Reports>.
  • Filter for the category "Principal Investigator".
  • A list of the reports is included below.

Step 3: Begin using the reports.

  • A job aid on how to access and generate the PI Reports is available here. (pdf)

Step 4: If questions arise

If questions arise on what you see on the reports, check the "Known Issues - Frequently Asked Questions" page: http://finsys.umn.edu/reporting/msg/. If questions remain, feel free to submit them to controller@umn.edu.

Faculty: Based on feedback and requirements provided by faculty, PIs, and staff, new reports have been created specifically for the use of faculty, particularly of use to principal investigators. These reports make use of a new feature your department maintains that links one person’s ID or a group of people to a list of ChartField strings, so you don’t have to run the same report for each ChartField string.

Primary PIs will have their list of sponsored projects pre-built, so they can begin using the new reports immediately to review sponsored activity. Detail reports can be accessed through the drill-down or directly from the menu.

A job aid on how to access and generate the PI Reports is available here. (pdf)

A dedicated web page of frequently asked questions or known reports defects on PI Reports can be found here.

Visit UM Reports at: http://www.umreports.umn.edu/

Department staff: Instructions on how to set up the ChartField string associations that will enable this new reporting feature are available here. (pdf) Once you have created a ChartField string reference list for your co-PIs, faculty members, or others, alert them that they may now visit UM Reports to start using the new reports.

While the new reference feature that links individuals or groups to lists of ChartField strings for reporting was initially designed for faculty and principal investigators, it can be used by anyone with access to UM Reports. Primary PIs will have their list of sponsored projects pre-built, so they can begin using the new reports immediately to review sponsored activity.

The Reports

Seven financial reports have been designed to meet the specific needs of Principal Investigators (PIs) but anyone will find them useful for calculating burn rates, preparing forecasts, and getting point-in-time financial information.

Sponsored/Non Sponsored Summary - Overall
This report will return data for sponsored projects, non-sponsored projects, and non-sponsored ChartField string activity. It provides summary data for budget, resources, encumbrances, expenses and balance remaining for each project or ChartField string. From here, drill to Sponsored Project Summary by Budget Period, Sponsored Project Activity by Budget Period, Sponsored Project Activity by Project Period, or Non Sponsored Activity by Fiscal Year. Further drill to Payroll Detail information and Transaction Detail information is available.

Non Sponsored Activity by Fiscal Year
This report will return data for non-sponsored ChartField string fiscal activity. It summarizes month and fiscal year to date data (budget, resource, encumbrance, and expense) through a specified calendar month and year. The report includes subtotals for payroll/fringe and non-payroll totals. Data is summarized at budget account level.

Sponsored Project Summary by Budget Period
This report will return data for sponsored project budget period fiscal activity. It provides a list of sponsored projects with summary data for budget, encumbrance, expenses and balance remaining by Budget Period. Expense and encumbrance data are reflected based on dates of the transactions. From here, drill to Sponsored Project Activity by Budget Period or Sponsored Project Activity by Project Period. Further drill to Payroll Budget Period summary reports and Budget Period Transaction Detail Information is available.

Sponsored Project Activity by Budget Period
This report will return data for sponsored project “budget period” fiscal activity. It summarizes budget period data (budget, encumbrance, and expense) at the budget account level. The report includes subtotals for direct and indirect costs.

Sponsored Project Activity by Project Period
This report will return data for sponsored project “project period” fiscal activity. It summarizes current month and inception to date data (budget, encumbrance and expense) at the budget account level through a specified calendar month and year. The report includes subtotals for direct and indirect costs.

Resources

Reporting tools.


Return to the top.