Presentation to NWEUG 2014 Presented by: Lewis-Clark State College

How to develop reporting views using SSMS (SQL Server Management Server) and create and deploy the reporting view using the Colleague Application Presentation to NWEUG 2014Presented by: Lewis-Clark State College Celeste McCormick, Sandra Boyd – IT

Lewis-Clark State CollegeOverview Founded 1893, public undergraduate college located in Lewiston, Idaho. Approx. 4500 enrolled students, 3000 FTE Colleague users Administrators: 4Power Users: 3Report Writers/Runners: 40Other users: ~150

What is a reporting view? What was once a QueryBuilder query using one table and many computed columns…

What is a reporting view? …now requires joining data from all of these tables.

What is a reporting view? IT can write a complex SQL query to perform the joins and incorporate all of the desired elements…

What is a reporting view? into a “reporting view” that looks like a single table that report writers can query against:

Identify users’ needs for reporting views • Identify the key report writers • Examine and assess their current slate of reports • Brainstorm future needs • List the Reporting Views that will be needed • Determine the columns to include in each view This is not an exact science. Each report writer will conceptualize this approach in a different way.

Develop and test reporting views in SSMS(One record per address type for a person)

Get help/ideas from delivered Ellucian views when developing your SQL code:Note : searching the internet on T-SQL is also a great resource Example: In SSMS, Right Click on dbo.PERSON_ADDRESSS_VIEW->Script View as->Create to->New Query Editor window:

