Business Objects Repository Reports - Financial data

· about queries

The Business Objects repository contains reports which users can use to retrieve data to which they have access. The reports available vary by data collection and business use, as listed below.

• General Use
• Grants Management
• Asset Management
• Express Mail
• FRES Work Requests
• PennWorks
• Tuition Distrtibution

To import reports for your own use:

Using InfoView:

The InfoView web page allows you to view the list of corporate documents in the Business Objects repository and refresh and download those documents via the web. To access the corporate documents using InfoView (Business Objects users only):

1. Point your browser to
   https://bobjprod.isc-seo.upenn.edu/InfoViewApp/logon.jsp
2. Enter your Business Objects ID and password in the User Name and Password fields that appear. Click OK to continue to the InfoView Welcome page.
3. The InfoView web portal allows you to view the list of reports in the Business Objects Public Folders, refresh those documents via the web, and if desired, save them to your desktop as Excel or pdf files. The Public Folders are listed in InfoView when you navigate to "Document List." You will only be able to view or use reports in the public folders that you have access to. If you have Web Intelligence (Webi) access, you can also edit these documents, modifying them to fit your needs. Modified documents should be renamed and saved to your own "My Favorites" folder on the portal..

CForm Transactions Detail presents information from the General Ledger Journal detail data available in the FINQUERY universe, and can be used to monitor C-Form payment journals (petty cash transactions and payments for items purchased by purchase order are excluded). Access to the General Ledger data in the Warehouse is required to run this report.

FINQRCM - Budget and Actuals displays a crosstab of year to date budget balances and actuals by month for RCM Categories for a given Center and Fund. It makes use of a union, which allows users to retrieve year to date budget balances in one column, along with actuals by month as the columns in the crosstab. It uses the RCM OBJECT TREE table in the Data Warehouse to mimic object groupings used in BEN Reports and BEN Financials reports.

FINQRCM - Previous comparison uses a simple crosstab, along with the Business Objects Previous function and categories from RCM_OBJECT_TREE to present a crosstab of RCM categories compared to previous periods. The three tabs of the report show the progression from raw data, to a simple crosstab (columns sorted by fiscal year and fiscal month, rows by RCM summary and category sequences), to the final crosstab including a column with variable comparing the data to the previous period. Note that the Previous function allows for comparison to the previous row (in a standard table format), or the previous column (in a crosstab format). The comparison could be between anything that occurs in consecutive rows or columns - this query just uses it to compare accounting periods year to year.

FINQUERY - Encumbrances by Org shows encumbrances by org, fund, object and PO number, for a given fiscal year and org(s).

FINQUERY - Year over year comparison is actually comprised of two separate queries (Current Period and Comparison Period), linked by the Balances COA_Account segment to allow comparison of balances between two periods. The queries are each represented separately on the appropriately named tabs, and, once linked, the combined results are presented on the Combined tab. Data in this report can be verified using BEN Financials online account inquiry, or comparing to BEN Financials 10X or 15X reports.

FINQUERY Template Universe - RCM 210 intended to allow users to mimic the BEN Financials RCM 210 report using the Data Warehouse BALANCES and GL_REPORT_TEMPLATE tables. Two queries (Financial Data and RCM Labels) are linked together (on the Sequence column), to join balances with the full skeleton of the RCM report. Therefore, the file consists of three tabs - the first (Financial Data) shows only the template lines for which the query retrieves balances. The second tab (RCM Labels) shows the skeleton of the RCM template. The final tab (RCM report) shows the entire RCM template, with balances populated where available. The Financial Data query makes use of a union, to allow users to retrieve all rows in the template - those represented by a parent object, and those represented by COA objects which do not roll directly to a K-parent. For more on the template table, refer to http://www.upenn.edu/computing/da/dw/genled/gl_report_template.t.html. Data in this report can be verified using the BEN Financials 210 report.

RSALMGT - Payments and Encumbrances is actually comprised of two separate queries (Payments and Encumbrances), linked by the posted account, job class and SSN. The Payments query retrieves all fiscal year to date payments for employees paid from a given ORG, while the Encumbrances query retrieves the remaining encumbrance balances for employees from the same posted ORG. The queries are each represented separately on the appropriately named tabs, and, once linked (common elements are Job Class, Posted COA Account, and SSN), the combined results are presented on the Combined tab to show the fiscal year commitment for each person (by account and job class). Data in this report can be verified using Salary Management reports, or at a higher level (by account segments) using BEN Financials reports

