Totals Inconsistent on Report

[Leah]

I Have a very complex report - I have attached the files and its the only report in the database. I Set it up so when you run it, it runs just one project - The subtotals for the PO Lines for each category don't always add up correctly. I have tried every scenario in changing my codes and even started from Scratch. It works on most of them, but around page 5 or 6 they start to be all wrong - this is for the Purchase Information on the right side of the report - this report only prints to 11x17 paper (if your printer can handle that size)- so you may have to change the setting so you can view on the screen.

[fddizon]

Re: Totals Inconsistent on Report

Leah,

i think you forgot to state the username and password to open your database attachment.

:)

I Have a very complex report - I have attached the files and its the only report in the database. I Set it up so when you run it, it runs just one project - The subtotals for the PO Lines for each category don't always add up correctly. I have tried every scenario in changing my codes and even started from Scratch. It works on most of them, but around page 5 or 6 they start to be all wrong - this is for the Purchase Information on the right side of the report - this report only prints to 11x17 paper (if your printer can handle that size)- so you may have to change the setting so you can view on the screen.

[Tom Cone Jr]

Re: Totals Inconsistent on Report

Try writing all the calc field expressions without nesting previously defined calc field expressions.

[Leah]

Re: Totals Inconsistent on Report

Sorry - thought I took the password off - its 19bart59

[Leah]

Re: Totals Inconsistent on Report

This Total does not have embedded Calculated Fields -

[Tom Cone Jr]

Re: Totals Inconsistent on Report

This Total does not have embedded Calculated Fields -

Which specific calc field does "This Total" reference?

And ... even more importantly ... which specific values are wrong on this report, and what should they actually be?

This is a very complex report. You are familiar with it. How about spelling out the details so that others can begin to learn how it works, too?

[Leah]

Re: Totals Inconsistent on Report

This report totals all the PO info for each Budget Item. Its on the Right side of the report. If you look at the first page Line1 F 1 - there are 2 line items and it totals correctly - but when you look at Page 6 - 2 A 3 - it totals correctly, but then you go to 2 A 6 - (starts on page 4 end on page 5) and it should be 19566.28 and is showing 17889.58 - It would be easier to figure out if the difference I could pinpoint to something - but I can't - then it seems from that point forward the totals are incorrect.- again with no specific reason why

[Tom Cone Jr]

Re: Totals Inconsistent on Report

This report totals all the PO info for each Budget Item. Its on the Right side of the report. If you look at the first page Line1 F 1 - there are 2 line items and it totals correctly - but when you look at Page 6 - 2 A 3 - it totals correctly, but then you go to 2 A 6 - (starts on page 4 end on page 5) and it should be 19566.28 and is showing 17889.58 - It would be easier to figure out if the difference I could pinpoint to something - but I can't - then it seems from that point forward the totals are incorrect.- again with no specific reason why

Well, that's some of the information needed. How about the specific calc field names?

[Leah]

Re: Totals Inconsistent on Report

The one that is giving me the problem is : CALC->tot_po_lines_Po_line_total_for_Budget_line_no - And I have tried every combination of the groups -

[Tom Cone Jr]

Re: Totals Inconsistent on Report

The one that is giving me the problem is : CALC->tot_po_lines_Po_line_total_for_Budget_line_no - And I have tried every combination of the groups -

I don't understand your grouping structures. Especially, 'Budget_fulllineno'.

Please explain the grouping definitions from top to bottom (first to last), describing how the records will be grouped and what table & field is used in each group break definition. e.g. at the moment I can't find the field that's used to define the group break for the 'budget_fulllineno' group, and I'm wondering if that group is necessary. I would think you would want to use the group footer for the 'PO Line' group to generate the total of a field in the PO_Lines table. What purpose does the Budget_fulllineno group serve?

[Al Buchholz]

Re: Totals Inconsistent on Report

Leah

Your group SubCat is sorting on one value and breaking on another expression.

Watch the length of the overall sort also. That 240 character limit was worked on, but I haven't proven that there is something yet in the length of the sorting string isn't still effecting the sorting/totaling results...

Did you copy some of the calc fields from one footer to another? I'm seeing some total() that has suspect grouping.. (ie the 3rd argument in the function)
Does this structure come from QB?

[Leah]

Re: Totals Inconsistent on Report

Al- The SubCat Group is not the one causing the issue. And the fields aren't even close to the 240 limit. My break on this group is just parsing the field into its parts and my sorting uses the same field. It is possible my Grouping on the total() is wrong - I have played with every combination to try and make it work.

Here is the breakdown of the Groups:
The Groups:
This report is based on our Budgets with the PO Information for purchases made against the Budget Item. The Budget Category consists of 3 fields � Budget Line NO, (Main Category- Always Numeric)Budget_line_sub_no(Sub Category- Entered as A or maybe A.1), Budget_Fullineno(Calc field that combines the two as Example 1.A.1)

The report Group Budget_Line_no is the main group and based on the same field - Usually no PO info except default stuff
Group SubCat is actually the next Category- as I explained above it gets me my first break in the Category - i.e. the A in 1.A.a, Group Budget_Fulllineno is the Budget_Line_sub_no that is calculated in the description and if its Say A.0 it will skip it because the A was the SubCat, if it�s A.1 it will only show the 1. The PO Line - which each line of the PO is assigned a Budget Line item that is linked to the Budget(1-many) is the PO Information for that Group Budget_Fulllineno

The main idea of this is to keep track of what we have issued PO�s for against each Budget item � and be able to see the detail of them, as well as see how much for that line item we have spent, and balance.

Hope this makes sense � It took a lot to get this to work correctly to this point � now if the totals would just work correctly it would make my day.

[Leah]

Re: Totals Inconsistent on Report

I just tried one thing and it seemed to help the one total - I have been fighting with, but creates another problem - which hopefully is easier to figure out (I think this is why I created a Group on PO Line_NO) - now my Receipt Total doesn't calculate correctly -

I was trying to put the Total of a Line Item for the PO that had been received to Date - since it could come in at different times, there could be multiple receipt records for each PO _line and hence the Total - any suggestions on making this work by moving this whole thing to Detail??

After looking at these calculated fields so long, my brain doesn't seem to be thinking clearly

[Tom Cone Jr]

Re: Totals Inconsistent on Report

Leah, if I understand you correctly, the records in your Budget_Fulllineno group are not necessarily a sub-set of the records previously grouped by SubCat. Right? That would explain the problem. As you nest these grouping levels each succeeding group break must be a self-contained sub-set of the previous groups collection of records.

[Al Buchholz]

Re: Totals Inconsistent on Report

Leah

Simplify - divide - conquer

Break this into smaller pieces that work and then combine.