panthersoft financials queries

PantherSoft FinancialsQueries

Office of the Controller

Agenda


Information about Running an Existing Query• Websites Resources UPK

• Call Center

What Data are you looking for? Relational Databases

• Defined

• Example

Finding the Data• Finding the Existing Data/Data Table

Navigation• Demo

Tables to Know

Resources for Query Help

Training Manuals and Guides are on-line at:http://panthersoft.fiu.edu/faculty_financials_training.html

Call 7-7200 Financial Systems and Support Services Help Desk


What are you looking for?


A RELATIONAL DATABASE matches data by using common characteristics found within the data set.

The resulting groups of data are organized, and are much easier for many people to understand.


Relational Database


Relational Database

Same Data

Data you are

looking for

TableKey


Table 1 Table 2

What information do you want?

INNER JOIN

An INNER JOIN is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two or more tables based upon the join-predicate , otherwise known as the parameters that identify the information you are looking for. The query compares each row of Table 1 with each row of Table 2 to find all pairs of rows which satisfy the join-predicate.

SpeedType Key TableDepartme

nt IDSite Fund

Program Code

Activity Nbr

100300000 01 210 61100302000

2

110410000 01 064 00110410000

2

123530000 01 210 40123532000

1

SAMPLE TABLES


Department ID Table Employee ID TableDepartme

nt IDDepartment Name

Manager ID

Employee ID

Employee Name

100300000 General Counsel Admin

1590030 1590030 Jones, Jane

110400101 Controller's Office 1509002 1509002 Smith, John123530000 Summer Orientation 1508990 1508990 Garcia, Jose

202034000 Economics Department

1508986 1508986 Thomas, Samuel

Field Name

How do you want to viewthe information

Visualize the order of the table. Organize the information the way it makes more sense to connect and easily read.

Rename field names so they are clear to all who need to read the report by editing the Heading from PS to Text.

If the field contains a code such as “Y” or “N” it may not be necessary to change the Translate Value field to a word, however if it is a “C” or “P” (cancel, close, complete, pending, processed or paid), you may wish to identify the code.

Most if not all fields can be aggregated by Sum, Count, Minimum, Maximum or Average

Draw the Picture…Then pull the information to fit your vision


Dept IDActivity

NbrSite Fund

Program Code

Department Name

Manager ID

Employee Name

100300000

1003020002

01 210 61General Counsel Admin

1590030 Jones, Jane

110401000

1104100002

01 064 00Controller's Office

1509002 Smith, John

123530000

1235320001

01 210 40Transfer & Transition Srvc

1508990 Garcia, Jose

202034000

2023420001

01 210 11Economics Department

1508986Thomas, Samuel

New Table Results


Looking for the Data

Find where the data is stored in PantherSoft

Know what data you would like to associate to the information you are looking for

Remember: The less you filter the more information you get back from the system


Travel Authorization


Find the Existing Data/Data Table

HitShift-Control-’J’

on this page


Ignore

Find the Existing Data/Data Table


Navigation


Navigation – Records Tab

AKA Table Name


Navigation – Record Tab

DEPT_ID

Identify the Data you wish to display and how you wish to

display it

View all the Fields first before you display them in any report

Select the data you would like to associate to the information you are looking for

Re-arrange the columns so that on the report they are easily read and associated to each other

If necessary sort the data for chronologyOffice of the

Controller


Navigation – Query Tab

SAME DATA


Navigation – Prompt Tab


Navigation – Fields Tab


Navigation – Field Editor


Navigation – Field Ordering


Navigation – Criteria Tab


Navigation – Criteria Properties


Navigation – SQL Logic Tab


Navigation - Run

1104120002


Navigation - Results


Navigation – Saving Query


Navigation – Saving Query

Save the Query as private until you are ready to make it public.When you are ready to publish this query for public use, contact the

Financial Systems and Support Services Business Analysts at 77200.


Advanced Features – Expressions Tab


Advanced Features – Looking for a Value in a List

No limit on the number of values you can enter

No limit on the number of lists you can have in the query

Limit on the number of values you can see at a time

Limit on the run time of the query

If you use the incorrect values, the query will bomb


Advanced Features – Looking for a Value in a List


Advanced Features – Expressions Properties


Advanced Features – Unions

SpeedType Key TableDepartme

nt IDSite Fund

Program Code

Activity Nbr

100300000 01 210 61100302000

2

110410000 01 064 00110410000

2

123530000 01 210 40123532000

1

SAMPLE TABLES DEMO


Department ID Table Employee ID TableDepartme

nt IDDepartment Name

Manager ID

Employee ID

Employee Name

100300000 General Counsel Admin

1590030 1590030 Jones, Jane

110400101 Controller's Office 1509002 1509002 Smith, John

123530000 Summer Orientation 1508990 1508990 Garcia, Jose

DEPT_TBLPERSONAL_DA

TA

SPEEDTYP_TBLLink these three tables to

obtain a report.

New Table Results


Dept IDActivity

NbrSite Fund

Program Code

Department Name

Manager ID

Employee Name

100300000

1003020002

01 210 61General Counsel Admin

1590030 Jones, Jane

110401000

1104100002

01 064 00 Controller's Office 1509002 Smith, John

123530000

1235320001

01 210 40Transfer & Transition Srvc

1508990 Garcia, Jose


Demo

Look for all Tables containing:TRAVEL

ACCOUNTS PAYABLE

CREDIT CARD

Table Contains

TAUTH VCHR CARD

DataEquals

TA 46405 V 13000USER

1147750

DemoRecreate these Queries:

Commodity Card: List all Card Holders, Dept/Activity and Names

CC_CARD_DATA PERSONAL_DATA

Travel: Is TA linked to ER? FIU_ASSOCIATED_TRAVELAUTH

EX_TAUTH_HDR EX_SHEET_HDR


Accounts Payable: AP Vouchers not Posted FIU_FSSS_AP_VCHRS_NOTPOSTED

VOUCHER

Questions ?


Open Lab

panthersoft financials queries

Documents