Link to home
Start Free TrialLog in
Avatar of Lisa Audain
Lisa Audain

asked on

Sum not calculating correctly

Hi,

I have a situation where a report was created in a lower version of Crystal and the totals on the report showed accurately.  However I am now using crystal report 2008 version 12 and I am having problems with my summation.

To be specific, two groups were created on the report.  The second group has the quantity shipped.  So on creating a total on the quantity shipped and I am getting a total but it is overstated.

I changed the summary to a count and I realized that the count is counting an extra record which is not on the report.  This was not occurring in the previous version of crystal so I am at a lost as to what is causing this issue.

Please help, any suggestion will be greatly appreciated.

Lisa
Avatar of James0628
James0628

I doubt that changing the CR version could change how many records you get in the report.  I certainly wouldn't expect it to under normal circumstances.  There are some settings in the report that can have that effect, like how the tables are linked, but if you're using the same RPT file (as opposed to trying to recreate the report from scratch under the new CR version), then any settings like that should be stored in the RPT file, so they should be the same under the newer CR version.

 Do you know which version of CR was used to create the report?  If it was a really old version, like CR 9 or earlier, then I guess there might have been some changes since that version that could affect the data.  I really don't know.  If it was CR 10 or later, then I doubt it.

 Do you have a copy of the same report, showing the same data, but run under the older version of CR?  If not, then maybe there is actually just another record there, and the report would have shown the same thing under the older CR version.

 What is the datasource for the report?  Tables?  Stored procedure?  ...

 If you're sure that there is an "extra" record, you could start by trying to figure out where it's coming from (eg. an additional record in one of the tables).  If the report is using tables, you could try temporarily adding some additional fields from some tables and look for "duplicate" entries, although if they truly are duplicates (all fields the same), then you won't be able to spot the extra record that way.

 James
Avatar of Lisa Audain

ASKER

Hi James

Thanks for the response.  Firstly, let me clarify by saying that the report was previously done in Crystal Reports 10 and I am running the report through an application that uses SQL as the database platform.  This is actually happening on two reports and the problem is basically the same.

In the application, the information is showing accurately on screen.  The total values are accurate and the amount of lines are also showing accurate.

In addition, what I have noticed is that the total on the reports will be correct sometimes and then for one or two invoices the total will be incorrect.  This sounds really strange to me.  I have worked with crystal reporting many times but not with Crystal reporting 2008 and this is the first time I have encountered this scenario.  The datasource is Application database running on SQL 2014.

It is using a couple of tables. no stored procedures for either reports.  I run some SQL scripts on the tables and it is returning the correct results.  I also placed it on other sections of the report but it returns the same values. I am totally at a lost here.

So instead of a summation, I decided to do a count and that is when I realized that the number of lines being counted was not correct.  So I expect that there is another record, but where and why, I do not know.

For one report the field Levy.  This is placed in the group footer of the report and the formula is   if Tax00 = 'Levy'  then Amt00 else  TaxAm00 + TaxAm01

Now I wanted the lines items to be totaled.  So I used a sum to total all the values from that formula.  The summation could not be placed on the page footer, where I wanted the sum, so I created another formula  is  sum ({@Levy}).

As mentioned the total comes out correctly on some invoices but not on others.  On the invoices that it did not come out probably I manually check each line item and it is correct so the line items does not seem to be a problem.

Any other suggestion?   Thanks again
Verify if you have the report to return DISTINCT records.
Open the report
Click DATABASE
Check if SELECT DISTINCT RECORDS is selected

Check the LEVY formula
Is it set to EXCEPTION FOR NULLS or DEFAULT VALUES FOR NULLS

mlmcc
You said that you wanted the total in the page footer.  Is that supposed to be a total for each page, or for the entire report?  Sum ({@Levy}) will give you the total for the entire report and it seems a little unusual to be putting a report total in the page footer, so I wanted to check.

 You said that when you run queries on the tables, you get the correct results.  You could try running the report for some data that gives you "extra" records and then go to Database > "Show SQL Query" to see the query that CR used.  You could copy that query and try running it in the db to see what you get.

 James
