Tracker Reconciliation and IDT Report Documentation

Open

UT Tracker Guides

Note that descriptions and account numbers have been obscured to protect the privacy of departments.

The best browser to view this report is Google Chrome.

To enlarge screenshots, use your document viewer controls to zoom in.

Table of Contents

Reconciliation Report

The Reconciliation Report is a Tableau Visualization developed by Technology Resources (TRecs). This data “viz” pulls information entered and held within the UT Tracker database, returning IDT and Letter Bill information using a variety of filter and search mechanisms. This report is useful for pulling internal account reports, assisting with reconciliation, and exporting data to a spreadsheet file in a variety of formats.

Overview

Default Filter Collapsed View

The default view of the Reconciliation Report shows a drop-down, labeled “Filters.” To view the full report, in the upper right-hand corner, click the triangle pointing to the right.

Open

Expanded View

Here you see the expanded view containing all filters and search fields available. Select one or more choices for each of the settings; the bar graph and report at the bottom of the page will be updated to reflect your criteria.

Resetting Filters

The Revert option in the upper left-hand corner above the Filters Pane resets the page to its default settings. Once embedded in Tracker, this will appear as a Reset button as part of the Filters Pane.

Interactive Bar Graph

Beneath the filter options, you will see Totals for each of the status categories and a bar graph based on your filter selections.

Report

Beneath the Bar Graph, you will see the report details in a spreadsheet layout.

Search Process

When searching within any of the drop-down filters with search functionality, note that you must first uncheck the (All) box prior to entering your data in order for the search to function as intended.

Within each drop-down filter, after you make a selection using the checkbox(es), you must click the Apply button for the selection(s) to be recognized.

To update the Status totals, the graph, and the report table at the bottom of the view to reflect your selected filters, click the orange Search Button in the lower left-hand corner of the Filters pane. You can tell when a filter is active by noting the grey filter icon with the red X.

Depending on the number of filters you selected, it may take a moment for the system to load your view as specified. If you add more filter specifications at this point, they will be in addition to prior filters. To reset filters, click on the Revert  button at the top left above the Filters pane.

Special Circumstances

If the Search button disappears, this means that either your selections do not meet the requirements for search criteria, or your search criteria do not return any records.

If the Search button turns blue, you currently have other filters in place.

If you click the blue Search button, this indicates an acknowledgement that those prior filters will be removed, and your more recent filters applied. Click the blue Search button to acknowledge this update. The button will turn orange again. Click the orange Search button to search using your more recent filter criteria.

Filter Functionality

Drop-Downs

All categories with a drop-down option can use either the check box or search method to locate information. Both methods require clicking the downward-pointing triangle on the right side of the drop-down bar.

Clearing Filters and Selections

To clear any checkbox filters in your view, click the All option at the top of your options list and click the Apply button. To clear drop-down filters, you can also click the filter icon with the red X to show all values. To clear search field filters in your view, click the X on the right side of the search bar. A grey filter icon shows that all options are selected and that no filters are in place.

Include and Exclude

Each of the drop-down and search options have additional functionality to either include or exclude the items selected. Note that the drop-downs will show a filter icon just to the left of the appearing drop-down arrow while search fields will only show the appearing drop-down arrow.

Making Selections

Credit or Debit Account

Select from the options that show in your drop-down menu, or search by typing in your account number in the top blank field. Note that account numbers are 10 digits, including dashes, and have trailing descriptions. The dashes (-) in the account number are required in the search function. You must first select a Credit account before the Debit accounts will load for selection.

Status

Definitions:

• Completed: Document has been final approved and completed in AP.

• Unpaid: Pending; letter billings/outstanding; processing in DEFINE but has not been completed.

• Declined: Document has been rejected.

• Submitted: Document has been submitted into Trefoil but has not been processed into DEFINE. The bill will be processed over the weekend.

Select single or multiple values by checking your preferred option to narrow your reporting results.

Report Date

Slides and Fields

To select a specific date range, you can either use the sliders (controls that let you slide a button from side to side to select values) or enter in specific dates.

Calendar Selector

Letter Bill Invoice Number

If you know the Letter Bill number, you can enter it here in the format LBNNNNNN. This will filter to the exact record entered.

DEFINE Document ID

This search includes both Letter Bills and IDTs. Make sure you are entering a zero (0) for G0VT documents.

Letter Bill or IDT

Filter by IDT, Letter Bill, or both in your search results.

Department

Select the department that you would like to filter by. Click the down arrow to open the drop-down list, then remove the check from (All). Select your preferred account(s). Note that this search is not case sensitive.

Letter Bill Notes or IDT Comments Search

Search for keywords in the Letter Bill notes and IDT comments.

Bar Graph Visualization

The bar graph showing in the Reconciliation report is interactive and responds to end user clicks within the filters panel, the bar graph and the report table.

Tooltips

Tooltips are boxes of information that pop up when you hover over particular sections of legends, charts or report tables.

To get totals by Status and Month, hover your cursor over the specific status color for a particular month. Doing so will display the tooltip box including the total of all IDTs and Letterbills submitted for that Status and Month.

Bar Chart Filter

Clicking a particular Status category in the graph legend will highlight the related portions of the bar graph while washing out the remaining statuses. Note that this does not update the report table at the bottom of the view.

