Transcription

CUNYFirst Query Training:Learning the BasicsMarcus Richardson – Associate Director of Financial AidThis presentation is based on a template provided by Mark Rivera from BMCC’s Office of Financial Aid

Agenda Records/Tables in CUNYfirst The query build lifecycle Creating a basic query from one table Adding records, selecting fields, & adding criteria Editing a basic query Creating a Query with multiple tables (Joining Tables) Joins Effective Dates Reviewing auto-joins Reorder/Sort Reusability- creating prompts

What is a Query? Why do we create or run queries?The What-The Why- A query is a request to retrievespecific information fromrecords/tables from a database. Reporting Reconciliation Auditing Running Processes Looking for Anomalies Testing the outcome of Processes Finding out how wide spread aproblem is

Records/Tables A Record or Table is a collection of related data in a database A record Consists of Columns (Fields) and rows. They are different types of tables for different types of data. Tables related to configuration and set up Tables related to daily student transactions

Fields, Rows, and Key FieldsFIELDROWEmpl IDAid 0222222222201590003333333332015300

Common CUNYfirst Tables SRVC IND DATA Student ServiceIndicator Data STDNT CAR TERM Student TermInformation pages STDNT GRPS HIST Student GroupHistory PERSON CHECKLST Checklist Codedata STDNT ENRL Student Enrollment table CLASS TBL SE VW Class TableStudent Enrollment View ACAD PROG Student AcademicProgram Table STDNT AWRD DISB FA Award Disb. VAR DATA FINA the variable data fieldsneeded for aid year SCC PRI NAME VW Name Information SCC EMAIL QVW E-Mail Addresses ITEM SF VW Line Item Details (theCustomer Account)

Creating a Query

Access to Reporting HR/Campus Solutions Reporting Tools Query Query Manager

Query Manager Homepage Create New Query Link

Searching & Adding a Record- RECORDS Tab By clicking “Create New Query”, the Query Manager Tool opens with the 9 tabsthat can be used in the building of the query. To begin, you would type in the name of the record/table you want to use in theQuery. If you do not remember the exact name of the record, you can use thewildcard symbol ‘ % ’ and search by part of the characters in the record name.

Scenario #1: You want to see the start date and end date ofthe 2017 Aid Year for Brooklyn College.We need to find record namethat would contain thatinformation.My desire outcome should beone row of data.

Searching & Adding a Record- RECORDS Tab In this example, I am able to search for any record name that contains theword “aid” in the record name title by typing %AID. By clicking “Show Fields”, I can see all the field names contained in therecord. To add the record to your query, you would click “Add Record”.

Show Fields

Add Record By clicking “Add Record”, the query manager tool opens the record in the “Query”tab so you can select the fields you want on your output as well as any manualcriteria you would like to add.

Selecting Fields- QUERY tab By checking off the field checkbox, those fields would be included in your queryoutput. The fields would now be viewable in the “Fields” tab of the query managertool.

The FIELDS tab

Now that I’ve selected my fields, what happens if I click RUN?

You have too many rows of data with the information you do not want, multiple schools and multipleaid years!!!!!DEMO

The query would not answer just your question but it wouldalso provide information you do not want such as the aidyear start and end dates for all schools and all aid years.IMPORTANT: Selecting your fields lets the query tool knowwhat information you want to display from the record dataBUT you need to add criteria to filter the output so you letthe query tool know what information you do not want todisplay from the record data.NEVER RUN A QUERY WITHOUT PUTTING INCRITERIA!

The FIELDS tab

Adding Criteria In this example, I am telling the query tool that I want the institution to equalBKL01 in my output– this will filter out all other institutions. After inputtingBKL01 as a constant, I will click “OK”.

Adding Criteria In this example, I am telling the query tool that I want the aid year to equal 2017 inmy output– this will filter out all other aid years. After inputting 2017 as a constant, Iwill click “OK”.

The CRITERIA Tab By adding the criteria for specific fields within your query, the query manager tooladds this information in the CRITERIA tab.

If you are all set, now click RUN to view the output of the query.DEMO

RUN Query Output

Concept Select the Records/Tables you need. Select the fields from the Records that you want on your query results/output. Select the criteria from the fields to filter your query to only show the data youwant to see on your output. Confirm and click Run! If you received the desired output Success If you have not received the desire output review and revise the query byfixing your criteria (editing the current criteria/deleting criteria/adding newcriteria)

Creating a query from onetableSTDNT CAR TERM

Adding a Record- STDNT CAR TERM

Selecting Fields- QUERY Tab

Selecting Fields- QUERY Tab Let’s Add: EMPLID – Emp Id ACAD CAREER – Academic Career INSTITUTION – Academic Institution STRM - Term ACADEMIC LOAD – Academic Load FA LOAD – Financial Aid Load

FIELDS Tab

Adding Criteria

Adding Criteria

Viewing Criteria

Run Output ResultsYou have created a query that provides all student enrollment in CUNYfirst for your school and for the1182 or spring 2018 term. The query also lets you see the student academic career level.

If you received the desired outcome, you can save yourprivate query

Saving a Query The Query Name cannot have any spaces, if you want to separate text or numbers,you can use an underscore “ ”. The maximum characters for the query name is 30. In the description, you can use spaces and write up to 30 characters. I would recommend creating a folder to store your private queries. The folder iscreated by inputting the folder name when you are saving the query. To completesaving action, click “OK”.

Saving a Query You now see the name and description of the query on the top of the page.

Using Long Descriptions or Changing Heading Text If you see a value in the XLAT column, it means these fields have translatedvalue options. To use this option or change the heading text, click “Edit”.

Editing Heading Text or Translated Values