Hi James,

Both reports do not have 'distinct records' selected.

1st Report  =  This report is an invoice report that has Levy Tax as one of the columns.  The report is grouped by customer.  So at the bottom for each customer's invoice I have two separate taxes, one for VAT and the other Levy.   When I manually check the line items they are correct but the total of the Levy is incorrect.    I want the total for each page per customer so the totals are place at the page footer.  If the number of invoices go over to the another page, the totals always appear at the end or bottom of the report.

Yes when I execute scripts on the database, it returns the correct values.  When I click on show query I am getting an error message about stored procedure not found but I am able to log in and it shows.  So I copied it into sql and executed the script and records do appear.

Any other suggestion?

2nd Report =  This is also an invoice but it is created and formatted differently.   It has two groups (Group header 1 - formula ( SOShipHeader.CpnyID} + {SOShipHeader.InvcNbr} + {SOPrintQueue.InvcNbr} + {SOShipHeader.ShipperID})  The name of the formula is @DocUniqe.    The second Group header 2 is LineRef.   The details section is suppress and the main information is placed on the 2nd Group footer with several sections of that group on the report.  

The group footer (section 2b) has the quantity shipped and the description fields.  It also has several group sections but they are suppressed based upon certain criteria in the report.   A Summary on the quantity shipped was placed on group footer 1 as SumofShipper.QtyShipped.

 This summation is what is incorrect.  

Do note that the summation is incorrect randomly.  On some invoices, it is showing accurately for both reports
ON the one try putting the summary in the group footer.

Page footers get somewhat different handling.  Just want to see if the group footer gets the correct values.

mlmcc
No same values.  I had a closer look at the SQL query in the report and on executing the same in SQL I examined the results closely and realized that one of the line items are duplicated by the same amount that the total is off by.

However, I execute some scripts directly on the tables and the results from the individual tables are correct.  Therefore it does not seem to be a problem with the formulae or where I put the summation.  Nevertheless, I am still not sure how to go about to correct the results....

Any ideas?
WHat SQL does the report use in both reports?
Something is causing Crystal to select a record twice

mlmcc
I had a closer look at the SQL query in the report and on executing the same in SQL I examined the results closely and realized that one of the line items are duplicated by the same amount that the total is off by.
That's what I thought.  It sounds like something in the reports (a table they use, or how the tables are linked) is causing some data to be included twice.

 You mentioned an error about a stored procedure, but you said earlier that the reports don't use stored procedures.  If not, then I'm wondering what that error was about.

 James
I am still a bit not clear though.  This issue is not happening for every invoice and it never happened before using previous versions.  It is the same report with the same structure being used in various crystal versions.   I will have a closer look at the link on the tables but that still does not explain why it is not happening for every invoice and in this version of crystal?

Yes, I did mention that it was not using stored procedures, but that was my mistake.   The error that I got seems to have come from the datasource not set properly.   So after I set the datasource that error disappeared.

When you asked 'WHat SQL does the report use in both reports?'   I am not sure I understand the question. Are you referring to the SQL query?
Yes.  Under the DATABASE menu is an option to SHOW THE QUERY or SQL.

We might see something there.

Are you familiar enough with the database to know if there are some tables with a 1 to many relation?

mlmcc
Hi, I have attached the report query on both reports for you.

Thanks for your help
SQL-Report-Query.txt
My initial look doesn't show anything.

I assume tables are joined on the primary key for the joined table.