Fund Summary (fundsummary.rep) gives a summary of the entire grant fund similar to the BEN Financials 115 report (listing key attributes) and also lists totals by object code for Budget, Month, Fiscal Year, PJTD Actual Expenditures, Encumbrances and BBA. A separate tab is provided to break down expenditures further by ORG/CREF. Another tab is used to project what the unobligated balance as a percentage of the current year budget will be at the end of the current budget year. The calculation is based on the average expenditures per day. This report is useful for administrators preparing NIH noncompeting continuations since NIH will want to know if an unobligated balance greater than 25% exists. If the projection shows a balance greater than 25% of the current year's budget, the administrator needs to investigate further before answering "no" to the question about whether an unobligated balance greater than 25% exists. This is the quickest way to look at any fund in its entirety.**

Fund Detail (Fund Detail.rep) Provides accounting period summaries of all expenditures and payroll for a given fund and any period of time. Separate tabs provided for: Accounting Period Summary, Payroll Period Summary, Payroll by Object Code, Payroll by Person, Exp Detail by Obj, Detail by ORG, Detail by CREF, Detail by Acct Period, and Detail by PO/User. [NOTE: This report is comprised of two separate queries, one of which queries the Salary Management data collection. If you do not have access to Salary Management data in the Warehouse, you will receive errors when trying to view the "Payroll" tabs of the report.]*

ORG Summary (ORG Summary.rep) gives a summary of all grant funds for which the ORG is the RESP ORG. Results, listed by fund, include fund description, PI name, account end date, and balances for PBIL, Operating Budget, Sum Grant Expenditures, Surplus/(Deficit), Revenue, Receivable, and Cash Received. Separate tabs are provided to identify grants sorted by fund, PI, account end date, Receivables only, Overdrafts only, funds due to freeze in the next 90 days, analysis of expired funds, late or due FSRs, funds with illegal object code charges, and a comparison of budgeted payroll to actual payments for each fund. This report can be used to identify a variety of potential problems (i.e.accounts with large overdrafts, receivables, revenue recognition, etc.) as well as provide the user with a quick summary of all his/her organization's grant funds. Additional queries have also been developed to list all funds where another ORG has charged funds of the RESP ORG (otherorgschgs.rep) and where the ORG has charged funds not under its RESP ORG (subbudgets.rep); when running those reports, the user should specify his/her own org at both prompts. *

ORG Wayward Summary (ORG Wayward Summary.rep) is the first of two queries required to identify wayward transactions. The other query is ORG Wayward Detail. ORG Wayward Summary is a query that is used to alert the user of the ORG/Fund wayward transactions that have occurred and require attention.ORG Wayward Detail (ORG Wayward Detail.rep) provides the line item detail for the ORG/Fund combination shown in the Org Wayward Summary Query.*

Detail Reports are used to provide transaction detail for any range of accounting periods after FY2000 for a given object code (DETAIL OBJECT RPT.rep ) or for all objects (DETAIL RPT.Rep) for a particular fund. Separate queries are used for Operating (DETAIL OBJECT RPT(Operating Bdgt).rep ) and Special Budget (DETAIL OBJECT RPT(Special Bdgt).rep), Cash (DETAILOBJRPTCASH.rep ) and Subcontracts (DETAILsubcontract.rep ) entries. Also available are reports by PI (DETAIL RPT (All grants of PI).rep) or RespOrg (DETAIL RPT (All grants of RESP ORG).rep), which are ideal for running monthly detail for all funds for a PI or RespOrg (since these queries bring back data for several funds we recommend that you run them for periods less than 3 months).*

Encumbrances (Encumbrances(RESP ORG).rep) identifies all currently open encumbrances for your Resp Org. Separate tabs are provided for All Outstanding, Outstanding on Unfrozen Funds, Outstanding on Frozen Funds, and Outstanding by PI. All Encumbrances and PO's need to be closed on frozen funds because a fund can not be disabled unless there are no encumbrances remaining on the fund. Encumbrances(FUND).rep provides all current outstanding encumbrances for one particular fund.*

717 Report (717 Org Report.rep) is tailored after the BEN Financials 717 report used by Research Services to identify funds that have been reported (or have no reporting requirement), but have not yet been disabled due to varying reasons. ***

4822 Writeoffs (4822writeoffs.rep) is used to identify all grant funds (by ORG) for which there has been a credit entry to object code 4822 during the current fiscal year (both MTD and FYTD totals are provided). This report indicates funds where there have been sharing entries or a write off of the direct cost overdraft (separate Object Codes are not used to distinguish between the two). 4822 writeoffs are to departmental unrestricted funds. *

