Multi-page report with totals for each page.

[James Merryfield]

Forum,

I have a report which runs into several pages. I cannot apply a GROUP to the records because the records are in alphabetical order and the number of entries in each alpha group change as new entries are made (and I do not want 26 pages (a,b,c,d, etc.,) in the report when 9 will do!). I need to place 17 entries on a page (achieved) and total the account values for those 17 on each page ,(similar function to the sum(aa:xx)on a spreadsheet) initialising the total field to zero at the beginning of each page so that the total achieved is the total for the subject page only.

I have used run_total() but that carries over the page total into the next page. The calculated field is placed in the page footer region of the report.

I feel that either there is a function to do this that I haven't got, or an algorythm out there that will reduce run_total() to zero on commencing a new page.

Can any one help?

Thank you

James

[Tom Cone Jr]

RE: Multi-page report with totals for each page.

James,

This is harder to do than one might think. The summary and total functions in the report engine are oriented to the data, not to the page size.

How are you limiting the number of records to 17 on each page? Your answer may help us identify a solution to your question.

In the meantime, the only idea I can dream up would be to create an artificial grouping field in your table. This field could be assigned a numeric value equal to the page number that particular record falls on in your report. Then you could group your report on that field and generate a group total in the group footer at the bottom of each page. To do this would require: an update operation to zero out the last batch of page numbers from the grouping field; arranging the table in the same sequence as your report, and then writing a script to step through the table one record at a time, putting a 1 in the first 17 recs, a 2 in the next 17, a 3 iin the next 17 and so on.

If this turns out to be the only solution, and the prospect of approaching it this way seems too much, you might check out Vers. 5's feature set. I believe (can't guarantee this at the moment) that Vers. 5 contains a built in page total function.

-- tom

[James Merryfield]

RE: Multi-page report with totals for each page.

Many thanks for your reply, Tom,

I am limiting the No. of records per page by the simple device of enlarging the print size to limit the number of entries that would print per page. I looked at the "No of records to keep with page header" but soon realised that the figure produced was a minimum record count. I don't know what later versions of this program provide in terms of functions but the reverse of that particular function could be very useful.

It is years since I programmed extensively in BASIC and I have only dabbled in XBASIC as the Alpha series is so strong on helpers (user since 1993)! I recognise the logic in your text as meaning nested counting loops and IF-THEN-ELSE statements and I have (until today) shied away from such constructs because of lack of recent use and cobwebs on the brain! I am, as I write, manually developing such constructs and see it as possibly the only way to go. My next problem will be how to insert the code into my report to make it work!

James

[Tom Cone Jr]

RE: Multi-page report with totals for each page.

James,

The brute force approach I outlined would require that the report layout be modifed to insert a group break on the new 'grouping field' which will contain a number representing the page on which a record will fall. It will also involve creating a new calc/summary field to generate a new group total, and this new field would be dropped into the group footer. The properties of the group break would also specify 'begin each group on new page'. This much is relatively easy.

There is one more piece that would be needed:

a script to run from a button which does several things:

- opens the table
- defines a query to sort and filter the records as desired in your report
- and then fetches the first record, assigns a page number to the grouping field, fetches the next, assigns a page number, ... until 17 have been assigned.
- the script would then increment the pagenumber and assign it to the next 17, and so on
- continuing until the eof is encountered.
- then closing the table.
- then calling the report

-- tom

[James Merryfield]

RE: Multi-page report with totals for each page.

Tom,

I can see that I didn't follow you fully in your earlier message. You are suggesting that I group the records prior to the PRINT operation by modifying the table. I can see that will work just fine. As a matter of interest my method was to count the records using a FOR ... NEXT loop and total the records producing a figure to be inserted into a field or variable. (see below)

DIM cash_total as n
DIM cheque_total as n
DIM counter as n
DIM group as n


cash_total = 0
cheque_total = 0
counter = 0
group = 1

FOR I = 1 to rec_count(people_info.dbf)

counter = counter + 1
cash_total = cash_total + people info-> last_cash
cheque_total = cheque_total + people_info-> last_cheque
IF counter = 17
THEN
PRINT cash_total
PRINT cheque_total
counter = 0
group = group + 1
cash_total = 0
cheque_total = 0
ENDIF
NEXT I