mlmcc
As far as I am aware, yes the tables are joined on the primary key.  There was no changes made on the tables being used from the prior version.
My guess is still that the "duplicated" records are because you're getting more than one record for that line from one or more tables.  If you're only getting extra records for specific lines, not every line on the invoice, then you can concentrate on the "detail" tables, not the "invoice" tables (because an additional record in an "invoice" table would probably duplicate every line on the invoice).  For example, SOLine is probably a "detail" table, while SOShipHeader is probably an "invoice" table, so you probably don't need to check SOShipHeader, at least not for now.

 You could start by running the report for an invoice that includes an "extra" record and then adding more fields from the "detail" tables to the report, looking for differences (eg. two records with different dates or reference #'s).  Of course if there is an additional record, but it has all of the same values as the first record, then there won't be any differences on the report.

 In that case, I would find an invoice that includes an item with an extra record, and then run queries in the db to look at the records for that invoice and item in each table, one at a time.  When looking at the other tables, make sure that you use the same matching criteria that the report is using (eg. for SOLine, check CpnyID, LineRef and OrdNbr).  If I'm right, you'll find 2 matching records in at least one "detail" table.

 If you find a table that includes 2 records for a given invoice line, the next step is figuring out why.  Perhaps the links between the tables in the report are wrong and one of those records is not really for that line and should not be included, but it's probably more likely that there really are 2 records for that line, in which case you'd need to figure out what they are, and how you need to handle them in the report.


 Also, just a general question:
 Did you happen to make any changes to your order software, or how you use it, recently?  I had a client that changed how they processed orders for some locations at one point, and it turned out that the new procedure processed the orders in a different way, which generated additional records in one table for some items, causing those items to be duplicated on reports (pretty much just what you're describing), until I changed the reports to recognize those orders and handle them differently.

 James
Actually what I did after running the sql report query and noticed that there was a duplication, I had a closer look at all the tables identified in the query.  I assumed that the duplication had to be on one of those tables, so I executed a couple of scripts on the detail tables and I found the duplication.

After which I deleted the duplication and now the report is showing the correct values.  I am not sure why the duplication existed; maybe a glitch in the system since it occurred randomly. However, I will keep monitoring the report to see how it behaves.

Thanks so much for your assistance.

Lisa
I'm glad that you found the extra records.  Hopefully you can find out where they came from and keep it from happening again.

 James
Hi James, while I  was able to get the totals to show correctly, I recognized that one of the reports had a slightly different issue and this may be a reporting issue rather than a table system issue.

With one of the reports, I am seeing one of the line items being split into two; so in this case it is not duplicated but two lines adding up to a total 125.  So for example one line is  60 and the other line is 65.  The total will add up to be 125.  This is being shown in one of the detail tables, so what I did was to delete one of the lines and then updated the line to show one total instead of two lines and the report then showed the correct values.  The table was SoShipLot.

So it seems when that table shows the line split into two,  the report is adding both lines as 125, so the total quantity on the report is showing 250, instead 125.   I hope that I was able to explain this particular issue.  Here is the formula that I used which is adding up the quantities, could  you have a look at it for me, maybe I did not take into consideration that scenario. The formula is:

if not isnull({SOLine.UnitDesc}) and ({SOLine.UnitDesc} <> {SOShipLine.UnitDesc}) then
{SOShipLine.QtyOrd}
* (if {SOLine.UnitMultDiv} = "M" then {SOLine.CnvFact} else 1/{SOLine.CnvFact})
* (if {SOShipLine.UnitMultDiv} = "M" then 1/{SOShipLine.CnvFact} else {SOShipLine.CnvFact})
else
{SOShipLine.QtyOrd}

I then inserted a summary on this formula to total the lines.

Thanks again
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Guys, sorry for the late response.  I was able to successfully resolve the issue by taking into consideration your combined suggestions.

I had a look at the table and field in which the report received its duplication and I did the summation on that field instead of the existing  field and I was able to get the correct totals.  

On the other report, which was slightly different, I deleted the extra records in SQL on that particular table and the report shown the correct value.  After the deletion I also had to update the table with the total of both records so that I could keep the tables in sync.

Thanks again for the assistance.

Lisa
You're welcome.  Glad that I could help.

 James