Editing Heading Text or Translated Values

Editing Heading Text or Translated Values

Editing Heading Text or Translated ValuesDEMO

Editing a query from onetableSTDNT CAR TERM

Using Query Manager to search & edit

Editing an Existing Query You can edit an existing query to make modifications OR to make a newquery based off the existing query. IMPORTANT: If you want to make a new query based off the existing query,please remember to use the “SAVE AS” feature to save the query under anew name so the existing query will not be overridden.

Editing an Existing Query

Editing an Existing Query- Adding a new criteria

Editing an Existing Query- Adding a new criteria

Editing an Existing Query- Adding a new criteria

Editing an Existing Query- Adding a new criteriaNow we have one query to identify all students registered full-time forterm 1182 or spring 2018 and their academic career status.DEMO

Creating a Query withmultiple tables (JoiningTables)STDNT CAR TERM &ACAD PROG

Joining Related RecordsRecord1Record2SharedFields

How Joining Restricts Your DataSTDNT CAR TERMACAD PROGCombinedRecordNote: Standard Inner Join on all Key Values

Query Manager Homepage Create New Query LinkLet’s add the STDNT CAR TERM record

Adding a Record- STDNT CAR TERM

Selecting Fields- QUERY Tab

Selecting Fields- QUERY Tab Let’s add only these 3 fields : EMPLID – Emp Id ACAD CAREER – Academic Career INSTITUTION – Academic Institution STRM - Term ACADEMIC LOAD – Academic Load FA LOAD – Financial Aid Load

Now let’s go back to the RECORD tabClick “Join Record” to join the ACAD PLAN record to theSTDNT CAR TERM record

Select what record to join with STDNT CAR TERMClick the “A STDNT CAR TERM” record to complete the join.

Auto-Join Criteria What Fields to Join On?When you join records in Campus Solutions you will automaticallybe prompted to join the records using matching key fields. Most ofthe time these join suggestions are good and should be followed.

Selecting Fields- QUERY Tab Let’s add only these 3 fields : ACAD PLAN – Academic Plan DECLARE DT – Declare Date REQ TERM – Requirement Term

Field TabLet’s add criteria to set the query for BKL01, 1179 term, and anenrollment status not equal to “N”.

Adding Criteria- Institution

Adding Criteria- Term

Adding Criteria- FA Load- NOT EQUAL TOUnder the condition type, lets use the drop down and instead of“equal to”, change the type to “not equal to”

Run to see outputDEMO

Save the query

Let’s Get the Names!

Let’s Join It to Table A STDNT CAR TERM

Auto Join Criteria

Effected Dated Row- What Does That Mean?This message means that the query tool has auto-created a criteriato receive the current or most recent row of data. In this example,a student may have multiple names in CUNYfirst but with theeffective date, we receive the most current primary name in thesystem.

Selecting Fields- QUERY Tab Let’s add only these 2 fields : LAST NAME FIRST NAME

Fields TabDEMO

Let’s Live on the Wild Side and Add Another Record!Let’s Add the SCC EMAIL QVW Table!

WARNING: LET’S REVIEW THIS AUTO JOIN-CRITERIAIf any of our perviously joined records contained an address type and now weare adding a second with SCC EMAIL QVW, we would be filtering out studentswhose CF e-mails were not the same! Let’s remove this auto-join because weknow the data may not be the same.

Selecting Fields- QUERY Tab Let’s add only these 2 fields : EMAIL ADDR PREFER EMAIL FLAG

Fields Tab View- Let’s Add Criteria on the preferred email address

Adding Criteria- a “Y” for preferred e-mail so we receiveone row per student

Let’s review and run!

Let’s Reorder and Sort Query Output- similar to Excel

Edit Field Ordering

Fields Tab

If you are all set, now click RUN to view the output of the query.

Success!!The query ran successfully and also resulted in the same number ofrows. This would mean that our joining tables did not filter anystudents off the report.DEMO

Reusability Queries are often used overmultiple semesters. Rather thanmaking a new query for everyaid year or every time the termchanges you can create aprompt which will allow you tochange your criteria value everytime the query is run. For Example, in our previous querywe added a criteria for theTERM. Let’s edit that to becomea reusable PROMPT.

Editing Prompt Properties

Prompts in the criteria tab

Prompt tab view

Prompt tab view – in run modeDEMO

Recap Records/Tables in CUNYfirst The query build lifecycle Creating a basic query from one table Adding records, selecting fields, & adding criteria Editing a basic query Creating a Query with multiple tables (JoiningTables) Joins Effective Dates Reviewing auto-joins Reorder/Sort Reusability- creating prompts

CUNYfirst Table Shortcut In order to find records ortables that contain thedata or fields you wish toquery in CUNYfirst. Youcan use the followingshortcut. In Google Chrome, rightclick on the page inCUNYfirst that contains thedata you need. Then click on “Inspect”.

CUNYfirst Table Shortcut Then select the cursorselection tool from theinterface that pops up.

CUNYfirst Table Shortcut Use the cursor to hover overthe data you are interested infinding the table for usewithin your query. The name of the table willappear. In this caseACAD PROG. Please beaware that sometimes theaddress in concatenated withboth the table name and thefield following. In this case thefield is TBL DESCR. If you putin the fullACAD PROG TBL DESCRyou may not get any resultswhen you search for the tableto add it to your query. Please be aware thatsometimes this shows a fieldthat starts with DERIVED. Thatis not a table, but data that isderived from the calculation ofother table information.

Q&A

CUNYFirst Query Training: Learning the Basics Marcus Richardson – Associate Director of Financial Aid This presentation is based on a template provided by Mark Ri