Hi folks,
It’s been a while since my last post! I’m still actively working with Visual FoxPro 9 (VFP9), especially in the reports area, but as many of you know, developing and maintaining www.foxypreviewer.com has been taking up most of my free time.
In the meantime, I’ve discovered some neat tricks in VFP reporting—many of which are now baked into the newer versions of FoxyPreviewer. While I haven’t been posting as frequently, I’ve definitely been busy behind the scenes.
What's This About?
One issue that keeps coming up from FoxyPreviewer users is a misalignment between Group Header labels and Detail band fields—especially when labels are right-aligned. This behavior can be frustrating, but it’s actually related to how VFP9 handles reports under REPORTBEHAVIOR 90
.
Rather than reinvent the wheel, I highly recommend checking out this very detailed blog post from Colin Nicholls:
🔗 Observations on Right-aligned labels in VFP9 Reports – by Colin Nicholls (April 2006)
In short, Colin explains:
"In Visual FoxPro 9.0, there is a new report engine that uses GDI+ to render output. The older, backward-compatible engine is still supported. You can switch between the two engines using the SET REPORTBEHAVIOR command."
To illustrate, here's what a report might look like:
-
Under
SET REPORTBEHAVIOR 80
: (everything looks fine)
-
Under
SET REPORTBEHAVIOR 90
: (right-alignment breaks)
A Word of Thanks
By the way, Colin and Lisa Nicholls' blogs at https://spacefold.com are absolute goldmines for VFP report tips. I often find myself thinking, "Let’s see what Lisa and Colin have to say about this"—and I always walk away with new insights.
So... What's the Fix?
The ideal solution is to replace Group Header labels with right-aligned fields that match the size and alignment of the Detail band fields. This not only solves the alignment issue but also improves the quality of exported Excel files (XLS/XLSX) when using FoxyPreviewer.
However, that’s often not realistic—many users have hundreds (if not thousands) of reports to deal with.
That’s why I created a simple script to automate the fix:
What It Does:
-
Analyzes your
.FRX/.FRT
files -
Matches Group Header labels to right-aligned Detail fields
-
Creates two modified versions of the report:
-
One replacing right-aligned labels with fields
-
Another adjusting label size and position to align properly
-
The modified reports are saved in the same folder with a random suffix added to the filename.
The script is intentionally kept as simple as possible so you can adapt and understand it easily. It has been tested successfully on many reports, but there may still be edge cases—please let me know if you encounter any!
Next Steps: Automating with FoxyPreviewer?
Yes, I’ve been considering adding this logic directly into FoxyPreviewer as an optional automatic fix. It wouldn’t be hard to implement—but I still believe developers should review and validate any automated changes to their reports.
Download the Script
You can save the script as FRX_RIGHTALIGNLABELS.PRG
, or download it directly here.
Thanks for reading! If you're working with VFP9 reports and run into this alignment issue, I hope this helps smooth things out.
Let me know your feedback—and as always, happy coding!
* File: FRX_RightAlignLabels.prg * Creates 2 new report copies having its labels right-aligned * Why? To avoid the known issue explained by Colin Nicholls in his blog * Article: "Observations on Right-aligned labels in VFP9 Reports" by Colin Nicholls (April 2006) * https://spacefold.com/colin/archive/articles/vfp9reporting/rightalign/monofonts.html * The alignment issue is known, and comes from VFP9, not FoxyPreviewer. * If you run any of your reports with SET REPORTBEHAVIOR 90 without FoxyPreviewer you'll see the issue there as well! * The main idea is to identify the labels that correspond to Right-Aligned numeric fields in the 'Detail' band, * and reposition the labels to match the right edge of the field Local lmMatch CLEAR CLOSE ALL LOCAL lcMyReport m.lcMyReport = GETFILE("FRX") IF EMPTY(m.lcMyReport) RETURN .F. ENDIF * Step 1: Load the FRX file into a ReadWrite cursor * To ease the manipulation an understanding convert the dimensions o Pixels and store in new fields USE (m.lcMyReport) ALIAS frx0 SHARED SELECT 000 AS nRec, 000 AS Match, 00000 AS x, 00000 as y, 00000 as w, 00000 as h, 00000 as R, * FROM FRX0 INTO CURSOR FRX READWRITE REPLACE ALL y WITH FLOOR(vPos * .0096), H WITH FLOOR(Height * .0096), ; x WITH FLOOR(hPos * .0096), W WITH FLOOR(Width * .0096), ; nRec WITH RECNO(), R WITH (x + w) ; FOR INLIST(OBJTYPE, 5, 6, 8, 9) IN Frx * Step 2: Capture full band vertical positions (TOP + HEIGHT) LOCAL lnTitle, lnMainHeader, lnColHeader, lnGrpHeader, lnDetail STORE 0 TO m.lnMainHeader, m.lnGrpHeader, m.lnTitle, m.lnColHeader, m.lnDetail SELECT frx LOCAL llHasHeader, llHasGrpHeader, llHasDetail SCAN FOR OBJTYPE = 9 AND h > 0 && AND INLIST(OBJCODE, 1, 2, 3, 9, 4) DO CASE CASE Objcode = 0 m.lnTitle = h + 20 CASE Objcode = 1 m.lnMainHeader = h + 20 m.llHasHeader = .T. CASE Objcode = 2 m.lnColHeader = h + 20 CASE Objcode = 3 m.lnGrpHeader = h + 20 m.llHasGrpHeader = .T. CASE Objcode = 4 m.lnDetail = h + 20 m.llHasDetail = .T. OTHERWISE ENDCASE ENDSCAN * Step 3: Determine the starting and ending of each band LOCAL lnTitle0, lnTitle1 LOCAL lnMainHeader0, lnMainHeader1 LOCAL lnColHeader0, lnColHeader1 LOCAL lnGrpHeader0, lnGrpHeader1 LOCAL lnDetail0, lnDetail1 m.lnTitle0 = 0 m.lnTitle1 = m.lnTitle m.lnMainHeader0 = m.lnTitle1 m.lnMainHeader1 = m.lnMainHeader0 + m.lnMainHeader m.lnColHeader0 = m.lnMainHeader1 m.lnColHeader1 = m.lnColHeader0 + m.lnColHeader m.lnGrpHeader0 = m.lnColHeader1 m.lnGrpHeader1 = m.lnGrpHeader0 + m.lnGrpHeader m.lnDetail0 = m.lnGrpHeader1 m.lnDetail1 = m.lnDetail0 + m.lnDetail * Step 5: Assign each field to its Band type LOCAL lcBand, lcBandIndex SELECT frx SCAN FOR INLIST(OBJTYPE, 5, 8) DO CASE CASE Frx.y >= m.lnGrpHeader1 AND Frx.y <= m.lnDetail1 AND Frx.OBJTYPE = 8 AND Frx.FILLCHAR = "N" AND Frx.OFFSET = 1 && Only the numeric fields, and right aligned m.lcBand = "Detail" m.lcBandIndex = "4" CASE Frx.y < m.lnTitle1 AND Frx.OBJTYPE = 5 m.lcBand = "Title" m.lcBandIndex = "0" CASE Frx.y < m.lnMainHeader1 AND m.llHasDetail = .F. AND Frx.OBJTYPE = 8 m.lcBand = "Header" m.lcBandIndex = "1" CASE Frx.y < m.lnMainHeader1 AND Frx.OBJTYPE = 5 m.lcBand = "Header" m.lcBandIndex = "1" CASE Frx.y < m.lnColHeader1 AND Frx.OBJTYPE = 5 m.lcBand = "ColHeader" m.lcBandIndex = "2" CASE Frx.y < m.lnGrpHeader1 AND Frx.OBJTYPE = 5 m.lcBand = "GrpHeader" m.lcBandIndex = "3" OTHERWISE m.lcBand = "" ENDCASE IF NOT EMPTY(m.lcBand) * Reuse the 'Platform' and 'BoxChar' fields to store in which band each field resides REPLACE Frx.Platform WITH m.lcBand, Frx.BOXCHAR WITH m.lcBandIndex IN Frx ENDIF ENDSCAN * If the FRX does not have a 'GroupHeader-3' band, then we pick the labels from the 'Title-1' band DO CASE CASE NOT m.llHasDetail && No detail band, so we work only at the Title SELECT * FROM FRX WHERE BOXCHAR = "1" AND OBJTYPE = 5 INTO CURSOR "FrxLabels" READWRITE CASE m.llHasGrpHeader SELECT * FROM FRX WHERE BOXCHAR = "3" AND OBJTYPE = 5 INTO CURSOR "FrxLabels" READWRITE CASE m.llHasHeader * BROWSE FOR INLIST(BOXCHAR, "1", "4") SELECT * FROM FRX WHERE BOXCHAR = "1" INTO CURSOR "FrxLabels" READWRITE OTHERWISE CLOSE ALL MESSAGEBOX("Unable to create the new report", 48) RETURN ENDCASE * Step 6: Find the matching Labels and Fields LOCAL laLabels(1, 2), lnLabels, loField, lnNewRight, lnRight m.lnLabels = 0 SELECT FRX * Let's find the matches LOCAL lcDetailChar IF m.llHasDetail m.lcDetailChar = "4" ELSE m.lcDetailChar = "1" ENDIF * Scan through all labels and find if there are matching fields for them LOCAL laMatches(1), lnMatches, lnMatch m.lnMatches = 0 m.lmMatch = 0 SELECT FrxLabels SCAN m.lnRight = FrxLabels.R SELECT FRX LOCATE FOR Frx.BOXCHAR = m.lcDetailChar AND OBJTYPE = 8 AND BETWEEN(FRX.R, m.lnRight - 2, m.lnRight + 2) IF NOT EOF() m.lnMatch = Frx.nRec REPLACE FRXLabels.Match WITH m.lnMatch IN FrxLabels m.lnLabels = m.lnLabels + 1 DIMENSION m.laLabels(m.lnLabels, 2) m.laLabels(m.lnLabels, 1) = FrxLabels.nRec SCATTER NAME m.loField MEMO m.laLabels(m.lnLabels, 2) = m.loField ENDIF SELECT FrxLabels ENDSCAN LOCAL laMatches(1), lnMatches, lnMatch m.lnMatches = 0 m.lmMatch = 0 SCAN FOR BOXCHAR = m.lcDetailChar AND OBJTYPE = 8 && Detail Numeric and right aligned fields m.lnRight = FRX.R SELECT FrxLabels LOCATE FOR BETWEEN(FRXLabels.R, m.lnRight - 2, m.lnRight + 2) IF NOT EOF() m.lnMatch = FrxLabels.nRec IF ASCAN(m.laMatches, m.lnMatch) = 0 m.lnMatches = m.lnMatches + 1 DIMENSION m.laMatches(m.lnMatches) m.laMatches(m.lnMatches) = m.lnMatch REPLACE FRX.Match WITH m.lnMatch IN FRX m.lnLabels = m.lnLabels + 1 DIMENSION m.laLabels(m.lnLabels, 2) m.laLabels(m.lnLabels, 1) = FrxLabels.nRec SELECT Frx SCATTER NAME m.loField MEMO m.laLabels(m.lnLabels, 2) = m.loField ENDIF ENDIF SELECT Frx ENDSCAN SELECT Frx IF VARTYPE(m.laLabels(1, 1)) = "L" MESSAGEBOX("No changes to apply", 32) CLOSE ALL RETURN ENDIF * Now we are ready to create our new report copies LOCAL lnField, n * Step 7: Create a new Report Copy with a replaced field instead of the original label * Replace the Label with a Field object, right aligned and resized LOCAL lcNewReport8 && OBJTYPE 8 = Field m.lcNewReport8 = ADDBS(JUSTPATH(m.lcMyReport)) + JUSTSTEM(m.lcMyReport) + "_8" + SYS(2015) + ".frx" SELECT * FROM FRX0 INTO TABLE (m.lcNewReport8) USE (m.lcNewReport8) ALIAS frxNew8 SHARED SELECT FrxNew8 FOR m.n = 1 TO m.lnLabels * Change the label to field m.lnField = m.lalabels(m.n, 1) m.loField = m.lalabels(m.n, 2) GO (m.lnField) LOCAL lnOldLeft, lnOldWidth, lnNewLeft, lnNewWidth m.lnOldLeft = FrxNew8.hPos m.lnOldWidth = FrxNew8.Width IF (m.lnOldWidth + 500) > m.loField.Width m.lnNewWidth = m.lnOldWidth + 1000 m.lnNewRight = FrxNew8.hPos + FrxNew8.Width m.lnNewLeft = m.lnNewRight - m.lnNewWidth ELSE m.lnNewWidth = m.loField.Width m.lnNewLeft = m.loField.hPos ENDIF REPLACE OBJTYPE WITH 8, Width WITH m.lnNewWidth, height WITH m.loField.Height, hPos WITH m.lnNewLeft, FILLCHAR WITH "C", OFFSET WITH 1 STRETCH WITH .T. IN FrxNew8 ENDFOR * Step 8: Create a new Report Copy with a a Right aligned label instead of the original * Enlarge the label * Reposition to the right-most position aligned to the relative field * Ensure right aligned LOCAL lcNewReport5 && OBJTYPE 5 = Label m.lcNewReport5 = ADDBS(JUSTPATH(m.lcMyReport)) + JUSTSTEM(m.lcMyReport) + "_5" + SYS(2015) + ".frx" SELECT * FROM FRX0 INTO TABLE (m.lcNewReport5) USE (m.lcNewReport5) ALIAS frxNew5 SHARED SELECT FrxNew5 FOR m.n = 1 TO m.lnLabels * Change the label to field m.lnField = m.lalabels(m.n, 1) m.loField = m.lalabels(m.n, 2) GO (m.lnField) LOCAL lnOldLeft, lnOldWidth, lnNewLeft, lnNewWidth m.lnOldLeft = FrxNew5.hPos m.lnOldWidth = FrxNew5.Width IF (m.lnOldWidth + 500) > m.loField.Width m.lnNewWidth = m.lnOldWidth + 1000 m.lnNewRight = FrxNew5.hPos + FrxNew5.Width m.lnNewLeft = m.lnNewRight - m.lnNewWidth ELSE m.lnNewWidth = m.loField.Width m.lnNewLeft = m.loField.hPos ENDIF * Picture field * For Report Labels, this field is empty for left justified (default), contains @I for centered or contains @J for right justified. REPLACE Width WITH m.lnNewWidth, hPos WITH m.lnNewLeft, PICTURE WITH ["@J"] IN FrxNew5 ENDFOR * Step 9: Close USE IN SELECT("frx") USE IN SELECT("frx0") USE IN SELECT("FrxLabels") USE IN SELECT("frxNew5") && Labels USE IN SELECT("frxNew8") && Fields MESSAGEBOX("A report copy was created having " + TRANSFORM(m.lnLabels) + ; " label(s) replaced as fields under the file name:" + CHR(13) + CHR(13) + m.lcNewReport8, 64) MESSAGEBOX("A report copy was created having " + TRANSFORM(m.lnLabels) + ; " label(s) repositioned under the file name:" + CHR(13) + CHR(13) + m.lcNewReport5, 64) * Show the original Report, and then, the 2 new versions MODIFY REPORT (m.lcMyReport) MODIFY REPORT (m.lcNewReport5) MODIFY REPORT (m.lcNewReport8) RETURN
No comments:
Post a Comment