Technique used to rotate text labels when creating a PDF file fromAccess

[James A. Fortune]

This might be of help to some people.

I have been producing some PDF reports lately directly from Access
using the basic techniques found here:

http://groups.google.com/group/comp.databases.ms-access/msg/a49f0ddea9315902

The rotation technique I use below can only be used when Access
creates the PDF file directly or when Access uses some PDF creation
tool that allows the developer to insert additional PDF commands. I
don't think that the Access 2007 PDF Add-in from Microsoft allows such
customization. Even if you can find an appropriate place to put the
PDF commands within the output from the Add-in, it would throw off the
byte counts used by the PDF file to index where the PDF objects are
contained within the file.

Some of my reports represent data grouped by month and presented along
the X-Axis. Since the user can choose any number of months for their
date range, I eventually want to group by months if there are less
than or equal to 24 months, by quarters if there are less than or
equal to six years and by years if over six years. Right now, I just
have the capability to have months on the X-Axis. If I have only a
few months, I might widen the width of the data rectangles and, either
way, the date labels can be placed horizontally along the X-Axis. If
I have lots of months, I find that I can squeeze more in if I rotate
the month labels counterclockwise. Here are some A97 code examples
that illustrate the basic idea:

N = DateDiff("m", txtStartDate.Value, txtEndDate.Value) + 1
C = 1#
dblLabelComfort = 0.75
dtStart = CDate(txtStartDate.Value)
dtEnd = CDate(txtEndDate.Value)

For I = 1 To N
MonthCount(I) = 0
MonthTitle(I) = DateAdd("m", I - 1, DateSerial(Year(dtStart), Month
(dtStart), 1))
Next intI
....

'Determine whether to use flat or rotated date labels:

For I = 1 To N
DateLabelWidth(I) = GetFontWidth(Format(MonthTitle(I), "mmm yy"),
"HelveticaBold", dblDateLabelFontSize)
If DateLabelWidth(I) > dblMaxDateLabelWidth Then
dblMaxDateLabelWidth = DateLabelWidth(I)
iMax = I
End If
Next I
'boolAngledDates - Compare the column width available to the date
width of the first month
If dblMaxDateLabelWidth / dblSpacerPlusBarWidth <= dblLabelComfort
Then
boolAngledDates = False
Else
boolAngledDates = True
End If

....

'Calculate horizonatal offsets to align the right side of the date
labels
If boolAngledDates = True Then
For intI = 1 To N
dblDateLabelOffset(intI) = DateLabelWidth(iMax) - DateLabelWidth
(intI)
Next intI
End If

....

'Rotate and place the date labels on the graph

'dblDateLabelRotateAngle = 60
For I = 1 To N
'Determine centerline of column
dblColClx = 216 + (C + 1) * dblSpacerWidth / 2 + (I - 1) *
dblSpacerPlusBarWidth
strStream = strStream & "q" & strCR
strStream = strStream & "BT" & strCR
strStream = strStream & "1 0 0 1 " & CStr(dblColClx -
dblDateLabelFontSize) & " " & CStr(155) & " cm" & strCR
strStream = strStream & "0.5 0.866 -0.866 0.5 0 0 cm" & strCR
strStream = strStream & "/F4 " & CStr(dblDateLabelFontSize) & "
Tf" & strCR
strStream = strStream & CStr(Round(dblDateLabelOffset(I), 3)) &
" 0 Td" & strCR
strStream = strStream & "(" & Format(MonthTitle(I), "mmm yy") &
") Tj" & strCR
strStream = strStream & "ET" & strCR
strStream = strStream & "Q" & strCR
Next intI

produces for 'Mar 09':

q
BT
1 0 0 1 308.166666666667 155 cm
0.5 0.866 -0.866 0.5 0 0 cm
/F4 12 Tf
2.004 0 Td
(Mar 09) Tj
ET
Q

q - keep graphics environmental settings local
BT - begin text
Set a new graphics origin at 308.17, 155
Rotate the graphics coordinate system 60 degrees
Use the font /F4 which has been previously assigned to Helvetica-Bold
sized to 12 pt.
In the new coordinate system, move in the X-Axis 2.004 pts using the
precomputed offset
Print the text value "Mar 09" at the current location
ET - end text
Q - restore the global graphics environmental settings

As can be seen when viewing the PDF file using, say NotePad, the
layout (static) part of the graph contains the following PDF commands
to rotate the Y-Axis text 90 degrees counterclockwise:

q
1 0 0 1 140 234.503 cm
0 1 -1 0 0 0 cm
BT
/F4 16 Tf
0 0 Td
(Number of Discrepancies) Tj
ET
Q

A sample output PDF file (produced from A97) is available at:

https://files.oakland.edu/users/fortune/web/Discrepancy.pdf

Note that the graph is in Landscape mode and requires that the page
PDF object contain:

/MediaBox [0 0 792 612]

instead of

/MediaBox [0 0 612 792]

When rotating text labels in any presentation system there is no
guarantee that the tops will be aligned perfectly because the bounding
box values are used to calculate the offsets rather than the actual
character boundaries with allowance for rotation changing the
distances to the edges, but the results seem to be adequate, at least
for now.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

This might be of help to some people.

I have been producing some PDF reports lately directly from Access
using the basic techniques found here:

http://groups.google.com/group/comp.databases.ms-access/msg/a49f0ddea...

Today, an A2K7 user had problems saving or printing the PDF file that
was produced from Access. They were running the same A97 app
converted to A2K format that all the A2K3 users use successfully. The
same database seems to produce different output depending on whether
it is run (in A2K compatibility mode) from A2K3 or from A2K7.
Currently, I only convert parts of the stream to Unicode when
including images, but I might change that to output Unicode everywhere
when creating any PDF object. The discrepancy graph doesn't contain
any images, so my first suspicion is that the A2K7 version outputs all
text to files as Unicode characters. Since the Access code doesn't
know that the widths are going to be doubled when output, the byte
counts could be off in only the A2K7 version. I'll have to do some
more investigating, such as comparing the file lengths of output from
both versions for the same graph or seeing if any other A2K7 users are
having the same problem. Also, it could be related to Vista or
Windows 7 vs. XP. I think all the A2K3 users are using NTFS
partitions. Until then, does anyone have a link to information about
how A2K7 outputs text when writing to a file under Windows 7 or XP?

Thanks,

James A. Fortune
(e-mail address removed)