1 / 46

BASIC QUERY CLASS

N.C. State University. BASIC QUERY CLASS. Financials Reporting. N.C. State University. Objectives: Understand the structure of a Relational Database Understand how Public and Private Queries differ Run queries with results to PeopleSoft and to Excel

darius
Download Presentation

BASIC QUERY CLASS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. N.C. State University BASIC QUERY CLASS Financials Reporting

  2. N.C. State University • Objectives: • Understand the structure of a Relational Database • Understand how Public and Private Queries differ • Run queries with results to PeopleSoft and to Excel • Change Fields and Criteria in an existing query and • save it as a Private Query Financials Reporting

  3. Relational Database A Relational Database stores data in two or more tables where relationships can be defined between the tables. A field will be in more than one table, and the tables can be joined or linked using that common field. These tables relate to each other and can be linked together because of these common fields (Journal_id and Journal_date. JRNL_HEADERJRNL_LN JOURNAL_ID JOURNAL_ID JOURNAL_DATE JOURNAL_DATE FISCAL_YEAR PROJECT_ID ACCOUNTING_PERIOD ACCOUNT OPRID DEPTID : PROGRAM_CODE MONETARY_AMOUNT JRNL_LN_REF LINE_DESCR : These fields are unique to this table. These fields are unique to this table. Because these two tables have common fields (Journal_id and Journal_date), they can be joined together. That way the JRNL_HEADER information (Fiscal_Year, Accounting_Period, OPRID) can be put next to each journal line from the JRNL_LN table.

  4. Public vs. Private Queries A Public Query is saved so that any user can access it and run it. Only a few people have access to save public queries to limit the number created. A Private Query is saved so that only the user saving it can access it and run it. Anyone with query access can save private queries.

  5. Terminology The following list provides chartfield terminology as it translates from FAS to PeopleSoft Financials. It is important that you become familiar with this new terminology. Throughout this handbook, the Financials terminology will be used.

  6. Log onto PeopleSoft Financials Reporting System Getting Started

  7. Navigate to the Query Panel…Go PeopleTools Query EXAMPLE 1: OPENING A PUBLIC QUERY AND RUNNING THE RESULTS TO PEOPLESOFT

  8. File Open to get to Open Query Box Type “Q” and click the “Down Arrow” icon to see the Public Queries

  9. Notice the “Owners” for these Queries are listed as “Public” indicating they are Public Queries. Find the Public Query named Q_Voucher_List_Not_Paid and highlight it by clicking on it. Then, click OK.

  10. The “Fields” tab shows the fields that are being selected from the tables in the query. Notice the name of the Public Query you selected. The “Query” tab in the lefthand side box shows the tables being queried and the available fields in those tables.

  11. The “Criteria” tab shows the criteria being used to narrow down our search. NOTE: The “SQL” tab shows the programming code behind the fields selected and criteria given.

  12. To Run the Query with results to PeopleSoft, click on the “Lighting Bolt” icon. A Prompt Box will appear so that you can enter your Project segment or Project phase, the beginning date, and the ending date. Enter these and click OK.

  13. The Results from the Query will appear on the “Results” tab.

  14. There are many fields selected in this query. Use the scroll bar to scroll through them.

  15. EXAMPLE 2: SAVING A PUBLIC QUERY AS A PRIVATE QUERY We want to change this query. Before we do that, it is a good idea to save it as a private query first. To save a query as a private query, you must update the query properties. File Properties to get to the Query Properties box

  16. Click on the Public box to uncheck it and click OK

  17. When saving a public query as a private query, you must rename it. Use the File Save As function. Navigate to File Save As

  18. In the Save Query As box, type in the new name for your private query and click OK • The private query name MUST be different from the public query name. • We suggest you use your initials at the beginning of the name. • Note that the system is case sensitive for query names. • The owner MUST be private. If it is not, you need to check the query properties we just changed.

  19. Notice that the Query has the new name and is Private

  20. Go back to the Fields tab and notice that Organization is not being selected • Scroll down the list of available fields in the lefthand side box until you find Deptid/Organization • Double click on Deptid/Organization to select it as a field EXAMPLE 3: CHANGING THE FIELDS AND CRITERIA IN A QUERY AND RUNNING THE RESULTS TO PEOPLESOFT We now want to change this query so that the information is selected based on organization rather than on project

  21. Notice that Organization is now listed on the Fields tab As a side note, notice that Monetary Amount has “sum” in the Agg column. This means that lines with identical information in all of the fields will be grouped together as one line with the amount aggregated or summed.

  22. Right Click on Organization and choose “Order By” We want the results to be ordered or sorted first by Organization

  23. Type “1” and click OK

  24. Notice that Organization now has “1” in the Ord column

  25. Right click on Organization and choose “Column Number” We want Organization to be the first column in our Results.

  26. Type “1” and click OK

  27. Notice that Organization is now the first field in the list

  28. Right click on Organization and choose “Criteria” We want to add Organization as a Criteria.

  29. Highlight Project_ID by clicking on it. Now click on the “Delete Criteria” icon Notice that Organization is now listed on the Criteria tab. We do not want to select the information based on the Project_ID, so delete it as a Criteria.

  30. On the Organization line, double click on the “Operator Drop Down” icon • Notice the various choices • We want to search for more than one organization, so select “In List” • Next, on the Organization Line, double click the blank area under “Expression” to bring up the Edit List prompt box Notice that Project_ID is no longer listed as a Criteria. We want to specify which Organization(s) we want to search for.

  31. On the Edit List prompt box, click “Add Value” to add an organization. It brings you to the prompt box on the next slide • Repeat this for each organization you want to add to the list to search on Click OK when you are finished entering your organizations

  32. Enter your 6-digit organization and click OK

  33. Click on the “Lighting Bolt” icon to run the query with results to PeopleSoft Notice the list of organizations in the criteria

  34. A prompt box will appear so that you can enter the beginning date and the ending date • Click OK • Notice that you are not prompted for Project_ID this time as we deleted it from our criteria

  35. In the Results notice… • Organization is selected • The results are ordered or sorted by organization • Organization is in the first column • The results are for the organizations we specified in the criteria • …reflecting all of the changes we just made

  36. Navigate to the Open Query Box…File Open. Type “Q” and click the “Down Arrow” icon to see the public queries. Highlight the Public Query named Q_Paid_by_OUC/Invoice # and click OK. EXAMPLE 4: RUNNING A QUERY WITH THE RESULTS TO EXCEL

  37. The Fields tab shows the fields being selected. You can add or delete fields, change the order the fields are presented in, change the order the fields are sorted in, and change the aggregate functions.

  38. Click on “Excel” icon to run the query with results to Excel. The Criteria tab shows the criteria being used to narrow down the search. You can add, change, or delete criteria.

  39. Excel will open if not already opened. You will be prompted for some values (organization, beginning date, and ending date in this example). Click OK.

  40. The Results from the Query will appear in an Excel spreadsheet. You can use all of the Excel functionality to analyze the data. The Results can be saved on your hard drive as an Excel spreadsheet for later use and supporting documentation.

  41. Navigate to the Open Query Box…File Open. Type “Q” and click the “Down Arrow” icon to see the public queries. Highlight the Public Query named Q_22010_Journals_by_Project and click OK. EXAMPLE 5: A PUBLIC QUERY TO PRACTICE SAVING AS A PRIVATE QUERY AND TO PRACTICE MODIFYING

  42. We will practice saving this public query as a private query and modifying the fields selected.

  43. We will also practice modifying the selection criteria and running the results to both PeopleSoft and Excel.

  44. Checklist • To navigate to the Query Panel: Go PeopleTools Query • To see existing public queries: File Open, Put “Q” as Query Name, Click on “Down Arrow” icon, Highlight Query Name, Click OK. • To see existing private queries: File Open, Put your initial as Query Name, Click on “Down Arrow” icon, Highlight Query Name, Click OK. • The fields selected can be changed on the Fields tab. • The Criteria used to narrow the search can be changed on the criteria tab. • Run the Query with results to PeopleSoft by clicking on the “Lighting Bolt” icon. • Run the Query with results to Excel by clicking on the “Excel” icon

  45. To save a public query as a private query: • Open the public query • File Properties • Uncheck Public, Click OK • File Save As • Put in the name of the query (suggest you use your initials for first 3 digits), Click OK • EIS Financial Query Resources Webpage: • http://www.fis.ncsu.edu/EIS/FIN/query/query.htm • Financials Training Webpage: • http://www.fis.ncsu.edu/FinTraining

More Related