Totals not adding correctly

[Andrea Merriam]

Hi,

I'm having a problem with my totals not adding correctly. I've attached a screen shot of what I'm getting. My totals expressions are as follows:

total(calc->Cost,grp->Entityreffullname,grp->detail)

total(calc->Cost,grp->Customerreffullname,grp->detail)

total(calc->Cost,grp->Grand,grp->detail)

The line item data is correct, and matches what I get from within QB. Adding rounding to the expressions has no effect. The data being calculated only goes to 2 decimal places.

Help??

Andy

[Al Buchholz]

Re: Totals not adding correctly

Andrea

Welcome to QRB World..!!

You are reporting from the TimeTracking table and the Entityreffullname and Customerreffullname are both 209 characters in length for a total of 418 characters.

The problem is that the sorting routine will only handle 240 characters of sortable data.

So what you need to do is to change the group field to an expression - left(Entityreffullname,100) which will only use the first 100 characters of the field. I'm guessing that is more than enough to cut all of the blanks off, but not cut off any needed data.

Do the same for other long fields and make sure that the total number of characters being sort on is 240 or less.

[Andrea Merriam]

Re: Totals not adding correctly

Thanks Al,

Can you be more specific--where exactly do I need to do this? I tried putting it in the totals expressions like this:

total(calc->Cost,grp->(left(Entityreffullname,100)),grp->detail)

and it didn't take--the changes weren't there after I closed the properties box and went back in.

Am I editing the totals fields in the group footers, or something else?

Andy

p.s. This is my third day working with QReportBuilder, so be gentle . . .

[Al Buchholz]

Re: Totals not adding correctly

Thanks Al,

Can you be more specific--where exactly do I need to do this? I tried putting it in the totals expressions like this:

total(calc->Cost,grp->(left(Entityreffullname,100)),grp->detail)

and it didn't take--the changes weren't there after I closed the properties box and went back in.

Am I editing the totals fields in the group footers, or something else?

Andy

p.s. This is my third day working with QReportBuilder, so be gentle . . .

I'm always gentle.... I hope..

Sorry I wasn't clear...

When you define the groupings, you should use the expression. Leave the totals alone- as long as you keep the grouping names the same....

See attached gentle image ;-)

ps - you are invited the Thursday webinar at 1 pm CST
pss - hope this helps - if not let us know....

[Andrea Merriam]

Re: Totals not adding correctly

Thanks again, but it still didn't work. I changed the group definitions as you showed, then tried with 50 instead of 100, then tried re-creating the totals fields, then tried closing and re-opening QRB, and the totals stayed wrong each time. What am I missing?

And as long as I'm bugging you, is there a way to choose a field in a table based on information received from an Ask variable? I have to be able to create historic labor costs reports, and I need the report to choose the field with the pay rate that is applicable for the time period entered by the user. I've been able to use a calculated field to parse the Ask results into a string that is the same as the appropriate table->field string, but the expression genie doesn't recognize it as a valid table->field reference. Is there a) a way to make the expression genie recognize my string as a valid table->field reference, or b) another way to go about this?

Finally, I keep getting an error when I preview my report: "Warning: Field object references an nonexistent field: A_CASE->DELETED." Do you have any idea what that might mean? To the best of my knowledge, no-one here has changed the structure of any of the tables, much less deleted any fields.

I'd love to do the webinar, but can't this week.

Thanks for all your help.

Andy

[Al Buchholz]

Re: Totals not adding correctly

Thanks again, but it still didn't work. I changed the group definitions as you showed, then tried with 50 instead of 100, then tried re-creating the totals fields, then tried closing and re-opening QRB, and the totals stayed wrong each time. What am I missing?

And as long as I'm bugging you, is there a way to choose a field in a table based on information received from an Ask variable? I have to be able to create historic labor costs reports, and I need the report to choose the field with the pay rate that is applicable for the time period entered by the user. I've been able to use a calculated field to parse the Ask results into a string that is the same as the appropriate table->field string, but the expression genie doesn't recognize it as a valid table->field reference. Is there a) a way to make the expression genie recognize my string as a valid table->field reference, or b) another way to go about this?

Finally, I keep getting an error when I preview my report: "Warning: Field object references an nonexistent field: A_CASE->DELETED." Do you have any idea what that might mean? To the best of my knowledge, no-one here has changed the structure of any of the tables, much less deleted any fields.

I'd love to do the webinar, but can't this week.

Thanks for all your help.

Andy

Hey Andy you missed a good webinar..

The A_CASE->DELETED object - haven't seen that before... (heard that before?) but something may have put an object out there that isn't needed... the view - object explorer might help find the object so it can be deleted, or you can build another report.

The ASK variable is available for calculations - even if it isn't shown by the helpers.. Just copy it from the selection area and paste it into a calculation and test.

We may have to do a session to see where the issue is in your groupings..... or export a report with the data and email it to me. alb @ bookwood.com

[Andrea Merriam]

Re: Totals not adding correctly

I was able to find and remove the object causing the error message by using the Object Explorer, so that's one problem solved.

As for the totals, the amount that is being totalled is a calculated field based on a pay rate from a linked Excel table as well as on data from the Timetracking table. It includes an If statement to determine which set of paydata to use for each Txndate. Could this be part of the problem? The individual line items are calculating correctly--when I export to Excel and run subtotals on just the line items, everything matches the info from QB.

I've tried rebuilding the report from scratch, and that didn't help. I removed and re-linked the Excel table and overrode the default 255 chars (changed it to 50) on the Employee field (which in the report is linked to the Entityreffullname field in the Timetracking table), just in case that might be a problem. Rebuilt from scratch again, using the newly-linked Excel table. Still not working.

Andy

[Al Buchholz]

Re: Totals not adding correctly

Andy

Not sure.. I'll need to recreate the report and data on my machine or work on yours...

email me if you have time to look at it now..

[Al Buchholz]

Re: Totals not adding correctly

As a follow up, I spoke with Andy and found that the totaling was a rounding error.

So I rounded the detail lines by creating a calculated field with the round() function and then totaled on the footers by dragging the rounded calculated field and having the genie create a total field based on the rounded calc field.

[Andrea Merriam]

Re: Totals not adding correctly

Thanks for the phone call, Al.

Ultimately, the detail problem was a rounding error. In the greater picture, the problem is a difference in rounding procedure between QRB and QB. When the individual totals appeared wrong, the grand total for my report was off from the QB report by $0.07. Once rounding was applied in QRB, the grand total was off by $0.37. I noticed when I exported the report to Excel that if the precision showing in the report upon export was 2 decimal places, with no rounding, the line items came out exactly the same as the QB report, and that the data exported only went to 2 decimals. However, if the export was done with 4 decimal place precision and no rounding, and I then did the rounding in Excel, the line item totals were off from the QB report by the $0.37. My question now is, why do I only get precision as shown when I do an export of my report data, especially if the "unshown" precision is still causing rounding errors within QRB? Alternatively, is there an option to truncate my data within QRB so that my totals will reflect the trucated data I get upon export to Excel?

Andy