Brio Training – Perkins Reports

May, 2002

 

Brio website:               http://www.its.mnscu.edu/data/index.html

Click on the “Oracle Warehouse” button on the left hand-side of the screen
                                    Click on the upper right-hand button, “Warehouse Logon”
  
Login:                          Click on the upper right-hand button, ‘Warehouse Logon’
Username = First 6 letters of your last name and the first 2 letters of your first name in lower case.
                                    Password = Will be provided at training.  (You can change your password by clicking on ‘Change Password’ located at the bottom of the logon box.  This changes it for Brio only.)   Click on the Logon button.

The first time you log into Brio, you will be prompted to install a web plugin.  This is a one-time installation.  A Viewlet demo about the plugin install is on the Brio website.  To view the demo, click on ‘Animated Brio Demos’ then ‘Brio Plugin Install Demo.’  There is also a Viewlet on how to build and run a query, ‘Building and Running a Basic Query.’

Brio Reports Page:      Click on “Reports Perkins”.  You will see three choices: “FY2001 Perkins Student”, “FY2002 Perkins Student”, and “FY2003 Perkins Student”.    Each additional year will continue to be added.

To Start:

Click on the “Process” Button at the top of the page.  This will prompt you for your username and password again.  It may take awhile to process the query depending on the number of rows you are retrieving.  The results will be returned one row per student. 

Columns in the Results Section:

• Fyr: Fiscal Year
• Inst ID: Institution identification
• Inst Name: Institution Name
• Campus ID: Campus Identification
• Tech ID: Student Identification
• Last Name
• First Name
• Age:  Age of the student
• Unknown Gender: 1=Gender of Student is Unknown
• Male: 1=Male
• Female: 1=Female
• Unknown Ethnic: 1=Ethnicity of Student is Unknown
• African American: 1=African American
• Asian or Pacific: 1=Asian or Pacific
• Caucasian: 1=Caucasian
• Hispanic: 1=Hispanic
• Native or Alaskan: 1=Native or Alaskan
• Disability: If the Disab_End date is greater than 0, the student is flagged with a 1 
• Econ Disadv: If the student is economically disadvantaged, and they have a declared major in the system, the student is flagged with a 1 in this column.
• First Adm Stat: The student's first admission status, example: 21-regularly admitted student.
• First Yrtr: The student's first year and term as a student
• Max Yrtr:     The student's highest term as a student in the corresponding fiscal year.  For example, if a student became a completer in fall of FY02, their Max Yrtr would be 20023.  For FY02, you should see either 20021, 20023, or 20025.

The next 11 columns (to the left of the STATUS column) with a purple column heading background, are major information:

• Major Cluster Nbr: The corresponding number of one of the 16 national clusters the student's major falls in.
• Major Cluster Name: The name of the perkins cluster the student's major falls in.
• Major Degree Pursue: The degree the student is pursuing, example: DIP=diploma
• Major Desc: The name/description of the student's major
• Major Cr Reqd: The number of credits the student's major requires to receive a degree
• Major CIP Code: The 6-digit cip code assigned to each major
• Major CIP Code Ext: 2-digit MN extension.  This 2-digit code enables finer distinctions among programs.  It indicates when a major is different within a 6-digit CIP.
• Major CIP Long Name: CIP Name
• Major Perk Elig Ind: Y=perkins eligible major
• Major Gender Equity: M=Non-traditional male major, e.g.; Nursing, F=Non-Traditional female major, e.g.; Drafting, B=Balanced major, not non-traditional
• Nbr of Majors: The number of majors the student has in the system

• Status:
• Completer - Student who has completed a program
• Concentrator - Student who has completed at least 33% of the program requirements and has a declared major in the system.
• Participant - Student taking at least one perkins eligible course or has a perkins eligible major
• Type Unknown – A student who does not have a declared major in the system and/or the major doesn't have a cip code or the correct cip code
• Not Perkins - Student who is enrolled in a non-perkins eligible program, i.e., liberal arts

The next 11 columns (to the right of the STATUS column) with a red column heading background, are award information (these are the completer students):

• Award Cluster Nbr: The corresponding number of one of the 16 national clusters the student's award falls in.
• Award Cluster Name: The name of the perkins cluster the student's award falls in.
• Award: The award the student received, i.e.; DIP, AAS, AS, CERT
• Award Desc: The name/description of the student's award received
• Award Cr Reqd: The number of credits the student's award requires to receive a degree (you will see a "0" for students who did not receive an award)
• Award CIP Code: The 6-digit cip code assigned to each award
• Award CIP Code Ext: 2-digit MN extension.  This 2-digit code enables finer distinctions among programs.  It indicates when an award is different within a 6-digit CIP.
• Award CIP Long Name: CIP Name
• Award Perk Elig Ind: perkins eligible award
• Award Gender Equity:  M=Non-traditional male program, e.g.; Nursing, F=Non-Traditional female program, e.g.; Drafting, B=Balanced program, not non-traditional
• Nrb of Awards: The number of awards received

