Table of Contents
| Table of Contents | ||||
|---|---|---|---|---|
|
Properties
| Page Properties | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Widget Connector | ||
|---|---|---|
|
00:08 Agenda
01:30 Access
02:48 Navigation (Page Navigation and Drill Through)
04:06 Filters (Filters and Slicers)
06:29 Visual Interactions (Highlight/Filter and Drill Down)
13:08 Report Contents
25:27 Understanding the Report (Additional Resources)
Work order and activity (code)
Work order and activity (name)
Expected expiry or end date
Unspent balance to date before and after commitments
Report Description
This report contains both summary and detailed information about the spending on research or research-related work orders.
In addition, department-level information on research activity (spending, unspent, budget vs. actuals) and pages to analyze expenses and view recently created work orders are included.
Work orders must have an individual designated as a PI associated with it to be considered "research or research-related" and can include work orders in other funds (operating, trust, and endowment).
Data Security
This report is subject to the reporting access data control hierarchy used in Unit4. An individual’s position in the data control hierarchy, as well as the level of access (HR/Scholarship details or not) will determine how much or how little information will be returned when viewing this report.
Research Administrators are administrators assigned at the work order level.
Department Administrators have any role classification at the org unit level and above.
Report Contents
- This report shows only Active work orders
- Filters applicable to the entire report include:
- OrgUnit
- Researcher name
- Work Order
- Work Order/Activity name
Summary
(1) Research-Related Work Order Summary
This table provides a high-level overview of work orders, with fields including:
NOTE: Dual-layer data security has been added as of August 2022 so that all individuals with reporting access as PIs can see the dashboard itself, but those who do not have salary/scholarship details access will simply see blank visuals where salary/scholarship detail data is represented.
Planned for Next Update
Future Versions and Investigations
· Further enhancements to GL transaction information, including investigating a mechanism to show names of individuals paid (i.e., for salary and scholarship payments) for a summary GL entry
· Enhancements to salary commitment information, including investigating a mechanism to show names of individuals included in commitment; show breakdown by person and by month/fiscal year
· Investigation of ability to match benefits with employee expense category
· Investigation of ability to show salaries/scholarships on a different “year” definition basis (i.e., calendar, sponsor)
· Investigate commitments for internal reassignments
· Find a solution for multi-year commitments; show end date for salary commitments
· Investigate reporting commitments from other sources (Plant Operations, future Grad funding, etc.)
· Investigation of micro-budgeting and showing research spending by category side-by-side with budget by category
· Investigate links to other data sources for additional information (grant information from Office of Research; student term end dates)
· Investigate link directly into source system(s), including ability to connect to receipt images in Concur/Unit4
· Investigate ability to add comments associated with data/work order
· Investigate updates to business processes to improve GL transaction descriptions, both within Finance and fed from source systems in other departments
· Investigate ‘time intelligence’ functionality for more dynamic time-period reporting
· Investigate reporting on matching work orders
· Investigate identification of work orders as IRA
Change History
Report Contents
- This report shows only Active or Parked (temporarily closed) work orders. Closed work orders are excluded.
- Filters applicable to the entire report include:
- OrgUnit
- Researcher name
- Work Order
- Work Order/Activity name
- Business Unit
Summary
(1) Research-Related Work Order Summary
This table provides a high-level overview of work orders, with fields including:
Work order and activity (code)
Work order and activity (name)
Expected expiry or end date
Unspent balance to date before and after commitments
Future budget (if any)
There are a number of supplementary pages containing details about the work order available via drill through, such as:
Drill through can be accessed via:
- Right-click > Drill through
- Left-click (to activate the drill through buttons) and then select the relevant button
Filters on this visualization include:
- Months to end date
- Expiration date aging category
- End date
- Fund
- Work order/activity (code)
- Work order/activity (name)
- Unspent to date
- Unspent with commitments
- Future budget
With a left-click on a work order row, the Number of Work Orders by Aging Category visual will highlight the aging category associated with the selection. The Research Spending by Expense Category visual will also filter to show only the spending for that one work order/activity.
Anchor drillbuttons drillbuttons
(2) Drill through buttons
| drillbuttons | |
| drillbuttons |
As a more obvious means of drill through, clicking on a particular work order in the upper table will activate the drill through buttons (they will turn yellow). The user can then click on one of the buttons to navigate to the drill through page.
(3) Research Spending by Expense Category
This bar graph shows the sum of spending by category for the work order(s) included in the current filter.
The expenses are categorized by equipment, interfund transfers, overhead, salaries & benefits, supplies and travel.
Filter on this visualization using:
- Amount
(4) Number of Work Orders by Aging Category
This pie graph shows the number of work orders (for the work order(s) included in the current filter) in different aging categories to easily see how close work orders are to expiry.
Filter on this visualization using:
- Aging category
- Months remaining to expiry
(5) Filter Pane
Filters exist for a single visual (visible when that visual is active), for the whole page, and for all pages.
Filters applicable to this entire page include:
- Fund
- OrgUnit
- Researcher
- RFA (Research Financial Analyst) name
Additional filters may be relevant based on the visualization that is active at a point in time.
(6) Page Navigation Menu
This menu allows the user to navigate between different pages in the report. Note that drill through destination pages do not appear in this menu and can only be accessed via the drill through.
The top of the page shows the work order/activity number and name selected.
(1) Cards
The upper portion of the page shows cards displaying various(7) Help Buttons
The help buttons include links to:
- The UW BI Notification App - a place for Finance to publish updates or information about service outages
- Guidance and Instruction Documentation - a link to these Confluence page instructions for the report
- Report an Issue in Jira - a link to submit a ticket to Finance about an issue with the report
- Provide feedback on this report - a link to a Microsoft Form to submit feedback about the report
| Anchor | ||||
|---|---|---|---|---|
|
The top of the page shows the work order/activity number and name selected.
(1) Cards
The upper portion of the page shows cards displaying various total balances and information for the work order/activity that was selected. Information includes unspent balances, future budget, expiry date, and workflow approvers.
(2) Research Spending by Expense Category
The visual is a pie chart showing the spending by high-level research expense category for the currently-filtered work order/activity. By hovering over the individual sections, the user can see the category and actual dollars in a tooltip popup. This is particularly helpful if the numbers are too large to appear on the main visual.
(3) Amount Spent Towards Total Budget
The visual is a compound bar graph showing the balances of actual spending, commitments, and unspent budget to-date in percentage values relative to the to-date budget for the currently-filtered work order/activity. By hovering over the segments of the bar, the user can see the dollars for the segment in a tooltip popup.
Note: If the selected work order/activity relates to a Trust or Endowment, this visual will not populate as no budget is set in those funds. Also, results for Operating work order/activity combinations will not be meaningful until carryforward entries are completed by Faculties (generally late summer/early fall).
(4) Filter Pane
Filters applicable to the entire page include:
- Work Order/Activity Name
(5) Back (to main Summary screen)
Click on the Back arrow button on the top left to return to the Summary page.
| Anchor | ||||
|---|---|---|---|---|
|
The top of the page shows the work order/activity number and name selected.
(1) Actual Transactions (in Unit4)
This table shows a list of the transactions in Unit4 for the work order/activity combination selected.
* NEW * Amounts (only on this page) are represented as negative for outflows/expenses and positive for inflows/income
| Info |
|---|
| Note that salary and scholarship entries are summarized in Unit4, so there may be payments to multiple individuals included in each line item. |
Columns in this table include:
- Transaction number
- Period
- Transaction date
- Account number and name
- Transaction comments (includes supplier name and invoice number for items entered through Accounts Payable)
- Amount
Filter on this visualization using:
- Account (number or name)
- Date
- Period
- Transaction comments
- Transaction number
- Amount
(2) Date Slicer
By default, this page shows transactions in the last 90 days. Use the date slicer to change the date range shown.
(3) Filter Pane
Filters applicable to the entire page include:
- Work Order/Activity Name
(4) Back (to main Summary screen)
Click on the Back* NEW * Drill through to Details
A connection has been added from the summary transaction(s) in Unit4 to the details from Workday/Quest/CentreSuite and purchase order information. Rows with a green arrow in the drill column have additional details available (subject to the user's reporting access level for Salaries/Scholarships). Right-click on any green arrow in the Drill column, select Drill through > Details to see Transaction Details.
The drill through will take you to a separate Details screen:
| Info |
|---|
Note that depending on the type of transaction, the amount will appear in one of 3 columns depending on it's tax status/treatment. See the note at the bottom of the page (highlighted above) for information on how each of the different types of transactions is presented. |
Click on the Back arrow button on the top left to return to the Summary Transactions page.
Salaries Paid by Fiscal Year (since Unit4)
This page shows the salaries paid since the implementation of Unit4 (May 1, 2017) broken down at a high level by account name and fiscal year.
Hovering over the table will make the drilling interaction menu appear.
Users can drill down on both rows (from salary account categories down to individual employee names) and on columns (from fiscal years down to individual months) by changing the Drill on via the dropdown and using the forked down arrow. Users can also right-click on a row or column header and select Drill down.
Filter on this visualization using:
- Account (number or name)
- Employee name
- Fiscal year
- Month
- Amount
(2) Date Slicer
By default, this page shows transactions in the last 90 days. Use the date slicer to change the date range shown.
(3) Filter Pane
There are no filters applicable to the entire page.
(4) Back (to main Summary screen)
Click on the Back arrow button on the top left to return to the Summary page.
| Anchor | ||||
|---|---|---|---|---|
|
Salaries Paid by Fiscal Year (since Unit4)
This page shows the scholarships salaries paid since the implementation of Unit4 (May 1, 2017) broken down at a high level by student type account name and fiscal year.
Hovering over the table will make the drilling interaction menu appear.
You Users can drill down on both rows (from student type salary account categories down to individual student employee names) and on columns (from fiscal years down to individual months) by changing the Drill on via the dropdown and using the forked down arrow. You Users can also right-click on a row or column header and select Drill down.
Filter on this visualization using:
- Student type
- Student Account (number or name)
- Employee name
- Fiscal year
- Month
- Amount
Open Commitments (in Unit4)
This page shows a list of the commitments in Unit4 for the work order selected.
Note that the salary entries are summarized, so there may be payments to multiple individuals included in each line item.
Columns in this table include:
- Date
- Account name
- Description (level of detail varies - for commitments relating to purchase orders, product lines have been included)
- Amount
- Supplier name
- Requisition number
- Purchase Order (PO) number
- Transaction number (TransNo)
Filter on this visualization using:
- Account (number or name)
- Date
- Description
- PO
- Requisition
- Supplier name
- Transaction number
- Commitment type
- Amount
Department Summary
- This page contains only Fund 105 information by default. Using filters, users can opt to include Fund 100 information. As Trust and Endowment funds (103/106) do not have budgets, they have been excluded from this page to avoid data misrepresentations. If a user doesn't have department-level access, they will only see the information they have access to.
(1) Unspent Research Budget by Department
This bar graph shows the side-by-side comparison of budget vs actuals plus commitments to date by department.
This visual will allow the user to drill down into the data all the way to work orders. As the user drills down, the other reports on this page will also filter to show the corresponding drilled data, as they are linked.
Filter on this visualization using:
- Fund
- Work order/activity
- Org unit
(2) Research Spending Breakdown by Department
This stacked bar graph visual shows actual spending by high-level research expense category by department.
Filter on this visualization using:
- Work order/activity
- Org unit
(3) Deficits by Researcher/Surpluses by Researcher
Researchers will appear in either the Deficits or Surpluses visual based on the net remaining balance in all of their Work Orders (both Fund 105 and Fund 100) together. Drill down on the bar for a particular researcher (right-click) to see the balances (including commitments) for the individual Work Orders making up the net Surplus or Deficit.
Filter on this visualization using:
- Work order/activity (number or name)
- Researcher name
(4) Filter Pane
Filters applicable to the entire page include:
- Fund
Non-Salary Details
(1) Non-Salary Transactions
This page shows a list of the non-* NEW * View Salaries Paid on Different Year Bases
Buttons have been added to allow the user to flip between seeing salaries broken down in a fiscal year presentation (default), a calendar year presentation (Jan-Dec), or a government year presentation (Apr-Mar).
The Salaries Paid by XXX Year table below will change accordingly.
| Info |
|---|
NOTE: Dual-layer data security has been added as of August 2022 so that all individuals with reporting access as PIs can see the dashboard itself, but those who do not have salary/scholarship details access will simply see blank visuals where salary/scholarship detail data is represented. |
| Anchor | ||||
|---|---|---|---|---|
|
Scholarships Paid by Fiscal Year (since Unit4)
This page shows the scholarships paid since the implementation of Unit4 (May 1, 2017) broken down at a high level by student type name and fiscal year.
Hovering over the table will make the drilling interaction menu appear.
You can drill down on both rows (from student type categories down to individual student names) and on columns (from fiscal years down to individual months) by changing the Drill on via the dropdown and using the forked down arrow. You can also right-click on a row or column header and select Drill down.
Filter on this visualization using:
- Student type
- Student name
- Fiscal year
- Month
- Amount
* NEW * View Scholarships Paid on Different Year Bases
Buttons have been added to allow the user to flip between seeing scholarships broken down in a fiscal year presentation (default), a calendar year presentation (Jan-Dec), or a government year presentation (Apr-Mar).
The Scholarships Paid by XXX Year table below will change accordingly.
| Info |
|---|
NOTE: Dual-layer data security has been added as of August 2022 so that all individuals with reporting access as PIs can see the dashboard itself, but those who do not have salary/scholarship details access will simply see blank visuals where salary/scholarship detail data is represented. |
| Anchor | ||||
|---|---|---|---|---|
|
Open Commitments (in Unit4)
This page shows a list of the commitments in Unit4 for the work order selected.
Note that the salary entries are summarized, so there may be payments to multiple individuals included in each line item.
Columns in this table include:
- Date
- Account name
- Description (level of detail varies - for commitments relating to purchase orders, product lines have been included)
- Amount
- Supplier name
- Requisition number
- Purchase Order (PO) number
- Transaction number (TransNo)
Filter on this visualization using:
- Account (number or name)
- Date
- Description
- PO
- Requisition
- Supplier name
- Transaction number
- Commitment type
- Amount
Non-Salary Details
(1) Non-Salary Transactions
This page shows a list of the non-salary transactions specified by the slicers at the top of the page (2, 3, and 4) and the filters (5).
Columns in this table include:
- Transaction number (Trans #)
- Period
- Transaction date (Trans Date)
- Work order/activity number
- Account name
- Transaction comments (level of detail varies depending on source system and method of posting)
- Amount
- Work order/activity name
Filter on this visualization using:
- Account name
- Amount
- Period
- Transaction number
- Transaction date
- Transaction comments
- Work order/activity (number and name)
(2) Date Slicer
By default, this page shows transactions in the last 12 weeks. Use the date slicer to change the date range shown.
(3) Amount Slicer
By default, this page shows transactions over $20,000. The range can be adjusted using the either the or by changing the amounts in the to/from boxes.
(4) Expense Category Slicer
This slicer allows a user to filter by a specific category of expense by clicking on the bar segment or the category description.
(5) Filter Pane
Filters applicable to the entire page include:
- Account
- Fund
- Researcher
Created
(1) Work Orders Created
This page shows a list of the non-salary transactions specified by the slicers at the top of the page (2 and 3) and the filters (4).
Columns in this table include:
- Work order name
- Work order number
- Created date
- Start date
- Work order manager
- Signing authority
- Expected end date
- Budget
Department Summary
- This page contains only Fund 105 information by default. Using filters, users can opt to include Fund 100 information. As Trust and Endowment funds (103/106) do not have budgets, they have been excluded from this page to avoid data misrepresentations. If a user doesn't have department-level access, they will only see the information they have access to.
(1) Unspent Research Budget by Department (Fund 105 only)
This bar graph shows the side-by-side comparison of budget vs actuals plus commitments to date by department for Fund 105 only.
This visual will allow the user to drill down into the data all the way to work orders. As the user drills down, the other reports on this page will also filter to show the corresponding drilled data, as they are linked.
Filter on this visualization using:
- Created date
- Expected end date
- Signing authority
- Start date
- Work order
- Work order manager
- Work order name
(2) Date Slicer
By default, this page shows work orders created in the last 4 weeks. Use the date slicer to change the date range shown.
(3) Work Orders Created Card
The card shows the number of work orders created in the time period defined, either by the slicer or by a filter. It also represents the number of work orders in the table list.
(4) Filter- Work order/activity
- Org unit
(2) Research Spending Breakdown by Department (Fund 105 only)
This stacked bar graph visual shows actual spending in Fund 105 by high-level research expense category by department.
Filter on this visualization using:
- Work order/activity
- Org unit
(3) Deficits by Researcher/Surpluses by Researcher - Research Funds Only/All Funds
Researchers will appear in either the Deficits or Surpluses visual based on the net remaining balance in their Work Orders. There is a button to flip between Research Only (default) and All Funds (net of Fund 105 and Funds 100/103/106 as applicable). Drill down on the bar for a particular researcher (right-click) to see the balances (including commitments) for the high level funding sponsor categories, then again to see individual Work Orders making up the net Surplus or Deficit.
Filter on this visualization using:
- Work order/activity (number or name)
- Researcher name
(4) Filter Pane
Filters applicable to the entire page include:
- Created date
- Fund
Instructions
The Instructions page has details for how to use and navigate within Power BI reports. It shows how you can drill down for more information, sort in different ways, and reset the page to the default presentation of the report.
Roadmap
Safe Harbor Statement
The following reflects the current plans based on known requests and information, data availability and capabilities in Microsoft Power BI. It is intended for information purposes only and is not a commitment to deliver any specific reporting or functionality.
Priorities and timing may change based on future events such as: new items identified, enhanced data and/or system capabilities, or other external constraints. Future investigation of specific items may also result in the conclusion that the request is not feasible. We will endeavor to update this roadmap as significant updates become available.
- Fund
Department Research
| Info |
|---|
NOTE: "Funding" for the purposes of this report is represented by the budgeted revenue recorded in Unit4 for a given fiscal year for a given grant (work order). This is noted as the Award Year in this report. |
(1) Total Federal, Provincial, Industry, and Other Funding
This visual shows the amount of Funding by sponsor. The default view shows All Sponsors based on the overall sponsor groups (Federal, Provincial, Industry, Other). The buttons above the visual can be used to change the view to exclude Tri-Agency (from the Federal group) or to see only Tri-Agency (broken down by the different agencies).
Hovering over a datapoint in the visual will bring up a tooltip showing the amount in each category for that year.
Left-clicking on a datapoint in the visual will filter the visual to the right.
Filter on this visualization using:
- Overall Sponsor (only All Sponsors and Non-Tri-Agency versions of the visual)
- Sponsor Type (only the Tri-Agency version of the visual)
(2) Total Federal, Provincial, Industry, and Other Funding
This visual is a tabular version of visual (1) which also shows the actual sponsor name and amounts by sponsor by award year.
The visual is filtered based on selections made for visual (1) but users can also filter on this visualization using:
- Award Year
(3) Net Funds Available (Overspending) by Sponsor/Type
This visual shows the net funds available (budget minus actuals minus commitments) and future budget information for the sponsor category chosen. This visual is also affected by the button selection above visual (1).
By default, this visual will show only information for non-closed work orders only.
As a matrix visual, the user is able to expand into several layers of detail from overall sponsor > sponsor type > sponsor > researcher.
(4) Filter Pane
Filters applicable to the entire page include:
- Department
- Overall Sponsor
- Sponsor Type
- Sponsor
Researcher Funding
| Info |
|---|
NOTE: "Funding" for the purposes of this report is represented by the budgeted revenue recorded in Unit4 for a given fiscal year for a given grant (work order). This is noted as the Award Year in this report. |
(1) Fiscal/Award Year Slicer
The slicer allows the user to filter the page to look at the data for a single fiscal year.
(2) Total Funding by Researcher and Overall Sponsor/Sponsor Type
This visual shows the amount of funding by sponsor for each researcher. The default view shows sponsors based on the overall sponsor groups (Federal, Provincial, Industry, Other). The buttons above the visual can be used to change the view to split out Tri-Agency (from the Federal group).
Hovering over a segment of the bar in the visual will bring up a tooltip showing the amount in that category for that year.
Left-clicking on a datapoint in the visual will filter the other visuals on the page.
| Info |
|---|
| Note: to filter for an entire Researcher, click on the researcher name to the left of the bar graph or use the Filter pane (6). To filter for a Researcher AND a specific sponsor category, click on the bar graph segment or use the Filter pane (6). |
Filter on this visualization using:
- Funding (amount)
- Overall Sponsor/Sponsor Type (depending on which version of the visual is on display)
- Researcher
(3) Researcher Metrics by Year and Funding Type
This matrix visual displays the total funding, maximum amount of individual grant funding, average of grant funding, and the median of grant funding by year, by researcher, by funding type.
| Info |
|---|
| Average and Median calculations exclude zero years. |
(4) Researcher Grants by Sponsor Type and Work Order Name
This visual lists each Unit4 work order with funding (budgeted revenue) in a given award year along with information about the Researcher, overall sponsor, sponsor type, and sponsor name.
(5) Number of Researchers with Funding by Year
This visual shows the trend in number of researchers with funding (budgeted revenue) by fiscal year the budgeted funding is recorded against.
(6) Filter Pane
Filters applicable to the entire page include:
- Department
- Overall Sponsor
- Sponsor Type
- Sponsor
Created
(1) Work Orders Created
This page shows a list of the non-salary transactions specified by the slicers at the top of the page (2 and 3) and the filters (4).
Columns in this table include:
- Work order name
- Work order number
- Created date
- Start date
- Work order manager
- Signing authority
- Expected end date
- Budget
Filter on this visualization using:
- Created date
- Expected end date
- Signing authority
- Start date
- Work order
- Work order manager
- Work order name
(2) Date Slicer
By default, this page shows work orders created in the last 4 weeks. Use the date slicer to change the date range shown.
(3) Work Orders Created Card
The card shows the number of work orders created in the time period defined, either by the slicer or by a filter. It also represents the number of work orders in the table list.
(4) Filter Pane
Filters applicable to the entire page include:
- Created date
- Fund
Instructions
The Instructions page has details for how to use and navigate within Power BI reports. It shows how you can drill down for more information, sort in different ways, and reset the page to the default presentation of the report.