FOCUS for SIS Beginning Report Writing

1. FOCUS for SIS Beginning Report Writing FOCUS for SIS Beginning Report Writing

2. FOCUS for SIS Beginning Report Writing Cheri Harwell (775) 784-4357(help) support@nevada.edu • Create Detail Reports against SIS data. • Basic Calculations in Reports. • Generate Summary Reports. Name and Institution, Experience with FOCUS, SIS/Job Submittal, Expectations

3. Agenda FOCUS for SIS Beginning Report Writing • Agenda • Overview • Write a Report • Formatting • Data Selection • New Fields • Summary Reports • More Summary Reports • Multiples • Bonus

4. 1. Overview FOCUS for SIS Beginning Report Writing • 1. Overview • What is Focus • Databases, Tables and Fields • Master File Descriptions – MFD • Getting in to FOCUS • TED

5. FOCUS for SIS Beginning Report Writing 1.A. 1.A. What is Focus Information Builders Product www.informationbuilders.com Information Builders provides all of the software and services to develop complete state-of-the-art information systems for turning data into useful business intelligence for real-time management of business. We call our solutions "i-business": the ability to get to any data in the enterprise, turn it into information, and deliver it so that people can use it to make their businesses more intelligent. Reporting Tool

6. FOCUS for SIS Beginning Report Writing 1.B.1 1.B. Databases, Tables and Fields SIS Database Table/File TOOK NAME PID TERM CLASS GRADE ADDR Fields CLAS

7. FOCUS for SIS Beginning Report Writing 1.B.2 1.B. Databases, Tables and Fields PERSON ID NAME __________ ___________ L000007774 SIMPSON, BARTHOLOMEW L II L000007772 MAYBELL, MARTHA L000007773 LIZARD, LARRY What is a record?

8. FOCUS for SIS Beginning Report Writing 1.C.1 1.C. Master File Descriptions - MFD FILENAME=NAME,SUFFIX=PRIVATE,$ SEGMENT=ROOT,$ GROUP=NAMEKEY , ALIAS=KEY, USAGE=A010 , ACTUAL=A010,$ FIELD=NAMEPID , ALIAS=NAMEPID , USAGE=A010 , ACTUAL=A010, TITLE='PERSON,ID' ,$ FIELD=NAMELTNM , ALIAS=NAMELTNM , USAGE=A025 , ACTUAL=A025, TITLE='LAST,NAME' ,$ FIELD=NAMEFRSTNM , ALIAS=NAMEFRSTNM , USAGE=A015 , ACTUAL=A015, TITLE='FIRST,NAME' ,$ FOCUS MFD Type: TED MASTER(filename) FILE=NAME FIELD USAGE USAGE ACTUAL NAME FORMAT LENGTH FORMAT TITLE NAMEKEY ALPHA 10 A010 NAMEPID ALPHA 10 A010 'PERSON,ID' NAMELTNM ALPHA 25 A025 'LAST,NAME' NAMEFRSTNM ALPHA 15 A015 'FIRST,NAME' X/NET Report

9. FOCUS for SIS Beginning Report Writing 1.C.2 1.C. Master File Descriptions - MFD Formats A-Alphanumeric N-Numeric I-Integer Actual P-Packed to conserve space

10. FOCUS for SIS Beginning Report Writing 1.C.3 1.C. Master File Descriptions - MFD Key Length

11. FOCUS for SIS Beginning Report Writing 1.C.4 1.C. Master File Descriptions - MFD SIS Screens vs. FOCUS Tables

12. FOCUS for SIS Beginning Report Writing 1.D.1 1.D. Getting in to FOCUS 1 Logon Mainframe 2 Select TSOSIS 3 F;S;region

13. FOCUS for SIS Beginning Report Writing 1.D.2 1.D. Getting in to FOCUS

14. FOCUS for SIS Beginning Report Writing 1.D.3 1.D. Getting in to FOCUS TED FOCEXEC(pgmname) Open a FOCEXEC Create a new FOCEXEC Run a FOCEXEC FIN to Exit FOCUS

15. FOCUS for SIS Beginning Report Writing 1.E.1 1.E. TED – Text Editor Commands TED is the FOCUS text editor that is used to create and change your procedures called FOCEXECs. Line Commands Primary Command Line

16. FOCUS for SIS Beginning Report Writing 1.E.2 1.E. TED – Text Editor Commands Primary Command Line GET FOCEXEC(pgmname) SAVE or FILE TOP or BOTTOM F7 (back) F8 (forward) F1 (help) LOCATE/ TED MASTER(file) RUN (please use caution) QUIT or F3 QQUIT (changes are cancelled) Line Commands (Prefix Area) I / I# Insert D/DD/D# Delete/Block Delete C/CC/C# Copy/Block Copy M/MM Move/Block Move P Preceding F Following ” Repeat Line