Obviously this is at a very early stage. But I wonder if I might get it to work!!
For speed of development I'm going to apply your idea as it will take me but a short time to modify my table.

Many thanks James

[James Merryfield]

RE: Multi-page report with totals for each page.

Hi Tom,

its 02:15 and after a few hours research, loads of tea, wife's in bed, but my script based on your suggestion works as intended!

There is a bug - the construct goes past the EOF but I'll fix that after some sleep! The report layout has to have a group based on the new group-field but that is a simple matter.

Further development planned for the function is to ask the user to define the number of entries per page. Neat idea?

Here's the code:

DIM counter as n
DIM page as n
counter = Weekly_update:tables:Weekly_update.records_get()
page = 1


Weekly_update.fetch_first()


FOR i = 1 to counter' cycle thro all records to rec num max

FOR v = 1 to 17

Weekly_update:tables:Weekly_update.change_begin()
Weekly_update:tables:Weekly_update.entry_d1 = page
Weekly_update:tables:Weekly_update.change_end()
Weekly_update.fetch_next()

NEXT v

page = page + 1

NEXT i

Weekly_update:tables:Weekly_update.change_end()

END


Thank you for the inspiration. I can build on this to produce more scripts and a more interesting database.

James

[JohnZaleski]

RE: Multi-page report with totals for each page.

This is untested but it might work without any preliminary code.
Make the sort break expression:
CEILING(mrecno()/17)
Important to use mrecno() rather than recno()

Probably has a better chance of working if the report is based on a single table or a set with no one to many child relations.

[JohnZaleski]

RE: Multi-page report with totals for each page.

I did a little test and the above worked.It worked regardless of how you sorted the detail records.The
mrecno() basically returns a "relative" record number to the report.

Formulas for totals had to include a subgroup to work.If the group label was called group_a, to add up quantities the following worked:
total(Quantity,GRP->Group_a,GRP->detail)

[Ken L Herrold]

RE: Multi-page report with totals for each page.

To John Z:

C L E V E R ! and intuitive!

Ken

[James Merryfield]

RE: Multi-page report with totals for each page.

Thank you to all contributors.

The problem has been solved and the report prints out beautifully.

The final script code is:

counter = 1
page = 1
'tbl = Weekly_update:tables:Weekly_update 'get pointer to the table
'if Weekly_update.mode_get() > 0 then 'an edit or enter is pending, must save it before proceeding
' ui_msg_box("Oops","A change or enter is pending. Must save or cancel first.")
'else
Weekly_update:tables:Weekly_update.fetch_first()

while .not.Weekly_update:tables:Weekly_update.fetch_eof()
Weekly_update:tables:Weekly_update.change_begin()
Weekly_update:tables:Weekly_update.entry_d1 = page
Weekly_update:tables:Weekly_update.change_end(.t.)
if mod(counter, 17) = 0 then 'increment page after each batch of 17
page = page +1
end if
counter = counter + 1
Weekly_update:tables:Weekly_update.fetch_next()
end while

Weekly_update:tables:Weekly_update.fetch_first() 'return to top of file
parentform.resynch() 'resynchronize the display
'end if
end


The base code was written by Tom Cone jr, and on applying it to my database I just needed to give it a few tweaks to get it to run correctly.

For the report layout I went back to the original A4 documentation and produced a print layout that put all the required data in the right places and saved a barrel load of ink in not having to print out the form structure each time a report was produced.

This application is not for general commercial use; it is used in my own business and has little prospect of being published commercially. I am publishing it here because the codearises from a collaboration between several forum members and as such is forum property. It might form a basis for others to produce similar work with Alpha 5 ver 4.

James

[JohnZaleski]

RE: Multi-page report with totals for each page.

Here's another approach.The first report is hardcoded with 17 lines.The second report will ask the user how many lines per page and what order to sort the report by.
The function mrecno() can be used in the group expression while the run_count() function cannot

[JohnZaleski]

RE: Multi-page report with totals for each page.

Forgot to attach