• Total Pell Grants: If the student is a pell grant recipient, they are flagged with a "1"
• Perkins Pell Grants: If the student is a pell grant recipient and they have a perkins eligible major and/or an award in the system, they are flagged with a "1”
• 5000 Limited English: 1= Yes
• 5001 Academic Disadvant: 1= Yes
• 5002 Single Parent: 1=Yes
• 5003 Displaced Homemaker: 1=Yes
• GPA: GPA of the student

The next 6 columns with the green column heading background are student’s accumulated credits:

• Fy Cr: The number of credits the student has earned in the fiscal year (in this case it is FY2002)
• Accum Cr: The number of credits the student has earned to date (used to calculate status)
• Fy Develop Cr: The number of developmental credits the student has earned in the fiscal year
• Accum Develop Cr:  The number of developmental credits the student has earned to date
• Fy Trans Cr: The number of transfer credits the student tranferred this current fiscal year
• Accum Trans Cr: The number of transfer credits the student has transferred to date.

• 1P1 NT Completers: All program completers in the fiscal year
• 1P1 DB Concentrators: All program completers and concentrators in the fiscal year.

***You can calculate your 1P1 percentage by using the total of 1P1 NT over 1P1 DB

• 4P1 NT Non-Trad Participants: All male or female participants in identified under-represented (non-traditional) programs within the fiscal year.  E.g.; males in a nursing program, or females in a welding program
• 4P1 DB Non-Trad Participants: All participants in a non-traditional program.

***You can calculate your 4P1 percentage by using the total of 4P1 NT over 4P1 DB

• 4P2 NT Non-Trad Completers: All male or female completers in identified under-represented (non-traditional) programs within the fiscal year.
• 4P2 DB Non-Trad Completers: All completers in a non-traditional program.

***You can calculate your 4P2 percentage by using the total of 4P2 NT over 4P2 DB

• Last Update: The date and time of the last report update

Five Pivot Tables

1. Inst Summary:                       This table provides aggregate totals of each data element by status (completer, concentrator, participant, type unknown, and Not Perkins).  **Most users have access to view and analyze the data for only their institution.  If so, they will only see their institution in this and all pivot tables.

2. Clusters:                               This table provides aggregate totals of each data element by each cluster and then by status (completer, concentrator, participant, type unknown, and Not Perkins). 

3. Pell Status:                           This table provides the total number of pell grant recipients, and the total number of perkins eligible pell grant recipients with a declared major.  The table provides this information by status (completer, concentrator, participant, type unknown, and Not Perkins). 

4. 4P1 Non-Trad Participants:     This table provides aggregate totals of each data element for both 4P1 Numerator (NT) and 4P1 Denominator (DB).  The “Yes” indicates those students that fall in either 4P1 Numerator and/or 4P1 Denominator.  The denominator total also includes those students from the numerator. 

5. 4P2 Non-Trad Completers:      This table provides aggregate totals of each data element for both 4P2 Numerator (NT) and 4P2 Denominator (DB).  The “Yes” indicates those students that fall in either 4P2 Numerator and/or 4P2 Denominator.  The denominator total also includes those students from the numerator. 

HOW TO:

Add Totals in a Pivot Table:    In order to add totals for columns in a pivot table, click on the gray handle at the bottom of a side label.  For example, in the Inst Summary pivot table, there is a gray handle at the bottom of the “Inst ID” and “Status” side label columns.  Click there, do a right-mouse click, and choose “Add Totals” (third option).  This will add the totals at the bottom of the table. 

Insert Pivot Tables:                  In the menu toolbar at the top of the screen, choose the fourth option, “Insert”.  From this drop-down list, you are able to insert a new query, table, pivot, chart, or report.  Once you insert a new pivot table, you can drag and drop fields from the Inst Results (Query) on the left-hand side (Sections Panel).  You can build a table by adding fields to the Side Labels, Top Labels, and Facts.

Duplicate Pivot Tables:            If you’d rather create your own pivot table from one of the five already created, you may do so by clicking on the pivot table you wish to duplicate, do a right-mouse click and you’ll be given three options; Delete Section, Rename Section, and Duplicate Section.  You may get rid of the pivot table altogether by choosing Delete Section, or you can duplicate the table, make the change you’d like to make and then choose Rename Section to give it a new name.

