AGINGREPORT - enhanced Aging report

In this third example, an aging report is enhanced to be more readable.  This shows the use of relative enhancements, which are those applied relative to the occurrence of text or regular expressions anywhere on the page.

uf90c –i sample3.txt –f advanced.rul –p pdf –o client:aging.pdf

This statement header identifies this rule set.

[AgingReport]

The only detect statement required is this one, looking for the report title at column 50, row 2.

detect 50,2,"Detail Aging Report"

These constants are used throughout the rule set.

# set up document constants

const MAXCOLS=131                                        # max cols to output

const MAXRCOLS=130                                        # MAXCOLS-1

const LEFTCOL=.5                                        # use 1 if empty

const RIGHTCOL=131.5                                # LEFTCOL for symmetry

const MAXROWS=66                                        # max rows to output

This report should print in landscape orientation, rather than the default portrait.  UnForm will scale the columns and rows to 131 by 66.

landscape

dpi 1200

gs on                                                        # graphical shading

cols MAXCOLS                                                # max output cols

rows MAXROWS                                                # max output rows

pcopies 1                                                        # max # of copies

The title "Aging Sample" will appear in PDF document properties.  It is ignored for laser output.

title "Aging Sample"                                # view in PDF properties

The following prejob code demonstrates the use of sdOfficeÔ to mine data from this report and export it to Microsoft ExcelÒ.  SdOffice can be running anywhere on your network on a system with Excel.  The code relies on your setting two variables correctly.  First, the sdo$ variable should be set to the path to the sdOffice client program sdofc_e.bb.  In addition, the value of gbl("$sdhost") needs to be set to the address or hostname of the system running sdOffice.  An optional way of doing this is to define an environment variable prior to running UnForm, called SDHOST.  If you use that alternative, then comment out the x$=gbl("$sdhost") line.

The code here contains enough error handling to ignore the code if sdOffice isn't present or fails to execute.

prejob {

 # set up sdOffice export to Excel

 # set to path to your sdoffice *.pv programs

 sdo$="/u0/sdofc/sdofc_e.pv"

 # You can set the environment variable SDHOST, or use this

 # stbl function to define the sdOffice server address

 x$=gbl("$sdhost","bcj")

 # initialize excel

 call sdo$,err=prejob_done,"newbook","",errmsg$

 if errmsg$>"" then goto prejob_done

 sdofc_init=1

 call sdo$,"show","",""

 call sdo$,"setdelim |","",""

 call sdo$,"writerow ID|Name|Phone|Over 60|Total","",""

 call sdo$,"format row=1,font=Arial,size=12,bold","",""

prejob_done:

}

The prepage code block starts with code that exports data to Excel, but only if the prejob code block successfully initializes the sdOffice connection.  In addition to that code, it also sets two numeric variables, colw and scol, based upon positions and widths of report column headers.  These values are used later in the rule set for fonting and line drawing.

