Getting in Line

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Oct-1997 >

Getting in Line

199710_de2 Dan Elliott            
Dan creates a report to mimic the output from a word processor. On the way, he exploits the Line method for a number of purposes. And he gets to keep his new job, too.
I work in an investment management firm. When I started here, my very first project was to create a commonly requested report for the marketing department. The purpose of the report was to show the holding information for a specific portfolio by both geographic region and industry sector, all compared to an index (see Figure 1). A quick look showed me that the best way to handle this report would be to create a series of subreports for a main report.

Click on picture to see full size Figure 1
As is often the case, this Access report was created to mimic a report that was already being created in a word processor. The calculations and the formatting involved were presently taking a user roughly 20 minutes to prepare using Microsoft Word. Needless to say, one of the requirements of the application was that the Access report had to look exactly like the report that it was replacing. This being my first project, it was really important that I figure out how to mimic the original report -- no matter what.
Making lines
The ability to create vertical lines in Access reports was first seen in Access 95 with the introduction of the Line method. Calling this method in the page event of the report gives the developer the ability to draw vertical or horizontal lines on the report. Rectangles, with and without fills, are also possible.
The horizontal line has long been a convention of the Access report and requires no code, thanks to the Line control. However, the elusive vertical line requires a bit more preparation. The major problem is that the height of each row in this report's "table" could vary with the number of detail lines in each sector I was reporting on. This meant that I had to calculate the row height to get the correct length for each vertical line. And, if I was going to draw a vertical line, I'd have to draw it after all the data was printed and the whole report fully "expanded" with data.
To begin with, I created a table called "tblComponents" to contain the data that the subreports were going to be based on. I used the DCount function to count the rows for each region in a sector and then found the region with the most rows by simply comparing the results. Finding the maximum row size for the Energy sector, for instance, looked like this:

strSQL = "region_group='" & strRegionOne & "'" _

 & " and deci_sector = 10"

intX1 = DCount("*", "tblComponents", strSQL)

strSQL = "region_group='" & strRegionTwo & "'" _

 & " and deci_sector = 10"

intX2 = DCount("*", "tblComponents", strSQL)

strSQL = "region_group='" & strRegionThree & "'" _

 & " and deci_sector = 10"

intX3 = DCount("*", "tblComponents", strSQL)

If intX1 >= intX2 And intX1 >= intX3 Then

    intEnergy = intX1

ElseIf intX2 >= intX1 And intX2 >= intX3 Then

    intEnergy = intX2

ElseIf intX3 >= intX1 And intX3 >= intX2 Then

    intEnergy = intX3

End If

Once I had each sector's maximum number, I calculated the total row count and used it in the placement of the grid. Each of the items that I add up in calculating the TotalLineCount are numbers that represent the largest number of rows for each sector:

intTotalLineCount = intEnergy + intMaterials + _

 intCapital + intConsumer + intServices + _

  intFinancial + intMulti

Calculating the twips
Now that I knew the number of records in a row, I needed to translate that into a linear distance: twips, a unit of measure that's 1/1440th of an inch. Twips are the standard unit of measurement for placing items on a report. I found the conversion factor that translates record count to linear distance through trial and error. While there might be a more scientific method to arrive at the factor, I found that I could easily determine the factor after a few attempts at using different numbers and print previewing the results. My ultimate goal was to find the twips per row so that I could place my grid accurately around the data. In the initial version of my report, I found the conversion factor to be 150. That is to say, each record printed in the report made the report 150 twips longer.
With those calculations in hand, I was ready to draw the lines. A trip to the Access Help file showed me the basic syntax for the Line method:

object.Line [[Step](x1, y1)] - _

  [Step](x2, y2)[, [color][, B[F]]]

The first parameter to the Line method describes the X and Y coordinates that the line is to be drawn to (remember that the Y coordinate in Windows starts at the upper left-hand corner of the window and gets larger as you move to the bottom of the window). For instance, this code draws a sloped line from the upper left-hand corner of the form to a position 200 twips down and 400 twips into the window:

Me.Line (0, 0)-(400, 400)

Three properties should be set before the line method is called, because they'll control how the line is drawn. The DrawWidth property takes an Integer value that represents the width of the line in pixels. I used 10 to match the lines in the report header that were created using the Rectangle control from the toolbox. The scale mode dictates the unit of measure for the line method to use. The units include twips, points, pixels, characters, inches, millimeters, or centimeters. (I used twips.) Finally, the ForeColor property contains a numeric expression that represents the value of the draw color for the line method. I used zero so that the draw color would be black:

Me.DrawWidth = 10

Me.ScaleMode = 1

Me.ForeColor = 0

You'd think that it would have been quite simple to draw my horizontal lines with the Line control. But I found that since I needed to have the lines intersect cleanly, it was much easier to use the Line method for the horizontal lines as well. I found myself reusing the coordinates for each line: One vertical line coordinate would also be a coordinate in the horizontal line that intersected it to form a corner of the report's grid. The only disadvantage is that these lines don't appear in the design view of the report (see Figure 2).

199710_de2 Figure 1
There were lines whose position on the report wouldn't vary. The simplest way to find where they were located was to use a ruler. It turned out, for instance, that the Cash rows was always 450 twips high because there were no records associated with it. While I couldn't measure the vertical positions of my lines for most rows because of the variation in row heights, I did use a ruler to determine end points of my horizontal lines because the report's column positions didn't move.
Each time the page event was triggered, I had to add 19 to the row count or my grid would be printed in the wrong place. The Help documentation seems to imply that the page event occurs only once. I found that for each row that appears in the detail section, a page event occurs; even though there's no extra space on the detail section above and below the report, that 19 was needed for the lines to appear in the correct location on the report. A little investigation with my ruler showed that the distance from the top of the page to the detail section of the report where the subreport was placed was 1900 twips. With all this information, I calculated the dblL_end variable as the distance in twips from the top of the page to the very bottom of the table:

dcnt_to_twip_FACTOR = 150

dblL_end = 450 + 19 + 1900 + 19 + _

  CDbl(intEnergy * dcnt_to_twip_FACTOR) + 19 + _

  CDbl(intMaterials * dcnt_to_twip_FACTOR) + 19 + _

  CDbl(intCapital * dcnt_to_twip_FACTOR) + 19 + _

  CDbl(intConsumer * dcnt_to_twip_FACTOR) + 19 + _

  CDbl(intServices * dcnt_to_twip_FACTOR) + 19 + _

  CDbl(intFinancial * dcnt_to_twip_FACTOR) + _

  CDbl(intMulti * dcnt_to_twip_FACTOR)

With L_end calculated, placing the following code in the page event of the report resulted in a horizontal line starting 0.625 inches in from the left and going to a point 3.25 inches from the left:

Me.Line (0.625 * 1440, L_end)-(3.25 * 1440, L_end) 

The code that follows shows how I calculated the length of the horizontal lines between the various sectors. Each successive sector is made up of the maximum number of rows for that sector and the maximum number of rows for all previous sectors.

intLi_Energy = 1900

intLi_Materials = 19 + 1900 + _

  CDbl(intEnergy * dcnt_to_twip_FACTOR)

intLi_Capital = 19 + 1900 + _

  CDbl(intEnergy * dcnt_to_twip_FACTOR) + _

  CDbl(intMaterials * dcnt_to_twip_FACTOR) 


As an example, here's the code that draws the line between the Energy and Materials sector, starting 0.625 inches from the left and going 7.75 inches across the page:

Me.Line (0.625 * 1440, intLi_Materials)- _

  (7.75 * 1440, intLi_Materials)

Generating the seven vertical lines in the report was, comparatively, a snap:

Me.Line (0.625 * 1440, 1900)-(0.625 * 1440, dblL_end)

Me.Line (2 * 1440, 1900)-(2 * 1440, dblL_end)

Me.Line (2.6875 * 1440, 1900)- _

   (2.6875 * 1440, dblL_end)

Me.Line (3.25 * 1440, 1900)-(3.25 * 1440, dblL_end)

Me.Line (4.75 * 1440, 1900)-(4.75 * 1440, intLi_Cash)

Me.Line (6.25 * 1440, 1900)-(6.25 * 1440, intLi_Cash)

Me.Line (7.75 * 1440, 1900)-(7.75 * 1440, intLi_Cash)

