I can’t think of a way to show the previous page total at the top of the next page if the record counts per page are dynamic.
You could do a running total using sum and partition by either as a custom field or in your report code. And you could show the running total for the previous record using lag or by subtracting the current record from the running total. But I don’t know how you’d make that appear only once at the top of the new page.
Do your description lengths vary so much that you can’t create groups of records with subtotals?
I can’t think of a way to show the previous page total at the top of the next page if the record counts per page are dynamic.
You could do a running total using sum and partition by either as a custom field or in your report code. And you could show the running total for the previous record using lag or by subtracting the current record from the running total. But I don’t know how you’d make that appear only once at the top of the new page.
Do your description lengths vary so much that you can’t create groups of records with subtotals?
Yes Tracy, the lengths of the descriptions are totally unpredictable. They can take 1 or more lines. If I make an ‘x’ number of lines items as a group, I won’t be sure that they will fit in one page.
You may be up against the limitations of the tool. I don’t think running page totals are supported and Chanaka has confirmed, in your other question, that Page() can’t be used in conditions.
If the report must be printed and must contain page totals, I would alter the layout to give the descriptions as much space as possible but also limit them so they can’t flow to a new line. I’d then figure out the optimal number or records per page and create page groups with subtotals.
Of course, you can also use other tools such SSRS and Excel to create alternate layouts for your operational reports.
I created an approximate algorithm using the worst-case scenario.
I assumed in the worst-case scenario, the description texts will take 35 characters per line and the page can fit in 15 lines. This scenario was the worst-case, when characters are capital letter, 35 chars will fit on one line. (I used the ‘M’ character, as it is the widest character of the font, to count minimum no. of chars per line).
I created few variables like -
chars_per_line_ := 35;
lines_per_page_ := 15;
line_counter_ := 0;
In the for
loop of the item, I counted the length of description text then took CEIL
of length/chars_per_line_
to determine how many lines it will occupy. Then increased the line_counter_
As soon as my line_counter_
increased the limit of lines_per_page_
I added some value in an XML flag-like column that I created in the schema.
On the layout, I checked if this flag column has a value or not, if it has, I added a page-break after
. The value in the flag column was the running-sum.
The value I added in the flag column was the running-sum only.
On the layout I used a trick. I added the flag column twice in 2 consecutive rows.
On the first row, I added the condition that if it has a value add a page-break-after
. So that the flag column on the 2nd row will shift on top of the next page.
This wasn’t an accurate algorithm but it handled the cases well. When the descriptions were in lowercase characters, more than 35 chars would fit on one line. So my algorithm will count more lines than they actually occupy. At least there is no harm in this approach as it will page-break early and leave a few blank lines in the last.
Sorry, I couldn’t share the code and screenshots because I left the company, so I don’t have access to the code.
Thanks.