Separating out multi-part key fields:Example: dbo.STUDENT_TERMS_VIEW SELECT LEFT(STUDENT_TERMS_ID,CHARINDEX('*',STUDENT_TERMS_ID+'*')-1) as STTR_STUDENT, LEFT(SUBSTRING(STUDENT_TERMS_ID,(CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999), CHARINDEX('*',SUBSTRING(STUDENT_TERMS_ID, (CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999)+'*')-1) as STTR_TERM, LEFT(SUBSTRING(SUBSTRING(STUDENT_TERMS_ID, (CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999), (CHARINDEX('*',SUBSTRING(STUDENT_TERMS_ID,(CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999)+'*')+1),999), CHARINDEX('*',SUBSTRING(SUBSTRING(STUDENT_TERMS_ID,(CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999), (CHARINDEX('*',SUBSTRING(STUDENT_TERMS_ID, (CHARINDEX('*',STUDENT_TERMS_ID+'*')+1),999)+'*')+1),999)+'*')-1) as STTR_ACAD_LEVEL FROM dbo.STUDENT_TERMS WITH (NOLOCK)

Sub-Select:Example: dbo.ADMISSIONS_VIEW SELECT APPL_APPLICANT, APPLICATIONS_ID, (Select LAST_NAME from PERSON WITH (NOLOCK) where ID = APPL_APPLICANT) AS LAST_NAME, (Select FIRST_NAME from PERSON WITH (NOLOCK) where ID = APPL_APPLICANT) AS FIRST_NAME, (Select MIDDLE_NAME from PERSON WITH (NOLOCK) where ID = APPL_APPLICANT) AS MIDDLE_NAME, (Select NICKNAME from PERSON WITH (NOLOCK) where ID = APPL_APPLICANT) AS NICKNAME, …. FROM APPLICATIONS as ap1 WITH (NOLOCK) WHERE APPL_APPLICANT IS NOT NULL ORDER by APPL_APPLICANT

Case Statement:Example: dbo.STUDENT_ACAD_PROGRAMS_VIEW SELECT … CASE WHEN IS_FPER_ALIEN = 'Y' OR IS_ETHNIC_ALIEN = 'Y' OR IS_RACES_ALIEN = 'Y' THEN 'Non-Resident Alien' WHEN IS_HISPANIC = 'Y' THEN 'Hispanic/Latino' WHEN RACE_COUNT >= '2' THEN 'Two or More Races' WHEN RACE_MAX = '1' THEN 'American Indian' WHEN RACE_MAX = '2' THEN 'Asian' WHEN RACE_MAX = '3' THEN 'Black or African American' WHEN RACE_MAX = '4' THEN 'Hawaiian/Pacific Islander' WHEN RACE_MAX = '5' THEN 'White' ELSE 'Unknown' END AS IPEDS_RACE_ETHNIC_DESC, ...

Concatenated _LS records:Example: dbo.L20_ADDR_PERSON_VIEW SELECT ... , STUFF((SELECT '; ' + PLS.PER_ETHNICS FROM PERSON_LS PLS WITH (NOLOCK) WHERE PLS.ID = p.ID FOR XML PATH ('')),1,2,'') AS 'PER_ETHNICS' ,STUFF((SELECT '; ' + PLS.PER_RACES FROM PERSON_LS PLS WITH (NOLOCK) WHERE PLS.ID = p.ID FOR XML PATH ('')),1,2,'') AS 'PER_RACES‘ … FROM ADDRESS_LS as al WITH (NOLOCK) inner join ADDRESS as a WITH (NOLOCK) On al.ADDRESS_ID = a.ADDRESS_ID inner join PERSON as p WITH (NOLOCK) On RESIDENTS = p.ID WHERE RESIDENTS IS NOT NULL

Computed Columns -vs- T-SQL code Using Computed Columns will s..l..o..wdown your reporting view. Use Computed Columns only if you are unable to use T-SQL code in place of the Computed Column, usually when a Computed Column calls a subroutine that cannot be rewritten in T-SQL due to the complexity. Examples: ,dbo.VW_S_CALC_AGE((select BIRTH_DATE from PERSON WITH (NOLOCK) where ID = STC.STC_PERSON_ID), GETDATE()) as 'AGE‘ ,dbo.STTR_ACTIVE_CRED(STUDENT_TERMS_ID) as 'STTR_ACTIVE_CRED‘ ,dbo.STTR_CUM_GPA(STUDENT_TERMS_ID) as 'STTR_CUM_GPA'

Remember to use WITH (NOLOCK) on all FROM and JOIN statements: SELECT …. (SELECT CTRY_DESC FROM COUNTRIES WITH (NOLOCK) WHERE COUNTRIES_ID = CITIZENSHIP) AS 'CITIZENSHIP_DESC', (SELECT CTRY_DESC FROM COUNTRIES WITH (NOLOCK) WHERE COUNTRY = COUNTRIES_ID) AS 'COUNTRY_DESC‘ ….. FROM ADDRESS_LS as al WITH (NOLOCK) inner join ADDRESS as a WITH (NOLOCK) On al.ADDRESS_ID = a.ADDRESS_ID inner join PERSON as p WITH (NOLOCK) On RESIDENTS = p.ID WHERE RESIDENTS IS NOT NULL

Create reporting views from UI (ESVS) See next slide for SQL Server script details

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L20_ADDR_PERSON_VIEW]') and OBJECTPROPERTY(id, N'IsView') = 1) exec ('create view [dbo].[L20_ADDR_PERSON_VIEW] as select ''Error'' c1, ''encountered'' c2, ''when'' c3, ''creating'' c4, ''L20_ADDR_PERSON_VIEW'' c5'); &execute& ALTER view dbo.L20_ADDR_PERSON_VIEW as Select RESIDENTS as ID ,(SELECT ADDR_EFFECTIVE_END from PSEASON WITH (NOLOCK) where PERSON_ADDRESSES = al.ADDRESS_ID and PSEASON.ID = p.ID) AS 'ADDR_EFFECTIVE_END‘ … &execute& Select statement from SSMS

Deploying reporting views from UI (DSVS)

Confirm reporting view was successfully created via SSMS (remember to refresh Views first)

Test reporting view in SSMS and MS Query SSMS: Right Click on dbo.L20_ADDR_PERSON_VIEW -> Select Top 1000 Rows

Test reporting view in SSMS and MS Query MS Query: Add table L20_ADDR_PERSON_VIEW and run queries against it:

Move reporting view to Colleague Test If your view uses any computed columns, install those first! Otherwise the installation of your view will fail. In Dev, create a savedlist that includes the name of the view:

Move reporting view to Colleague Test CDEC in Dev: Create a custom declaration to include the savedlist

Move reporting view to Colleague Test CPKG in Dev: Create a custom package to include the custom declaration Then in MSUG in Test, install the package in a group

Create data dictionary of reporting view The dictionary will aid the report writer in understanding what data the view can report • The dictionary also serves as a reference for IT in case future changes need to be made We are also documenting if a computed column is being used in the view.

Have users test reporting view Testimonials: “A report that used to take me three hours to build now takes three minutes.” “I was able to send an email to 2700 students. I couldn’t have done without the reporting view.” You might need to make changes based on their feedback.

Advantages of reporting views Create and manage reporting views using the Colleague software Can combine data from several tables Users do not need to know SQL language to join tables, the view does it for the users …and we in IT learned a lot and have become savvy in T-SQL!

Disadvantages of reporting views • Do I need to show the testimonials again? • Our disadvantages are mainly limited to the free reporting tools that we use (MS Query, MS Access) • MS Query: the window is not expandable nor user-friendly • MS Access: the reports are not easily publishable

THE END … • Questions? • Contacts: • Celeste McCormick - cmmccormick@lcsc.edu • Sandra Boyd – saboyd@lcsc.edu

Presentation to NWEUG 2014 Presented by: Lewis-Clark State College