Question

IFS Report - Show cumulative sum of amounts from previous pages

  • 25 January 2022
  • 4 replies
  • 459 views
R
Rank
Userlevel 2
Badge +7
  • Rohit
  • Sidekick (Partner)
  • 36 replies

Hi,

I am fetching Lines and Items from “Application for Payment” Screen and displaying them on an Order Report.

I have an amount field on item level that appears on the report page body.

I need to display a field on every page of the Report. This field needs to calculate the sum of that amount field upto the previous page.

For example, on Page 3, the sum of amounts till page 2 will be shown. On Page 4, sum of amounts till page 3.

Illustration of the requirement

The no. of pages will depend on the no. of lines and items in the Application for payment screen, which is unbounded.

Also, the no. of lines and items that will fit in one page of the report is dynamic. Since I am displaying the description text of these lines and items, I don’t know how long the description can be and how many lines it will occupy on the Report layout.

 

Any help is appreciated. Thanks in advance.

This topic has been closed for comments

4 replies

T
Rank
Userlevel 4
Badge +11

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?

 

R
Rank
Userlevel 2
Badge +7

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.

T
Rank
Userlevel 4
Badge +11

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.  

 

R
Rank
Userlevel 2
Badge +7

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.

 

 

Terms & ConditionsCookie settings