Report grouping

[jackie77]

Hi all

I am looking for some help with the layout of one of my reports, what I want it to do is divide the results/rows into blocks of 50 all with a different invoice number, followed by a number of calculations before displaying another group of 50. Ideally I would like the 50 rows to be displayed on one page (i.e. fit to page) but this is not essential. I really do not have a clue on how to go about this I have looked a grouping and page breaks but I am new to this and really would like some advice as to how best to achieve this.

Any help at all would be great

Thanks
Jackie

 

[Bilbo_Baggins_Esq]

Well, this is a similar to need as I encountered once when attempting to produce a multi year amortization report with yearly totals after every December payment

I had to create a temp table into which the first group of records are appended.
A query then ran the calculations appended to the last record in that temp table (you have to add an ID column to the table so they all stay sorted correctly).
Then a query appends that to a final table.
then a query clears the temp table
Then the process repeats until certain values are reached.
In my case, when the principal balance equaled zero.
In your case, you could use a processed column with check boxes and a counter.
Or, in VBA you could asign a variable to the total record count and then count down for every record added and then exit when it equals zero.

Then the final report is fed from the final table instead of the table you're currently using.

Of course, you also need to clear the temp and final tables when done so you can start fresh the next time you need it.

I know it's kind of vague, but I don't know your exact structure.
I think if you look at your challenge in light of this process, you might be able to apply the same concept to your need.

 

[jackie77]

Thanks for the reply however it sound really complicated if I forget about the calculations etc is there a way to right code that uses page breaks to start a new page after 50 rows of text, the invoice number can then be manually inputted in a text box? would this be possible if so has anyone any ideas of what the code would be?

Thanks

Jackie

 

[Bilbo_Baggins_Esq]

You can put sup reports in the footer, and you can size the body such that there is only a specific number of rows in the main body, HOWEVER, I do not know of a way that you can cause the footer of each succisive page to be totals for only that page without more scripting involved.

for example you could add a check box and total the check boxs records in the footer, open and print the report, close the report, clear the first 50 check boxes, check the next 50, and reopen the report, etc, etc.

However, you would not be able view it all as one contigious report, and you would get a report title at the top of every printed page.

 

[jackie77]

Thanks again for replying , I found this example on the web but I can seem to get it to work


-------------
Page Breaks
In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group in a data region (typically a group immediately outside the detail), add a page break to the group, and then add a group expression to group by a specified number of rows.

• The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this results in a page break every 25 rows.

=Int((RowNumber(Nothing)-1)/25)

----------------------

but when I put the code in the group expression is brings up the error
Undefined function 'RowNumber' in express

it also places square brackets around the Nothing

=Int((RowNumber([Nothing])-1)/25)

Any ideas

Thanks again
Jackie

 

[Bilbo_Baggins_Esq]

Hmmm, well that looks promising for your usage, but I'm afraid I can't help you with this feature too much.

My usage was different in that I needed to have several totals per page, and not just at the bottom of each page.

However, I think I may have just figured something out for you, Try this:

Get your table or query populated with the data you want.
Click on the reports button
click new
Choose Report Wizard and select the data source in the drop down click OK
SEnd all the columns you want to the selected fields side
click next
In this next step, select a field that you want to be counted, in my case I used Line_ID, and then hit the single arrow at the top to add that grouping to the report
Then hit the Grouping options button
On the grouping intervals drop down set it to 50s and click ok
back at the main dialog click next
set the sort order off of a column and then click the summary options
This opens a dialog box that allows you to perform some calculations per defined group (in this case by 50s)
click ok
click next back at the main dialog
choose you layout click next
choose your style click next
click finish

It may not be EXACTLY the way you want it to look, but now you can play with it, and look at the objects and how they are defined, and tweak it just the way you want it.

I hope this helps because I just learned something too!

 

[jackie77]

Hi

This is looking promising, thanks for all your help it was good of you to look into it for me, I really do appreciate it, I am now just making a few changes to my tables etc to try and get it to do what I want

Thanks again
Jackie