Clicking the graph itself in the colored bar sections will highlight that status category in the bar graph and display a tooltip showing that group’s status, the month reported, and the full amount processed. At the same time, this action filters the report table at the bottom of the view to show the corresponding list of reports. To remove these filters, click in the white space of the bar graph to return to the default view.

Bar Chart Views

Hover over the lower left-hand corner of the chart between the X and Y axis. This will reveal a minus sign (-) and a plus sign (+). Click the minus sign to zoom in to a narrower range of dates. Click the plus sign to zoom out and see a broader range of dates. During testing, pressing the Revert  option in the upper left-hand corner above the Filters Pane resets the page to its default settings. Once embedded in Tracker, this will appear as a Reset button as part of the Filter View.

Report Table

Sorting

You can sort the data in each column in the report alphabetically, by date, or by amount, depending on the column’s data format. You can sort in ascending order for dates and amounts, and in both ascending and descending order for Department or Status. To sort, hover over your preferred column and the sort all or sort alphabetically icon will appear. By default, items will in order of the Department-Credit Account-Status-Debit Account Number-Submitted Date-Amount.

You can also hover over your preferred column and a drop-down arrow will appear.

Click this drop-down arrow to activate a pop-up menu with various options. Sort by your chosen column in alphabetical order, data source order, sum total or submitted date.

Hiding/Revealing Columns

To hide columns, hover over the column header to the left of the column to be hidden. A minus sign (-) will appear. Click the minus (-) icon to hide all columns between the currently selected column and the amount column. To bring the hidden columns back into view, hover over the initial column. A plus (+) sign will appear. Click the plus (+) icon to reveal the adjacent column to the right. Repeat this step for each hidden column until you are back at the default view with all columns visible.

Summary Views and Drilldowns

Summary views are available for each column entry field and provide drilldowns or links to additional information about that entry.

Clicking an item in the Department, Credit Account, or Letter Bill ID/Debit Account Number data fields will group items together with the same Credit/Debit account or IDT number, provide, in a pop-up, the total number of items grouped together and a sum of all items.

In cases where items are grouped within the report by department or account, clicking the department or account field will summarize items within that group. These summaries contain the total of items selected, the total amount of all items, and a link to the first UT Tracker entry.

For the Amount column in the report section, you can hover or click the dollar amount to see the pop-up box with the UT Tracker link. Clicking the link takes you to the selected entry in UT Tracker. If you are not logged into UT Tracker when you click the link, you will need to enter your UT EID and password to view the entry.

You can also click the Amount column header to view totals for all amounts in the report. Clicking the amount column will highlight the header and all amount rows in blue and show a pop-up box showing the number of transactions and their combined total.

Exporting Reports

To export a report, review the Crosstab and PDF options below.

Crosstab

PDF

IDT Report

The IDT Report pulls information from the UT Tracker app to search for IDT information using filters and search fields. It is useful for pulling internal account reports, assisting with reconciliation, and downloading data to a spreadsheet file. See below for guidance on how to use these features. Note that descriptions and account numbers have been obscured to protect the privacy of departments.

Overview

Default View

The default view of the report shows the report in the main body with the filter options on the right-hand menu.

Column Headers

The report column headers include the following: Month of Service Date, Description, Submitted User, Day of Processed Date, Credit Account, Debit Account, Status, DEFINE Document Id, DEFINE Code DTN, and Amount.

Functionality

Drop-Downs

All categories with a drop-down option can use either the check box or search method to locate information. Both methods require clicking the downward-pointing triangle on the right side of the drop-down bar.

Clearing Selections

In order to clear any checkbox filters in your view, click the filter icon with the red X. To clear search filters in your view, click the Grey X on the right side of the search bar.

Include and Exclude

For search fields, there is an option to either include or exclude the items selected.

Making Selections

Year and Month of Processed Date

Select your Year or Month of Processed Date by checking or unchecking the boxes shown in the drop-down bars. You can alternatively search for specific data by entering a year and month in the blank row just above the check boxes. The options will automatically filter to your search item.

Credit and Debit Accounts

Select from the options that show in your drop-down menu, or search by typing in your account number in the top blank field. Note that account numbers are 10 digits and do not include dashes. You must first select a Credit account before the Debit accounts will load for selection.

After your Debit account is selected, the data will update in the view at the bottom of the report.

Status

Definitions:

• Completed: Document has been final approved and completed in AP.

• Unpaid: Pending; letter billings/outstanding; processing in DEFINE but has not been completed.

• Declined: Document has been rejected.

• Submitted: Document has been submitted into Trefoil but has not been processed into DEFINE. The bill will be processed over the weekend.

To select your Status, select one or more options by checking the appropriate boxes.

Submitted User

To select the Submitted User, scroll to view and choose options by checking or unchecking the appropriate boxes. You can also type a specific user ID in the top blank bar to view results.

Search Description

Enter a description of your choice in the blank field to search and press enter to filter the table.

Search Document ID

Enter the Document ID of your choice in the blank field to search.

Search DTN

Enter the DTN of your choice in the blank field to search.

Downloading Data

To download your current view in spreadsheet format, click the Download Data button.

Make your format selections choosing from csv or excel and click the Download Button.