17. FOCUS for SIS Beginning Report Writing 1.E.3 1.E. TED – Text Editor Commands

18. 1. Review FOCUS for SIS Beginning Report Writing 1. Review FOCUS is a reporting tool. Data is stored in tables and fields which are defined by MFD’s. Format of field can be Alphanumeric, Numeric, Integer or Date. Key defines the unique identifier of a table. Start FOCUS. TED is the text editor for FOCUS. Primary and Line Commands of TED. Exit FOCUS using FIN command.

19. 2. Reporting Basics FOCUS for SIS Beginning Report Writing • 2. Write a Report • Basic Rules • Print • Sort • Select

20. FOCUS for SIS Beginning Report Writing 2.A.1 2.A. Basic Rules • All you need for a FOCUS report are: • Data • File Description • FOCEXEC Running online isn’t recommended, unless you run against QA data or have a record limit defined. Use the batch methods with FOCBAT or Job Submittal to run Production reports.

21. FOCUS for SIS Beginning Report Writing 2.A.2 2.A. Basic Rules FOCEXEC Report

22. FOCUS for SIS Beginning Report Writing 2.A.3 2.A. Basic Rules 00000 * * * TOP OF FILE * * * 00001 -* SAMPLE FOCEXEC 00001 TABLE FILE NAME 00002 PRINT 00003 NAMEFRSTNM 00004 NAMEDTOFBTH 00005 NAMEPID 00006 END 00007 * * * END OF FILE * * * Required Components TABLE FILE filename Verb – PRINT display all records END – must be on separate line. At least one space between words is required. Recommend using indentation, but not required.

23. FOCUS for SIS Beginning Report Writing 2.B. 2.B. Print 00000 * * * TOP OF FILE * * * 00001 TABLE FILE filename 00002 PRINT 00003 NAMEFRSTNM 00004 NAMEDTOFBTH 00005 NAMEPID 00006 BY NAMELTNM 00007 END 00008 * * * END OF FILE * * * Sort Command – BY fieldname 00000 * * * TOP OF FILE * * * 00001 TABLE FILE filename 00002 PRINT 00003 NAMEFRSTNM 00004 NAMEDTOFBTH 00005 NAMEPID 00006 00007 END 00008 * * * END OF FILE * * * Print is a Verb – Only 1 Verb Objects – Fields to Print

24. FOCUS for SIS Beginning Report Writing 2.C.1 2.C. Sort No Sort Sorted BY statement is placed after the PRINT fields, although it prints first and in the order listed. Up to 31 Sort Phrases.

25. FOCUS for SIS Beginning Report Writing 2.C.2 2.C. Sort Change Sort Direction RESI sample with 2 sort fields. Multiple Sort Fields

26. FOCUS for SIS Beginning Report Writing 2.C.3 2.C. Sort Invisible Sort Field -Every occurrence is displayed -Move sort field to print where desired. Repeat Field

27. FOCUS for SIS Beginning Report Writing 2.D.1 2.D. Select Limit Records WHERE expression [;] Expression Simple compare of a field to a set value. If comparing to an alphanumeric field then place value in single quotes, ‘?’. Multiple Selection

28. FOCUS for SIS Beginning Report Writing 2.D.2 2.D. Select Relationship

29. 2. Review FOCUS for SIS Beginning Report Writing 2. Review Need a TABLE FILE, Verb and END statement to create FOCUS report. PRINT verb is used to list data. PRINT field field Sort in descending order with HIGHEST. Hide sort fields - NOPRINT. BY [HIGHEST] field [NOPRINT] Select with the WHERE command using EQ, NE, GT, GE, LT, LE, and others.

30. 3. Formatting FOCUS for SIS Beginning Report Writing • 3. Formatting • Column Title • Heading • Footing • Separations • Fields

31. FOCUS for SIS Beginning Report Writing 3.A. 3.A. Column Title To override the assigned title use the AS option. Use a comma to split title onto multiple lines. /C /L /R to align the column heading as center,left,right: AWRDPID/C AS ‘PERSON’ The column titles have been set for us in the MFD Title field. Column Titles

32. FOCUS for SIS Beginning Report Writing 3.B. 3.B. Heading Heading Line between Table File and End. TABLE FILE AWRD HEADING CENTER “GREAT BASIN COLLEGE” “AWARDS FOR 2001 </2” PRINT AWRDPID … END Center the heading above the report, multiple lines of text and skip two lines before the data. To add run date to heading, include &DATE within quotes.

33. FOCUS for SIS Beginning Report Writing 3.C. 3.C. Footing Footing Line between Table File and End FOOTING BOTTOM CENTER "UADCJH1.AWRDLIST" Center the footing at the bottom of the page. Recommend placing name of focexec in footing.

