Implementing BASIC MV Reports

Basic MV Reports is a method that allows you to present your Unidata data sets as Excel pivot tables. Pivot tables can be distributed separately to your user community. Source data is updated on demand or by scheduled phantoms. The process can be integrated into any menu system that can call UniBasic subroutines.

• Compile BASICMVREPORTS in any Unidata directory
• Create Q-Pointers for all data files that are required for your report
• Write a UniBasic program to update and modify your report data
• Call BASICMVREPORTS
• Handle the delivery of the resulting .dbf file to a local or network share
• Create an Excel pivot table template with a specific ODBC data source
• Refresh your Excel template

BASICMVREPORTS is a self-contained UniBasic program that produces a binary data file in dBase (.dbf) format. You control its operation through the following arguments.

INF – the name of the Unidata file containing the data to include in the output. This is typically a file constructed using your own UniBasic routine that accepts input; performs selects; normalizes multi-value data sets; and performs conversions and translations.

INFIELDS – dictionary items to include in the output. If set to “ALL” then all D-type attributes are included.

XKEY – the name of an indexed field on the data file. This is typically a value unique to the current report and can reference a specific user session or a particular phantom calling the report. If the value provided is set to the literal “SELECT-LIST” then the value in XVAL (next argument) is the name of the select list to use.

XVAL – value to use when selecting records from the source data file. If this value is multi-valued (delimited with CHAR(253)) then it is handled as a list of keys and no further select is performed. If the value in XKEY is “SELECT-LIST” then the value in XVAL is the name of the prebuilt select list which is retrieved. Otherwise the value is used as the indexed key. The type of selection is determined by the dictionary of the indexed field.

OUTF – path and name for the completed dBASE file. Value 1 is the name of the file; value 2 is the operating system path.

OUTMD – multivalue list of mode type switches that control the operation of the report generator.

If OUTMD<1,1> equals the literal “FULL” then the output is a full formatted dBASE file written with the .dbf extension. If value 1 equal “INFO” then the output consists of dBASE component parts that can be later combined and assembled into a complete file.

OUTMD<1,2> is set to “YES” if conversion formatting is to be applied to the output data. The default is “NO” which uses raw data from the source file.

OUTMD<1,3> is the dictionary field number to use as the column heading in the output. The default is attribute 4 which is the normal field name. Field names must be 10 characters or less and cannot include some special characters. Using this switch allows you to specify a name for each column in the output without interfering with other uses of your source file.

OUTMD<1,4> is set to “YES” if the output file is to be compressed.

RESULT – returns “OK” if all went as planned. This return argument is set to “ERROR” followed by an error message if a problem was encountered.