Writing Brio Queries on Banner data

Writing Brio Queries on Banner Data
Getting Started
To write a Brio query, start with the tables where your data is stored. Consider which table will have most of your data and begin with that as your central or "driving" table. For example, if you will be running Admissions data, start with SARADAP. For student data, use SGBSTDN. Understanding your data and knowing where it is stored is probably the most important requirement for writing Brios.
Tables
Banner tables have keys that are used to uniquely identify each record. Records for people are identified using the PIDM. Admissions and student data is further identified by the relevant term (e.g. 200503). It's important to note the identifiers for each table because this is how you will join the table to other tables. Since the people-related tables are identified by a PIDM, you must obtain the first name, last name and Banner ID (if needed) from another table: SPRIDEN. Most people-related queries will include SPRIDEN because you will probably want to display a name and the Banner ID (SID). However, SPRIDEN would not be necessary if you were just doing counts. Because records are inserted into SPRIDEN each time an identifier or name change occurs, SPRIDEN can contain multiple entries for a person. Therefore, whenever you use the SPRIDEN table, be sure to filter for the SPRIDEN_CHANGE_IND to be null.

Identifier (PIDM)
The tables that you can access are in the lower left pane in the Brio window. Click on the plus sign to the left of "Table" to expand the list. To access data in a table, left click on the table name to highlight it then hold the mouse button down and drag it into the work area. To retrieve data from multiple tables, they must be joined together on common data values.
Joins
In general, joins allow you to associate data in different tables so that you can pull data for one entity, such as a student. As long as two tables have some common field of meaningful data, they can be joined. Relationships between related tables can be one-to-one or one-to-many. A one-to-many relationship is probably the most common – a class can have multiple students, a student can have more than one address, an applicant can have many test scores. Conceptually, a one-to-one relationship would be with the SPBPERS (personal) data – a person has only one birth date, etc.
Examples of Joins

1. Simple join – SGBSTDN to SPRIDEN

In SPRIDEN, the PIDM is used as the unique key. There can be multiple records for each student in the SGBSTDN table, so the Term Code Eff is also be considered identifier, but it's not a key. By filtering on only one term code, you will get one record per student for the selected term and you can then pull the first and last name from SPRIDEN. The inner join (equal join) assumes that records exist in BOTH tables that match (in Banner, you would not be able to have a record in SGBSTDN without its existing in SPRIDEN, unless there were a data problem). Note the Null selection on Spriden_Change_Ind.

1. Outer join – SGBSTDN to SORPCOL

SORPCOL is the table that contains prior college data. Not every student has their prior college data stored in Banner. This outer join shows a plus sign towards the SGBSTDN table. By making this an "outer" join, you pull all of the student records for the term and you will display prior college data if it exists. Where there is no prior college data, you will get a null (empty) value. NOTE: If this were to be changed to a simple join, then you would only see students with prior college data.
To create a join, click on the item to be joined in one table (usually the Pidm) and, holding your left mouse button down, "drag" an imaginary line to the matching field (pidm) in the other table. A real line should appear, with an equal sign, meaning that this is a Simple join. To change the join, double click on the join line. This will bring up a dialog box with a series of choices:


The "Left" or "Right" refers to the relative positions of the tables and can change based on where the tables are in the Query. What is most helpful is to read what will be retrieved. In our example, we would choose the Right outer join so that all of the records from SGBSTDN are retrieved and matches from SORPCOL will be included where they exist. Remember, if this were a Simple join, ONLY students with prior colleges would display.

3. Outer join – SGBSTDN to SYVADDR to STVNATN

Once an outer join is set on one table, any table joined from that table should also have an outer join unless you specifically want to filter out data. In the example above, joining SGBSTDN to SYVADDR (a view of the SPRADDR table) should be an outer join because you might not have a specific address for a student (e.g. a student may not have an MA address).
The STVNATN table simply allows you to print out the name of the country instead of the code. Note that the Spraddr_Natn_Code in the SYVADDR view is the field used to join to the Stvnatn_Code in the STVNASTN table. If this were an inner join to STVNATN, you wouldn't get the address from SYVADDR if a Natn_code didn't exist in STVNATN. Most STV tables in Banner are used for decoding values, so this probably wouldn't happen. However, there can be multiple look-up tables (e.g. one STVMAJR table is used only for web applications and has a subset of majors), so be sure to choose the right one if you see more than one.
To see who "owns" a table, drag the table into the work area and right click on it. Choose Properties. This will display the owner. For example, "Saturn" owns most of the basic Banner tables. Most users are restricted to the tables that are logical for data access in their department, so differentiating among tables with different owners is not usually a concern.
When joining, keep in mind the primary data you are trying to obtain. Many of the other tables then become "look-up" tables, such as a major description (STVMAJR), country name (STVNATN).
Common STV tables are:
STVMAJRmajors
STVNATNcountry names
STVSBGIsource codes with descriptions, used for prior colleges (type = 'C')
STVAPDCapplication decision
STVSTATstate names
STVTESCtest score codes and names
See Appendix I for more info.
Building the Request
Writing a brio is typically an iterative process, but it's best to start with a plan of what you'd like to see in your Results. Make a list of the items and check that you have the tables that contain those items. To include an item, simply highlight it and drag it into the Request line:


Before processing the Brio, decide which records you'd like to see. This is accomplished using Limits. If you'd like to see current students in your school, then the Sgbstdn_Term_Code_Eff should be set to the current term (e.g. 200601) and the Sgbstdn_Coll_Code_1 should be the college code for your school (e.g. FS).

Specifying limits is discussed in basic Brio training and documentation, so it won't be covered here. Remember to set the Spriden_Change_Ind equal to "Null." That will assure that you will retrieve the most recent Spriden (name) record from Banner.
When writing Brios, you may wish to prompt a user for a value in the Limits dialog box (allow the field to vary from one session to the next). A typical field would be the term code. If you click on the arrow to the right of the Limit label, you will see the following:

Highlight the field you'd like to vary by clicking on it , then click on "Var." The button will change to the following:

When you run the Brio, the Limits dialog box will pop up for any field that has a V. Be careful about fields that do not vary. They will filter data and you may not be aware of what is being restricted.
Before you process the Brio, there's one last tip. You can restrict the number of records to be retrieved by clicking on the Request button to the left of the Request line.

This will bring up the Query Properties Box. Check "Return First" and enter a reasonable number of Rows. The query will stop running when the row count is achieved. This prevents the possibility of retrieving thousands of records when you are not sure if you've written the Brio correctly. It's a beginner's safety net!

Now you can click on the Process button to run the Brio!
Summary
There's always more to say about any topic, but this should give you the basic tools for writing a Brio. If the data that is returned is not as expected, re-check your joins and your limits. Be sure you understand what data is contained in a table and its format. For example, any Limit you place on a field must match its contents and its case (e.g. FS, not fs). Writing brios takes practice. Start with an existing Brio and make changes. Then add a table by joining on the pidm or code. It can be fun!
APPENDIX I. Common Validation tables
Common Tables for use in Brios
STVTERM