Vista Views Report Building

1. PowerCampus Version 8.3.2 Vista Views Report Building 102

2. Recap of 101 – What is Vista views? • A tool used to create custom views with Vista Reports • Users can select pre-defined tables and other custom views • Views and tables can be secured by profile, user name/operator id, or formal name(this view is not for you) • Users can add columns, create joins, develop where clauses, group by, sort, and customize data, etc

3. Recap of 101 – What is Vista views Continued • Custom views, pre-defined tables, security settings, and other important features (joins, where clauses, group by, sort, compute, syntax preview) Preview shows results from the SQL syntax tab Data Tables – Holds data on students, applicants, inquires, etc Code Tables – Hold the various user-defined code tables for your institution System Views – Hold any system-defined views which are currently on the system User Views – Hold any views that you have created System Tables – Holds the various system tables which are currently on the system • Compute allows the user to: • Customize reports for requestor’s use, personal use, or meeting other reporting needs by • Group By is a feature used to group data. Examples include: • Displaying the number of classes a student is taking • Displaying the number of terms a student has been enrolled in • Where clauses filter data. Some examples include: • Defining a specific year, term, and/or session • Defining a specific program, degree, and/or curriculum • Separating enrolled from withdrawn students • Eliminating non-credited students from results Rows can be unique by clicking Distinct Syntax tab allows the user to edit the SQL syntax directly (System Administrator Role) • Manipulate strings • Perform mathematical computations • Working with null values

4. Vista Views 102 - Joins Make All the Difference • Joins are needed when selecting more than one table • Tables need to relate to each other in order for syntax to run successfully • Joins help to relate tables to one another • Three types of joins can be created • Equal Join • Right Outer Join • Left Outer Join

5. Vista Views 102 - Joins Make All the Difference Cont. Illustration – Equal Join A&B All of the matching records from Table A and Table B Equal Join = A&B intersected

6. Vista Views 102 - Joins Make All the Difference Cont. Example – Equal Join Table A = People Table B = Telecommunications

7. Vista Views 102 - Joins Make All the Difference Cont. Illustration – Right Outer Join A&B All of the records from Table B and all the records from Table A that match Table B Right Join = Table B + (A&B) intersected

8. Vista Views 102 - Joins Make All the Difference Cont. Example – Right Outer Join Table A = People Table B = Telecommunications

9. Vista Views 102 - Joins Make All the Difference Cont. Illustration – Left Outer Join All of the records from Table A and all the records from Table B that match Table A A&B Left Join = Table A + (A&B) intersected

10. Vista Views 102 - Joins Make All the Difference Cont. Example – Left Outer Join Table A = People Table B = Telecommunications Where clause is the same as the example for the Equal Join and Right Outer Join

11. Vista Views 102 - Joins Make All the Difference Cont. Overall Results • Equal join results include students from the People Table that have alternative email addresses • Right join results have all the students from the Telecommunications that have alternative email addresses and those students in common with both tables (Telecommunications and People) • Left join results include all students from the People table with or without an alternative email address

12. Vista Views 102 – TROUBLE JOINING TABLES? Quick access Slide Phone, Address, Demographics and IPEDS Ethnicity

13. Vista Views 102 – TROUBLE JOINING TABLES? Quick access Slide Cont. Education

14. Vista Views 102 – TROUBLE JOINING TABLES? Quick access Slide Cont. GPA’s and Credits

15. Vista Views 102 – Changes In Join Tab • Join tab changes start with PowerCampus version 8.4 and higher • Joins are between Tables as opposed to Tables and Columns • Left Outer, Right Outer, and Equal Joins can be set up between Tables • Columns will have to be set up for Left and Right Outer Joins • Equal Joins are Cross Joins instead of Inner Joins (Cross Joins act like an Inner Joins with a Where Clause) • Filters have been added for Outer Join Tables. “And” is given for all filters • Syntax for Vista Views are now written in new SQL. Square brackets are used to differentiate between Tables, Columns, and custom column names

16. Vista Views 102 – Changes In Join Tab Cont.

17. Vista Views 102 – Changes In Syntax

18. Vista Views 102 – Modifications Case When Examples – Undergraduate Population

19. Vista Views 102 – Modifications Cont. Left, Right, and Substring Examples

20. Vista Views 102 – Useful WEB Resources for modifications Websites: Stackover flow - http://stackoverflow.com/ Sql Server Helper - http://www.sql-server-helper.com/default.aspx Microsoft Developer Network - http://msdn.microsoft.com/library W3schools - http://www.w3schools.com/sql/default.asp Google – http://www.google.com (your problem sql or sql your problem)

21. Vista Views 102 - References • PowerCampus Vista User Guide Release 8.1, June 2012 • PowerCampus Release Guide Release 8.4, November 2013

22. Vista Views 102 - Discussion Questions/Comments Contact Information Name: Anthony Williams Title: Dir. Institutional Research College: Metropolitan College of New York Email: awilliams@mcny.edu Office #: 212-343-1234 ext 2830