PantherSoft Financials Queries

1. PantherSoft FinancialsQueries Office of the Controller

2. 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 Office of the Controller

3. 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 Office of the Controller

4. What are you looking for? Office of the Controller

5. Relational Database 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. Office of the Controller

6. Relational Database Table Key Data you are looking for Same Data Office of the Controller

7. What information do you want? 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. INNER JOIN Table 1 Table 2 Office of the Controller

8. SAMPLE TABLES Field Name Office of the Controller

9. 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 Office of the Controller

10. New Table Results Office of the Controller

11. 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 Office of the Controller

12. Travel Authorization Office of the Controller

13. Find the Existing Data/Data Table Hit Shift-Control-’J’ on this page Office of the Controller

14. Find the Existing Data/Data Table Ignore Office of the Controller

15. Navigation Office of the Controller

16. Navigation Office of the Controller

17. Navigation – Records Tab AKA Table Name Office of the Controller

18. Navigation – Record Tab DEPT_ID Office of the Controller

19. 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 chronology Office of the Controller

20. Navigation – Query Tab SAME DATA Office of the Controller

21. Navigation – Prompt Tab Office of the Controller

22. Navigation – Fields Tab Office of the Controller

23. Navigation – Field Editor Office of the Controller

24. Navigation – Field Ordering Office of the Controller

25. Navigation – Criteria Tab Office of the Controller

26. Navigation – Criteria Properties Office of the Controller

27. Navigation – SQL Logic Tab Office of the Controller

28. Navigation - Run 1104120002 Office of the Controller

29. Navigation - Results Office of the Controller

30. Navigation – Saving Query Office of the Controller

31. 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. Office of the Controller

32. Advanced Features – Expressions Tab Office of the Controller

33. 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 Office of the Controller

34. Advanced Features – Looking for a Value in a List Office of the Controller

35. Advanced Features – Looking for a Value in a List Office of the Controller

36. Advanced Features – Looking for a Value in a List Office of the Controller

37. Advanced Features – Looking for a Value in a List Office of the Controller

38. Advanced Features – Expressions Properties Office of the Controller

39. Advanced Features – Unions Office of the Controller

40. Advanced Features – Unions Office of the Controller

41. Advanced Features – Unions Office of the Controller

42. Advanced Features – Unions Office of the Controller

43. DEPT_TBL PERSONAL_DATA SAMPLE TABLES DEMO SPEEDTYP_TBL Link these three tables to obtain a report. Office of the Controller

44. New Table Results Office of the Controller

45. DemoLook for all Tables containing: Office of the Controller

46. Office of the Controller

47. 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 Office of the Controller

48. Questions ? Office of the Controller

49. Open Lab Office of the Controller