Basic MV Reports - A New Way to Handle U2 Reporting
There are many ways to report and analyze data contained in your U2 databases. I would like to share with you a simple process that overcomes many of the obstacles normally associated with analyzing MultiValue data. To meet my real-world needs, I have developed a callable UniBasic subroutine that will convert your U2 data to transportable DBF files (dBASE 4) that can be natively used by Excel as an ODBC source for pivot tables or reports.
dBASE is a venerable file format that supports up to a 2gb file size and can contain hundreds of thousands of records with up to 255 fields. These are theoretical values, but I've created practical applications with very large data sets without encountering any capacity issues.
Unlike CSV, dBASE is a binary format, so fields can be designated as text or numeric in a way which is recognized by Excel. dBASE files can be compressed. It is a very effective format for use with the string data stored in U2 databases.
Basic MV Reports (BMVR) helps you present Unidata data sets as Excel pivot tables. Pivot table templates can be distributed separately to your user community and conveniently reused when source data is updated on demand or by scheduled phantoms. The process can be integrated into any menu system that can call UniBasic subroutines.
The subroutine supports two dBASE file creation modes: FULL mode creates a stand-alone file that can be opened by Excel. INFO mode creates component elements of the .DBF format which can be combined with the stored results of other runs of the same report. This supports a form of data warehousing and can be very efficient when managing historical data.
You control the selection and formatting of your data. Basic MV Reports is not a report writer. It operates as a form of middleware dependent on your selected and formatted data. Multi-user support is provided through arguments that utilize the indexing you provide on your source data.
The steps to implement a report can be summarized as:
- Compile and catalog the BMVR.DBASE.ENGINE subroutine in any U2 directory
- Create Q-Pointers for all data files that are required for your report
- Run your UniBasic program to select, update and format your report data
- Call BMVR.DBASE.ENGINE with required arguments
- Handle the delivery of the resulting .dbf file to a local or network share
- Open your Excel pivot table template designed for a specific ODBC data source
- Refresh your Excel template
Free is a Very Good Price
No strings, no fine print, I'll send you all the code and instructions needed to use this no-nonsense solution to your Unidata reporting requirements. There is no charge or obligation and you are free to use, modify and integrate these tools as you see fit. All code is in text format and contains no binary or compiled elements. Free really is a very good price, and the value is demonstrable.
Just email your request to: basicMVreports@gmail.com and I'll get a package to you immediately.
My mission is to empower you to get more value from your Unidata/Universe databases without the high cost and complexity often associated with MultiValue reporting solutions. Utilizing Excel pivot tables as a presentation layer allows your users to expand their analysis without tying up your programming staff. One back-end solution can support many end-user requests.
If you're pleased with the value and have other needs, you're invited to reach out to talk about it. For more information, please check-out my website at: www.basicMVreports.com .
Thanks in advance for your consideration of Basic MV Reports. I look forward to hearing from you.
Source File Configuration and Multi-user Implementation
Create a standard U2 file to hold the data for your report. The file must be sized to hold the maximum number of records you expect to include multiplied by the number of simultaneous users you expect. This is a temporary file that is cleared after the .dbf output is created. Each record is keyed using a unique session ID and a counter. Use @LOGNAME : @TTY concatenated with a numeric record counter to create a unique record key. Create an attribute (typically, UNIQUE_ID) on the file and INDEX this attribute. Store @LOGNAME : @TTY in this attribute.
Create dictionary attributes for each data element you intend to include in your report. Define each attribute with the position, length and data type as you normally would. All attributes are single valued. The display name (dictionary attribute #4) is used as the .dbf file column header. These names must be 10 or less characters long, must be in all CAPS and must not contain any special characters except the underscore. You can also create a new dictionary attribute for the .dbf column header and specify that attribute when BMVR.DBASE.ENGNE is called. This can be useful if you have other uses for your reporting file that require a more descriptive display name.
Populate the Reporting File
One of the first and last steps in your data population routine will be to remove records from the report file for this unique user session [Figure 1].
UNIQUE = @LOGNAME:@TTY CLEAR.CMD = "SELECT <your report file> WITH UNIQUE_ID LIKE ":QUOTE(UNIQUE) PERFORM CLEAR.CMD CAPTURING SPONGE CDONE = 0 LOOP UNTIL CDONE READNEXT CLEAR.ID ELSE CDONE = 1 DELETE <file variable opened to your report file>,CLEAR.ID REPEAT
Figure 1
Your report file population program can be simple or complex. You are in control of all joins, lookups, calculations and other data transformations that you require. When each reporting record is completed it is written to the report file using a key equal to @LOGNAME:@TTY:"*": <counter>.
You might be asking yourself why the subroutine does not support virtual attributes since the ability to use I-type and V-type attributes is an important feature of U2. The short answer is "simplicity and performance". Using a dedicated and indexed source file populated using UniBasic is simply the most flexible way to manage large amounts of data. Your report population subroutine can calculate virtual attributes in line or duplicate the results using UniBasic as needed.
Yes, this may seem like a lot of additional work, but the results can be very well received by your users. Pivot tables provide users with the tools to get new insight from data sets and a single pivot table can satisfy numerous reporting requests.
Control the operation of the subroutine through the arguments in Figure 2.
SUBROUTINE BMVR.DBASE.ENGINE(INF,INFLDS,XKEY,XVAL,OUTF,OUTMD,RESULT)
Figure 2
INF - the name of the Unidata file containing the data to include in the output. This is 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. Only D-type attributes are supported.
XKEY - the name of an indexed field on the data file. This is a value unique to the current report and can reference a specific user session or a 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. Value 3 sets the operating system (W = Windows (default) or U = Unix). This setting determines operating system file copy and delete commands.
OUTMD - multi-value 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 equals "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, must be in all CAPS 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.
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.
Code fragment to call Basic MV Reports subroutine.
Insert the code in Figure 3 into your report population routine to call the dBASE generation. Upon return, the .dbf file will be located per your specification.
INF = "<your report source file name>" INFLDS = "ALL" XKEY = "UNIQUE_ID" UNIQUE = @LOGNAME:@TTY XVAL = UNIQUE OUTF = "" OUTF<1,1> = "<name for .dbf file>" OUTF<1,2> = "<path where you want the .dbf written>" OUTF<1,3> = "U" OUTMD = "" OUTMD<1,1> = "FULL" OUTMD<1,2> = "YES" OUTMD<1,3> = 4 OUTMD<1,4> = "NO" RESULT = "" * CALL BMVR.DBASE.ENGINE(INF,INFLDS,XKEY,XVAL,OUTF,OUTMD,RESULT)
Figure 3
The dBASE routine can handle a very large number of records without overflowing shared memory because the .dbf file is written to disk sequentially[Figure 4].
OSBWRITE DBF.DATA.REC ON DBF.FV AT DBF.BYTE DBF.BYTE += LEN(DBF.DATA.REC) DBF.DATA.REC = ""
Figure 4
Upon return you handle the delivery of the file. Clean-up the reporting file by removing all the indexed records you added for this instance of the report.
Delivering Results to Users
The final .dbf file must be copied to a specific folder/file name defined in the ODBC configuration of your pivot table. The best results are obtained by configuring a folder on the root (C:\) drive of a user's desktop.
When processing an on-demand report request from a single user a simple way to make this happen is to send the .dbf via your email system. You can include both the .dbf and a copy of the associated Excel pivot table template as attachments to the email. The email message provides instructions for the user to detach the .dbf to the defined ODBC folder.
When processing a report generated by a phantom and intended to be accessed by any number of users, the .dbf can be written to a Windows network share. Macro programming in the pivot table template copies the file from the network share to the user's ODBC folder. The user can select from available .dbf versions. The pivot table template uses a defined file name in a defined location.
Note that the Excel pivot template can be refreshed against new data repeatedly while maintaining all formatting and calculations. The pivot table can have multiple pre-formatted tabs.
Figure 5 is a simple example of how Basic MV Reports delivers results. The pivot table itself can provide a significant amount of row-level calculation; data formatting and, of course, the slicing & dicing that pivot tables are designed for.
Figure 5
MultiValue Data Handling
Handling multi-value arrays in your report requires that you write an individual report record for each element in the array. Build a base report record containing data for all single value elements you are using and use this as a base for each of the multi-value array elements.
Figure 6 is an SBclient user interface displaying a single U2 record using an MV array detailing the invoice line items for a billing job.
Figure 6
Converted to dBASE and displayed in Excel the results look like this. The source data could contain any number of U2 records with the MV arrays flattened. This allows the pivot table to filter and sort using any combination of data elements. For example, you might want to compare pricing across customers for one or more invoice codes [Figure 7].
Figure 7
Setting Up ODBC Connections In Excel
In Windows 10, access the Control Panel; double-click Administrative Tools; double-click ODBC Data Sources (32-bit) to display this dialog [Figure 8].
Figure 8
Configure as shown by selecting dBase IV and the directory where you are storing the .dbf files.
Open a new Excel workbook and Insert a pivot table. Select "Use an external data source" and click on "Choose Connection". In the next dialog click on "Browse for More …" and then click on "New Source". Select OBDC DSN from the list and click Next. Choose the OBDC data source that you created above. Finally select the .dbf file from the displayed list and click Next to add a description of this connection and click Finish. Click OK on the final dialog box. You can now begin to format your pivot table. After formatting and saving the pivot table you can use this workbook again each time you update the .dbf table that you pointed it at.
You may already know about OBDC data sources. For more information you can check this website or any of the numerous sources on the internet.
