Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Crystal Report 2008 no...
Microsoft Dynamics SL (Archived)

Crystal Report 2008 not totaling accurately

(0) ShareShare
ReportReport
Posted on by Cynthia Audain 1,270

Hi All

I came upon a strange scenario in Crystal report 2008.  I say it is strange because the problem did not exist in the previous version of Crystal but the total on the report using the new version is now inaccurate.

To be specific, I created a custom report in the previous version to sum on quantity shipped; it worked fine.  The summation was placed in the group footer but after upgrading to SL 2015CU1, the report has started to behave abnormally.

When the summation for the total quantities seems to be duplicating one of the line items.  Also when I do a count on the number of lines that should be totaled it is telling me that it is 12 but it should be 11.

I am presently at a lost as to what is causing this issue.  Has anyone noticed this before?  Any suggestions, and assistance in this matter will be greatly appreciated.

Regards 

*This post is locked for comments

  • Verified answer
    Cynthia Audain Profile Picture
    Cynthia Audain 1,270 on at
    RE: Crystal Report 2008 not totaling accurately

    Rick, I was able to resolve the issue.  I changed the formula on the report to summarize on the table that split the line items into two.  On making the change the totals are now  showing correctly.

    I also thought of another solution, so I deleting one of the line items on the suspected table and I also realized that the report was corrected. However, in this resolution I had to update the remaining line with the corrected values shipped.

    Thanks for all your suggestions though.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Crystal Report 2008 not totaling accurately

    Lisa, as I said earlier, I do not deal with the sales order / shipper portions of Dynamics SL so I am hoping someone that does can give you a better response.  What I do believe is that the issue appears to be related to a sales order that was filled from more than one lot causing 2 lot records to be attached to the one shipping record.  I suspect that the report is creating its totals from the shipping record hence the doubling of the quantity and price.  If you need lot information on the report then the solution may require form a view of the lot using a group by instead of going directly after the lot table.  Again, I could be off base here since these tables are out of my normal activity.

  • Cynthia Audain Profile Picture
    Cynthia Audain 1,270 on at
    RE: Crystal Report 2008 not totaling accurately

    Hi Rick, I was able to narrow down the issue to SoShipLot table.  When I look at this table, I am seeing on one of the reports that is giving a similar problem, I saw where the line item was split into two lines.  The report instead of adding up the lines, it duplicated the values.

    For example the total line item on the shipper is 125, but on the table it is showing two lines one has 60 and the other 65. The report is looking at both lines and adding 125 making it 250.  Now I was able to delete one of the lines and updated the remaining line with the total of 125 and the report showed the correct totals.  So it seems like the formula being used to total the quantities may not have taken that into consideration but I am not sure how to fix the report.

    Could you have a look at the formula itself to see if anything appears odd to you?  Here is the formula:

    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 insert a summary on this formula to add up the line items.  The total is overstated by 125 on the report because of the above scenario.

    Your thoughts will be appreciated.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Crystal Report 2008 not totaling accurately

    Lisa, I do not see anything obvious in the query but, then again, it is using several tables that I do not deal with that much so perhaps someone else might have an idea.  I have no strong reason for saying this but the fact that the query is joining in the note table twice caught my attention even though the joins for the two instances are using what appears to be the correct criteria.  I suppose the main reason for suspecting the note table is the fact that you stated that only one item is duplicate so I was focused on a table that would not frequently be populated.

    One kind of brute force way to run this down is to execute the query in SQL looking for the duplicate line and then modify the query in SQL adding in the full contents of all the tables and add a where clause to limit the results to the duplicate item and then look at which table included more than one line to the same line as all the other tables.  Once you have that, you should be able to determine where the query needs another criteria on the join of that table or that the table has an unexpected duplicate record.

    Sorry I was not more help.

  • Cynthia Audain Profile Picture
    Cynthia Audain 1,270 on at
    RE: Crystal Report 2008 not totaling accurately

    Hi Rick

    The only filter is  - {SOPrintQueue.RI_ID} = val(RIPARAM( "RI_ID" ))

    The query behind the report is as follows.  Thanks for the assistance.

    SELECT "Terms"."Descr", "SOType"."Descr", "SOShipHeader"."ShipperID", "SOShipHeader"."CustID", "SOShipHeader"."BillName", "SOShipHeader"."BillAddr1", "SOShipHeader"."BillAddr2", "SOShipHeader"."BillCity", "SOShipHeader"."BillState", "SOShipHeader"."BillZip", "SOShipHeader"."ShipAttn", "SOShipHeader"."ShipName", "SOShipHeader"."ShipAddr1", "SOShipHeader"."ShipAddr2", "SOShipHeader"."ShipCity", "SOShipHeader"."ShipState", "SOShipHeader"."ShipZip", "SOShipHeader"."OrdDate", "SOShipLine"."InvtID", "SOShipLine"."UnitDesc", "SOShipLot"."WhseLoc", "SOShipLine"."AlternateID", "SOShipLine"."AltIDType", "SOShipLine"."Descr", "SOShipHeader"."CpnyID", "SOShipHeader"."OrdNbr", "SOShipHeader"."SOTypeID", "SOShipLine"."LineRef", "SOShipLine"."OrigInvtID", "SOShipMark"."City", "SOShipMark"."State", "SOShipMark"."Zip", "SOShipLine"."QtyShip", "SOShipLine"."CurySlsPrice", "SOShipLine"."CuryTotMerch", "SOShipHeader"."CuryID", "SOShipHeader"."InvcDate", "SOPrintQueue"."Reprint", "SOShipHeader"."DropShip", "SOShipMark"."Name1", "SOShipLot"."QtyShip", "SOPrintQueue"."RI_ID", "SOShipLot"."LineRef", "SOShipLot"."LotSerNbr", "Inventory"."LotSerTrack", "SOShipLine"."CpnyID", "SOShipLine"."ShipperID", "Country"."Descr", "ShipCountry"."Descr", "SOShipHeader"."InvcNbr", "SOPrintQueue"."ShipperID", "SOShipHeader"."BillAttn", "SOShipHeader"."NoteID", "SOShipLine"."NoteID", "SOType"."Behavior", "SOHeaderMark"."Name1", "SOHeaderMark"."City", "SOHeaderMark"."State", "SOHeaderMark"."Zip", "SOShipLine"."CnvFact", "SOShipLine"."UnitMultDiv", "SOShipLine"."QtyBO", "Site"."Name", "SOLine"."UnitDesc", "SOShipLine"."QtyOrd", "SOLine"."UnitMultDiv", "SOLine"."CnvFact", "SOPrintQueue"."InvcNbr", "SOShipHeader"."Cancelled", "SOShipHeader"."ASID", "SOShipHeader"."WSID", "SOShipHeader"."CustOrdNbr", "SOShipHeader"."SiteID", "SOShipHeader"."CuryTotPmt", "SOShipHeader"."CuryTotMerch", "SOShipHeader"."CuryTotMisc", "SOShipHeader"."CuryWholeOrdDisc", "SOShipHeader"."CuryTotFrtInvc", "SOShipHeader"."CuryPremFrtAmt", "CertificationText"."CertText00", "CertificationText"."CertText01", "CertificationText"."CertText02", "CertificationText"."CertText03", "FrtTerms"."Collect", "SOShipLine"."TaxAmt01", "SOShipLine"."TaxAmt00", "SOShipLine"."TaxAmt02", "SOShipLine"."TaxAmt03", "SOShipLine"."TaxID00", "SOShipHeader"."CuryTotTax", "Snote"."sNoteText", "Snote1"."sNoteText"

    FROM   ((((((((((((((("HipacApp"."dbo"."SOPrintQueue" "SOPrintQueue" INNER JOIN "TestApp"."dbo"."SOShipHeader" "SOShipHeader" ON ("SOPrintQueue"."CpnyID"="SOShipHeader"."CpnyID") AND ("SOPrintQueue"."ShipperID"="SOShipHeader"."ShipperID")) LEFT OUTER JOIN "TestApp"."dbo"."SOType" "SOType" ON ("SOShipHeader"."CpnyID"="SOType"."CpnyID") AND ("SOShipHeader"."SOTypeID"="SOType"."SOTypeID")) LEFT OUTER JOIN "TestApp"."dbo"."Terms" "Terms" ON "SOShipHeader"."TermsID"="Terms"."TermsId") LEFT OUTER JOIN

    "TestApp"."dbo"."SOShipLine" "SOShipLine" ON ("SOShipHeader"."CpnyID"="SOShipLine"."CpnyID") AND ("SOShipHeader"."ShipperID"="SOShipLine"."ShipperID")) LEFT OUTER JOIN "TestApp"."dbo"."Snote" "Snote" ON "SOShipHeader"."NoteID"="Snote"."nID") LEFT OUTER JOIN "TestApp"."dbo"."CertificationText" "CertificationText" ON "SOShipHeader"."CertID"="CertificationText"."CertID") LEFT OUTER

    JOIN "TestApp"."dbo"."SOShipMark" "SOShipMark" ON ("SOShipHeader"."CpnyID"="SOShipMark"."CpnyID") AND ("SOShipHeader"."ShipperID"="SOShipMark"."ShipperID")) LEFT OUTER JOIN "TestApp"."dbo"."Country" "Country" ON "SOShipHeader"."BillCountry"="Country"."CountryID") LEFT OUTER JOIN "TestApp"."dbo"."Country" "ShipCountry" ON "SOShipHeader"."ShipCountry"="ShipCountry"."CountryID") LEFT OUTER JOIN "TestApp"."dbo"."SOHeaderMark" "SOHeaderMark" ON ("SOShipHeader"."CpnyID"="SOHeaderMark"."CpnyID") AND ("SOShipHeader"."OrdNbr"="SOHeaderMark"."OrdNbr")) LEFT OUTER JOIN "TestApp"."dbo"."Site" "Site" ON "SOShipHeader"."SiteID"="Site"."SiteId") LEFT OUTER JOIN "TestApp"."dbo"."FrtTerms" "FrtTerms" ON "SOShipHeader"."FrtTermsID"="FrtTerms"."FrtTermsID") LEFT OUTER JOIN "TestApp"."dbo"."SOShipLot" "SOShipLot" ON (("SOShipLine"."CpnyID"="SOShipLot"."CpnyID") AND ("SOShipLine"."LineRef"="SOShipLot"."LineRef")) AND ("SOShipLine"."ShipperID"="SOShipLot"."ShipperID")) LEFT OUTER JOIN "TestApp"."dbo"."Snote" "Snote1" ON "SOShipLine"."NoteID"="Snote1"."nID") LEFT OUTER JOIN "TestApp"."dbo"."Inventory" "Inventory" ON "SOShipLine"."InvtID"="Inventory"."InvtID") LEFT OUTER JOIN "TestApp"."dbo"."SOLine" "SOLine" ON (("SOShipLine"."CpnyID"="SOLine"."CpnyID") AND ("SOShipLine"."OrdLineRef"="SOLine"."LineRef")) AND ("SOShipLine"."OrdNbr"="SOLine"."OrdNbr")

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Crystal Report 2008 not totaling accurately

    Lisa, it sounds like your where clause on the join is not robust enough.  It may have worked in the past due to what data existed but now there is additional data that has exposed the problem.  If you would like to post the query behind the report and include whatever Crystal filters are also applied I would be happy to take a look and offer a suggestion.  Without more information it is impossible to give you additional guidance.

  • Cynthia Audain Profile Picture
    Cynthia Audain 1,270 on at
    RE: Crystal Report 2008 not totaling accurately

    You are absolutely correct. I figured that out while executing the sql query in the report and the returned records revealed a duplication. But I have not yet figure out how to correct it.

    It was the same report used in the previous version and I never had this issue before.

    Any suggestion as to why it is occurring now and how to fix it?

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Crystal Report 2008 not totaling accurately

    Lisa, I extensively use Crystal Reports for my clients as well as for my Dynamic SL enhancements and have been using 2008 for quite some time now and I am confident the issue is not with Crystal Reports.  What you describe is most likely the result of a multi-table join where you are bringing in a support table and picking up 2 records from the support table for a main table record thereby causing your count and total to be inflated.  Take you Crystal Query and drop it into SQL so that you see all the fields from all the joined tables and I bet you will find your culprit.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans