How Do I ... Create some complicated data groups and build a recap?

I recently had to create a very complicated report for a client that covers several different techniques. I thought it would be good to share this modified sample with everyone.

At first, the report sounded simple (don't they all!) Create a list of orders, sorted and subtotaled by customer and each customer should start on a new page. That's easy, I can handle that. Put the orders in the detail band. Create a data group on the Customer and mark it to start on a new page. Put subtotals for the customer in the Data Group Footer band.

Oh .. the client says .. the list of orders per customer can get quite long. Can we have ledger-style shading or a line every so many lines? Sure, I think I can handle this. Oh ... the client says again ... we'd also like subtotals at the bottom of each page showing the totals of just that page. That should be easy .. I can use the page footer band. Oh wait .. as I think that through some more, it's not going to work because on the last page for the customer, the data group footer band would print and then the page footer would show up at the bottom. That's not cool. Hmm ... these two requirements combined make things a little sticky .. but I'm sure I can come up with the answer.

Then comes the killer requirement .. Remember those page subtotals we want at the bottom of each page .. well .. at the end of the customer we'd also like a recap that shows each one of those subtotals like the following:

Page 1, $999.99
Page 2, $999.99
Page 3, $999.99

Yuk .. I really don't like that one especially combined with the ledger-style and page subtotals. Now I'm really going to have to put some thought into this report. I think my head is spinning!

For this example, I'm using VFP 9 SP2 (although it should work with previous versions because I'm not using a ReportListener or any of the new features.) I'll use the TasTrade data stored in C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data. I wanted to be flexible with the number of lines per ledger section and the number of lines per page so I decided to control them with a variable. That way, if the customer changes their mind down the road I can easily change it.

I also didn't want to mess around with trying to build the data for the recap ahead of time. Again, I was thinking down the road about using this technique for other situations and if I were to use it on a report with stretchable fields, I didn't want to use hard-coding to "count" each record and figure out the appropriate page it falls on. I just wanted to let it flow and let the report build the recap data for me no matter how many records appeared on a page. I also didn't want to have to hard-code all the variables to be totaled. I wanted some generic code that totals all the numeric or currency fields.

Let's start by looking at the final report as shown below in Figure 1. The first page shows the records for the first customer. The second page shows the "recap" for that customer, which only contains one page. The third and fourth pages do the same thing for the second customer. The fifth through seventh pages show the details for the third customer and the last pages shows the "recap" of each of the three previous pages which belong to the third customer.









Figure 1: The final printed report.

Next, let's look at the simple program that I use to create the data and print the report. Notice that I use two variables to control the groupings. I could have hard-coded this in the report itself, but this is more flexible.

*-- Build the data
SELECT '1' AS RecType,
    Orders.Customer_ID, ;
    Customer.Company_Name, ;
    Orders.Order_Number, ;
    Orders.Order_Date, ;
    CAST(Orders.Discount AS N(14,2)) AS Discount, ;
    CAST(Orders.Freight AS N(14,2)) AS Freight, ;
    CAST(NVL(SumLines.nPrice, 0) AS N(14,2)) nPrice ;
    FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Orders' ;
  LEFT OUTER JOIN 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Customer' ;
    ON Customer.Customer_ID = Orders.Customer_ID ;
  LEFT OUTER JOIN ;
      (SELECT OrdItems.Order_ID, ;
        SUM(OrdItems.Unit_Price * OrdItems.Quantity) AS nPrice ;
        FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\OrdItems' ;
        GROUP BY OrdItems.Order_ID ;
      ) SumLines ;
    ON SumLines.Order_ID = Orders.Order_ID ;
  INTO CURSOR tmpReportData READWRITE

*-- Index the data
SELECT tmpReportData
INDEX ON Customer_ID + RecType + Order_Number TAG SortKey
GOTO TOP

*-- Set some variables to control # of lines
*-- for each section and # of lines per page.
nMaxLinesPerLedger = 2
nMaxLinesPerPage = 6

*-- Run the report
REPORT FORM ComplexGroups_Recap TO PRINTER PROMPT PREVIEW

Besides the above program, I also need a function that I'm going to call from the report itself. This function will update the cursor that is driving the report by either adding a new record for a particular page, or adding to the numeric fields for the given page. Note that I have to take great care to move the record pointer back to where it was when this function was called. Otherwise, you'll make a mess of the running report. Add the following function to the bottom of the program that runs the report so it's available to the report.

************************
FUNCTION AddToPageTotals
************************
LPARAMETERS tcField, txPage, tcSeek, tcIndex

LOCAL loRecord, lnRecNo, lcAlias, lcSeek, lx

*-- Don't do this on the total records themselves!
*-- (Can you say "infinite loop")
IF RecType = '2'
  RETURN
ENDIF

*-- Remember where we are
lnRecNo = RECNO()
lcAlias = ALIAS()