34. FOCUS for SIS Beginning Report Writing 3.D. 3.D. Separations Can separate sections of a report with blank lines, underlines or put them on separate pages. BY field UNDER-LINE BY field SKIP-LINE BY field PAGE-BREAK REPAGE

35. FOCUS for SIS Beginning Report Writing 3.E.1 3.E. Fields An MFD has a defined format for each field, you may change that format for reporting purposes.

36. FOCUS for SIS Beginning Report Writing 3.E.2 3.E. Fields

37. FOCUS for SIS Beginning Report Writing 3.E.3 3.E. Fields PRINT AWRDPID/C AS 'PERSON' AWRDOROFRAM/D7 AS 'ORIG AMT' AWRDCUOFRAM/D9.2M AS 'CURR AMT‘ AWRDCRSNCD AS 'REASON'

38. FOCUS for SIS Beginning Report Writing 3.E.4 3.E. Fields D9.2M Length.Decimal Examples

39. 3. Review FOCUS for SIS Beginning Report Writing 3. Review Column title is changed with the AS option. To change alignment of the column title use /C, /L, or /R. HEADING should be between TABLE FILE statement and END. Use double quotes around text. CENTER is optional. Use </# within quotes to skip lines. FOOTING statement with CENTER and BOTTOM options. Separate section options UNDER-LINE, SKIP-LINE, and PAGE-BREAK. Field definitions have 3 parts a type, length and edit option.

40. 4. Data Selection FOCUS for SIS Beginning Report Writing • 4. Data Selection • Available Operators • Alphabetic Comparison • Numeric Comparison • Multiple Criteria • Date Comparison • Field to Field Comparison

41. FOCUS for SIS Beginning Report Writing 4.A. 4.A. Available Operators WHERE expression; WHERE fieldname relation value1 [OR/AND expression] [;] Compare a field to a set value, compare field to another field, compare field calculation to value, complex relationships with AND/OR. Relation Other Options

42. FOCUS for SIS Beginning Report Writing 4.B.1 4.B. Alphabetic Comparison And/Or Conditions Examples already used WHERE RECORDLIMIT EQ 5 WHERE NAMELTNM LT ‘M’ WHERE AWRDAWYR EQ ‘2001’ Alphanumeric Comparison Value is enclosed in single quotes. WHERE RESISTCD EQ ‘NV’; WHERE RESISTCD EQ ‘NV’ OR ‘CA’; WHERE RESISTCD NE ‘NV’ OR ‘ ‘;

43. FOCUS for SIS Beginning Report Writing 4.B.2 4.B. Alphabetic Comparison Mask Trailing Characters Mask Leading Character WHERE NAMELSTNM EQ ‘SMITH$*'; WHERE RESICNTCD EQ '$W$'; Contains characters somewhere within field WHERE CLASCLNM CONTAINS ‘COMPUTER'; Omit a character string from the result. WHERE CLASCLNM OMITS ‘COMPUTER';

44. FOCUS for SIS Beginning Report Writing 4.C.1 4.C. Numeric Comparison WHERE SGPAGPAVL GT 3.5; WHERE (SGPATOTUNEN LE 60) OR (SGPATOTUNEN GE 170);

45. FOCUS for SIS Beginning Report Writing 4.C.2 4.C. Numeric Comparison Calculation in the WHERE statement.

46. FOCUS for SIS Beginning Report Writing 4.D.1 4.D. Multiple Criteria In English ask “I want Nevada and California”. AND – all must be true. OR – one must be true. WHERE RESISTCD EQ ‘NV’ OR ‘CA’; WHERE (SGPATOTUNEN GE 60) AND (SGPATOTUNEN LE 170); Inside a Range. Outside of Range.

47. FOCUS for SIS Beginning Report Writing 4.D.2 4.D. Multiple Criteria Each line is an “AND” condition. Is the same as: WHERE SGPATOTUNEN GE 80; WHERE SGPATOTUNEN LE 100; WHERE (SGPATOTUNEN GE 80) AND (SGPATOTUNEN LE 100);

48. FOCUS for SIS Beginning Report Writing 4.E. 4.E. Date Comparison SIS Dates are stored as “Numeric 9” and are formatted as YYYYMMDD. Example would be “20001031”. Can use Numeric Comparison techniques to select with these fields. Numeric edit options don’t help much to format the field. Calculations with these dates? How many days between Begin Date and End Date? 20010512-20010116= 396 days. We would like to get 116 days, cover in Chapter 5.

49. FOCUS for SIS Beginning Report Writing 4.F. 4.F. Field to Field Comparison

50. 4. Review FOCUS for SIS Beginning Report Writing 4. Review Syntax: WHERE expression; Compare Alphanumeric, Numeric, Dates, calculated values, and fields. Enclose an Alphanumeric value in single quotes. Masks can be used for character fields using the $ as a place holder. Numeric comparison can include decimal values (no quotes).