Database Report Generator (DBReport)
Cynthia A. Chopp
The Database Report Generator (DBReport) of the Epicure Distributed Database System (EDDS) is a menu driven program which allows users to generate reports consisting of information from the Central Database System (CDB) . It was developed using VAX C and uses Epicure Screen Management (ESM) and Database Report Server/UTI (DBRPT) routines. DBReport runs on both terminals and workstations and has a standard Epicure menu interface. DBReport has online HELP, that can be used while the user is running the program.
DBReport allows users to choose searching keys, sorting keys, content keys, property keys, and output options, as well as automatic reports. The searching keys determine search criteria for what is to be retrieved from the database. The sorting keys allow the user to choose the order in which the returned information is to be displayed. The content keys allow the user to choose the data fields that will appear in the report. The property keys allow the user to screen devices output, by choosing specific properties. Output options consist of the screen, file, or both. The user has the option of picking where output is written and naming the filename as well. If the user does not want to continually setup all the keys each time they run a report, they may create an automatic report. Some automatic reports are already defined. The user may define and retrieve their own automatic reports. Users may also use these files in a batch job run. (Batch job runs, using these files, are discussed in a later section.)
DBReport contains nine Main Menu options from which users may make selections. It will take selections made from these menus and access report/search routines to retrieve database information requested, sort the returned information if specified, and output the values of the various property and attribute keys chosen . The nine Main Menu selections are SEARCH KEYS, SORT KEYS, REPORT CONTENTS, PROPERTY OUTPUT, OUTPUT OPTIONS, AUTOMATIC REPORTS, RUN OPTION, PRINT SCREEN, and EXIT PROGRAM, and are described in the following sections.
Users have the ability to search the entire EPICURE database  for specific information, sort the information returned by the search, display various database fields of device information that fit their chosen search specifics, output specific properties, and output this information to a file, screen, or both. Users also have the ability to select or create automatic reports, allowing them to minimize menu selections.
The following menu is used for Searching, Sorting, Report Content, and Property Output. It will be referred to as the General Menu throughout this document. Since the menuing in the Searching selection is more complex than that of Sorting, Report Content, and Property Output, Appendices A, B, and C have been provided to give a thorough description of this menu choice. Appendix A contains examples related to adding search keys, Appendix B contains examples related to deleting search keys, and Appendix C contains examples related to modifying search keys.
The Clear Selections option will erase all selections currently displayed. DBReport will bring up a dialog box asking the user for verification that they want everything that is currently displayed to be cleared. NOTE: All dialog boxes of the DBReport program default to ``NO''.
The Add Selections option will bring up a menu of possible selections to add to the list.
The Delete Selections option allows users to delete selections that have already been made. When the Delete Selections option is chosen, a menu containing selections already made will appear. At this point the user may delete a selection by moving the cursor onto it and pressing return.
The Modify Selections options will allow the user to change an already existing selection to a different one. DBReport will display a menu of the keys already chosen. The user will select a key from this menu to modify or may exit with no changes. If the user chooses a key to modify, another menu will appear for the user to choose the new selection.
The Ok Selections option allows the user to save the current settings and return to the Main Menu.
The UNDO Selections option will bring up a dialog box asking the user for verification that they want everything that they changed during that entry into the selection to be replaced with what was there before they selected that Main Menu option.
The following sections describe the eight Main Menu options available:
The SEARCHING KEYS menu is a list of keys in which devices can be selected according to specific data or information related to them. The maximum number of searching key selections is three. For each searching key chosen, a maximum of four properties are allowed, each having a maximum of five data selections. (Properties are defined as reading, setting, status, control, reading alarm, status alarm, ``any'' property, and ``all'' properties.) Since not all searching keys have properties related to them, those with no related properties will only have the maximum of five data selections. All searching key selections are logically ``and''ed together for the search, while all property and data key selections are logically ``or''ed together for the search. An example of a searching selection is the following: A searching key selection of crate, with a property key of reading, with a data selection of 2,3,4 for crate values, with another property key selection of setting with a data selection of 5,9,12 for crate values. Only one searching key was chosen -- crate, yet it contained multiple crate numbers for multiple properties.
When adding a searching key, a menu will appear along with a listing containing those selections which have already been made. This menu along with a brief description of how the searching key input will be accepted, is shown in the table which follows. Only the searching keys will appear in the menu for the user to see.
When a user makes a selection, it is displayed to the right of the menu. The selected searching key will determine the form of user input. Appendix A shows all different ways in which searching keys may be added. If a user selects three searching keys, the user will not be allowed to add any more new searching keys. They will, however, be allowed to add to any existing property and attribute keys. The maximum number of property keys that can be added per searching key is four (that is, if the search key has any related properties), and a maximum of five actual data selections (these are the values of the searching key selected).
Some of the search selections shown have ``*'', ``**'', ``***'', or ``****''. This means that not only will users be choosing a primary key to search, but also a property key, and then a specific data field to the primary key as well. The following lists are property key selections related to their corresponding primary search keys. Not all primary search key selections have property key lists. Most primary search key selections have only data key selections that are specific fields.
In the above property listings, the property selections of Any and All are shown. If the user selects either property no more properties will be allowed to be added since selecting one covers all properties. If a user selects Any, the data selections searched for may be found in any properties of a device. If All is chosen, the data selections searched for must be found in all properties of a device.
The SORTING KEYS menu is a list of property and attribute keys by which the devices can be sorted for a custom formatted output. The maximum number of sorting keys allowed is five. Devices will be sorted in the order in which the sorting keys are chosen. Sorting is done using the VMS SORT Utility Routine. Each sort selection made will be displayed to the right of the menu. Once done selecting sort keys, users may leave the menu by choosing the menu option *RETURN TO PRIOR MENU*.
The following is the SORTING KEYS menu from which users may make selections, along with a description of how they will be sorted (numerically or alphabetically).
All sorts will be in ascending order (lowest to highest) for numerical sorts and alphabetical for character sorts. Since sorts may be property related, multiples of a device name may be output. An example of this follows.
The REPORT CONTENTS menu is a list of property and attribute keys for formatting user output in which users can choose to create what their output is to look like. Users can have up to thirty-eight content keys output and may specify duplicates. When users are done specifying their report contents, they may select the *RETURN TO PRIOR MENU* selection to return to the General Menu. The following displays the REPORT CONTENTS pulldown menu, along with a description of the width of each field when output. With every report content selection, the maximum size of each field will be used followed by a single space to separate the fields. Thus, the number in parenthesis below is the field size plus one for the single space needed to separate it from the next output entry. For the content selection of Device Name, space has been allocated for both the device name and property.
When users choose a report content selection, the choice will be displayed to the right of the menu. Another display on the screen will show users a running count of the width of their output, along with how many characters are left to fill a 130-character screen. The user may choose as many report content selections as they wish (up to thirty-eight). A maximum of 500 characters can be output per line. If an output greater than a 130-character screen is requested, the user may use the PF1 left arrow and PF1 right arrow to scroll their screen left and right to see the entire display. Any output greater than 500 will be truncated to 500 characters. The order in which users choose their report contents will be the order in which they will be displayed. If users do not choose any report content, a default of device name will automatically be output.
The column headings of all the REPORT CONTENTS menu options, are dependant on what the users choose to output. Users will not have the ability to choose or create their own column headings, and no titles to reports will be created. Titles may be a future option.
The PROPERTY OUTPUT menu is a list of properties that may be specified by the user to screen devices output. If the user chooses to output only reading property information on the devices, they may do so by selecting Reading, and RETURN TO PRIOR MENU. If the user wants both reading alarm and status alarm properties they may select each of these options, and then select RETURN TO PRIOR MENU. Also, if the user chooses to output the devices as is, they do not need to select a property at all. If no properties are chosen, the program defaults all properties to be output. The maximum number of property output keys allowed is six. As each property selection is made, the program will display, to the right of the menu, the users selections. Once done selecting property keys, users may leave the menu by choosing the menu option *RETURN TO PRIOR MENU*.
The following is the PROPERTY OUTPUT menu from which users may make selections.
The OUTPUT OPTIONS menu allows users to have their report output placed in a file, on the screen, or both. If users do not choose an output option prior to running a report (whether it is an automatic report or not), the output will default to the screen. The output location will stay the same through all reports unless changed by the users. There will be no limit on how many reports can be made. If using the file output and creating more than five reports with the same name, reports will begin to be deleted.
The users will have the option of choosing from two defined output file locations or choosing their own file location (limited by available disk space). If users choose output to go to a file, the file may be located in the user's home directory (SYS$LOGIN), the scratch disk (USR$SCRATCH) or a specific filename and directory. NOTE: A scratch disk is only located on the WARNER system and not on DISNEY. The name given to an output file will be DBR-(Time Stamp).DAT, DBR-(Time Stamp).TMP, or the user specified name, depending on what the user chose. Choosing a .TMP filename, however, will allow the Mad Deleter to clean up the files every two days .
If the chosen output location is user-defined, the user will be prompted for a directory and title. If the user chooses not to type one in, they will be notified later in the run that the file cannot be opened, and will be asked if they would still like to run with output only going to the screen.
The pulldown menu from the OUTPUT OPTIONS will look as follows.
After output is viewed, or the run completed, the following will appear on the screen.
The AUTOMATIC REPORTS menu allows users to choose reports already set up, save their own created reports, and retrieve their own created list. The application supplied reports have a defined report of information that is related to a particular source class, handler id, dap type, etc. Users will only need to choose one of the automatic reports, and a particular source class (for selections Normal and A-to-D Type), or retrieve a canned report of their own, and DBReport will set up the appropriate form of keys and report information. If users want the information to be output other than the default of the screen, they must change their Output Option selection after choosing their automatic reports. By choosing an automatic report, users will not have to go through selecting many menu keys, but instead, once a canned report is selected DBReport will go through the Run Option menu automatically.
The following is a list of the AUTOMATIC REPORTS options that can be selected at this time, along with a description of their output. If the user selects the Survey Info selection, a dialog box will appear stating that the report has not been implemented.
The following submenu appears after an automatic report selection of Normal or A-to-D Type is selected. The users must choose a source class from which their report is to be made. If users select Cancel, the automatic report chosen will not be run. They will, however, be brought back to the AUTOMATIC REPORT menu to make another selection. If they choose to Cancel from the AUTOMATIC REPORT menu, they will be brought back to the Main Menu, and no automatic report will be started.
The following is an output example of what the Normal option of the automatic reports, with a source class chosen of P0, looks like.
The property abbreviations that the DBReport program will be using are as follows: RE stands for reading, SE stands for setting, ST stands for status, CN stands for control, RA stands for reading alarm, and SA stands for status alarm.
The Save Current Selections option allows users to to create their own files of specific keys, so they may retrieve them later without repeating report selections. The Retrieve Personal File selection allows users to read in their own files created by DBReport just like an automatic report file would be brought in. These two options allow frequent users of DBReport great time-saving efforts, by making it easy to generate the same report files for their own personal use. These user created files may be run in a batch job of the DBReport, for those listings that are wanted frequently (every day or week). This is described more in section Batch Job Runs.
The RUN OPTION menu initiates the database search. It takes the most recent selections from the searching, sorting, content, property, and output menus, and requests the database information. When the RUN OPTION is chosen, a screen containing the users choices is shown, along with where output is going to be placed. A visual of this screen is shown in FIGURE 1. If an output file is chosen, and it cannot be opened, the user will be asked whether to continue with the run with output going to screen only, or cancel run. If no file errors occur, a question will then be asked whether to begin the report or to cancel. If users choose to create the report and at least one search key is chosen, the report will begin to be built. If no search keys are chosen, an error will appear on the screen stating that at least one search key must be selected for a report to be made. If users choose to cancel the run of the report, they will be brought back to the Main Menu, and their previous selections will be in effect until changed.
The PRINT SCREEN menu allows the user to output information contained on their screen to their specified printer. Once the user selects PRINT SCREEN, a list of all available printers is shown. The user may scroll through the printer options using the arrow keys until they find their choice and make their selection. Please be aware that only what is being viewed on the screen, prior to selecting the PRINT SCREEN menu, will be printed. The PRINT SCREEN menu is locked out for most of the DBReport program. If users want output to go to a file, select the appropriate output option.
The EXIT menu option allows the users to cleanly terminate execution of the DBReport program. By choosing the EXIT option the users will exit DBReport, their screen will return to its original width, and they will be put back to the location from which the DBReport program was selected to run.
To run DBReport as a batch job, users must first run DBReport as a menu driven program and create and save a file with all selections of keys wanted. The output option must be set to one of FILE ONLY. If it is not defined to file only, the program will default it to that. If output was to go to the screen, it will be sent to the SYS$LOGIN: directory (top directory) with the filename DBR-(Time Stamp).TMP. Next the user may either type the following at the dollar sign prompt (to run DBReport themselves), place the following in an EVERYNITE.JOB file, an EVERYWEEK.JOB file, or an EVERYMNTH.JOB file, (depending on when the user wishes to have the file run):
(Where DIR:filename.xxx is the directory and filename of the file of keys set up and saved by the user through the DBReport menu driven program.) Please note that it is very important to include the directory of where your file is located so the program may find it. If the above line is placed in an EVERY****.JOB file, this file must be located in the users SYS$LOGIN: directory. The user must then mail a message to SYSTEM stating what type of file they have and their login name. This information is needed by the system manager to set up the EVERY****.JOB. If the user chooses to run the program as an EVERY****.JOB an EVERY****.LOG file will be created after each run in their SYS$LOGIN: directory. This file will show if the job ran successfully or not.
The first step in using the DBReport program is to select a searching key(s). At least one searching key must be chosen for DBReport to run. This key(s) will be used to search the entire database for specific devices. After this is done, the sorting, content, and property keys may be chosen. These different sets of keys will be used to sort the information gathered, output the specifics of the device(s) wanted, and output only those properties wanted. If no property output keys are selected, the program will output all properties as a default (reading(RE), setting(SE), status(ST), control(CN), reading alarm(RA), status alarm(SA)). For the report contents, the default is for device name to be output. No sorting keys are needed to run. Once these selections have been made, the location may be chosen. Output may go the screen, to a file, or both. These files include location in the home directory, as well as on the scratch disk. Users may also choose their own directory and file. Default for output location is to the screen only.
When all selections are completed, the run option will execute the file. The program will display all selections made and wait for final responce of yes or no to run.
If output is to the screen, the information will be brought up using the TPU editor , which can search, scroll, etc. The file, though brought up through TPU, has the keypad defined like EDT . To scroll the screen left or right (for output larger than 132) press PF1 right arrow, or PF1 left arrow. This will scroll the screen 50 characters to the left or right. Also, any data field containing X's means that the field in question contains invalid data which cannot be retrieved or found in the static library, or is out of range. Devices that contain a dap type of CRYO will not be output.
When completed, the number of devices found in the search will be shown, along with the total number of devices actually output. Also, the total search time DBReport took will be output, and finally the total number of devices found in the database will be output.
The user may choose an automatic report which will bring in all searching, sorting, report content and output keys. These canned reports may be of the ones already defined -- Normal, A-to-D Type, Synthetic, Cryo, or Survey Information (not implemented), or they may be ones defined by the user. After selecting keys, the user may save his or her own selections under a directory and filename to be retrieved later. This file can also be used in a batch job run of the DBReport. This approach saves the time needed to select the same keys over and over again, along with manually having to run DBReport every day it is needed. Once an automatic report is brought in, it drops through to the Run Option menu. If the user would like to make modifications to the selections brought in, he or she may do so by canceling the run. All automatic reports brought in can be modified to the user's specifications through the menus.
If a DISK QUOTA ERROR occurs while running DBReport, users may need to do the following for more space. At the dollar sign ($) prompt type the following: define/job sys$scratch usr$scratch[yourusername] Where it says ``yourusername" please put your own user name. This area is temporary storage and will allow all files being used, to be stored there.
In future versions, titles to reports can be created by the user or DBReport. At this time, no titles are added to the output.
Another future option may be the ability to choose whether an output listing may be sorted in ascending or descending orders.
In future versions the Automatic Report of Survey Info may be completed. At this time, due to lack of data this selection cannot be processed.
Also, another feature that may be added is to be able to select other user's reports or the user's own from a menu listing, instead of typing in the directory and filename.
CC: Normal Distribution
KEYWORDS: Database, Application, Report Generator
Four paths may be taken when adding a searching key. When the Searching Key menu is chosen and the user wishes to add a key, the user may ...
Two paths may be taken when deleting a search key. When the Searching Key menu is chosen and the user wishes to delete a key, the user may ...
Two paths may be taken when modifying a search key. When the Searching Key menu is chosen and the user wishes to modify a key, the user may ...