Report totals wrong?

[Rob Twohy]

I ran a yearly report for sales. I have only had 3 sales on Atomic Avenue, so this pic represents EVERYTHING. Those totals are whack. What could be causing that?

[Mark J. Castaneda]

Definitely weird total...Let me notify the CB programing team about this one.

[Peter R. Bickford]

Definitely weird. Can you send your active database to support@comicBase.com so we can examine it and see what's up? (You can use Sidekick to back it up, then let support known when they can look at it)

 

[Rob Twohy]

Also I had someone buy a book that was already sold. I run my inventory IMMEDIATELY upon sales notification, and also I had a book priced wrong. I sold a Supergirl #1 in NM- condition for $11.50 and it should have been $93. I'm definitely experiencing EXPENSIVE growing pains with CB/Atomic Avenue.

[Rob Twohy]

Pete, I backed it up, do I need to physically send (via email) or do they have the option to look on the server side?

1 hour ago, Peter R. Bickford said:

Definitely weird. Can you send your active database to support@comicBase.com so we can examine it and see what's up? (You can use Sidekick to back it up, then let support known when they can look at it)

 

 

[Peter R. Bickford]

I looked into it, and after messing around with it for several hours, I'm at a bit of an impasse. The TLDR version is:

Your actual database is fine, your data is good, but it looks like limitations in Crystal Reports are making it impossible to get proper totals on the detailed sales report. At this time, it doesn't seem possible to get accurate totals through Crystal Reports from the detailed sales report due to limitations in that platform--unless we completely reimagine how the data gets to the report, which isn't likely to happen in the near term.

The long, complicated version:
Crystal Reports is insisting on calculating out these summary fields for every line on the reports, which normally is fine, but since several fields like sales commission, shipping etc. don't exist on a line-by-line basis for each item on the report and are in fact totals for the order, they wind up getting summed up for every line on an order, vastly overstating their amounts. With a bunch of tedious work, it'd possible to get an accurate grand total of the goods totals, since they're basically Price * Qty for each line on the order. The problem comes in that shipping, commission, etc. only exist per order, not per line. Worse, it's not possible to come up with a "per-item" version of these fields at report time, since doing so would depend on knowing the sum of an order's qty at the "detail" time, but Crystal Reports doesn't calculate it until the "grouping" time (when the order number changes). We tried a host of methods of working around this, which gave some partial success, but nothing that would give you the "bottom line" numbers you'd want.

It's certainly possible that we might tumble to a different way of twisting the report around in order to pull this off, but about the only way we can think of pulling off the trick is to "pre-chew" the data in some way to build out the fields that Crystal Reports would need in order to calculate these fees on a per-item basis properly. Unfortunately, when we got into the specifics of what was needed, it's actually seeming like a surprisingly tricky job to pull off, and probably involves significant changes to both the report and ComicBase. It's also possible that someone else knows a Crystal Reports trick which would allow us to attack the problem from another angle that's eluded us, but at the moment, we're frankly stumped as to what that might be.

We've put out a new build of the software which removes the totals from the detailed version of that report, noting that they're available in the basic version of the report. It's a band-aid for sure, and I'd like to revisit it in the future, but at the moment, it's likely where we'll need to leave that particular version of the report unless we come up with something clever.

-Pete

[Rob Twohy]

Yikes!

ThanX Pete for that explanation. I sure don't envy all the programming that goes into all of this... HA!

Rob