N.C. State University
This presentation is the property of its rightful owner.
Sponsored Links
1 / 46

BASIC QUERY CLASS PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Basic query class

N.C. State University

BASIC QUERY

CLASS

Financials Reporting


Basic query class

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


Basic query class

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_IDJOURNAL_ID

JOURNAL_DATEJOURNAL_DATE

FISCAL_YEARPROJECT_ID

ACCOUNTING_PERIODACCOUNT

OPRIDDEPTID

: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.


Basic query class

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.


Basic query class

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.


Basic query class

Log onto PeopleSoft Financials Reporting System

Getting Started


Basic query class

Navigate to the Query Panel…Go PeopleTools Query

EXAMPLE 1: OPENING A PUBLIC QUERY AND

RUNNING THE RESULTS TO PEOPLESOFT


Basic query class

File Open to get to Open Query Box

Type “Q” and click the “Down Arrow” icon to see the Public Queries


Basic query class

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.


Basic query class

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.


Basic query class

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.


Basic query class

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.


Basic query class

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


Basic query class

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


Basic query class

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


Basic query class

Click on the Public box to uncheck it and click OK


Basic query class

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

Navigate to File Save As


Basic query class

  • 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.


Basic query class

Notice that the Query has the new name and is Private


Basic query class

  • 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


Basic query class

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.


Basic query class

Right Click on Organization and choose “Order By”

We want the results to be ordered or sorted first by Organization


Basic query class

Type “1” and click OK


Basic query class

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


Basic query class

Right click on Organization and choose “Column Number”

We want Organization to be the first column in our Results.


Basic query class

Type “1” and click OK


Basic query class

Notice that Organization is now the first field in the list


Basic query class

Right click on Organization and choose “Criteria”

We want to add Organization as a Criteria.


Basic query class

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.


Basic query class

  • 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.


Basic query class

  • 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


Basic query class

Enter your 6-digit organization and click OK


Basic query class

Click on the “Lighting Bolt” icon to run the query with results to PeopleSoft

Notice the list of organizations in the criteria


Basic query class

  • 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


Basic query class

  • 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


Basic query class

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


Basic query class

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.


Basic query class

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.


Basic query class

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.


Basic query class

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.


Basic query class

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


Basic query class

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


Basic query class

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


Basic query class

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


Basic query class

  • 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


  • Login