Chart a Pivot:                           You may chart any pivot by choosing the “Insert” command in the menu toolbar at the top of the screen and select, “Chart This Pivot”.  Once there, you may change the chart to suit your analysis.  There are numerous options, such as pie chart, horizontal bar, vertical cluster, etc.  The icon that allows you to make these changes is located on the last toolbar at the top of the page.  The icon looks like a miniature chart.  **One thing to note, if you chart a pivot with a lot of data, the chart will be unreadable.  Be sure to only chart pivots that have manageable amounts of data.

Helpful Functions in Results Section

Limit
To limit the data to a certain campus, status or even gender, click on the gray bar at the top of the column you’d like to limit and do a right-mouse click and click “Limit” (first choice).  You will receive a limit box.  Click on “Show Values” on the left-hand side and then choose what you’d like to limit.  This is a very handy tool when trying to focus on a small sub-population of students.

Sort Ascending or Descending
You can sort any column you choose in ascending or descending order.  Click on the gray bar at the top of the column you’d like to sort, do a right mouse click and you will be given two choices; “Sort Ascending” (A-Z), or “Sort Descending” (Z-A).  This is helpful if you’d like to sort students by tech id’s, by campus if you’re a multi-campus institution, or by cip codes, majors or degrees.

Add Computed Item/Hide, Unhide Columns/Modify Column

You can create new columns by computing an already existing column.  For example, the Disability column was created from the field, “Disab_End”, and the formula used is (Disab_End > 0) {1} else {0}.  This reads, if the disab_end field is greater than 0, make it a 1, else make it a 0.  Many of the columns in the result section were computed from other columns.  In order to compute an item, click the gray top bar of the column you wish to compute from and do a right-mouse click and choose “Add Computed Item”.  A box will pop up where you can write your formula.  There are icons below that can assist with the formula.  At the top of the box, there is a text field that says, “Computed”.  If you highlight this word, you can change the name to whatever you choose. 

Once you have computed a new column from an already existing column, you may hide the column if you no longer would like it displayed in your result section.  Simply click on the column you wish to hide (gray bar at the top of the column) and do a right-mouse click.  Scroll down to “Hide Column”.  If for some reason, you’d like the column back to either compute another column or you decide you’d like to display it in your results, you can unhide the column by doing the same thing, but choose “Unhide Column” instead.

Once you have created a computed item in a column, you have the option of modifying the formula.  Simply click on the gray bar of the column you’d like to either view or change, do a right-mouse click and choose “Modify Column”.  This will allow you to view the existing formula for that column, and/or change it. 

Grand Total

You can insert a column total at the bottom of the pivot table.  Simply click on the column you’d like a total for, do a right-mouse click and choose “Grand Total”.  You will receive a box that asks what type of grand total function you’d like to perform.  The grand total function will default to “Sum”, however, you may choose average, count, maximum, etc.  If you’d like to run a total, leave the default set to sum; you then have the option of choosing what columns you’d like a total for.  You can choose just one or highlight as many columns as you’d like by holding down the control key and clicking on multiple fields.

Numbers

You can change the way numbers are displayed, by clicking on the column, doing a right-mouse click and choosing the “Number” option.  (This option is toward the bottom).  Simply choose which format you prefer.  One thing to note, it will not change every column with numbers, only the column you have highlighted.  You can choose multiple columns by either holding the control key down and choosing multiple columns, or go to the “Edit” command in the menu toolbar and choose “Select All”.  This will highlight every column in the results section.

Saving a Brio Document

In order to save a brio document, click on “File” in the menu toolbar at the top of the screen and choose “Save Options”.  Scroll over and choose the “Save Query Results With Document” option.  Make sure that the “Query” box and the “Inst Results” box are checked.  If you don’t do this first, the next time you open up this document, you will not have any results and you will have to re-process.  When you re-process, it refreshes your data with the data from the last upgrade (the data is upgraded weekly).  So if you wanted the data from the point in time that you saved it, you will have lost it.  After you complete the Save Options, go to file, “Save As”.  You can save the document anywhere you’d like.  Just as an FYI…the extension for a Brio document is .bqy.  When you re-open the saved .bqy document, you will re-open it within Netscape Navigator.

Exporting from Brio to Excel

In order to export a brio document into an excel document, click on “File” in the menu toolbar at the top of the screen and choose “Export” and then choose “Section”.  The file type should automatically default to excel, but if it doesn’t, you may choose that from the drop-down list.  Once you save the document you can open it up in excel.  You can then format the spreadsheet.