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 2014

Presented by:

Lewis-Clark State CollegeCeleste McCormick, Sandra Boyd – IT

Lewis-Clark State CollegeOverviewFounded 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…


…now requires joining data from all of these tables.


IT can write a complex SQL query to perform the joins and incorporate all of the desired elements…


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 resourceExample: 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_VIEWSELECT

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_IDinner join PERSON as p WITH (NOLOCK) On RESIDENTS = p.IDWHERE RESIDENTS IS NOT NULL

Computed Columns -vs- T-SQL codeUsing Computed Columns will s..l..o..w down 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_VIEWasSelect 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 QuerySSMS: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

You might need to make changes based on their feedback.

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.”

Advantages of reporting views

1. Create and manage reporting views using the Colleague software2. Can combine data from several tables 3. Users do not need to know SQL language to join tables, the view

does it for the users

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 - [email protected]• Sandra Boyd – [email protected]

presentation to nweug 2014 presented by: lewis-clark state college

Documents