SCATTER NAME m.loRecord
m.loRecord.RecType = '2'
m.loRecord.&tcField = txPage

lcSeek = EVALUATE(STRTRAN(m.tcSeek, 'XTMPX', 'm.loRecord'))
IF SEEK(m.lcSeek, m.lcAlias, m.tcIndex)
  *-- add the amount fields to the existing record
  lnFields = AFIELDS(laFields)
  FOR ln = 1 TO lnFields
    lcField = laFields[m.ln, 1]
    lx = EVALUATE(m.lcField)
    IF VARTYPE(lx) $ 'NY'
      REPLACE (m.lcField) WITH NVL(EVALUATE(m.lcField),0) + NVL(m.loRecord.&lcField,0)
    ENDIF
  ENDFOR
ELSE
  *-- Add the record for this page
  INSERT INTO (m.lcAlias) FROM NAME m.loRecord
ENDIF

*-- Go back to the "detail" record that we were processing
GOTO m.lnRecNo

Now let's get to the definition of the report, which is the meat of this solution. Figure 2 shows how the report definition will look when we're done.


Figure 2: The report definition.

Start by creating 3 report variables:

• rnLinesOnPage


• Value to Store = 0
• Reset = Report
• Calculation Type = Count


• rnLinesForCustomer


• Value to Store = 0
• Reset = Report
• Calculation Type = Count


• Dummy


• Value to Store = AddToPageTotals('Order_Number', TRANSFORM(_PageNo, '999999'), 'XTMPX.Customer_ID + XTMPX.RecType + XTMPX.Order_Number', 'SortKey')
• Reset = Report
• Calculation Type = None

At first thought, it might look like I made a typo above because none of the variables reset at the Customer group or Page group. But trust me .. it's not a mistake. Just bear with me and it will make sense soon.

The last variable is used to call the function we created earlier. It looks a little complex but that's just because I made the function generic so I can reuse it on other reports. You could simplify it by taking away all the parameters and hard-coding the function.

Next, create 4 different Data Groups:

• 1: Customer_ID, New Page Number 1, Reprint group header on each page


• 2: RecType, New Page


• 3: INT(rnLinesForCustomer / nMaxLinesPerPage), New Page


• 4: INT(rnLinesOnPage / nMaxLinesPerLedger)

Expand the #1 Data Group Header band and put the Customer field.

Put the detail fields in the Detail band and lay them out as you desire.

Expand the #4 Data Group Header band and add a horizontal line to separate the chunks of detail records. I made my light gray so it's not so distracting.

Expand the Data Group Footer bands for #3 and #4 so you can add some totals. Copy your numeric fields from the Detail band into the two different group footer bands. Next, change the Calculate option on each to SUM and reset on the applicable group.

Now go ahead and add whatever column headings you want. You can use Print When logic of RecType = '1' if you want to print something for just the regular data. Use RecType = '2' if you want to print something on just the page recaps.

At this point you might think we're done, but we're not. Remember when we defined the report variables I mentioned that we are not resetting them at the appropriate data groups. The reason is that whenever you try to alter the variable that is being used to control the groups, the VFP Report Writer gets completely confused. You end up with more breaks than you want and often times you get pages with no detail records and just some totals.

So here's the trick behind this whole report. We need to reset the report variables ourselves. Well, actually, we aren't resetting them, we're fudging them. The problem is that if we reset them to zero, we get the extra breaks I just mentioned. So instead of resetting them to zero, we fudge them to a value that makes the next page start at a clean point.

We are going to alter these variables in the On Entry and On Exit Expressions of the #2 Group Footer band as follows:

On Entry: _VFP.SetVar('rnLinesForCustomer', rnLinesForCustomer- MOD(rnLinesForCustomer, nMaxLinesPerPage))

The above "expression" resets the rnLinesForCustomer variable to a value that is equal to what the count would be if the page was completely full of lines, without resetting it for each page. So if we allowed 10 lines per page, at the end of the first page we reset it to 10, at the end of the 2nd page we reset it to 20, and so on. Regardless of how many lines actually printed, we just make it think that full pages have been printed. By doing this, we avoid the funky extra breaks that happen when you try to zero out the variable.

On Exit: _VFP.SetVar('rnLinesOnPage', rnLinesOnPage- MOD(rnLinesOnPage, nMaxLinesPerLedger))

The above "expression" does the same thing for the ledger breaking as the previous one did for the page breaking. It really doesn't matter which one is in the On Exit and which one is in the On Entry. I just needed to reset two variables and this gave me a place to do each one.

That's it. We're done. Simple, right? Well .. of course it's simple when you already know the answer. Trust me .. it wasn't that simple for me to come to this conclusion, but now you can benefit from my troubles. If you want the sample program and report I used to create this blog, just send me an email and I'll gladly send it to you.