Created by Miki Yanagi, last modified on Feb 26, 2019
BI-4
-
Finance Reports for BI tool
Open
| Category | Report Name | DM JIRA Ticket | Table Used | Notes | Created? |
|---|
Benefits & Payroll (FI)
| (The link above is for NSFA only, but create for universal version.) |
 DM-160
-
HR Journal Act - Employee
Closed
DM-163
-
NEW Dept HR Journal Act Fund
Closed
| | #FN1 | No |
| Category | Report Name | DM JIRA Ticket | Table Used | Notes | Created? |
|---|
| Budget Analysis | Budget vs Actuals (DM-476- 480 are all for UMPI, but we can make these report as universal and make a master report by adding prompts for DEPTID and range of ACCOUNT. Subtotaling by DEPTID) |
DM-476
-
NP-Arts &Sciences Budget vs Actual
Closed
DM-477
-
NP-Athletics Budget vs Actual
Closed
DM-478
-
NP-E&G ACTUAL TO BUDGET ALL-OK
Closed
DM-479
-
NP-Facilities Budget vs Actual
Closed
DM-480
-
NP-Prof Programs Budget vs Actual
Closed
| | #FN3 | No |
| Budget Analysis | SalesTax Collected vs. Sales Revenue Based on these reports, but I am not sure how we can combine into one report. The criteria is all different. - ALDW Sales Tax - 5.5% SalesTax Collected vs. Sales Revenue PT
- Sales Tax - 8% SalesTax Collected vs. Sales Revenue PT
- Sales Tax - 9% SalesTax Collected vs. Sales Revenue PT
- Sales Tax - 5% SalesTax Collected versus Sales Revenue PT
- Sales Tax - 7% SalesTax Collected versus Sales Revenue PT
|
DM-371
-
Sales Tax - 8% SalesTax Collected vs. Sales Revenue PT
Client Wait
| |
| No |
| Budget Analysis | Transfer check Joan McPhearson uses this report every day and need subtotals. |
DM-267
-
Transfer check
Closed
| | #FN4 | Google Drive |
| Budget Analysis | Revenue & Expense Report - DM-129- UMS_DTS_REVENUE_EXPENSE is master query
- DM-84 is for fund=23 only (MEIF)
- DM-110 is grand total summary for all campus
- DM-274 is for University Outreach only
- DM-499- The account range is not restricted to 40000-89999.
- DM-494- has REVENUE_DIV field (case statement)
Expense Report (Expense for Fund 23 - This link is for FUND=23 only, but make it universal by adding a prompt for FUND_CODE.) YTD_ACTUAL: some reports just use UM_ACTUALS_YTD and others calculate YTD actual as (CASE WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_ACTUALS_YTD WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_ACTUALS_YTD WHEN A.ACCOUNT >= '30000' AND A.ACCOUNT <= '39999' THEN A.UM_PROJECT_TO_DT ELSE 0 END) , but this revenue and expense reports limits account from 40000- 89999. So it doesn't matter for this report. However, we need to think about how it label when we create datamart. PowerBI report | | | - Should I add list of exclusions of accounts?
- add stat_code= ' ' ?
- #FN2
| Google Drive |
| Budget Analysis | Multiple Year Budget Report - Show FY on column headers (field)- Show FY19 instead of FY1
- Can prompts used in the variables?
- add FY1 (last YTD or last 2 years) actual, FY2 (Current Year) base, FY2 (Current Year) current budget, FY3 (next year) budget, FY4 Future budget?
|
DM-316
-
DJONES - BIG BUDGET 900 FY19
Closed
DM-481
-
NP- 2-YEAR ACTUAL, CY BASE, FUT BUD
Closed
DM-482
-
NP-Sales Service Auxiliary
Closed
| | #FN9 |
|
| Budget Analysis | ITS Dept Summary (David Demers Report) |
BI-24
-
Create datamart for David Demers Report
Done
BI-32
-
Need clarification on few fields on UM_GLQ_REPRT_VW
Done
|
| Need to review these fields. It seems these fields are only used in this report and may not be up-to-date. - UM_ITS_CLASS
- UM_ ITS_CATEGORY
- UM_ITS_CATEGORY_SORT
- usage on Discoverer reports
|
|
| Category | Report Name | DM JIRA Ticket | Table Used | Notes | Created? |
|---|
Facilities
| Facilities Budget Analysis Report (UMS05 Facilities Budget Analysis - This link is for UMS05 only, but make it universal.) |
DM-179
-
NJH UM FY15 Facilities Budget Analysis Detail
Closed
DM-293
-
A UMS02 Facilities Departmental Balances
Closed
DM-296
-
A Department by month for review
BI Tool
DM-305
-
A UMS02 All Departmental Balances
BI Tool
DM-307
-
A Facilities accounts review (could only retrieve SQL)
BI Tool
| | #FN5 Has 5 years of data plus monthly data for the most recent FY. | No |
| Facilities | (Combined all fields from UMS_DTS_CAPI_MAIN_PROJ_EXPENSE and UMS_DTS_CAPITAL_PROJECT.) - we can add more prompts like fund, program_code, Project_id, etc. |
DM-142
-
FY 17 Capital and Maintenance Projects Expense Review by Campus - Sightlines
BI Tool
DM-292
-
A Capital Projects Expense - Sightlines - UMF data
Closed
DM-328
-
CAPITAL PROJECT REVIEW
Parked
| | #FN8 add subtotals & grand total. |
|
| Category | Report Name | DM JIRA Ticket | Table Used | Notes | Created? |
|---|
MEIF
| MEIF Report | | | #FN7
| No |
| Category | Report Name | DM JIRA Ticket | Table Used | Notes | Created? |
|---|
| Year End | UMS Accounting Office's Master report in Discoverer (YE- Expense Analysis) : UMS_DTS_YE_EXPENSE_ANALYSIS | | UM_GLQ_REPRTING |
|
|
Year End
| YE YEP- GL Reports - YE YEP-GL Accounts ACCT LOC
- YE YEP-GL Accounts UMS01 to UMS07
- YE YEP-GL Accounts UMS08 - Budget
- YE YEP-GL Accounts UMS08 - Facilities
- YE YEP-GL Accounts UMS08 - ITS
- YE YEP-GL Accounts UMS08 - Payroll, HR
- YE YEP-GL Accounts UMS08 - SP
- YE YEP-GL Accounts UMS08 - SPC
- YE YEP-GL Accounts UMS08 No OPUNT balances
- YE YEP-GL Accounts UMS08 With OPUNT balances
- YE YEP-GL Accounts UMS08 ACCT
|
DM-395
-
YE YEP -GL Accounts UMS08 - Budget
Client Wait
DM-182
-
YE YEP-GL Accounts UMS08 - Facilities
BI Tool
| | - The blank line is added between project or description of account
- Each month's data and all available calculated fields should be clickable so that they can be in and out easily.
- Sort by step number, which is calculated filed.
- Need all versions separately.(Prompts probably won't work.)
- Needs "," (comma) for dollar value
- Needs to keep all footnotes
| No |
| Year End | Janis' RU (Roll-Up) Reports - YE -RU-AA
- YE -RU-B - Accounts, Grants and Pledges Receivable
- YE -RU-B.1 - Accounts Receivable - RF
- YE -RU-B.2 - Grants Receivable - CF
- YE -RU-C - Inventories and Prepaid
- YE -RU-C.1 Prepaids - RF
- YE -RU-CC
- YE -RU-C-FN Cash and Equivalents
- YE -RU-D
- YE -RU-D Campus
- YE -RU-DD
- YE -RU-EE
| | | - The "WP Ref" field is changing all the time and it is used by Janis only. It is refereed to her detail reports.
- The reports should be sorted by the "sort" field, which is original to Janis's reports.
- Need all versions separately.(Prompts probably won't work.)
|
|
| Year End | Janis' Detail Reports with WP (Word Paper) Ref - YE - Accounts payable
- YE - Accounts payable 3 Yr
- YE - Accounts payable 3 Year Comparison
- YE - Accounts payable RF (Restricted Fund)
- YE - Accounts Receivable - Fund 89
- YE - Accrued Liabilities
- YE - Accrued Liabilities PF (Plant Fund)
- YE - Accrued Payroll-Severance CF-CC.4.2
- YE - Agencies
- YE - CF-B Accounts Receivable - Current Fund
- YE - Conrtributions
- YE - deposits adv payments
- YE - Deposits and Advance Payments - CF
- YE - Deposits and Advance Payments - CF-DD.1
- YE - Deposits and Advance Payments - RF
- YE - Direct Loans Advanced CF-B.6
- YE - Grants Refundable
- YE - Interunits
- YE - Inventories and Prepaid Lead Sheet JM
- YE - Inventories JM
- YE - Misc AR CF-B.3
- YE - Notes payable-Internal
- YE - Payroll Tax Liability CF-CC.2
- YE - Pension Accounts
- YE - Pledges
- YE - Repairs and Maintenance GL Detail
- YE -Prepaid Travel CF-C.2.1 JM
- YE -Prepaids - RF JM
- YE -Prepaids JM
- YE -Prepaids Plant Fd
| | | - Need all versions separately.(Prompts probably won't work.)
- The report is detail report with "WP Ref" field as a reference that is tied with "RU" reports.
- It has fields like Acct Descr, Deptid, Project ID, Class, Fund Code, Account, PTD amount at Year End of the current year and previous year. It has also % change and amount change for the current and previous year (sometimes have 3 years comparisons).
- The "WP Ref" field is changed often and used by Janis only.
|
|
| Year End | Deficits Report - Designated Programs (UM version & others)
- Gifts & Gifts - greater than 1000
- SFA PROJECTS
- LOC G&C
- Non LOC G&C
- Cost Sharing
- - has more fields
| |
| #FN6 |
|
BI-24 BI-32 ITS fields.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document)