Shaded band sizing
Unfortunately, there were two more issues to address. I still had to add the shading of the rows that the original version of the report featured. Again, the Line method came to my rescue. By using the B parameter, I caused the Line method to draw a box with its corners at the two coordinates instead of drawing a line between them. Adding the F parameter and a color number caused the box to be filled with the specified color.
Even though print previews showed the result as being an opaque gray box over what I was trying to shade, this code actually resulted in a light gray shade on the area specified. The sample code draws a rectangle 1.5 inches from the left margin that's 0.75 inches wide (that is, it goes to 2.25 from the left margin). Again, 1900 is the distance from the top of the report, and dblL_end is the distance from the top of the page to the end of the table near the bottom of the page. The result is a one-inch wide vertical bar nearly the entire length of the page:

rpt.Line (1.5 * 1440, 1900)-(2.25 * 1440, dblL_end), _

  14935011, BF

The second issue was trickier. Invariably, the size of the dataset would change based on what account or item I was printing. It was important that I make maximum use of the paper for the report to be accepted by the users. When there wasn't much data, the users wanted a large grid. If there was lots of data, they wanted it all on one page.
In my application, the size of the dataset ranged from 20 to 100 rows. The report object that would print the 20-rows scenario and make good use of the space was unusable for the 100-row scenario. To solve this, I came up with two different main reports and two different sets of subreports. As I mentioned earlier, the size of the subreport dictated the twip-to-row-count conversion. By combining the main reports with different-sized headers with different-sized subreports, I was able to fill the page when there were a few rows and pack the page when there were lots of rows.
The first report was rpt_Global_Equity_Management_BIG -- a big report for smaller datasets. This report had larger top and bottom margins, a second descriptive title ("Portfolio Summary"), and a larger header area. The conversion factor was 195 twips per row, indicating the larger row size. The second report was rpt_Global_Equity_Management. This report had smaller top and bottom margins, no second descriptive title, and my original conversion factor of 150. I also had two versions of the subreports: one set with tall rows and one set with short rows.
Since all my data was kept in one table, I just checked its size to determine which combination of reports and subreports I would use. I would then open the appropriate report in design mode, set it to use the right subreports, and print the report. Here's a sample of the code that mixed and matched the reports:

'Big main, big subreports

 If intTotalLineCount <= 55 Then

 DoCmd.Echo True

 DoCmd.OpenReport _

    "rpt_Global_Equity_Management_BIG", A_DESIGN

  Reports![rpt_Global_Equity_Management_BIG]! _

   [bucket1].SourceObject = "Report.rptsubsub1"

  Reports![rpt_Global_Equity_Management_BIG]! _

   [bucket2].SourceObject = "Report.rptsubsub2"

  Reports![rpt_Global_Equity_Management_BIG]! _

   [bucket3].SourceObject = "Report.rptsubsub3"


  VerticalScale = 195

  DoCmd.Echo True

  DoCmd.OpenReport "rpt_Global_Equity_Management_BIG", _                



'Big main, small sub

ElseIf TotalLineCount >= 56 And _

 TotalLineCount <= 60 Then

  DoCmd.Echo False

  DoCmd.OpenReport _

    "rpt_Global_Equity_Management_BIG", A_DESIGN

  Reports![rpt_Global_Equity_Management_BIG]! _

   [bucket1].SourceObject = "Report.rptsubsubone_tiny"

  Reports![rpt_Global_Equity_Management_BIG]! _

    [bucket2].SourceObject = "Report.rptsubsubtwo_tiny"

  Reports![rpt_Global_Equity_Management_BIG]! _

    [bucket3].SourceObject = "Report.rptsubsub3_tiny"


  VerticalScale = 150

  DoCmd.Echo True

  DoCmd.OpenReport _

   "rpt_Global_Equity_Management_BIG", A_PREVIEW    

End If

The line method and the Page event are very powerful tools for formatting reports in Access. What made it work was the Page event. That event really impressed me as I became more familiar with it while working on this report. Since Access uses the banded report model, it's often hard to selectively format items vertically on the report. Only by using the Page event could I add formatting to the report once it had been populated with data and fully "expanded."
And, by the way, everybody liked the report.


Your download file is called LINES.EXE in the file

This is found in theon this page

Other Articles on Reporting

Things about Reports that you probably dont know

Reports: Multiple Columns and Subreports

Creating a Great Report

Saving Reports is a Snap

Printing Pesky ZIP Codes and Set the Start Page Number

How to Pad Access Reports With Blank Rows