prepage{

 # if prejob hasn't initialized sdoffice, skip this code

 if sdofc_init<>1 then goto sdofc_complete

 for row=1 to 66

               ln$=text$[row]

               # customer heading row contain phone numbers

               x=mask(ln$,"\(...-...-....\)")

               while x

                       custid$=mid(ln$,1,6)

                       custname$=trim(mid(ln$,8,30))

                       custphone$=trim(mid(ln$,38,14))

                       x=0

               wend

               # totals - 50 plus spaces followed by digit-.-digit-digit

               x=mask(ln$,"^"+fill(50)+".*[0-9]\.[0-9][0-9]")

               while x

                       amount60=cnum(mid(ln$,87,11))

                       amount90=cnum(mid(ln$,98,11))

                       amount120=cnum(mid(ln$,109,11))

                       over60=amount60+amount90+amount120

                       total=cnum(mid(ln$,120,11))

                       export$=custid$+"|"+custname$+"|"+custphone$+"|"

                       export$=export$+str(over60)+"|"+str(total)

                       call sdo$,"writerow "+export$,"",""

                       x=0

               wend

 next row

sdofc_complete:

 # Now for some tricky code.

 # Agings can have different headings and column widths

 # To use version 5 features allowing variable columns and rows,

 # the following code will calculate starting positions

 # and column widths. It assumes a consistency in column widths,

 # 1 char negative, and 1 blank space between each column

 hd1$=text$[7]                                # temp heading line with agings

 x=pos("Type"=hd1$)

 xhd1$=trim(hd1$(x+4))                # remove all except agings

 x=pos(" "=xhd1$)

 x$=xhd1$(1,x-1)                        # get first column header

 xhd1$=trim(xhd1$(x))

 x=pos(x$=hd1$)                                # find true position

 x1=x+len(x$)-1                                # get end of first column

 # now find end of 2nd column

 x=pos(" "=xhd1$)

 x$=xhd1$(1,x-1)                        # get second column header

 x=pos(x$=hd1$)

 x2=x+len(x$)-1                                # get end of second column

 # now calculate mask width less space between columns

 colw=x2-x1-1

 # now calculate start of first field

 scol=x1-colw+2

}

The postjob code block performs some closing formatting control if the job is exporting data to Excel.  If sdOffice is not being used, based upon the attempt to initialize it in the prejob code block, then this code is skipped.

postjob{

 # if prejob hasn't initialized sdoffice, skip this code

 if sdofc_init<>1 then goto sdofc_complete2

 call sdo$,"leaveopen","",""

 call sdo$,"format autofit","",""

 call sdo$,"format col=1,numberformat=@","",""

 call sdo$,"format col=4,numberformat=""###,##0.00""","",""

 call sdo$,"format col=5,numberformat=""###,##0.00"",bold","",""

 call sdo$,"insertrow 1","",""

 call sdo$,"mergecells range=A1:E1","",""

 call sdo$,"writecell range=A1,value="+$22$+ \

         "Over 60 Aging Values as of "+date(0)+$22$,"",""

 call sdo$,"format range=A1:E1,center,size=15,bold","",""

sdofc_complete2:

}

Here, finally, are the commands to enhance the formatting of the report.  The initial commands use text commands with cut expressions to move the report header data around and change the fonting.

# heading section

const BLFONT=univers,10,bold,italic

const BSFONT=univers,9,bold,italic

cbox .5,.5,RIGHTCOL,5,5,30

# line 1

text 2,1.25,{trim(cut(1,1,10,""))},BSFONT                                # date

text 1,1.25,{trim(cut(20,1,100,""))},BLFONT,center, \

 cols=MAXRCOLS        # comp name

text 1,1.25,{trim(cut(121,1,15,""))},BSFONT,right, \

 cols=MAXRCOLS        # page #

# line 2

text 2,2.35,{trim(cut(1,2,10,""))},BSFONT                                # time

text 1,2.35,{trim(cut(20,2,100,""))},BLFONT,center, \

 cols=MAXRCOLS        # rpt title

# line 3

text 1,3.45,{trim(cut(20,3,100,""))},BSFONT,center, \

 cols=MAXRCOLS        # sub heading

# line 4

text 1,4.45,{trim(cut(20,4,100,""))},BSFONT,center, \

 cols=MAXRCOLS        # sub heading

This section formats the column headings.  The left portion is drawn with text commands, while the aging columns are fonted with font commands, which use the positions from the values calculated in the prepage code block.

# detail heading section

const HFONT=univers,10,italic

cbox LEFTCOL,5.25,RIGHTCOL,7.5,1,20

# line 1

cerase 1,6,MAXCOLS,6

text 1,6,"Customer # & Name",HFONT

text 38,6,"Phone #",HFONT,center,cols=14

text 54,6,"Contact",HFONT

# line 2

cerase 1,7,49,7

text 3,7,"Invoice #",HFONT

