1 / 34

Reporting Tips and Techniques From a FOCWIZARD

Reporting Tips and Techniques From a FOCWIZARD. Noreen Redden Information Builders. Reporting Tips and Techniques. HEADINGs (and SUBHEADs) Centering only Certain Lines SUBHEADs When YOU want them (not when FOCUS does) Report Body SUBTOTALs and SUMMARIZEs One Line Only Please

risa
Download Presentation

Reporting Tips and Techniques From a FOCWIZARD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Reporting Tips and Techniques From a FOCWIZARD Noreen Redden Information Builders

  2. Reporting Tips and Techniques • HEADINGs (and SUBHEADs) • Centering only Certain Lines • SUBHEADs When YOU want them (not when FOCUS does) • Report Body • SUBTOTALs and SUMMARIZEs • One Line Only Please • Only the word TOTAL, no BY Value • SUBTOTALs By PAGE, Not Sort Grouping • OVER for BY fields? • ACROSS • “labels” • ACROSS Summarization .

  3. Reporting Tips and Techniques Center? HEADING SUBHEAD No Value

  4. Reporting Tips and Techniques One Line

  5. Reporting Tips and Techniques

  6. Reporting Tips and Techniques

  7. HEADINGs and SUBHEADs

  8. Reporting Tips and Techniques • Center Only One Line of HEADING • Style Sheet HEADING "Run Date: &DATEDMYY " "Page: <TABPAGENO " "Report of Job History for Employees of The XYZ Corporation" "<Hgroup " " " " <HArea " "Department:<+0> <DEPT <+0> " " <+0> " … TYPE=HEADING, LINE=3, JUSTIFY=CENTER, $

  9. Reporting Tips and Techniques • Center Only One Line of HEADING • Fixed Format (No Style Sheet) HEADING CENTER "RUN DATE: &DATEDMYY <200" "PAGE: <TABPAGENO <200 " "REPORT OF JOB HISTORY FOR EMPLOYEES OF THE XYZ CORPORATION" "<HGROUP " " " " <HAREA " "DEPARTMENT: <DEPT "

  10. Reporting Tips and Techniques • HEADINGS appear at the top of each page, before Column Headings. • SUBHEADs appear at control breaks, after Column headings. • How to get a SUBHEAD ONLY when the control break is in the middle of a page, not at the start of the page? • USE TABPAGENO and WHEN

  11. Reporting Tips and Techniques … COMPUTE XYZ/A5 = EDIT(TABPAGENO); NOPRINT COMPUTE ABC/A5 = LAST XYZ; NOPRINT ON DEPT SUBHEAD " " "SUBHEAD DEPARTMENT:<+0> <DEPT " " " WHEN ABC EQ XYZ HEADING CENTER "RUN DATE: &DATEDMYY <200" "PAGE: <TABPAGENO <200 " "REPORT OF JOB HISTORY FOR EMPLOYEES OF THE XYZ CORPORATION" "<HGROUP " " " " <HAREA " "DEPARTMENT:<+0> <DEPT "

  12. Reporting Tips and Techniques RUN DATE: 07/08/2007 PAGE: 2 REPORT OF JOB HISTORY FOR EMPLOYEES OF THE XYZ CORPORATION REGIONAL AREA: CENTRAL DEPARTMENT: ADMIN SERVICES ID NUMBER SALARY XYZ ABC --------- ------ --- --- 000000180 $25,400.00 00002 00001 *TOTAL $25,400.00 SUBHEAD DEPARTMENT: MARKETING 000000040 $62,500.00 00002 00002 *TOTAL $62,500.00

  13. Report Body

  14. Reporting Tips and Techniques • Title for SUBTOTAL, + Value of Sort Field Must fit under displayed Sort Fields, or the line will break. TABLE FILE EMPDATA PRINT NAME SALARY AS 'Salary' … BY Grouping NOPRINT BY AREA NOPRINT BY DEPT NOPRINT BY PIN AS 'ID Number' … ON AREA SUBTOTAL AS '*TOTAL AREA' ON DEPT SUBTOTAL AS '*TOTAL'

  15. Reporting Tips and Techniques

  16. Reporting Tips and Techniques • Change Alpha Verb Objects to BY fields where possible. TABLE FILE EMPDATA PRINT -* NAME SALARY AS 'Salary' … BY Grouping NOPRINT BY AREA NOPRINT BY DEPT NOPRINT BY PIN AS 'ID Number‘ BY NAME … ON AREA SUBTOTAL AS '*TOTAL AREA' ON DEPT SUBTOTAL AS '*TOTAL'

  17. Reporting Tips and Techniques

  18. Reporting Tips and Techniques • Create a “Dummy” BY field with Blank value. DEFINE FILE EMPDATA BLANK/A1 = ‘ ‘; END TABLE FILE EMPDATA PRINT -* NAME SALARY AS 'Salary' … BY Grouping NOPRINT BY AREA NOPRINT BY DEPT NOPRINT BY BLANK NOPRINT BY PIN AS 'ID Number‘ BY NAME … ON AREA SUBTOTAL AS '*TOTAL AREA' ON BLANK SUBTOTAL AS '*TOTAL'

  19. Reporting Tips and Techniques

  20. Reporting Tips and Techniques • Subtotals By Page TABLE FILE EMPDATA PRINT SALARY AS 'Salary' COMPUTE XYZ/A5 = EDIT(TABPAGENO); NOPRINT COMPUTE ABC/A5 = LAST XYZ; NOPRINT COMPUTE RTOT/D12.2M = IF XYZ EQ ABC THEN RTOT + SALARY ELSE SALARY; NOPRINT COMPUTE RTOTA/A30 = FTOA(RTOT,'(D12.2M)','A30'); NOPRINT COMPUTE CTR/I5 = IF XYZ EQ ABC THEN CTR + 1 ELSE 1; NOPRINT COMPUTE ACTR/A5 = FTOA(CTR,'(D5c)','A5'); NOPRINT BY Grouping NOPRINT PAGE-BREAK BY AREA BY DEPT BY BLANK NOPRINT BY PIN AS 'ID Number' FOOTING BOTTOM "Page Totals: Number of Employees <ACTR Total Salary <RTOTA " ON BLANK SUBTOTAL AS 'Total‘ ON AREA SUBTOTAL AS ‘Total Area’

  21. Reporting Tips and Techniques

  22. Reporting Tips and Techniques • The Report is Too Wide TABLE FILE EMPDATA PRINT OLDSALARY AS 'Salary' BY PIN AS 'ID Number' BY Name BY SALARY AS 'Current Salary' BY HIREDATE AS 'Hire Date' BY HIGHEST EFFECTDATE AS 'Effective Date' ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML END

  23. Reporting Tips and Techniques

  24. Reporting Tips and Techniques TABLE FILE EMPDATA PRINT COMPUTE ASAL/A30 = FTOA(SALARY, '(D12.2M)', 'A30'); NOPRINT COMPUTE CTR/I3 = IF PIN EQ LAST PIN THEN CTR + 1 ELSE 1; NOPRINT COMPUTE ACTR/A3 = EDIT(CTR); NOPRINT COMPUTE COL1/A30 = IF CTR EQ 1 THEN PIN ELSE IF CTR EQ 2 THEN Name ELSE IF CTR EQ 3 THEN ASAL ELSE ' '; AS ' ' EFFECTDATE AS 'Effective Date' OLDSALARY AS 'Salary' BY PIN NOPRINT BY HIGHEST EFFECTDATE NOPRINT ON PIN SUBFOOT "<Name" WHEN ACTR LE '001'; ON PIN SUBFOOT "<ASAL" WHEN ACTR LE '002'; ON PIN SKIP-LINE

  25. Reporting Tips and Techniques

  26. Reporting Tips and Techniques • Another Too Wide Report. TABLE FILE EMPDATA SUM SALARY BY DIV BY DEPT ACROSS PIN END

  27. Reporting Tips and Techniques • Create a Column based on Sort Order FILEDEF WIZ1 DISK C:\IBI\APPS\wizards\wiz1.ftm -RUN SET ACROSSLINE = OFF TABLE FILE EMPDATA SUM SALARY BY DIV BY DEPT BY PIN ON TABLE HOLD AS WIZ1 END -RUN DEFINE FILE WIZ1 COL/I5 WITH PIN = IF DIV NE LAST DIV THEN 1 ELSE IF DEPT NE LAST DEPT THEN 1 ELSE IF COL GT 3 THEN 1 ELSE COL + 1; ROW/I5 WITH PIN = IF COL EQ 1 THEN ROW + 1 ELSE ROW; END TABLE FILE WIZ1 SUM PIN OVER SALARY BY DIV BY DEPT SKIP-LINE BY ROW NOPRINT ACROSS COL NOPRINT END

  28. Reporting Tips and Techniques

  29. Reporting Tips and Techniques • ACROSS SUMMARIZE (NF 7.6) DEFINE FILE EMPDATA JOB1/A3 = EDIT(JOBCLASS,'999'); SDEPT/A10 = EDIT(DEPT,'9999999999'); END TABLE FILE EMPDATA SUM CNT.PIN AS 'POP' OVER SALARY AS 'SALARY' OVER COMPUTE AVERAGE = C2 /C1; BY DIV NOPRINT SUBHEAD "DIVISION: <DIV" BY SDEPT AS DEPARTMENT ACROSS JOB1 AS 'JOBCLASS' ON JOB1 SUMMARIZE WHERE JOB1 EQ '019' OR '257' END

  30. Reporting Tips and Techniques PAGE 1 JOBCLASS 019 257 TOTAL DEPARTMENT ------------------------------------------------------------------------ DIVISION: CE MARKETING POP 0 1 1 SALARY . $62,500.00 $62,500.00 AVERAGE . 62,500.00 62,500.00 PERSONNEL POP 1 0 1 SALARY $45,000.00 . $45,000.00 AVERAGE 45,000.00 . 45,000.00 SALES POP 2 1 3 SALARY $169,100.00 $43,000.00 $212,100.00 AVERAGE 84,550.00 43,000.00 70,700.00 DIVISION: CORP ACCOUNTING POP 3 0 3 SALARY $224,500.00 . $224,500.00 AVERAGE 74,833.33 . 74,833.33

  31. Reporting Tips and Techniques • COMPUTE after ACROSS TABLE FILE EMPDATA SUM CNT.PIN NOPRINT SALARY NOPRINT COMPUTE NAV = C2/C1; NOPRINT BY DIV NOPRINT BY SDEPT AS DEPARTMENT SUM CNT.PIN AS 'POP' OVER SALARY AS 'SALARY' OVER COMPUTE AVERAGE = SALARY/CNT.PIN; BY DIV NOPRINT SUBHEAD "DIVISION: <DIV" BY SDEPT AS DEPARTMENT ACROSS JOB1 AS 'JOBCLASS' COMPUTE TOTAL = C1; OVER COMPUTE SALARY = C2; OVER COMPUTE AVERAGE = C3; WHERE JOB1 EQ '019' OR '257' END

  32. Reporting Tips and Techniques PAGE 1 JOBCLASS 019 257 TOTAL DEPARTMENT ------------------------------------------------------------------------ DIVISION: CE MARKETING POP 0 1 1 SALARY . $62,500.00 $62,500.00 AVERAGE . 62,500.00 62,500.00 PERSONNEL POP 1 0 1 SALARY $45,000.00 . $45,000.00 AVERAGE 45,000.00 . 45,000.00 SALES POP 2 1 3 SALARY $169,100.00 $43,000.00 $212,100.00 AVERAGE 84,550.00 43,000.00 70,700.00 DIVISION: CORP ACCOUNTING POP 3 0 3 SALARY $224,500.00 . $224,500.00 AVERAGE 74,833.33 . 74,833.33

  33. The End Thanks

More Related