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.
Access the growing library of Toad queries designed to help fiscal staff build reports based on relevant data.
Incomplete Documents (see information here)
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.
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.
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.
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.
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.
Transaction Detail for Dept Reporting
Returns every direct expenditure transaction that occurred during the specified cycle and FY for the designated org. Information may be pulled by orgn Level2 through Level5 or the entry level (Level8). Results include Transaction Date, Document, Description and may easily be verified in Banner FGIBDST.
- FY18 through Current FY Transaction Detail by Cycle (pull date for multiple or individual fiscal years)
- FY17 Transaction Detail by Cycle
- FY16 Transaction Detail by Cycle
- FY15 Transaction Detail by Cycle
- FY14 Transaction Detail by Cycle
- FY13 Transaction Detail by Cycle
- FY12 Transaction Detail by Cycle
Mimics the QMenu Active Jobs report. You will be prompted to enter the TKL upon query execution.
- 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 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.
We offer four different levels of training for those interested in learning new Toad skills:
Toad I - Tips & Tricks
Building a simple query
Toad II - Data Manipulation
Toad III - Advanced Outputs
Toad IV - The Think Tank
Problem solving as a group
Please check the calendar here on our website for dates and locations!
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.
Mapping BlueWhale Drive - Provides access to Toad documentation and stored queries
Reverse Engineer an SQL Query - Turn your SQL code in to a visual diagram to make editing an easier process.