text 12,7,"Due Date",HFONT,center,cols=8

text 21,7,"P/O #",HFONT

text 32,7,"Order #",HFONT

text 39,7,"Terms",HFONT,center,cols=5

text 45,7,"Type",HFONT,center,cols=4

# using variables from prepage, enhance aging headings                

font {scol},7,{colw-1},1,HFONT,right

font {scol+1*(colw+1)},7,{colw-1},1,HFONT,right

font {scol+2*(colw+1)},7,{colw-1},1,HFONT,right

font {scol+3*(colw+1)},7,{colw-1},1,HFONT,right

font {scol+4*(colw+1)},7,{colw-1},1,HFONT,right

font {scol+5*(colw+1)},7,{colw-1},1,HFONT,right

font {scol+6*(colw+1)},7,{colw},1,HFONT,right,bold

The report body is enhanced using UnForm's ability to scan for patterns and anchor enhancements to those patterns.  The first series of font commands scan for two spaces in the region from column 1, row 9 through column 2, row 66 (defined as the constant MAXROWS above).  At each point in that search region, if the two spaces are found, a font command is issued relative to the location.  This changes the font of the input data at that location.

The second series of font commands looks for customer heading line types, by searching for any alpha or digit character in the region 1,9 though 2,66.  A different set of font commands is then issued for those positions.

# detail data section

const BDFONT=cgtimes,10,bold

const DFONT=cgtimes,10

# invoice line

font "  @1,9,2,MAXROWS",2,0,8,1,DFONT

font "  @1,9,2,MAXROWS",11,0,8,1,DFONT,center

font "  @1,9,2,MAXROWS",20,0,10,1,DFONT

font "  @1,9,2,MAXROWS",31,0,7,1,DFONT

font "  @1,9,2,MAXROWS",38,0,5,1,DFONT,center

font "  @1,9,2,MAXROWS",44,0,4,1,DFONT,center

# using variables from prepage, enhance agings

font "  @1,9,2,MAXROWS",{scol},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+1*(colw+1)},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+2*(colw+1)},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+3*(colw+1)},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+4*(colw+1)},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+5*(colw+1)},0,{colw},1,DFONT,decimal

font "  @1,9,2,MAXROWS",{scol+6*(colw+1)},0,{colw+1},1,BDFONT,decimal

# cust line

font "~[A-Z0-9]@1,9,2,MAXROWS",0,0,6,1,BDFONT

font "~[A-Z0-9]@1,9,2,MAXROWS",7,0,28,1,BDFONT

font "~[A-Z0-9]@1,9,2,MAXROWS",37,0,14,1,BDFONT,center

font "~[A-Z0-9]@1,9,2,MAXROWS",53,0,36,1,BDFONT

shade "~[A-Z0-9]@1,9,2,MAXROWS",0,-.15,{RIGHTCOL-1.5},1,20

The following commands look for sequences of dashes, which indicate sub total lines.  Wherever a sequence of 50 dashes occurs, a box is drawn and input data is bolded.  In addition, the original dashes are removed with the hline command.

# customer totals

hline "---",erase

# example of UnForm command with continuation to next line

box "--------------------------------------------------", \

 -1,.25,{RIGHTCOL-53},1.25

bold "--------------------------------------------------",0,1,120,1

Finally, grand total lines are treated with special fonting and a box.

# grand totals

const DFONT=cgtimes,11,bold

# sample of box command with increased thickness and double lines

box "Grand Total:",-9.5,-1.25,MAXRCOLS,2.25,5,30,dbl 9

font "Grand Total:",0,0,12,1,BDFONT,13

font "Grand Total:",{scol-10},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+1*(colw+1)},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+2*(colw+1)},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+3*(colw+1)},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+4*(colw+1)},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+5*(colw+1)},0,{colw},1,DFONT,decimal

font "Grand Total:",{scol-10+6*(colw+1)},0,{colw+1},1,DFONT,decimal