Intranet Reporting in UniVerse
Version 1.2 - Feb. 24 1999
Ross Morrissey
ross@filmworks.com
(206)281-1390 x378
The original version of this article was created to accompany a presentation at the October 15th 1998 Meeting of
SAPUG in Bellevue, Washington.
New material was added after a presentation at the February 17th 1999 Meeting of MVDBUG in Portland, Oregon.
Contents
Linking up UniVerse reporting to an intranet came about as a solution to a problem with library software customers seeking to validate the URLs they had stored in their databases. By making an ad-hoc report available on the intranet, we were able to use any one of a number of freeware URL validation tools, and the project was done in an afternoon.
Paper reports have a well understood user interface, but are expensive and can't be updated. It has always been possible to report to a flat file and browse it, but software needed to be written, and users needed to learn how to use it. By making these flat files available on an intranet, the users get their well understood user interface, they can print as little or as much information as they need, and use the data directly in their PC applications.
At Seattle FilmWorks, we have web servers running on the majority of our UniVerse hosts. Web-based reports have been well received by users, and one report has become the de facto home-page for one department. This page is discussed in detail below.
An intranet uses the tools of the Internet (servers and browsers) within a company's internal network (i.e. inside the firewall).
Just redirecting many large reports to a web page will save paper - the user will only need to print what they need, and it is possible to track report usage and eliminate unused reports that you may be printing routinely.
The savings and added convenience really don't compare to the power of hypertext reports.
It is easy to incorporate HTML in reports using standard Recall. By adding Hyperlinks to reports, they become more useful, allowing users to take action based on the report, or seek more detail, perhaps from a supplier's web site, or provide a link to a customer detail screen.
The web browser is a familiar application to the user (unlike ftp). It is easy to save a file from a web browser, and open it in another application. By careful formatting of the file, and corresponding selection of file extension, it is sometimes possible to directly launch a PC application.
Apart from some initial setup in Unix, these reports are produced with standard UniVerse PAragraphs, Recall Statements, SETPTR parameters, and DICT items. No Basic programming beyond I-types is required.
This has a number of advantages, including reduced development time and cost. Not only do you not have to program, but you don't have to pay for other people to program - the tools described are free.
Don't try the following on a live account until you've tried it in a test area first. Make sure you have backups and log what you're doing.
I've been working almost exclusively in UniVerse for the last four years - about the length of time the Internet has been popular. If anyone has special tips that you've found helpful on other platforms, let me know, and I'll include them in the next revision of this document.
We need a server and we have to tell the server where to find our data.
One big step on your intranet is installing a web server. We use Apache for all our internal Unix web servers. It's running internally on machines ranging from a 2.1 gig PC to a multi-terabyte server. We haven't had any problems with it. For details and a free copy of Apache, see http://www.apache.org. The rest of this article assumes you have Apache installed (pathnames will be slightly different with another web server, but the technique is identical).
The key to the entire intranet is one command: ln -s - it allows us to make the contents of any file or directory available as a local file or directory. The Unix command ln creates an additional filename, or hard link, for an existing file, and is indistinguishable from the original. ln -s creates a symbolic link to the existing item's name, and behaves much like a hard link. Creating a hard link to a directory can corrupt the data system, so we use a symbolic link. The contents of the link are the same as the contents of the original file or directory. A symbolic link (reports) is shown below.
Creating the Link
Move to your web document directory, and establish a symbolic link to your &HOLD& (or any type 19 file). Next, make your &HOLD& file readable and browseable to the world (you may not need this last step).
cd /usr/local/etc/apache/htdocs
ln -s /uvhome/\&HOLD\& reports
cd /uvhome/
chmod a+xr \&HOLD\&
This will create a directory, reports, that can be directly browsed from any web browser
on your intranet with an address like http://yourbox/reports. If you fire up your browser and load this file, you'll find any reports that are already in your &HOLD& file - you might be surprised at what you find - web servers provide a very convenient, detailed, scrollable directory listing.
You can follow the same steps add other type 19
files as required. Type 1 files can be used too, but don't get caught by the 14 character filename limit - something your users might not think of in the context of html files. You might find that you have a number of files that had been used for transferring data to PCs in the past - these are ideal candidates.
Here is a look at what we use:
$ cd /usr/local/etc/apache/htdocs
$ ls -al | grep '^l'
lrwxrwxrwx 1 ross mis 23 Aug 24 11:48 ML -> /rimmer/SFW.B/MKT.LISTS
lrwxrwxrwx 1 ross mis 15 Aug 3 15:15 PROT -> /sfw/SFW.B/PROT
lrwxrwxrwx 1 ross mis 16 Sep 24 17:39 WT -> /rimmer/SFW.B/WT
lrwxrwxrwx 1 root other 7 May 25 13:00 cgi -> cgi-bin
lrwxrwxrwx 1 root other 8 May 25 13:00 icons -> ../icons
lrwxrwxrwx 1 ross mis 22 Jul 20 16:49 mktshare -> /share/mkting/mktshare
lrwxrwxrwx 1 ross mis 23 Aug 26 11:45 pulls -> /rimmer/SFW.B/MKT.LISTS
lrwxrwxrwx 1 ross mis 12 May 27 14:45 reports -> /sfw/SFW/DOS
There are two quick ways to publish information on the intranet: printing to &HOLD& and using SAVE-LIST. Neither of these methods requires programming.
The simplest way to publish to the intranet from UniVerse is to print to &HOLD& files. Depending on the level of use of &HOLD& files at a given site, the intranet may already be populated with reports.
The contents of the &HOLD& file that was linked previously will be displayed with file sizes and create dates by browsing to the symbolic link.
Example &HOLD& listing
We can publish raw text or html to the &HOLD& file.
This is the default method of publishing to the Intranet. All that is required is that the output go to the &HOLD& file.
Use the &HOLD& option, 3 in your SETPTR. You can extend the page length to
give you just one header if desired. Use a banner name with the extension
.txt - this tells the browser to display the report as pre-formatted text.
This will give you a quickly browseable list of files in your VOC...
SETPTR 0,,,0,0,3,AS sortv.txt,BRIEF,NOHEAD
SORT VOC WITH F1 = "F]" (P
Large font titles are an obvious improvement to the appearance of reports, but much more is possible, graphics and hyperlinks can easily be added with I-types and make a great impression, cheaply.
Use page dimensions bigger than your report will be. The browser will
ignore extra white space when it returns the document to the user. Page widths
should accommodate any html tags - some of these columns can be wide!
I generally use 999 as a width. Page lengths should exceed the length
of the report - you don't want to display the HEADING twice because it
contains your html document header. I use a page length of 9999 by
default. Route
output to the &HOLD& file. Use an extension of .htm or .html on your
banner name, this tells the browser to interpret the html tags.
I've come up with some rules of thumb you may find useful...
- Start your HEADING with <html>
- If you want the users to automatically get the most recent version of the report when they open it, include <META HTTP-EQUIV='Pragma' CONTENT='no-cache'> in the <head></head> portion of the HEADING. This tells most browsers to over-ride the users cache settings and fetch a fresh copy each time.
- Use a <pre> tag at the end of your heading - white space is compressed to a single space unless the <pre> tag is used, messing up column alignment for reports.
- Don't use FOOTING unless you want a report as long as the page defined in your SETPTR
- Avoid Tables - they need a </table> tag in each line or in the FOOTING
- Make sure the column heading is wide enough for any html links provided by I-types - a line break will invalidate the link. This leads to the 309L width in the example below. The trailing white space is thrown away by the browser.
This report contains a list of digital reprints ordered by customers using 1-800-FILMWORKS and an IVR (Integrated Voice Response) application. The user can link to a page of thumbnails or a web page that allows them to re-generate (RE-Render Internet Prints) reprints for miscut orders (this is only available if the order has been processed). As you'll see later, this report is used frequently.
Link to Thumbnails from Original Roll column above
This is an example of a link to an external page - in this case thumbnails of the roll in question
from a pre-existing web application. By spending a little time on a dictionary item,
we provide a great deal of extra value for the user at little cost.
Link to Re-Rip from [Re-Rip] column above
The second link on this page goes to an application that the user would normally provide
with parameters. By defaulting to the parameters in the report, accuracy and
productivity are increased.
This PAragraph creates the IVR Incoming report. Note the width, length and option 3 in SETPTR. The .html extension on the name forces the browser to interpret the results.
HIP.INCOMING
001 PA
002 SETPTR 0,999,9999,0,0,3,AS ivr.incoming.html,BRIEF,NOHEAD
003 SORT DIP BY-DSND HIP.INDEX BY ID BREAK.SUP HIP.INDEX CUST.NO CUST.NAME SO.I
D ORDER.DATE ACCEPT.DATE ITEM IMAGE LINKS ID-SUPP HEADING "<html><head><titl
e>IVR Traffic at 'T'</title><META HTTP-EQUIV='Pragma' CONTENT='no-cache'></h
ead><body><H3>IVR Traffic at 'T'</H3><PRE>" LPTR
This report contains an I-type that provides one or two links depending on the order status...
Note the WIDTH - enough room so that the links are not folded in the html source. The column
headings are padded with spaces so that a STR(".",300) isn't tacked on the end of the headings.
As an alternative, the column headings could be suppressed, and included in the html of the HEADING after
the <pre> tag.
ED DICT DIP LINKS
0001: I
0002: IF SO.ID THEN RE.RIP ELSE ROLL.LINK
0003:
0004: Original ýRoll
0005: 309L
0006: S
ED DICT DIP ROLL.LINK
0001: I
0002: '<a href="http://photomail.filmworks.com/pmlogin.asp?P1=':CUST.NO
:'&P2=':ROLL.NUM:'&P3=0">':ROLL.NUM:'</A>'
0003:
0004: Original ýRoll
0005: 99L
0006: S
ED DICT DIP RE.RIP
0001: I
0002: ROLL.LINK:'':'<A HREF="'
:'http://film works.filmworks.com/cgi/control.cgi?Application=RE.RIP&HIPId='
:HIP.ID:'&E ffect=':EFFECT:'&OrderId=':SO.ID:'&OriginalPD=':ROLL.NUM:'&Frame='
:IMAGE: '&Process=D':SIZE+2:'X':EFFECT:'&Quantity=':QUANTITY:'&BizCode=0':1
:'&Button=Load':'">[ReRIP]</A>'
0003:
0004: Original ýRoll
0005: 309L
0006: S
The SAVE-LIST command will save a list of keys to a file. If we use some imagination in creating these keys, and use one of our intranet-enabled files, this can be a potent tool for making data available to PC applications on the intranet. The trick is to define virtual keys using I-types that correspond to rows of a spreadsheet.
This is a technique Brian Bond had introduced at Seattle FilmWorks several years ago. At first glance it is easy
to overlook the power of this method. By adding these files to the intranet with a symbolic link we get even
more versatility and ease of use.
Two useful formats for these virtual keys are Tabbed and .CSV. Tabbed virtual keys have fields delimited by tabs. .CSV is the Windows filename extension for comma separated values - a common format for spreadsheets and the PC applications.
This PAragraph gathers some marketing statistics and dumps the results into a tab-delimited
file that can be loaded into a spreadsheet for analysis. The COLVAL.n DICT items are arbitrary
DICT items, a mixture of D types and I types.
02 ED MKT.PL JLSTATS
0001: PA
0002: SELECT MCP STATS.DS
0003: SAVE-LIST MKTSHARE JLSFW.TXT
02 ED DICT MCP STATS.DS
0001: I
0002: COLVAL.1'L#6':CHAR(9):130ID:CHAR(9):COLVAL.3'R#9':CHAR(9):COLVAL.4'R#9'
:CHAR(9):COLVAL.5'R#9':CHAR(9):COLVAL.6'R#12':CHAR(9):COLVAL.7
0003:
0004: stats
0005: 80L
0006: S
SAVE-LIST MKTSHARE JLSFW.TXT in a web browser
These Tabbed virtual keys form a simple Data Mart that can be easily and effectively manipulated in Unix, using tools like sort, grep, egrep, and wc for analysis; and cut, awk, and perl to extract results. These Unix utilities run against flat files hundreds of times faster than similar queries in UniVerse. They aren't much use to end users, but if you have to make several passes through a file in a hurry, consider making one pass in Universe to create some virtual keys and do the rest of your work at Unix. I routinely work with virtual key files with tens or hundreds of thousands of records. Just getting a feel for the data would take hours of ad hoc reporting in UniVerse. It's surprising how good the Unix environment and its tools are for handling text files, and how quickly you can build a repertoire of one-line commands to do your work
(few tasks require a full-blown shell script).
Some Examples - task is followed by Unix command and roughly equivalent UniVerse command...
How many lines are there?
wc -l example.txt
COUNT EXAMPLE
How many lines containing the letters "blue" are there?
grep "blue" example.txt | wc -l
COUNT EXAMPLE WITH F1 = "[blue]" OR WITH F2 = "[blue]" ...
How many lines starting with the letters "blue" are there?
egrep "^blue" example.txt | wc -l
COUNT EXAMPLE WITH F1 = "blue]"
Create a subset, blue.txt, of example.txt containing lines beginning with "blue"
egrep "^blue" example.txt > blue.txt
LIST EXAMPLE WITH F1 = "blue]"
Create a subset, blue.txt, of example.txt containing the 2nd tab-delimited field of lines beginning with "blue"
egrep "^blue" example.txt | cut -f2 > blue.txt
SELECT EXAMPLE WITH F1 = "blue]" SAVING F2
How many unique values of the second tab-delimited field are there?
cat example.txt | cut -f2 | sort -u | wc -l OR
cat example.txt | sort -u +1 -2 | wc -l
SELECT EXAMPLE SAVING UNIQUE F2
Create a list of the second tab-delimited field for every 10th line beginning with "blue"
egrep "^blue" example.txt | perl -ne 'print unless $.%10' > blue.txt
SELECT EXAMPLE WITH F1 = "blue]" SAVING F2 SAMPLED 10
Capture the first 7 characters followed by the 11th and 12th characters of each line
cat example.txt | perl -lne 'print unpack(A7,$_).unpack("x10 A2",$_)' > new.txt
LIST EXAMPLE EVAL "@RECORD[1,7]:@RECORD[11,2]"
This PAragraph gathers the same marketing statistics and dumps the results into a comma delimited file that will loaded directly into a spreadsheet by most web browsers.
02 ED MKT.PL JLSTATS.CSV
0001: PA
0002: SELECT MCP STATS.DS.CSV
0003: SAVE-LIST MKTSHARE JLSFW.CSV
02 ED DICT MCP STATS.DS.CSV
0001: I
0002: '"':COLVAL.1'L#6':'","':130ID:'","':COLVAL.3'R#9':'","':COLVAL.4'R#9'
:'","':COLVAL.5'R#9':'","':COLVAL.6'R#12':'","':COLVAL.7:'"'
0003:
0004: stats
0005: 80L
0006: S
SAVE-LIST MKTSHARE JLSFW.CSV in a web browser
Note that Internet Explorer automatically launches Excel with a temporary copy of the .CSV file cached on the users machine. Changes can be saved to the users machine.
UniVerse allows us to define printer drivers in Unix allowing a great deal of flexibility for our reports.
If we wanted to make information available on the internet proper, we might use a driver that would
ftp the reports to an external server. If we had a perl script that nicely packaged the reports,
we could pipe reports to it with a printer driver - this would be the place to incorporate
tables and fonts if desired. We haven't found the need for printer drivers yet.
VMark introduced UVEXPORT in UniVerse release 6.3. It is documented in a VMark technical bulletin available on-line in PDF form at <URL:http://www.ardentsoftware.com/services/support/universe/pdfs/740074.pdf>. Once UVEXPORT is installed, the user supplies source and target filenames, a list of fields, and selection and sort criteria via a screen interface, and the specified file is exported to a flat file in either a delimited ascii (.CSV) or Lotus 123 (.WK1) format. You can't supply UVEXPORT a select list which limits its utility, but adding field delimiters is handled internally, which is much cleaner than gluing them in with I-descriptors. The WK1 format is useful for exporting to a spreadsheet - it exports column headings and a title, and can open directly to Excel (make sure you have an association set up in Windows Explorer).
Here is the UVEXPORT screen set up for a simple download of the keywords in the VOC.
+-------------------------[ UniVerse Export Facility ]-------------------------+
| File Export Help |
| |
+-Description--: Lotus Example Export Format-: WK1 -+
| Database : VOC SELECTION : WITH TYPE = "K]" |
| Result : VOCLIST.WK1 SORT : BY @ID |
| |
| Field #1 : @ID Field #9 : Field #17 : |
| Field #2 : TYPE Field #10 : Field #18 : |
| Field #3 : DESC Field #11 : Field #19 : |
| Field #4 : Field #12 : Field #20 : |
| Field #5 : Field #13 : Field #21 : |
| Field #6 : Field #14 : Field #22 : |
| Field #7 : Field #15 : Field #23 : |
| Field #8 : Field #16 : Field #24 : |
| |
| [ Help Region ] |
]Enter the name of a dictionary field you wish to export. Press for a -+
|list of all dictionary fields. Press when you are done and select |
|Export to generate the desired export results. |
| |
| |
| |
+------------------------------------------------------------------------------+
The browser opens this in Excel...
One of the advantages of using Apache to serve reports is the tracking of access that is provided. Using the access logs, we can determine who is using reports, and how often they are being used. This makes decisions about whether to modify or perhaps even whether to discontinue a particular report easy. Although we can't show that the user actually read the report, we are far ahead of the paper report in tracking usage.
There are many tools for analyzing server logs and if we were running into resource issues with our intranet, it might be worth installing one of them, but a simple scan through the file provides a lot of useful information in any case.
The ivr.incoming report has been requested how many times?
$ grep ivr.incoming /usr/local/etc/apache/logs/access_log | wc -l
374
Here is a detailed look at the most recent 10 accesses of this report:
$ grep ivr.incoming /usr/local/etc/apache/logs/access_log | tail
207.82.203.189 - - [12/Oct/1998:14:38:25 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 170763
207.82.203.19 - - [12/Oct/1998:14:46:50 -0700] "GET /reports/ivr.incoming.html HTTP/1.1" 200 170763
207.82.203.19 - - [12/Oct/1998:15:05:47 -0700] "GET /reports/ivr.incoming.html HTTP/1.1" 200 170763
207.82.203.19 - - [12/Oct/1998:15:10:23 -0700] "GET /reports/ivr.incoming.html HTTP/1.1" 200 170763
207.82.203.19 - - [12/Oct/1998:15:11:03 -0700] "GET /reports/ivr.incoming.html HTTP/1.1" 200 170763
207.82.203.189 - - [12/Oct/1998:15:19:11 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 147456
207.82.203.189 - - [12/Oct/1998:15:37:33 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 170763
207.82.203.189 - - [12/Oct/1998:16:29:48 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 179542
207.82.203.189 - - [12/Oct/1998:17:03:53 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 179542
207.82.203.189 - - [12/Oct/1998:17:04:02 -0700] "GET /reports/ivr.incoming.html HTTP/1.0" 200 179542
It really is satisfying to see users regularly accessing these intranet reports...
Paper reports are kept around as long as possible because of the time and effort involved in
printing them. If we don't print them, we can regenerate them more frequently, either when
the user wants them, or, ideally, just before that point.
It's always easier for the user to click on the link to their report and have it presented in one step. This can be accomplished one of two ways - you can write a UniVerse BASIC Program to invoke the report and return the result (perhaps via redirection) - or in most cases, you can build the reports ahead of time. This second approach is the one we currently use. Using the cron facility built into UniVerse, we set up hourly and daily batches of reports. The hourly reports are generally completed in less than five seconds, while the daily reports can take hours to run. The process involves three layers, crontab, a shell script, and a paragraph. You could do this in one step, directly from crontab, but splitting it up makes maintenance very simple. It's just a matter of adding a line to the appropriate paragraph when you want to schedule a process.
These crontab settings invoke two shell scripts, adrian, and walter. Adrian runs
every hour and walter runs Monday through Friday at 6 PM. Standard Output and
Standard Error are both ignored.
$ crontab -l
0 * * * * /sfw_uv/INTERNET.SYS/scripts/adrian 1>/dev/null 2>/dev/null
0 18 * * 1-5 /sfw_uv/INTERNET.SYS/scripts/walter 1>/dev/null 2>/dev/null
For further information on cron and crontab, consult your man pages.
These shell scripts (Unix's version of PAragraph) merely invoke UniVerse from the appropriate directories, and execute the VOC items ADRIAN and WALTER respectively.
$ cat /sfw_uv/INTERNET.SYS/scripts/adrian
#/bin/sh
cd /sfw/SFW
/usr/opt/uv/bin/uv "ADRIAN"
cd /sfw/SFW.B
/usr/opt/uv/bin/uv "ADRIAN"
$ cat /sfw_uv/INTERNET.SYS/scripts/walter
#/bin/sh
cd /sfw/SFW
/usr/opt/uv/bin/uv "WALTER"
cd /sfw/SFW.B
/usr/opt/uv/bin/uv "WALTER"
The paragraphs invoked by the shell script executed by cron contain the commands to be executed at the UniVerse level.
Here are examples of the two paragraphs we use...
ADRIAN
001 PA These commands are invoked by cron every hour
002 DO HIP.INCOMING
003 DO HIP.ORDER
004 DO HIP.CS
WALTER
001 PA These commands are invoked by cron every weeknight at 6:00 PM
002 PHANTOM DO CD.COUNT
Ross Morrissey has been working in UniVerse on Unix platforms for seven years and has created over 25,000 html documents over the last four years. He has been programming professionally for the last 17 years, the last 12 in multi-value environments. Before joining Seattle FilmWorks in 1998, Ross was a Senior Programmer Analyst working on library software at Geac Canada in Toronto.
- Version 1.2 - Feb. 22 1999
- Clarified Symbolic Links - added diagram
- Augmented SAVE-LIST section - added Tabbed and .CSV material
- Added UVEXPORT section
- Fixed HTML Typos
- Version 1.1 - Oct. 21 1998
- Updated Web Document to match PowerPoint Presentation
- Clarified and Expanded descriptions of examples
- Fixed Typos
- Version 1.0 - Oct. 20 1998
- First Publication on Internet
.
