N DECUS U. S. Chapter     SIGs Newsletter,   Volume  5, Number 1  September 1989>                         Wombat Examiner,   Volume 11, Number 1  @ ----------------------------------------------------------------/                  DTR/DECREPORTER: User's Friend ?   Jim Copenhaver, Simons-Eastern Consultants, Inc., Decatur, GA @ ----------------------------------------------------------------  > The Computer Systems and Support department at Simons (Atlanta; Region) was presented with a challenging problem.  We had a A rapidly growing user population comprised of managers, department @ heads, and staff personnel that had little exposure to a VAX and> the flexibility and versatility available to them. This group,= almost overnight, required access to data that had previously @ only been available to them via weekly reports.  Each individual> had specific needs for extracting data that varied drastically@ from user to user.  Because of the large number of users, it wasA not possible for us to tailor the reports for each specific user.   ? Since many of our weekly reports were produced with DATATRIEVE, ; it seemed like DATATRIEVE would be the logical solution for : reporting problems.  Unfortunately, several new users were< confused by DATATRIEVE and cluttered up the CDD with aborted? procedures.  Our solution was to use DECREPORTER in conjunction 8 with DATATRIEVE to provide the users with a platform for> producing reports.  We called this platform "Simons_Reporter".  ; I took a "cookbook" approach that will allow anyone (with a @ familiarity with the CDD, DATATRIEVE, and DECREPORTER) to set up a similar platform.   ; I reduced the process down to seven phases.  Each phase, if = followed, will step you through the creation of the platform.    PHASE 1:  > For those unfamiliar with the CDD, the first phase is the mostA important and the most difficult to implement.  It involves using A the CDD utility DMU and how the CDD protections differ from those @ of VMS.  If anyone needs to look up what, exactly the privileges= shown here are, please refer to page A-48 in the VAX/CDD/Plus ! User's Guide (VAX/CDD/Plus V4.0).   A The following DMU commands will create either a CDD subdictionary @ or a CDD directory.  Most applications will not need to create a> separate subdictionary, and the *.dic file associated with it.8 Your specific needs may require this for security, space; considerations, or some other reason, so it is listed here.   + DMU> CREATE CDD$TOP.SYSTEMS.SIMONS_REPORTER E DMU> CREATE/SUBDICTIONARY=CDD$DSK:SIMONS_REPORTER.DIC SIMONS_REPORTER   = Here I have entered DMU and created the directory to house my ? platform.  Notice that I specified the entire CDD path name for < this dictionary.  The New CDD is more selective and will not@ allow you to be as "careless" when you create a dictionary using? DMU.  Be sure that if you create a subdictionary that the *.dic = file created has the VMS protections set to allow proper user  access.   = Protection on the subdictionary and some of its objects might 
 look like:  (         _CDD$TOP.SYSTEMS.SIMONS_REPORTER" 1.      [*,*], Username: "DBADMIN"2         Grant - CDHPSX, Deny - none, Banish - none 2.      [ENG_APPL]5         Grant - DEMPRSUWX, Deny - none, Banish - none & 3.      [*,*], Username: "JCOPENHAVER"5         Grant - DEMPRSUWX, Deny - none, Banish - none 
 4.      [*,*] 0         Grant - EPRS, Deny - none, Banish - none  /         SIMONS_REPORTER.TIME_REPORTING_SYSTEM;2 &           Access Control List is empty  3         SIMONS_REPORTER.TIME_REPORTING_SYSTEM_REC;1 &           Access Control List is empty  ? Here, I have several commands to set the protections on the new ? directory.  These protections allow specific users or groups of ? users limited access to the directory.  The EPRS protection for A the general users is most important.  This allows users to access @ all of the existing reports or to create new reports.  This will? become more important later.  For the first position, we have a : user called "DBADMIN". This is a user we created to manage> everything in the CDD.  We make sure that whenever we create a= new dictionary, DBADMIN is the user that occupies position 1. ? For position 2, we have a group called "ENG_APPL" that actually > maintains this directory.  The protections have been opened upA for them (and for me on position 3).  For position 4, the general > users have only EPRS.  These protections are important at this; point because using DMU to create the directory and set the : protections allows the users to access the domains, record> definitions, and procedures using INHERITED privileges.  Using6 the CDD's CDO utility will not allow this flexibility.  > When you decide to implement CDD Security features, you should> also keep in mind that VMS file protections and file ownershipA have not changed!  Just because you grant someone write access in A the CDD doesn't necessarily mean that the person has write access  on the RMS level.    PHASE 2:  ? The second phase of setting up the platform is performed at the 7 DCL level.  While this phase is not "critical", it adds 1 functionality and ease of operation to the users.   < These commands (modified to suit your environment) should be added to your system startup:   H $ DEFINE/SYSTEM/NOLOG SIMONS_REPORTER "_CDD$TOP.SYSTEMS.SIMONS_REPORTER"0 $ SIMONS_REP*ORTER:==REPORT/PATH=SIMONS_REPORTER  A This shows how we defined a logical for the CDD directory we used > as the platform.  By creating a logical for the CDD path, this> keeps users from wandering around the CDD and causing problemsA for other users.  The symbol we created uses the same name as the = logical.  Few users would check to see if a symbol was also a A logical.  This symbol and logical were created at system startup.   ; A command procedure executed at login time would look like:    $ CDD_DICT := "DOESNT_EXIST") $ ACCOUNT   = "''F$GETJPI("","ACCOUNT")'" % $ ACCOUNT   = F$EDIT(ACCOUNT,"TRIM")' * $ USERNAME  = "''F$GETJPI("","USERNAME")'"% $ USERNAME  = F$EDIT(USERNAME,"TRIM") 	         . 	         . 	         . ; $ IF ACCOUNT .EQS. "CMPT_SER"  THEN CDD_DICT := ISD$LIBRARY 	         . 	         . 	         . = $ DEFINE/NOLOG CDD$DEFAULT "_CDD$TOP.''CDD_DICT'.''USERNAME'" 7 $ DEFINE/NOLOG MY_CDD      "''F$TRNLNM("CDD$DEFAULT")'"   < Here is a key element in setting up this phase.  The command< procedure shown is accessed when a user first logs in.  Most> users don't know that this even exists.  When we add a user to? the system, we also add them a personal CDD subdirectory.  This @ we have keyed to the "group" they belong to in the UAF.  MembersA of the same UIC group will probably need to share procedures more = than users from another UIC group.  They will also be able to ? share common databases without opening the protections to world  read.   ? With that in mind, we define each individual user's CDD$DEFAULT A and define the logical MY_CDD.  This logical allows users to save : a new DTR/DECREPORTER report to their personal CDD$DEFAULT( without keying in the full CDD pathname.   PHASE 3:  ? Now that we have the CDD directory created, the protections set = in the CDD, and the necessary symbols/logicals defined, it is @ time to populate the directory with domains, record definitions, and procedures.   > The first thing you do is look around the CDD for any existing@ DATATRIEVE procedures you want to use.  These procedures are the@ ones that most users are comfortable with, have seen output from6 in the past, and probably cannot be reproduced through DECREPORTER.  < Here is how I extracted a sample DECREPORTER procedure and a$ sample DATATRIEVE procedure to disk.  L DMU> EXTRACT CDD$TOP.ISD$LIBRARY.JCOPENHAVER.USER_IMAGE_REPORT DECUS_DTR.BAKN DMU> EXTRACT CDD$TOP.ISD$LIBRARY.JCOPENHAVER.USER_SEARCH DECUS_DECREPORTER.BAK  7 DMU is the preferred way to extract a procedure.  While = DATATRIEVE can be used here, it is a good practice to use DMU 3 while moving DECREPORTER and DATATRIEVE procedures.   < One of the nice things about DECREPORTER is that it produces= DATATRIEVE code.  While the two are not combined as a unified > product now, they should be.  If you look at code generated by? DECREPORTER, you will notice several things that are not common > to DATATRIEVE but are permitted by it.  The SET SEMICOLON, the< SET SEARCH, and the SET LOCK_WAIT are good examples of this.  A A typical report command file originally generated for DATATRIEVEn would look like:  6 !  DATATRIEVE PROCEDURE TO GENERATE USER IMAGE REPORTS !- READY DTR_CIS_DATA ! READY OBJECTS SHARED READp READY DTR_DATE READm ! * !             ESTABLISH CURRENT COLLECTION !i' PRINT "ESTABLISHING CURRENT COLLECTION"s !p !l% PRINT "BEGINNING COLLECTION, ETC...."  !e !,1 FIND REPDAT IN DTR_CIS_DATA WITH DEPT_NO GT "099"V !a* SORT REPDAT BY DEPT_NO, USERNAME, OBJ_NAME !o !e, DECLARE REIMB_CHARGE PIC 9(9)V99 COMPUTED BY
     CHOICE6         (PROJECT BETWEEN "1000" AND "9999") THEN SALES     ELSE 0.0 END_CHOICE.u/ DECLARE NONREIMB_CHARGE PIC 9(9)V99 COMPUTED BYa
     CHOICE&         (PROJECT LT "1000") THEN SALES     ELSE 0.0 END_CHOICE.w DECLARE REIMB PIC 999V99.t !o. PRINT "BEGINNING USER IMAGE REPORT GENERATION" !y( REPORT REPDAT ON SAC_QRSO:USER_IMAGE.RPT     SET COLUMNS_PAGE = 132     SET REPORT_NAME =eB         "COMPUTER RESOURCE ACCOUNTING"/"VAX COMPUTING SYSTEM"/" "/         "USER IMAGE REPORT"A     AT TOP OF PAGEM         PRINT REPORT_HEADER, SKIP, DATES FROM DTR_DATE, SKIP 3, COLUMN_HEADER        AT BOTTOM OF REPORTV/         PRINT SKIP 1, COL 21, ("REPORT TOTAL"),c         COL 50,c.         TOTAL REIMB_CHARGE USING $,$$$,ZZZ.99,         COL 70,r1         TOTAL NONREIMB_CHARGE USING $,$$$,ZZZ.99,i         COL 90,R-         TOTAL CONN_SEC/3600 USING ZZZ,ZZZ.99,t         COL 110,*         TOTAL CPU_SEC/10 USING ZZZ,ZZZ,ZZ9
 END_REPORT !w. PRINT "USER IMAGE REPORT GENERATION COMPLETED" !o
 FINISH ALL  7 This sample DATATRIEVE procedure looks nothing like thei@ DECREPORTER procedure in many ways.  However, VAX/TPU will allow? us to edit this procedure at the DCL level and disguise it as af? DECREPORTER procedure.  The procedure must be changed by adding  at the beginning   SET SEMICOLON; SET SEARCH;a SET LOCK_WAIT;  8 Each statement must be terminated with a semicolon.  And   SET NO SEMICOLON;l SET NO SEARCH; FINISH ALL;i  A must be appended to the end of the procedure.  It is important toe= change anything in the procedure at this time.  Once you havei@ gotten DECREPORTER to accept a report procedure, you will not be@ able to edit the procedure after it is in the CDD and still have it accessible via DECREPORTER.  < With these changes, the procedure LOOKS like the DECREPORTER? procedure now.  All of the syntax for the two procedures is ther< same.  Using DECREPORTER`s RDMU utility, we can now add this" procedure into our new dictionary.   <FIGURE TITLE: Main RDMU Menu>1                              VAX DECreporter V2.1c  6                          Dictionary Management Utility              2                             MAINTENANCE SELECTIONS.                                 DOMAIN        .                                 RECORD        .                                 REPORT        .                                 EXIT Utility              1                              ENTER SELECTION:       4 <FIGURE TITLE: Specification of Report Name in RDMU>K                                                        VAX DECreporter V2.1S  P                                                    Dictionary Management Utility            I                                                           SIMONS_REPORTERn_ EXIT Utility                                ARNED;1                                     ARNED;2rc PHS_PROJ_STD_2;1                            PHS_PROJ_STD_3;1                            PHS_STD_1;1se PROJECT_3709C;1                             PROJECT_LISTING;1                           TRS_BUS_DEV;1eh TRS_DEPT_STD_1;1                            TRS_DEPT_STD_2;1                            TRS_DEPT_STD_3;1k TRS_DEPT_STD_4;1                            TRS_IND_ALL_PROJ;1                          TRS_IND_AREA_PROJ;2rh TRS_IND_PROJ_AREA;1                         TRS_PROJ_STD_1;1                            TRS_PROJ_STD_2;1          # Enter new REPORT name:   decus_demoe  A <FIGURE TITLE: Specification of Report Command File Name to RDMU>"K                                                        VAX DECreporter V2.1n  P                                                    Dictionary Management Utility            I                                                           SIMONS_REPORTERc_ EXIT Utility                                ARNED;1                                     ARNED;2ic PHS_PROJ_STD_2;1                            PHS_PROJ_STD_3;1                            PHS_STD_1;1 e PROJECT_3709C;1                             PROJECT_LISTING;1                           TRS_BUS_DEV;1Vh TRS_DEPT_STD_1;1                            TRS_DEPT_STD_2;1                            TRS_DEPT_STD_3;1k TRS_DEPT_STD_4;1                            TRS_IND_ALL_PROJ;1                          TRS_IND_AREA_PROJ;2eh TRS_IND_PROJ_AREA;1                         TRS_PROJ_STD_1;1                            TRS_PROJ_STD_2;1          & Enter REPORT command file:   decus.tmp  7 <FIGURE TITLE: RDMU Screen Loading the Report SelectionoK                                                        VAX DECreporter V2.1O  P                                                    Dictionary Management Utility          N                                                     SIMONS_REPORTER.DECUS_DEMO   SET SEMICOLON; SET SEARCH;a6 !  DATATRIEVE PROCEDURE TO GENERATE USER IMAGE REPORTS !  READY DTR_CIS_DATA;r !  READY OBJECTS SHARED READ READY DTR_DATE READ; !e                                                                                                                              More...    N                                                      Storing Dictionary Object  ? At this point, if you enter SIMONS_REPORTER from the DCL level,"9 you will see the "new" report by accessing the "Recall an < Existing Report" option.  If selected, the report should run4 providing that all necessary changes have been made.  1 <FIGURE TITLE: User Recalling an Existing Report> K                                                        VAX DECreporter V2.1D            I                                                           SIMONS_REPORTER _ EXIT Utility                                ARNED;1                                     ARNED;2sc PHS_PROJ_STD_2;1                            PHS_PROJ_STD_3;1                            PHS_STD_1;1 e PROJECT_3709C;1                             PROJECT_LISTING;1                           TRS_BUS_DEV;1Mh TRS_DEPT_STD_1;1                            TRS_DEPT_STD_2;1                            TRS_DEPT_STD_3;1k TRS_DEPT_STD_4;1                            TRS_IND_ALL_PROJ;1                          TRS_IND_AREA_PROJ;2 h TRS_IND_PROJ_AREA;1                         TRS_PROJ_STD_1;1                            TRS_PROJ_STD_2;1              * Recall report PROJECT_LISTING;1  (Y/N)?  Y  ? If you want to mix DATATRIEVE and DECREPORTER procedures in them@ same CDD directory, they would all show up when someone used theA "Recall an Existing Report" option.  However, if someone tried tofA access a DATATRIEVE procedure from DECREPORTER, they would get ane error message.  b <FIGURE TITLE: Error Message Generated when Trying to Access a DATATRIEVE Report using DECREPORTERI                                                           VAX DECreportert            I                                                           SIMONS_REPORTERh    _ EXIT DECreporter                            ARNED;1                                     ARNED;2ch DECUS_DEMO;1                                PHS_PROJ_STD_2;1                            PHS_PROJ_STD_3;1i PHS_STD_1;1                                 PROJECT_3709C;1                             PROJECT_LISTING;1 h TRS_BUS_DEV;1                               TRS_DEPT_STD_1;1                            TRS_DEPT_STD_2;1j TRS_DEPT_STD_3;1                            TRS_DEPT_STD_4;1                            TRS_IND_ALL_PROJ;1h TRS_IND_AREA_PROJ;2                         TRS_IND_PROJ_AREA;1                         TRS_PROJ_STD_1;1 TRS_PROJ_STD_2;1    ! Select the report to be recalled:h  p                      DECUS_DEMO;1  has been created by Datatrieve.  Please use Datatrieve to Process this Report  = Unless you have complicated CDD domain definitions, it may beg? easier to simply recreate them in the new directory using RDMU.V> If you want to move a domain definition, the easiest method in$ this case, is to use DATATRIEVE to:   &    1. extract the domain to a DCL file     2. edit the file if necessary    3. reload using DATATRIEVE           DTR>@FILENAME.I   PHASE 4:  @ The best feature of this platform is it's ability to have onlineA help built into the record definition.  DECREPORTER is quite niceR< in this aspect, where DATATRIEVE is not.  If you have simple@ record definitions that exist in the CDD, I suggest you use RDMU9 to recreate them and incorporate the helps at that point. , However, it almost never works out that way.  A Here is an abbreviated record definition that allows users to see < the "helps" added in the record.  All the user does is press <PF4>.  ' DEFINE RECORD TIME_REPORTING_SYSTEM_REC2 DESCRIPTION IS8 /* This is the record for the Time Reporting System  */.    TRS_DOMAIN_REC STRUCTURE."         /* week ending dd/mm/yy */(         WEEK_ENDING     DATATYPE IS TEXT,                         SIZE IS 8 CHARACTERS8                         PICTURE FOR DATATRIEVE IS "X(8)"<                         QUERY NAME FOR DATATRIEVE IS "WEND".         /* Project number */(         PROJECT         DATATYPE IS TEXT,                         SIZE IS 5 CHARACTERS9                         PICTURE FOR DATATRIEVE IS "X(5)".          /* Area number */Z(         AREA            DATATYPE IS TEXT,                         SIZE IS 3 CHARACTERS9                         PICTURE FOR DATATRIEVE IS "X(3)".h#         /* Hours project to date */s@         HOURS_PROJECT_TO_DATA DATATYPE IS RIGHT SEPARATE NUMERIC(                         SIZE IS 7 DIGITS                          SCALE -1>                         PICTURE FOR DATATRIEVE IS "S9(6)V9(1)"<                         QUERY NAME FOR DATATRIEVE IS "HPYTD"A                         EDIT STRING FOR DATATRIEVE IS "-Z(5)9.9".A     END TRS_DOMAIN_REC_STRUCTURE.% END TIME_REPORTING_SYSTEM_REC RECORD.e  ? If you look at this record definition, you will see how "helps"o9 look in the actual record definition itself.  When a user A accesses this record definition from DECREPORTER, all he needs toe= do is press <PF4> and the help for that particular field willcA appear on the screen and help him determine if he wants to select  it.h  > If you have an existing DECREPORTER record definition that you9 want to move to the platform, follow these steps exactly:   >  1. Enter DMU and set your default over to the dictionary that=     holds the record definition (if it is different than youre     CDD$DEFAULT).   >  2. Type "LIST" to make sure that the record definition is out?     there.  Type DMU> EXTRACT/RECORD record_name out_file_name. @     If the record definition is a DATATRIEVE definition, you can=     edit it at this point and add any help messages you want.   >  3. The ONLY way to get this record back into the CDD with the9     help messages intact is to follow these step EXACTLY:   *     a. $define cdd$default simons_reporter     b. $CDDL out_file_name    4. Re-enter DMU and type   -                    DMU> LIST/FULL record_name   4     on the record and check the record for accuracy.  :  5. Reset your CDD$DEFAULT to its correct dictionary path.     PHASE 5:  A Earlier we covered the way you can set protections using the CDD. @ There are a few things that should be added here.  For practicalA purposes, you can consider that the CDD protections sit on top ofN= the DCL protections.  That is, even if a file has a DCL world ? read protection on it, they cannot access it via the CDD unless < you set it up that way.  On the other hand, just because you> allow them to access something via the CDD doesn't necessarily@ mean that DCL will permit it.  This is a strong security feature and should not be overlooked._  < An example of this is a personnel database containing salary? information.  A manager may need the information there, but not"= want the clerks to have access to it.  Even if the clerks hadC@ other valid procedures they needed to run from the platform, the6 manager could eliminate anyone from snooping around by0 restricting the access to the file at DCL level.   PHASE 6:  6 Earlier, we covered how to move the altered DATATRIEVEA procedures, DECREPORTER reports, domains, and record definitions. 8 We ended up with a platform that was populated with some@ procedures, but not everything that some users may need.  As the= person creating this platform, you need to have a flexibilityE: that will allow users to create their own reports from the; platform but not clutter it up with them.  One of the first A things we did was create a logical called "MY_CDD".  This logicalR> will allow users to enter the platform, create a new report toA their specifications and save it to their personal CDD directory.   H <FIGURE TITLE: Illustration of Person Saving a Report to MY_CDD.Whatever --------------------------------------------------------Selection Criteria---------------------------------------------------------             U                                              SIMONS_REPORTER.PROJECT_HISTORY_SYSTEM;2   _    PROJECT                                     DEPT                                        AREARD    ACT_CODE                                    HOURS_PROJECT_TO_DATE                    ' Name of this report:  my_cdd.decus_demo   8 This report can be accessed directly from the user's CDD= directory by using either DATATRIEVE or DECREPORTER.  It will ; retain all pointers to the platform, its domains and recordc> definitions but will not clutter the platform up with a report only one person will use.l  9 DECREPORTER has some limits that DATATRIEVE doesn't.  ForR> example, DATATRIEVE will allow you to create a report over 132? characters wide.  DECREPORTER alone will not.  However, you cane@ create a "SHELL" from DECREPORTER and modify it to your specific needs.  = A typical report procedure created by DECREPORTER looks like:    SET SEMICOLON; SET SEARCH;  SET LOCK_WAIT;, SET DICTIONARY CDD$TOP.PROD$LIBRARY.QUANTUM; READY DTR_CIS_DATA SHARED READ;  REPORT DTR_CIS_DATA;4     ON * ."the report output device and filespec  "; SET COLUMNS_PAGE = 132;  SET LINES_PAGE = 55; SET REPORT NAME = "DECUS DEMO";  PRINT (     COL   2, NODE ("NODE") USING XXXXXX,,     COL   9, PROJ_NO ("PROJECT") USING XXXX,3     COL  17, PROJ_SUF ("PROJECT"/"SUFFIX") USING X, 7     COL  25, DEPT_NO ("DEPARTMENT"/"NUMBER") USING XXX, :     COL  36, USERNAME ("USERNAME") USING XXXXXXXXXXXXXXXX.?     COL  51, OBJ_NAME ("OBJECT"/"NAME") USING XXXXXXXXXXXXXXXX, 8     COL  68, CONN_SEC ("CONNECT"/"TIME") USING XXXXXXXX,=     COL  77, CPU_SEC ("CPU"/"TIME"/"SECONDS") USING XXXXXXXX, :     COL  90, PAGE_FAULTS ("PAGE"/"FAULTS") USING XXXXXXXX,6     COL  99, DIRECT_IO ("DIRECT"/"IO") USING XXXXXXXX,6     COL 108, BUFF_IO ("BUFFERED"/"IO") USING XXXXXXXX,9     COL 117, PRINT_PAGES ("PRINTED"/"PAGES") USING XXXXX,r-     COL 125, SALES ("SALES") USING XXXXXXXX ;g AT BOTTOM OF REPORTE PRINTs     SKIP 2 ; END_REPORT;t SET NO SEMICOLON;V SET NO SEARCH; FINISH ALL;   = The limit on this report is 132 columns wide, but my specificpA needs require a report that goes beyond that.  I know which field 5 I want to add and the size that the report should be.   % So it can be "fixed" by changing from    SET COLUMNS_PAGE = 132;;   to   SET COLUMNS_PAGE = 150;   
 and replacing_    ,     COL 125, SALES ("SALES") USING XXXXXXXX;   with  ,     COL 125, SALES ("SALES") USING XXXXXXXX,J     COL 136, AUTHORIZATION_CODE ("AUTHORIZATION"/"CODE") USING XXXXXXXXXX;  @ This procedure has been modified to allow a report limit of 150.? We defined a form that will allow our LN03 to print it.  I also @ added a field to the procedure.  One way to do this is to create? a field at the end of the report and replace it with the "real" A field.  After the procedure has been edited to your satisfaction,t: enter RDMU and add the report from there.  Remember to use# DECREPORTER syntax for any changes.    PHASE 7:  A The final phase is to let the users know how to use the platform.o? It seems like it would be easy for anyone to use, but users canh< be quite hesitant to try something new.  The easiest way for> everyone to learn about it is send a memo around with detailed? instructions.  There are several drawbacks to this method.  Thel< first is that many people never read their memos.  They will= glance at it and throw it in the trash.  Another problem withi= memos is that it is hard to target the memo to the people whoo will actually use the platform.n  > Another approach is to hold a "brown bag seminar" to show them@ how the platform really works.  After the demonstration, you can> pass out the instructions to those who attend.  You might hold< several of them to accommodate those who may have missed the first "brown bag".  @ If you have the personnel that can do it, have someone go around> to each of the users and show them individually how to use the@ platform and customize their own reports.  This insures that you? get the necessary feedback from the users on how to improve theg& procedures and the user documentation.  @ ----------------------------------------------------------------)                        Dear Wombat Wizard @ ----------------------------------------------------------------   Dear Wombat Wizard:n  @ I have an indexed data file approaching 15,000 records.  The key? is 6 characters long.  The first character is alphanumeric; the ? remaining 5 are numeric only.  In other words, some of the keys  have an alphabetic prefix.  8 What I need to do is change the prefix to a suffix.  The: objective is a 6-character key with the first 5 characters$ numeric and the sixth alphanumeric.   ; What I need is some ideas as to how to convert the existing ? records (probably several hundred of them) from the A99999 form 7 to the 99999A form without doing each record manually. 1  )                                   Signed,T    8                                   Need Some Keys Changed    
 Dear Changed:.  @ Assuming that the records are located in an RMS file (even if it8 is in Rdb), it still shouldn't be too hard.  I would use> DATATRIEVE, naturally!  I am also assuming that the key is the@ primary key, which means that it cannot be changed.  In order toA change the primary key, you must delete the record and reenter itl: with the new key value.  If MOST of the records need to be= altered, I would consider just setting up a new data file andRA transfer over each record, making the conversion in the key fieldw> as necessary.  If less than 30% (a fairly arbitrary number) of= the record need to be changed, then I would just do it in thetA same file.  Take each record, look at the key to see if it beginsmA with an alphanumeric, if it does, then do a store to a new recordX; with the appropriate change to the key field (put the alphaeA portion on the end instead of the front).  If storing in the sameo% file then delete the original record.   0 The record definition for the data file could be   DEFINE RECORD FOO-RECORD USING 01 FOO-REC.f    03 KEY-FIELD PIC X(6).     03 REST-OF-RECORD PIC X(n). ;_  @ Then the statements and commands to restructure those particular2 records which begin with an alpha could look like:   READY FOO AS OLD SHARED WRITEe READY FOO AS NEW SHARED WRITE 9 FOR OLD WITH KEY-FIELD NOT BETWEEN "0" AND "999999" BEGIN      STORE NEW USING BEGINeO         KEY-FIELD = FN$STR_EXTRACT(KEY-FIELD,2,5)|FN$STR_EXTRACT(KEY-FIELD,1,1)b'         REST-OF-RECORD = REST-OF-RECORDo         ENDr	     ERASED     ENDi  @ There are several other ways you could do this.  One of the more> interesting is by using a group field in two domains.  The old record would look like   01 FOO-REC.y    03 KEY-FIELD.       05 ALPHA-CHARACTER PIC X.t%       05 NUMERAL-CHARACTERS PIC X(5).n    03 REST-OF-RECORD PIC X(n). ;i  " and the new record would look like   01 FOO-REC.n    03 KEY-FIELD.%       05 NUMERAL-CHARACTERS PIC X(5).a       05 ALPHA-CHARACTER PIC X.t    03 REST-OF-RECORD PIC X(n). ;a  ' Note the change in order of the fields.v  9 After you have restructured your data file, you will mostrA certainly want to recover the space in the deleted records with at@ CONVERT/RECLAIM and re-optimize the file by the well known steps= ANALYZE/FDL, EDIT/FDL, and CONVERT/FDL which are described in  your RMS tuning guide.  A One of DATATRIEVE's most useful features is its ability to easilye restructure data.a  )                                   Signed,s  >                                   The Wombat Wizard (LJ&SC&JG)  @ ----------------------------------------------------------------0                  The 4GL Problem for New Orleans6            Winston Tellis, Problem Session Coordinator@ ----------------------------------------------------------------  > At a meeting of DTR/4GL SIG sponsored Working Group Chairs and< Counterparts at the 1989 Spring Symposia in Atlanta, Working: Groups expressed an interest in cooperating in one or moreA symposia presentations.  One suggested activity was that each 4GL-@ Working Group present a solution to the same problem.  Since the= logistics for such a symposia presentation would require some @ significant commitments by members of the working groups, it was@ decided that such a presentation could not be made in Anaheim in@ the Fall of 1989 but could be accomplished in New Orleans in the Spring of 1990.   @ Each of the DTR/4GL SIG Working Groups and DATATRIEVE is invited? to select a representative to present a solution to the problemD@ which follows at the Spring 1990 DECUS Symposium in New Orleans.5 There will be one and only one presentation from eachc= participating 4GL.  The representative must be chosen and theeA DTR/4GL SIG Steering Committee notified by the end of the Anaheimt@ Symposia so that the appropriate amount of time may be scheduled@ for New Orleans.  The order of presentations in New Orleans will; be determined by a random drawing; each presenter will haven< exactly the same amount of time to present their solutions.   2 The problem is a "People Database" browse problem.    The record layout is as follows:@     id number              7 digit numeric, primary key, no dupsE     last name              15 characters secondary key (dups allowed) (     first name             10 characters&     middle initial         1 character5     title of address       (Mr., Ms., Mrs., Dr., etc)I2     seniority              (Jr., Sr., II, III etc)/     address including      city, state, and zipU7     soundex of last name   secondary key (dups allowed)I  @ You are to write and describe an application in which a user may? enter the id number, or last name, or part of last name, or the  soundex of the last name.O  > If the entry selects a unique record, the user gets to look at5 the record to confirm that it is the desired record. (  ; If the entry is not unique, the user gets records to browseC: through in sort order of last name, first name, and middle: initial (either by paging or scrolling through a list both5 forward and backwards) to select the desired record. G  A The browse display will include the id number, the full name witht  title and suffix, and the city.   @ The data base is large; there are more than 10,000 records.  The@ application must be prepared to deal with at least 400 potential= records in the browse (approximately 20 screens of 20 recordso each).  @ There is no restriction on the programming technique and whether> or not you have to link to another language (3GL) to execute aA particular routine.  For those 4GLs not having a built-in SoundexL: algorithm, you may use the MACRO routine described in Bart= Lederman's article in the March 1989 issue of the Newsletter,h* Volume 4, Number 7, pages DTR-5 to DTR-12.  @ Comparing how each product approaches the problem should be most interesting.@ ----------------------------------------------------------------B                                 Wombat Magic, Spring 1989 - Part 2_     Session Co-Chairs: Dana Schwartz, DOD and Bert Roseberry, U. S. Coast Guard, Washington, DC V             Session Editor: Joe H. Gallagher, Ph. D., 4GL Solutions, Overland Park, KS@ ----------------------------------------------------------------9 Editor's note: The following is Part 2 of a highly editedh< transcription of the Wombat Magic Session at the 1989 Spring> DECUS Symposium in Atlanta, Georgia, which occurred on May 11,= 1989.  Part 1 appeared in the July 1989 newsletter.  Materials> which was presented on transparencies has been merged into the? oral presentation.  An attempt has been made to convey both thea< technical content of the Magic Session as well as the humor,? covert intellectual swaggering, and the spirited interchange ofg= the presentations.  Material which appears in the text withinh@ square brackets [] has been added by the editor in an attempt to9 improve the understandability of this very exciting Magice= Session.  The material presented here is not presented in the ) same order as it occurred in the session.r  7 John Putnam, Harris Trust and Savings Bank, Chicago, ILo  9 John described a problem in which he was asked to produceu? approximately 2400 pages of text and horizontal bar charts in 2 < week window.  The report had to effectively match the report@ format and presentation quality of prototype reports produced by@ outside consultants.  And the report had to be done in-house and< in a timely fashion.  The problem is that mixed test and bar> graphs using Tellagraph and a LN03 laser printer to 12 minutes= per page.  [A quick calculations will tell that it would take , more than 19 days to print all the reports.]  A So John's obvious answer to the problem was to use the DATATRIEVEo( Report Writer (and his Talaris Printer).  > The report had to made horizontal bar graphs which represented? the percent of favorable, neutral, and unfavorable responses tot@ an in-house questionnaire.  For each record, two or three graphs? needed to be produced -- for group A and B and optionally groupe C.  @ The "trick" to the problem was fully understanding and using the- escape sequences of the Talaris 1590 printer.e  : The Talaris native function to plot rulers has the command format:   !          <ESC>[P1;P2;P3;P4:P5;P6|)  = where P1 is 0 for the absolute position or 1 for the relativea@ position, P2 is the x starting position in thousands of an inch,@ P3 is the y starting position in thousands of an inch, P4 is the; horizontal length, P5 is the vertical height, and P6 is thek" pattern.  The default pattern for   2          black is 65535(10) or 1111111111111111(2)2          grey  is 43690(10) or 1010101010101010(2)3          white is 00000(10) or 0000000000000000(2).   : The Talaris escape sequence to save the current horizontal position in save area 1 is            <ESC>[1;1 x  @ and the sequence to restore the current horizontal position from save area 1 is            <ESC>[0;1 x  7 The complete procedure to drive the Talaris printer is:k  ( DEFINE PROCEDURE PRINT_SURVEY_BAR_REPORT READY CURCC_DOM SHARED DECLARE CURCC1 PIC 999.  DECLARE CURCC2 PIC 999.i DECLARE NEMPCC1 PIC 9999.d DECLARE NEMPCC2 PIC 9999.  DECLARE NEMPCC3 PIC 9999.O DECLARE CURCCTITLEX PIC X(60). DECLARE RPTYPEX PIC X. FOR FIRST 1 CURCC_DOMP  BEGIN    CURCC1 = CUR_CC1e    CURCC2 = CUR_CC2     RPTYPEX = RPT_TYPEc    NEMPCC1 = NUMCC1e    NEMPCC2 = NUMCC2l    NEMPCC3 = NUMCC3     CURCCTITLEX = CURCCTITLED  END READY SURVEY_REP_ABC SHARED < DECLARE THEME_A_FAV COMPUTED BY THEME VIA THEME_AFAV_TABLE.MA DECLARE THEME_ORDER COMPUTED BY IF ORDER = 0 THEN 999 ELSE AFAV .| DECLARE TWIDE PIC 9999.1
  TWIDE = 4000E( FIND ALL SURVEY_REP_ABC WITH THEME LT 143 SORT BY DESC THEME_A_FAV,ASC THEME,DESC THEME_ORDERa REPORT ALL CURRENT ON SURBARRPT  SET COLUMNS_PAGE=250 !sN ! Each record prints three bars (the third bar does not print if CFAV missing)J ! of comparative survey data (AFAV,BFAV, AND CFAV are favorable responses)E ! (AUN ... unfavorable), the middle segment is wishy washy responses.eG ! The three bars are comparing a parent group with a specific group andC ! with different year  ! J ! Each bar has three bar segments that have total width in inches of TWIDEJ ! with the first segment being AFAV (value 0-100) percent of total on leftG !          right hand segment  AUNF (  "        )  "  of total on rightrI !          middle segment      (100 - AFAV - AUNF) with different shadings !NG ! Pad each bar with enough blanks so that the t(145) folds each bar andn& ! left justifies in the proper column. !uD ! The <ESC>[0;1 x recalls beginning (left) position of bar requiringG ! a string of spaces to position text that is to appear at right of bar  !bI PRINT SKIP 3,COL 1,FN$STR_EXTRACT(ITEM VIA ITEM_TABLE,1,6)(-) USING X(6),-F       COL 8,FORMAT FN$STR_EXTRACT(ITEM VIA ITEM_BIG_TABLE,7,239) USING&             X(239)||""(-) USING T(45),       COL 60,("A   " | AFAV |->       "   <ESC>[1;1 x[1;0;0;"| FN$NINT(TWIDE*(AFAV/100.00)) ||D       ";65436;|<ESC>[1;" | FN$NINT(TWIDE*(AFAV/100.00))   || ";0;" |7       FN$NINT(TWIDE*((100.0 - AFAV - AUNF)/100.00))  ||n       ";65436;2048|<ESC>[1;" |5       FN$NINT(TWIDE*((100.0 - AFAV - AUNF)/100.00))||a.       ";0;" | FN$NINT(TWIDE*(AUNF/100.00))  ||D       ";65436;43690|<ESC>[0;1 x   {MANY SPACES HERE}       "| AUNF |!       "            B   " | BFAV |fC       "   <ESC>[1;1 x<ESC>[1;0;0;"| FN$NINT(TWIDE*(BFAV/100.00)) ||hD       ";65436;|<ESC>[1;" | FN$NINT(TWIDE*(BFAV/100.00))   || ";0;" |7       FN$NINT(TWIDE*((100.0 - BFAV - BUNF)/100.00))  ||g       ";65436;2048|<ESC>[1;" |5       FN$NINT(TWIDE*((100.0 - BFAV - BUNF)/100.00))||c.       ";0;" | FN$NINT(TWIDE*(BUNF/100.00))  ||F       ";65436;43690|<ESC>[0;1 x   {MANY SPACES HERE}         "| BUNF |K       IF CFAV = " " AND CUNF = " " THEN "" ELSE "            C   " | CFAV |.C       "   <ESC>[1;1 x<ESC>[1;0;0;"| FN$NINT(TWIDE*(CFAV/100.00)) ||mD       ";65436;|<ESC>[1;" | FN$NINT(TWIDE*(CFAV/100.00))   || ";0;" |7       FN$NINT(TWIDE*((100.0 - CFAV - CUNF)/100.00))  ||        ";65436;2048|<ESC>[1;" |5       FN$NINT(TWIDE*((100.0 - CFAV - CUNF)/100.00))|| .       ";0;" | FN$NINT(TWIDE*(CUNF/100.00))  ||!       ";65436;43690|<ESC>[0;1 x"| D       "                                                           "|       CUNF || "" ) USING T(164) # AT TOP OF PAGE PRINT SKIP 7,COL 42, > 'SURVEY:  HARRIS BANK EMPLOYEE OPINION SURVEY', SKIP 3, COL 3,- 'ITEM REPORT:  '|CURCCTITLEX(-) ,SKIP,COL 45, * '   SUBGROUP',COL 76,'SYMBOL   N', COL 45,' '   --------',COL 76,'------   -',SKIP,r+ COL 45,CHOICE RPTYPEX = "D" THEN "GROUP   " -               RPTYPEX = "G" THEN "DEPARTMENT"l+               RPTYPEX = "S" THEN "DIVISION"                ELSE "???"4       END_CHOICE | "   " | FORMAT CURCC2 USING ZZ9 |       "   (1988)" , 7       COL 79,"A   " |  FORMAT NEMPCC2 USING ZZZ9 ,SKIP, + COL 45,CHOICE RPTYPEX = "D" THEN "DIVISION"O-               RPTYPEX = "G" THEN "GROUP     "s+               RPTYPEX = "S" THEN "SECTION "t               ELSE "???"4       END_CHOICE | "   " | FORMAT CURCC1 USING ZZ9 |       "   (1988)",7       COL 79,"B   " |  FORMAT NEMPCC1 USING ZZZ9 ,SKIP,t+ COL 45,CHOICE RPTYPEX = "D" THEN "DIVISION"l-               RPTYPEX = "G" THEN "GROUP     "d+               RPTYPEX = "S" THEN "SECTION "n               ELSE "???"4       END_CHOICE | "   " | FORMAT CURCC1 USING ZZ9 |       "   (1986)",9       COL 79,"C   " |  FORMAT NEMPCC3 USING ZZZ9 ,SKIP 4,t2       COL 6, THEME VIA THEME_TABLE(-) USING T(73),D       COL 75,"% FAVORABLE       % NEUTRAL        %UNFAVORABLE",SKIP,5       COL 70,  "<ESC>[1;0;0;"| FN$NINT(TWIDE*.333) ||a;       ";65436;|<ESC>[1;" | FN$NINT(TWIDE*.333)   || ";0;" |        FN$NINT(TWIDE*.333)  ||e       ";65436;2048|<ESC>[1;" |       FN$NINT(TWIDE*.333)||y%       ";0;" | FN$NINT(TWIDE*.333)  ||i       ";65436;43690|"c! AT BOTTOM OF THEME PRINT NEW_PAGEe
 END_REPORT
 END_PROCEDUREr   The report looks like:  @ [Editor's note: Since the hardcopy of the newsletter is produced@ on a Postscript printer, it is not possible to exactly reproduce@ the Talaris output.  Also the report below has been narrow to 805 columns rather than the original 132 column report.]]   =                   SURVEY: HARRIS BANK EMPLOYEE OPINION SURVEY   + ITEM REPORT: ELECTRONIC PROCESSING DIVISION   :                       SUBGROUP                   SYMBOL  N:                       -------                    ------  -<                    GROUP         401   (1988)    A       448<                    DIVISION      450   (1988)    B        98<                    DIVISION      450   (1986)    C        59M                                     %FAVORABLE      %NEUTRAL     %UNFAVORABLE O Q41: USEFULNESS ...               aaaaaaaaaaaaaaaxxxxxxxxxxxxxxxaaaaaaaaaaaaaaab  O 41D. Immediate ...       A   74   aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxaaalO                          B   77   aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxaal . . .aO 41E. Co-workers ...      A   53   aaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxaaaaaaaaa O                          B   49   aaaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxxxxaaaaaaaa  . . .e  A With this procedure, John was able to give better output than theeA outside consulting firm and run the Talaris printer at full speedh of 16 pages per minute.b  +                      Part 3 of Wombat Magic  will continue next month