Funds Reported includes two reports: Rptsissued.rep lists all funds (by ORG) that have been reported on by Research Services, with the amount reported. FSRDI.rep lists the current differences between actual Direct and Indirect amounts and what was reported by Research Services. *

Funds Disabled (disablebyorg.rep) lists all funds (by ORG) that have been disabled by Research Services. This report can be very helpful, as there is no option in BEN Financials currently to print only enabled funds; BEN Financials will generate reports for all active grants for each ORG. *

PBIL Violate (PBIL Violate.rep) can be used to identify all funds for which PBIL does not equal PBUD. Accounts where PBUD has a value and PBIL doesn't usually indicates the existence of an advance account. *

Payroll/Personnel Reports:
Payroll.ep is used to identify all salary charges made on a specific fund (listed by person or by object code) for any period of time.
Payroll(all grants of RESP ORG) is ideal for running monthly detail for all funds for a RespOrg. Similarly, Payroll(all grants of PI) can be used for retrieving detail for all funds for a PI. Since these queries can bring back data for several funds we recommend that you run them for periods less than 3 months.
Personnel.rep is used to identify all payments made to a specific person for a period of time, and provides a cumulative percentage of all payments over that period of time that were charged to a specific fund. These reports are used extensively in reviewing the validity of effort on non-competing proposals.
ORGPersonnel.rep displays current payroll distributions, including percentages and fund descriptions, for monthly and weekly paid employees for a given home organization. When the salary is above the NIH cap (for NIH-sponsored funds only), the report also displays the percentage the distribution contributes to the current cap. Additionally, separate tabs are provided to display current allocations hitting expired grants, as well as allocations hitting suspense because the grant is frozen. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for these reports.

Payments after Account End: Paylate.rep is used to identify identify all grants that which have been used in employee payments where the check date or pay period end date is after the fund's Account End. The information is summarized by ORG, detail provided by fund, with a separate tab indicating funds that can be worked on prior to freezing. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for this report.

Proposal Reports (proposal.rep) can be used to identify all proposals processed for any given period of time, and whether they were awarded or are pending. The report can also be used to identify all advance account proposals processed. *
Note: you must have access to the Sponsored Projects collection in the Data Warehouse to retrieve results for this report.

Grant Monitor (GrantMonitor.rep) is a series of reports that alert the user of funds which may need attention, ie., overdrafts, discrepancies between PBIL/PBUD and Budget, unallowable charges, NIH noncompeting continuations which are due, potential problems with clinical trial cash, clinical trials which may have residual balances, receivables, funds where AISs may need to be updated because the sponsor sends out one notice for multiyear awards, or funds sponsored by the federal government which may have problems with cash draw-down. Click here to view more detailed documentation of each report tab.*

*Reports contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine.
**Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Jason Molli, of the Office of Research Services.
***Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Nick Majuri, in the School of Medicine.
****Report contributed by Jenny Sherwood, Department of Medicine, Division of Rheumatology; original development by Rich Snyder, of the Office of Research Support Services in the School of Medicine.

Asset Management

Aging Report of Untagged Equipment: This metric report lists items in BEN Assets with a Tagging Status of “Ready for Tagging”, and groups assets by the number of days between the Create Date in BEN Assets and the current date, such as <30 days; 30 to 60; 60 to 90; 90 to 120; >120.

Asset Additions: This report identifies asset acquisitions for a specified time period, by Major and Minor category, along with asset attributes and related invoice detail.

Asset Change History: Identifies assets that have had a change in select key attributes such as location, responsible ORG, acquisition cost, accumulated depreciation, or research amount.

Assets that Cannot be Located: Identifies assets with “Found During Inventory?” value of “N” that have not been retired in BEN Assets. This report can assist in monitoring assets identified as having been not found during the inventory to ensure that all such assets are subsequently located or appropriately retired.

Assets with Invalid Custodian Tagger Location or RespOrg: This report identifies those assets whose Custodian, Tagging Contact, Asset Responsible ORG and/or Location are invalid, and need to be corrected in BEN Assets.

Assets Inventory: Displays the results of asset inventory, based on the Inventory Name selected at the time of report execution. Only assets with any sort of discrepancy between data in BEN Assets and information returned by the inventory will be displayed.

