1 / 48

FOCWIZARDS – MacGyver to Solve Your Problems: GUI or Code

FOCWIZARDS – MacGyver to Solve Your Problems: GUI or Code. Noreen Redden. The Files. FILENAME=ORDERS, SUFFIX=SQLMAC , $ SEGMENT=ORDERS, SEGTYPE=S0, $ FIELDNAME=ORDER_NUM, ALIAS=ORDER_NUM, USAGE=A5, ACTUAL=A5 ,MISSING=ON, $

coby
Download Presentation

FOCWIZARDS – MacGyver to Solve Your Problems: GUI or Code

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. FOCWIZARDS – MacGyver to Solve Your Problems: GUI or Code Noreen Redden

  2. The Files FILENAME=ORDERS, SUFFIX=SQLMAC , $ SEGMENT=ORDERS, SEGTYPE=S0, $ FIELDNAME=ORDER_NUM, ALIAS=ORDER_NUM, USAGE=A5, ACTUAL=A5 ,MISSING=ON, $ FIELDNAME=PROD_NUM, ALIAS=PROD_NUM, USAGE=A4, ACTUAL=A4 ,MISSING=ON, $ FIELDNAME=ORDER_DATE, ALIAS=ORDER_DATE, USAGE=HYYMDS,ACTUAL=HYYMDS,MISSING=ON, $ FIELDNAME=STORE_CODE, ALIAS=STORE_CODE, USAGE=A6, ACTUAL=A6 ,MISSING=ON, $ FIELDNAME=PLANT_CODE, ALIAS=PLANT_CODE, USAGE=A3, ACTUAL=A3 ,MISSING=ON, $ FIELDNAME=SALES_REP, ALIAS=SALES_REP, USAGE=A50, ACTUAL=A50 ,MISSING=ON, $ FIELDNAME=QUANTITY, ALIAS=QUANTITY, USAGE=I11, ACTUAL=I4 ,MISSING=ON, $ FIELDNAME=LINEPRICE, ALIAS=LINEPRICE, USAGE=D20.2, ACTUAL=D8 ,MISSING=ON, $ FIELDNAME=LINE_COGS, ALIAS=LINE_COGS, USAGE=D20.2, ACTUAL=D8 ,MISSING=ON, $ FIELDNAME=RETURNS, ALIAS=RETURNS, USAGE=I11, ACTUAL=I4, ,MISSING=ON, $ FIELDNAME=WARRANTYEXP, ALIAS=WARRANTYEXP, USAGE=D20.2, ACTUAL=D8 ,MISSING=ON, $ FIELDNAME=SHIPPINGCOST, ALIAS=SHIPPINGCOST, USAGE=D20.2, ACTUAL=D8 ,MISSING=ON, $ FILENAME=STORES_EN, SUFFIX=SQLMAC , $ SEGMENT=STORES_EN, SEGTYPE=S0, $ FIELDNAME=STORE_CODE, ALIAS=STORE_CODE, USAGE=A6, ACTUAL=A6 ,MISSING=ON, $ FIELDNAME=STORENAME, ALIAS=STORENAME, USAGE=A30, ACTUAL=A30, MISSING=ON, $ FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A15, ACTUAL=A15, MISSING=ON, $ FIELDNAME=REGION, ALIAS=REGION, USAGE=A25, ACTUAL=A2 MISSING=ON, $ FIELDNAME=STATE, ALIAS=STATE, USAGE=A25, ACTUAL=A25, MISSING=ON, $ FIELDNAME=CITY, ALIAS=CITY, USAGE=A25, ACTUAL=A25, MISSING=ON, $ FILENAME=INVENTORY_EN, SUFFIX=SQLMAC , $ SEGMENT=INVENTORY_EN, SEGTYPE=S0, $ FIELDNAME=PROD_NUM, ALIAS=PROD_NUM, USAGE=A4, ACTUAL=A4, MISSING=ON, $ FIELDNAME=PRODTYPE, ALIAS=PRODTYPE, USAGE=A15, ACTUAL=A15, MISSING=ON, $

  3. The First Report Requested

  4. No Problem

  5. No Problem

  6. Report 1 TABLE FILE ORDERS SUM SALES COST COMPUTE PROFIT/D12.2 = LINEPRICE – LINE_COGS; MARGIN BY REGION NOPRINT BY STATE NOPRINT BY HIGHEST TOTAL PROFIT NOPRINT BY CITY BY STORENAME HEADING "Report of Sales” "Region <REGION" "State <STATE" "For Year &Order_Year " Where Order_Year EQ &Order_Year END

  7. And Then They Asked for: No Problem

  8. Add A JOIN file; Change the Sort field …

  9. Report 1 TABLE FILE ORDERS SUM SALES COST COMPUTE PROFIT/D12.2 = LINEPRICE – LINE_COGS; MARGIN BY REGION NOPRINT BY STATE NOPRINT BY HIGHEST TOTAL PROFIT NOPRINT BY PRODTYPE HEADING "Report of Sales” "Region <REGION" "State <STATE" "For Year &Order_Year " Where Order_Year EQ &Order_Year END

  10. Can You Put Them Together? (With Totals Please)

  11. No Problem – Compound PDF • SET COMPOUND • EXEC Report1 SUM … BY REGION BY STATE BY CITY • EXEC Report2 SUM … BY REGION BY STATE BY PRODUCT • EXEC Report3 SUM … BY REGION BY STATE • Output • Report1 for All States within Region • Report2 for All States with Region • Report3 for All States within Region • OR • Report1, Report2, Report3 for New Jersey • Report1, Report2, Report3 for New York • Report1, Report2, Report3 for Pennsylvania

  12. And, of course, And “Burstable” by State

  13. Suppose We Had 3 Copies of each record, DEFINE FILE ORDERS SRT/A10 = IF Copy_number EQ 1 THEN CITY ELSE IF Copy_number EQ 2 THEN PROD_TYPE ELSE 'TOTAL' END TABLE FILE ORDERS SUM SALES COMPUTE PROFIT… BY REGION BY CITY BY Copy_number NOPRINT BY HIGHEST TOTAL PROFIT BY SRT AS '' END ORDER_NUM PROD_NUM STORE_CODE CITY STATE REGION PROFIT Copy_number

  14. The McGyver Master FILENAME=FOCMCGYV, SUFFIX=FIX, SEGNAME=ONE, SEGTYPE=S0 FIELDNAME=CONTROL, ,A1,A1,$ . . . SEGNAME=TWO, SEGTYPE=S0,PARENT=ONE , OCCURS = VARIABLE FIELDNAME=CHAR1,,A1, A1, $ FIELDNAME=CTR,ALIAS=ORDER, FORMAT=I4 , I4, $ DEFINE REPORT_NUMBER/I4 = CTR;

  15. What to do? Enter McGyver • A Record is one instance of each referenced Segment/TABLE. • DEFINE fields are created at the lowest Segment/TABLE referenced in the DEFINE expression. • McGyver: • Create a lower level segment with DEFINE based JOIN • Higher level fields are inherited by the lower level segment • Use DEFINE to use the higher level fields at the report number level.

  16. Original Data

  17. McGyver JOIN JOIN CONTROL WITH STORENAME IN orders TO UNIQUE FOCMCGYV.ROOT_SEG.CONTROL IN FOCMCGYV AS J2 END DEFINE FILE orders CONTROL/A1 WITH STORENAME = ‘X’; END

  18. Now We Have ORDER_NUM PROD_NUM STORE_CODE CITY STATE REGION PROFIT DEFINE FILE ORDERS CONTROL/A1 WITH STORENAME = 'X'; END TABLE FILE ORDERS PRINT LINEPRICE COMPUTE RECNO/I5 = RECNO + 1; AS 'Record Number' BY REGION BY STATE BY CITY BY STORENAME BY PRODTYPE BY CTR WHERE CTR LE 3 END CONTROL CHAR1 CTR

  19. Records AFTER the JOIN

  20. Let’s Look At this Logically

  21. Now We Have FILEDEF FOCMCGYV DISK -RUN … -WRITE FOCMCGYV X12345 -RUN JOIN CONTROL WITH STORENAME IN ORDERS TO UNIQUE FOCMCGYV.ROOT_SEG.CONTROL IN FOCMCGYV AS J2 END DEFINE FILE ORDERS CONTROL/A1 WITH STORENAME='X'; SRT5/A30= IF COUNTER EQ 1 THEN CITY ELSE IF COUNTER EQ 2 THEN PRODCAT ELSE IF COUNTER EQ 3 THEN ' ' ELSE ' '; SRT6/A30=IF COUNTER EQ 1 THEN STORENAME ELSE ' '; END -RUN

  22. Now We Have

  23. McGyver – Putting it All Together File FILE=FOCMCG,SUFFIX=FIX SEGNAME=ONE,SEGTYPE=S0 FIELD=CONTROL,WHICH,A1,A1,$ SEGNAME=TWO,OCCURS=VARIABLE FIELD=CHAR ,CHAR , A1,A1,$ FIELD=COUNTER,ORDER, I4,I4,$ -WRITE FOCMCG X1234 JOIN JOIN CONTROL WITH field IN host TO CONTROL IN FOCMCG AS J1 DEFINE DEFINE FILE HOST CONTROL/A1 WITH field = ‘X’; Sortfield/Axx = IF COUNTER EQ 1 THEN …

  24. No Problem - JOINs

  25. No Problem – DEFINEs DEFINE FILE ORDERS CONTROL/A1 WITH STORENAME='X'; ORDER_YEAR/YY=HPART(ORDER_DATE, 'YEAR', 'YY'); SRT5/A30= IF COUNTER EQ 1 THEN CITY ELSE IF COUNTER EQ 2 THEN PRODCAT ELSE IF COUNTER EQ 3 THEN ‘Total ' ELSE ' '; SRT6/A30=IF COUNTER EQ 1 THEN STORENAME ELSE ' '; END

  26. No Problem – TABLE TABLE FILE ORDERS SUM SRT6 AS '' LINEPRICE AS 'Sales' LINE_COGS AS 'Cost' COMPUTE Profit/D12.2 = LINEPRICE - LINE_COGS; COMPUTE Margin/D12.2 = Profit / LINE_COGS; BY REGION NOPRINT BY STATE NOPRINT BY COUNTER NOPRINT BY TOTAL HIGHEST Profit NOPRINT BY SRT5 AS '' ON STATE PAGE-BREAK ON COUNTER SUBHEAD "Stores Ranked by Profit" WHEN COUNTER EQ 1; ON COUNTER SUBHEAD "Products Ranked by Profit" WHEN COUNTER EQ 2; ON COUNTER SKIP-LINE ON SRT5 SKIP-LINE

  27. Totals on Top ??

  28. No Problem – TABLE DEFINE FILE ORDERS CONTROL/A1 WITH STORENAME='X'; … XSRT/I1 = IF COUNTER EQ 3 THEN 1 ELSE 2; END TABLE FILE ORDERS SUM … BY REGION NOPRINT BY STATE NOPRINT BY XSRT NOPRINT BY COUNTER NOPRINT …

  29. Build the Launch Page • Parameterize the Report: WHERE REGION EQ '&REGION.(FIND REGION IN STORES_EN).REGION.'; WHERE COUNTRY EQ '&COUNTRY.(FIND COUNTRY IN STORES_EN).COUNTRY.'; WHERE ORDER_YEAR EQ '&ORDER_YEAR.(<2003,2003>,<2004,2004>).Year.'; WHERE COUNTER EQ &COUNTER.(OR(<Total By State,3>,<Total by State/Store,1>,<Total by State/Product,2>)).Report.

  30. Build the Launch PageParameterize the Report

  31. The Launch Page Parameters

  32. The Launch Page Parameters

  33. Running – All Three Options

  34. Need Another?

  35. What to Do? • Add Option 4 to the DEFINE DEFINE FILE ORDERS SRT2/A30=IF COUNTER EQ 4 THEN ' ' ELSE STATE; SRT5/A30= IF COUNTER EQ 1 THEN CITY ELSE IF COUNTER EQ 2 THEN PRODCAT ELSE IF COUNTER EQ 3 THEN 'Total ' ELSE IF COUNTER EQ 4 THEN STATE ELSE ' '; SRT6/A30=IF COUNTER EQ 1 THEN STORENAME ELSE ' '; SRT3/I1 = IF COUNTER EQ 4 THEN 1 ELSE IF COUNTER EQ 3 THEN 2 ELSE 3; OVER_SRT/I1 = IF COUNTER EQ 4 THEN 1 ELSE 2; END • Change launch page and include New Parameter Value for Option.

  36. The Launch Page Parameters

  37. Output – Really Close, but HEADING ?

  38. McGyver Even works with COMPUTE • COMPUTE HEADING field based on COUNTER. • Since COUNTER is numeric, ALWAYS use the Average (AVE.) in COMPUTES. COMPUTE MYTITLE/A60 = IF AVE.COUNTER EQ 4 THEN ' ' ELSE 'State: ' | STATE; HEADING "Report of Sales" "Region: <REGION " "<MYTITLE" "For Year: <Order_Year "

  39. Output

  40. Output

  41. Output

  42. Oh, that Telephone Book? FILEDEF focmcgyv DISK c:\ibi\apps\baseapp\focmcgyv.txt -RUN -WRITE focmcgyv X123 -RUN JOIN CONTROL WITH PIN IN EMPDATA TO CONTROL IN FOCMCGYV AS AB -RUN DEFINE FILE EMPDATA CONTROL/A1 WITH PIN = 'X'; SORTFLD/A20 = IF ORDER EQ 1 THEN DEPT ELSE IF ORDER EQ 2 THEN LASTNAME ELSE FIRSTNAME; WHCHSRT/A15 = IF ORDER EQ 1 THEN 'Department' ELSE IF ORDER EQ 2 THEN 'Last Name' ELSE 'First Name'; END

  43. Oh, that Telephone Book? TABLE FILE EMPDATA PRINT COMPUTE COL1/A20 = IF AVE.ORDER EQ 1 OR 2 THEN FIRSTNAME ELSE IF AVE.ORDER EQ 3 THEN LASTNAME ELSE DEPT; AS '' COMPUTE COL2/A20 = IF AVE.ORDER EQ 1 THEN LASTNAME ELSE DEPT ; AS '' BY ORDER NOPRINT PAGE-BREAK BY SORTFLD AS '' BY ORDER NOPRINT PAGE-BREAK BY SORTFLD NOPRINT ON TABLE HOLD AS CEMP1 FORMAT PPT WHERE ORDER LE 3 HEADING "REPORT OF EMPLOYEES SORTED BY <WHCHSRT " … END

  44. Report of Employees Sorted BY Department ACCOUNTING EVELYN SANCHEZ TIM ANDERSON BEN SOPENA JOHN WANG ANNE LOPEZ ADMIN SERVICES WILLIAM MORAN LAURA GORDON CONSULTING KATE WANG KARL WHITE DAVID ELLNER CUSTOMER SUPPORT ANTHONY RUSSO VERONICA WHITE ROSE HIRSCHMAN ERWIN FERNSTEIN MARKETING DANIEL VALINO MICHAEL BELLA RUTH ADAMS DORINA PATEL PAMELA SO MARCUS CVEK CASSANDRA LEWIS JEFF LIEBER ERICA DONATELLO CHRIS GOTLIEB MARSHALL CONTI PERSONNEL MARIANNE PULASKI JIM NOZAWA YOLANDA KASHMAN ADAM CONRAD ELAINE GRAFF PROGRAMMING & DVLPMT KATRINA ROSENTHAL MARIE CASTALANETTA ARLEEN MARTIN CAROL PUMA SALES LOIS CASSANOVA PETER ADDAMS MARK MEDINA KAREN LASTRA ERIC DUBOIS WILMA OLSON HENRY CHISOLM

  45. Report of Employees Sorted BY Last Name ADAMS RUTH MARKETING ADDAMS PETER SALES ANDERSON TIM ACCOUNTING BELLA MICHAEL MARKETING CASSANOVA LOIS SALES CASTALANETTA MARIE PROGRAMMING & DVLPMT CHISOLM HENRY SALES CONRAD ADAM PERSONNEL CONTI MARSHALL MARKETING CVEK MARCUS MARKETING DONATELLO ERICA MARKETING DUBOIS ERIC SALES ELLNER DAVID CONSULTING FERNSTEIN ERWIN CUSTOMER SUPPORT GORDON LAURA ADMIN SERVICES GOTLIEB CHRIS MARKETING GRAFF ELAINE PERSONNEL HIRSCHMAN ROSE CUSTOMER SUPPORT KASHMAN YOLANDA PERSONNEL LASTRA KAREN SALES LEWIS CASSANDRA MARKETING LIEBER JEFF MARKETING LOPEZ ANNE ACCOUNTING MARTIN ARLEEN PROGRAMMING & DVLPMT MEDINA MARK SALES MORAN WILLIAM ADMIN SERVICES NOZAWA JIM PERSONNEL OLSON WILMA SALES PATEL DORINA MARKETING PULASKI MARIANNE PERSONNEL PUMA CAROL PROGRAMMING & DVLPMT ROSENTHAL KATRINA PROGRAMMING & DVLPMT RUSSO ANTHONY CUSTOMER SUPPORT SANCHEZ EVELYN ACCOUNTING SO PAMELA MARKETING SOPENA BEN ACCOUNTING VALINO DANIEL MARKETING WANG KATE CONSULTING JOHN ACCOUNTING WHITE VERONICA CUSTOMER SUPPORT KARL CONSULTING

  46. Report of Employees Sorted BY First Name ADAM CONRAD PERSONNEL ANNE LOPEZ ACCOUNTING ANTHONY RUSSO CUSTOMER SUPPORT ARLEEN MARTIN PROGRAMMING & DVLPMT BEN SOPENA ACCOUNTING CAROL PUMA PROGRAMMING & DVLPMT CASSANDRA LEWIS MARKETING CHRIS GOTLIEB MARKETING DANIEL VALINO MARKETING DAVID ELLNER CONSULTING DORINA PATEL MARKETING ELAINE GRAFF PERSONNEL ERIC DUBOIS SALES ERICA DONATELLO MARKETING ERWIN FERNSTEIN CUSTOMER SUPPORT EVELYN SANCHEZ ACCOUNTING HENRY CHISOLM SALES JEFF LIEBER MARKETING JIM NOZAWA PERSONNEL JOHN WANG ACCOUNTING KAREN LASTRA SALES KARL WHITE CONSULTING KATE WANG CONSULTING KATRINA ROSENTHAL PROGRAMMING & DVLPMT LAURA GORDON ADMIN SERVICES LOIS CASSANOVA SALES MARCUS CVEK MARKETING MARIANNE PULASKI PERSONNEL MARIE CASTALANETTA PROGRAMMING & DVLPMT MARK MEDINA SALES MARSHALL CONTI MARKETING MICHAEL BELLA MARKETING PAMELA SO MARKETING PETER ADDAMS SALES ROSE HIRSCHMAN CUSTOMER SUPPORT RUTH ADAMS MARKETING TIM ANDERSON ACCOUNTING VERONICA WHITE CUSTOMER SUPPORT WILLIAM MORAN ADMIN SERVICES WILMA OLSON SALES YOLANDA KASHMAN PERSONNEL

  47. The McGyver TechniqueThanks for Coming the Correct Tool

More Related