This allows query reports to be stored and used whenever required
Queries can also be setup to produce excel spreadsheets and
text files
The MVtools query reporting tool is a front end for the native query language called ACCESS or ENGLISH. The reports are entered using RPGMAN. The Mvtools make use of todays PC software such as excel and can prepare excel spreadsheets direct from the reporting tool using the transfer definitions defined in FTPMAN.
RPGMAN can be used simply to print or display records but can also be used to extract data to a host system file and optionally transfer it to an area accessible from your PC desktop. The extracts use sequetial file access and append one line from the report at a time to allow very large extracts to be prepared. This tool can be used to produce ‘normalised’ records from an Mvdatabase.
A database of reports can be entered and output to printer, screen textfile or comma separated values.
For all this to work the MVOB SPEC item must be set up properly with details of the host system working directory and the transfer definition item to use to access a remote machine. Transfer definitions can be entered using a screen accessed from TCL using the command. TXDEFS
Formatting the Data
Data that is transferred using the FTP command will not be
formatted or manipulated. If data is required in a different
format to that which exists on the MultiValue database then
the Query Reporter should be considered for use. For example
when ‘Relational database tables are prepared from the Multi-valued
database.
To do this properly will require some knowledge
of dictionary items. Use FLDMAN
to create dictionary items using a screen.
Knowledge of query report options such as
BY-EXP.
To make a Relational database from your
MV database you will have to create separate records for
each multi-valued set.
Start by using the report query generator
RPGMAN to produce a simple listing for
each new table on the relational database.
Extracting data for loading into excel Setting the output options to ‘X’ will result make the Query Reporter select the required items and extract them to a host system .csv file. The name of this file is a field in the report definition screen. The file will be produced in the default working directory for the user as defined in the system setup (MVOB SPEC) item. The file will even be transferred from here to a remote machine if any transfer is defined for the user or for the whole system. This means that the file can be opened from a directory directly visible to the users PC and can be opened in excel. Values will be wrapped in double quotes bu default but this can be switched off in System settings
Any transfer definitions can be set up through a separate data
entry screen
This tool works from the TCL command >RPGMAN