Ready for Tagging: This report identifies assets with a Tagging Status of “ Ready for Tagging”. This report can assist the school/center Tagging Contact in locating and tagging assets for verification in BEN Assets. The report can be downloaded to an Excel spreadsheet, where it can be used to record the tag number and tagging date which will be entered in BEN Assets. The report can also be used to verify asset information such as model number, serial number, etc., with the information in BEN Assets to identify any changes needed to the information in the system.

Report of Assets: This report identifies active (non-retired) assets based on parameters entered by report requestor. This report can assist the School/Center with asset inventory, identification and location.

Report of EIP Assets: This report identifies active (non-retired) equipment in process (EIP) assets based on parameters entered by report requestor. This report can assist the School/Center with EIP asset inventory, identification and location.

Retired Assets: Identifies asset retirements for a specified time period, by Major and Minor category, along with asset attributes and related invoice detail.

Tagging Metrics: This metric report displays counts of assets within a center by tagging status, as well as number of assets tagged by month, and tagging aging for untagged items.

Untaggable Assets: Identifies moveable assets that have been designated as ‘untaggable’ in BEN Assets.

Express Mail

Express Mail Shipment Detail: Provides Express Mail shipping detail and summary information for a selected ORG range and transaction period.

FRES Work Requests

Open and Closed Work Requests: Uses the FRES Work Request universe to display header and phase information for open and closed Facilities and Real Estate Services (FRES) work requests - i.e., those with a status of 10-OPEN,70-CANCEL,80-WRK-CMP,or 90-CLOSED. The second tab displays the phase comments, as entered and maintained using the Facility Center application.

Open Work Request Aging: Contains two tabs presenting data from the FRES Work Request universe. The first tab, Aging Summary, totals counts of Work Request phases for all open (status less than 70-CANCEL) phases by category. Counts are grouped into days open buckets based on Phase Est Start Date relative to the report execution date. The second tab, Open Work Requests Detail, shows headers and phase information for the open items aggregated on the first tab.

Preventive Maintenance: Uses the FRES Work Request universe to display header and phase information for open and closed Facilities and Real Estate Services (FRES) preventive maintenance work requests - i.e., those with a status of 10-OPEN,70-CANCEL,80-WRK-CMP,or 90-CLOSED. All requests with an estimated phase start date within two weeks of the prompted date range are included.

School_Center Work Request Charge Detail: Uses the FRES Work Request universe to display charge detail information for open and closed Facilities and Real Estate Services (FRES) work requests based on prompted extract dates.

Work Request Cost Hour Summary: Uses the FRES Work Request universe to display cost and hour totals for open work request phases, by subledger (e.g., contract, labor, material). In addition, associated work request and phase header information are presented, as well as data about purchase orders related to the work requests.

Work Request Status History: Uses the FRES Work Request universe to display work request history over time. Phases are grouped under each work request, and highlighting in the various columns show how a record changed from the one prior. The last record will always show the current information for the work request.

PennWorks

PennWorks Administrative Roles - Description required: Uses the RSALMGT universe and can be used to identify administrative roles in need of updates in PennWorks. The single report tab displays individuals having an active role associated with job class 140010 (formerly known as A-1 Academician) for which a PennWorks administrative role has not yet been associated. The Role ORG and term dates are displayed, along with the Role State.

PennWorks Change Log: Uses PennWorks change log data available in the Payroll Change Log (change_log) universe, and can be used to monitor updates made to employee records in the PennWorks system. Access to the Change Log data in the Warehouse is required to run this report. The first tab, "Changes Summary", displays by individual, PennWorks update actions and the date they occurred, along with basic information about the person who performed the update for the report prompt timeframe and Home ORGs. The second tab, "Element Change Detail", provides the detail old/new values, by data element changed, for each update listed in the first tab.

PennWorks Compensation - Academic Base Review: Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to identify individuals for whom Academic Base compensation may need to be entered or updated in PennWorks. The first tab, “Standing CE and Lecturer C - Without Academic Base” displays records only for individuals in the Faculty population who should have an Academic Base Salary, but for whom one has not been entered in PennWorks for the report timeframe. The second tab, “Standing CE and Lecturer C - All Compensation Types”, displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population in the Home ORG range.

PennWorks Compensation - Administrative Stipend and Role Review:
Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used together to identify individuals for whom Administrative Stipends or roles may need to be entered in PennWorks.
The first tab, “Administrative Stipend Salary without Admin Role” displays records for individuals for whom an Administrative Stipend has been entered on the PennWorks Compensation tab, but who do not have a PennWorks administrative role active for the stipend timeframe. The second tab, “Admin Roles without Administrative Stipend Salary”, presents the converse view, showing individuals who have an administrative role in PennWorks, but for whom an Administrative Stipend has not been entered for the role timeframe.

