Column page totals are sometimes incorrect

[Former Member]

Hello,

We are using CR to produce a government DD1149 form. Each line item has a dollar value. The report dollar total of a multi-page report is correct, but the sub-totals per page are occasionally incorrect. Some page totals include the value of the first item on the following page.

Regards,

Paul

[Former Member · Accepted solution]

Hi Paul,

Will you provide the Running Total Field settings that are generating the page subtotals?

Also, how occasionally is it? Is there a pattern (say, every 3rd page)?

Thanks, Ben

[Former Member · Accepted solution]

Hi Ben-

The pattern is easy. It happens when a record straddles more than one page. The text honors the 'keep on one page' code, but the page total calculation does not.

Since there isn't a page total function in CR we use this solution:

In the page header add a formula
WhilePrintingRecords;
Global NumberVar  pageTotal;
pageTotal := 0;
''

In the detail section add a formula to do the total
WhilePrintingRecords;
Global NumberVar  pageTotal;
pageTotal := pageTotal + {YOurField};
''

In the page footer
WhilePrintingRecords;
Global NumberVar  pageTotal;
pageTotal

Regards,

Paul

[Former Member · Accepted solution]

Alright, tried your code: worked perfectly for me.  In the Section Expert: do you have multiple Details sections? If so, do you have Keep Together checked for each subsection as well as the parent Detail section?

Maybe if we can keep your records from splitting, the problem will go away.

Ben

[Former Member · Accepted solution]

Ben-

We only have one Details section and yes, Keep Together is checked. Since we only have one Details section, I believe that we don't have a Parent Detail section.

Our customers vary the amount of info in the records, we don't have control over when a record will split between pages.

Is it a bug that Keep Together doesn't keep the cost on the correct page?

Paul

[Former Member · Accepted solution]

Not sure it's a bug...so you only have 1 Details section: how many lines are in it? Most (maybe all) of my Details sections are 1 line, so they can't split.  Is it a single line with Can Grow attribute checked?

Another question: could your records be split into 2 Details sections: Details A would contain the cost, Details B would contain the variable overflow?

[Former Member · Accepted solution]

The number of lines can vary, 2-4 is common. I've attached a sample of the error. You can see that after the 3rd record there's room or another record if it only had 1 or 2 lines. The Sheet Total should be 9 instead of 29.

[Former Member · Accepted solution]

Hi Paul,

I took my records, smashed a Detail field to be really small so I could replicate your Can Grow, multiple line records, and used your code, & I got the correct sum in my Page Footer on all 50 pages (with your code).

You must've suppressed the pageTotal in the Details section (the gov doesn't need to see that, right?).  What does it say if you un-Suppress it?  Do you see the $29 pageTotal up in the Details part before your Page Footer spits out the wrong pageTotal?  The reason I ask is that I can see what the pageTotal will be in my data by looking at my last record.

Oh, and it appears that you may need to fix the "QUANTITIESR ECEIVED" text field in the page footer, Section 17.

Ben

[Former Member · Accepted solution]

I'm not sure smashing the Detail field will reproduce to issue. You need to have records that take multiple lines and enough space in the Detail field that a record with only a line or 2 would fit, but a record with 3, or more lines doesn't fit.

I tried un-Suppressing the pageTotal and it was also incorrect.

I've saved off a report with data. Would it be helpful to send you it? If so how do I get it to you?

[Former Member · Accepted solution]

Before you send me data & rpt, please send me a screen shot of the report with the unsuppressed pageTotal.  I want to see something.

[Former Member · Accepted solution]

Here you are. Errors occur on pages 1 and 2.

[Former Member · Accepted solution]

Sorry about the lag time: been out of the office. Do you have a slice of data to send (if not, I can generate my own) and send me the *.rpt? Send it to bMeibos at ChemtechFord dotcom.

[Former Member · Accepted solution]

Found it! And I got it to reproduce on my end.  Here's the fix:

Your equation reset (in PHa) contains a subtraction of your summation (@I total cost).  I simply set my variable to 0.  What your's does is subtract the last calculation of "@I total cost" (from the previous page) thus putting your summation off by that much. Put this in to your equation for Balance_Reset:

WhilePrintingRecords;

Shared NumberVar BalanceDue_total;

BalanceDue_total := 0

[Former Member · Accepted solution]

Ben-

I tried your fix and the error increase by the value of the first record. In the sample I gave you, the Sheet Total should be 290.00, I was getting 590.00, and with your change it's 610,00.

[Former Member · Accepted solution]

Interesting.  You have an instance of your cumulative iteration (Balance_Cum) in PHd.  I think that is why it's now off by the amount of your first record, so let's try removing that.  It will probably put you back to $590, but let's try.

[Former Member · Accepted solution]

Ben-

I think that the 2 items that you found:

PHa: BalanceDue_total := 0 - {@(I) total cost}; instead of just: BalanceDue_total := 0

and the Balance_Cum in PHd

are remnants of the original developer trying all sorts of fixes. These offset each other. When both or corrected we are still back at square one. Which is when a record overlaps pages it's value is used on both pages. It doesn't 'keep together'

Where to now?

[Former Member · Accepted solution]

OK.  Let's adjust the location of the formula @line_Desc.  Currently, you have it dropped into Details as a Formula Field.  The formula within (somewhat lengthy), I believe, concatenates the string during the print pass.  If you create a Text Box, then drag & drop the @line_desc formula into it, my understanding is that the String will be concatenated during the reading pass.  It will not change the look of your field, but perhaps it will shift the calculation of the length (which determines page breaks) prior to the print pass.

Worth a try.

[Former Member · Accepted solution]

Bingo! It looks like this will work with 'Can Grow' chosen on the text box.

Thank you for staying with me.