slide1
Download
Skip this Video
Download Presentation
BASIC QUERY CLASS

Loading in 2 Seconds...

play fullscreen
1 / 46

BASIC QUERY CLASS - PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BASIC QUERY CLASS' - darius


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
slide1
N.C. State University

BASIC QUERY

CLASS

Financials Reporting

slide2
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

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

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

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

slide7
Navigate to the Query Panel…Go PeopleTools Query

EXAMPLE 1: OPENING A PUBLIC QUERY AND

RUNNING THE RESULTS TO PEOPLESOFT

slide8
File Open to get to Open Query Box

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

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

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

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

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

slide15
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

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

Navigate to File Save As

slide18
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.
slide20
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

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

slide22
Right Click on Organization and choose “Order By”

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

slide25
Right click on Organization and choose “Column Number”

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

slide28
Right click on Organization and choose “Criteria”

We want to add Organization as a Criteria.

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

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

slide31
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

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

Notice the list of organizations in the criteria

slide34
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
slide35
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
slide36
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

slide37
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.
slide38
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.

slide39
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.
slide40
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.
slide41
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

slide42
We will practice saving this public query as a private query and modifying the fields selected.
slide43
We will also practice modifying the selection criteria and running the results to both PeopleSoft and Excel.
slide45
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
slide46
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
ad