PennWorks Compensation - Salary Distributions and Payments Review:
Consists of three tabs which present information from the PennWorks and Salary Management data available in the RSALMGT universe, and can be used to compare PennWorks Faculty compensation salaries to distributions and actual payments.

• The first tab, "Faculty Compensation Comparison Summary" displays the Faculty compensation salaries and annualized distribution totals, summarized by person, with variances highlighted in red.
• The second tab, “Faculty Compensation Distribution and Payments” displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population eligible for compensation data maintenance, in the Home ORG range. In addition, fiscal year calculated totals for PennWorks and Payroll distributions, as well as actual year to date Payments are summarized by Faculty Compensation category.
• The third tab, “Monthly Payments Review”, provides a view of actual Payments to the individuals eligible for compensation data maintenance in the Home ORG range, for a single prompted Accounting Period. Monthly Payment totals are compared to calculated monthly Faculty Compensation salary and Role Annual Salary amounts. Summary regular pay differences of greater than $100 are highlighted in red.

PennWorks Compensation and Roles: Uses the RSALMGT universe and can be used to monitor PennWorks compensation and role data. The single report tab displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population eligible for compensation data maintenance, in the Home ORG range.

PennWorks - Distributions Ending Review: This report consists of two tabs which present information from the PennWorks and Payroll data available in the RSALMGT universe, and can be used to review PennWorks and Payroll distributions ending during a given date range. The first tab, "PennWorks Distributions" displays PennWorks distributions for each individual, along with related role information and distribution and role states (only Final and Projected states are included). The second tab, "Payroll Distributions", displays the distributions for the same individuals that are in the Payroll system for processing.

PennWorks - Distributions Review: This report consists of two tabs which present information from the PennWorks and Payroll data available in the RSALMGT universe, and can be used to review PennWorks and Payroll distributions.

PennWorks - Distributions vs Pay End Date: This report consists of three tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to review whether distributions are active for employees as of the desired pay period end date.

PennWorks - Projected Roles and Distributions: Uses the RSALMGT universe and can aid in monitoring PennWorks roles or distributions whose state needs to be changed to "Final." The single report tab displays and highlights, by individual, PennWorks role and distribution records having a role or distribution state of “Projected.” In addition, role term start dates and distribution start dates less than a month in the future from the report run date are highlighted in red.

PennWorks - Role Salary and Distribution Review: Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to compare PennWorks role annual salaries to distribution totals for monthly-paid employees. The first tab, “Salary and Distribution Comparison” displays PennWorks roles for each individual, along with annualized distribution totals for the role. The right-most column displays the difference between the role annual salary and the calculated annual distribution total. Differences of greater than $100 (positive or negative) or are highlighted. The second tab, “Distribution Detail”, displays the underlying Distribution COA Number and start/end dates that comprise the total on the first tab , to aid in navigating to the particular distributions in PennWorks. All roles and compensation states for current monthly or weekly-paid (non-hourly) employees are included in this report. External distributions not paid from Penn’s Payroll system are included where available.

Salary Increase - Current Faculty Compensation for Export: Presents information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to create the spreadsheet of faculty compensation information (e.g., academic base salary, etc.) needed to begin the Salary Increase Program process to update faculty compensation within an organization.

Salary Increase - Current Roles for Export: Presents information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to create the spreadsheet of role information needed to begin the Salary Increase Program process within an organization.

Salary Increase - Faculty Roles and Compensation Reports: Consists of several tabs which present information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to monitor progress of, and review data entered during the Salary Increase Program cycle for faculty roles and compensation categories. Throughout the report, current role information from PennWorks is displayed along with “new” data entered in the Salary Increase Program modules (if available). Faculty compensation data (current and new, if available) is also presented. The report is comprised of three providers, one of which retrieves role data, another the compensation categories, and the third retrieves any related justification information.

Salary Increase - Role Salary and Distribution Reports: Consists of several tabs which present information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to monitor progress of, and review data entered during the Salary Increase Program cycle for staff and faculty roles and distributions. Throughout the report, current role information from PennWorks is displayed along with “new” data entered in the Salary Increase Program modules (if available). The report is comprised of three providers, one of which retrieves role and summary distribution data, the second retrieves any related justification information, and the last retrieves more detailed distribution data.