TOAD Resources

Toad is an extremely useful tool that can access information from Banner tables to customize reports and eliminate repetitive steps often used when compiling data in Excel.

Please visit the OIT Toad page (you will need to log in with your UA username and password to view the content) to download the Toad installation package and access helpful information regarding official SQL training opportunities, the various licenses offered through OIT, and additional resources offered to help UA make use of this powerful tool. Access to the UA Ad-Hoc Databrowser Team is also available at this site. 


Bluewhale is the Toad shared drive that contains canned reports for student, human resources, and financial purposes. How do I connect to this drive? Please see the instructions!

Locked out of Toad?

Entering an incorrect username/password combination three times in a row will automatically lock your account. To reset your password, please contact Lynn Wrightsman at x6969. You will be asked for your Toad ID (fnxxx) so make sure to have it handy.

Users from other campuses can locate their account Security Coordinator by using the pull-down list in the Get Started section on the OIT Accounts & Access website.


TOAD Queries

Access the growing library of Toad queries designed to help fiscal staff build reports based on relevant data.

 

 Incomplete Documents (see information here)

Budget Control Detail

Returns the budget, reservations, encumbrances, YTD, and balance information for a chosen Budget Control Org or for an entire Unit (level 4). This information can be reviewed in the FGIBAVL Banner screen, however Org detail is included in the query to easily pinpoint budget deficits.

Org Rollup Structure

Pull a list of all orgs that roll up to a given Level (level 3 - VC and level 8 - entry level) within the current hierarchy structure.

ProCard Transaction Detail

Returns a list of all items within a document (ex. PNP00001) to identify the document reference # or sequence # associated with a specific line item. This is very useful when JVing ProCard charges that now map in to Banner with a PNPxxxxx document #, which is the QP# equivalent used for the new JPMorgan website. Although this was created for ProCard document numbers, the query will return results for any document number including JVs, Finance #s (Fxxxxxx), and Budget Revisions.

Prog/Fund/Acct Code Mismatches

The query used to populate the bi-monthly report posted to the Program Code Compliance page. Run it for your unit to capture recently corrected mismatch issues to stay on top of these errors before they show up on the quarterly reviews. 

SubLedger Detail for Dept Reporting

Returns the budget, reservations, encumbrances, YTD, and balance information for a given cycle and FY range. This report can be limited by Org Level and Fund or Fund Type.

Active Jobes by TKL

Mimics the QMenu Active Jobs report. You will be prompted to enter the TKL upon query execution.

Labor Distribution

  • Labor Distribution by TKL - displays current and future labor distributions for each employee within a user-specified home TKL.
  • Labor Distribution by TKL - Detail - displays more in-depth information to include hourly rate, FTE, etc. (pulls the job TKL data instead of the home TKL)
  • Labor Distribution & DLevel Mismatch Listing - Checks the Home Dept dlevel listed in PEAEMPL against the dlevel associated with the labor distribution org. This mismatch will affect the way eDir displays employee locations as well as certain reports that base employee figures off the Home Dept. 
*Queries must be saved to your computer and then opened from within Toad.


**Queries written by the Office of Finance & Accounting are done so in the current release for TOAD. We recommend end users dowload and utilize the current release from the OIT software download site but maintain an older version (3.3) on your PC to protect against any unintended compatibility issues.

 

Training & References

Check out the different levels of training courses offered by OFA to see which one fits your skill level. Access reference guides for various Toad topics to keep you on track when developing or editing queries.

 

TRAINING

We offer four different levels of training for those interested in learning new Toad skills:

Toad I - Tips & Tricks
Building a simple query
Blackboard Recording:
March 6, 2014

Toad II - Data Manipulation
Calculated fields

Toad III - Advanced Outputs
Subqueries
Blackboard Recording:
March 20, 2014

Toad IV - The Think Tank 
Problem solving as a group

Please check the calendar here on our website for dates and locations!

 

REFERENCES

The documents below are meant to provide Toad users with easy-to-follow references in order to assist with the completion of various functions within the environment.

Toad Setup - Instructions meant for new computer installations or for use in the Computer Labs

Calculated Fields - How to insert a custom field in to your query

DECODE Statement - Syntax and examples of the SQL "If...then" statement

CASE Statement - Syntax and examples of a more flexible version of the DECODE statement.

Locating Banner Tables in Toad - A SW document walking users through the process of finding table and field names in Toad.

Reverse Engineer an SQL Query - Turn your SQL code in to a visual diagram to make editing an easier process.