# Seemingly Simple SSRS Sum is not so Simple **URL:** \ **Category:** Kinetic ERP\ **Created:** [July 11, 2022, 5:26pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312 "2022-07-11T17:26:59Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 5:26pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/1 "2022-07-11T17:26:59Z")
For an unknown reason, my SSRS sum expression is returning the incorrect value. In fact, the value is always 8 or 12 times (on the dot and seemingly random) of what it should be. I’m using the below formula to sum up the total value of my PackSlip report. `=Sum(Fields!OrderLinePrice.Value * Fields!Calc_DspPlannedQty.Value) ` ![image](https://www.epiusers.help/uploads/default/original/3X/f/2/f257b3fe5d4e9ad6705e9d6163fea0168964ec07.png) If I try it on each line, I get the same issue. `=Sum(Fields!OrderLinePrice.Value) ` ![image](https://www.epiusers.help/uploads/default/original/3X/6/e/6e34985ad1698ad6f012f48d807ba10095a77910.png) This is the table relationship in the Data Definition. ![image](https://www.epiusers.help/uploads/default/original/3X/1/4/14bec4767a1e3989795ac31a90b0ba920f1ff7ec.png) And this is the JOIN statement `LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T10 ON T2.Company = T10.Company AND T2.OrderNum = T10.OrderNum AND T2.OrderLine = T10.OrderLine ` For some sanity, I also checked what I think I should be getting with a BAQ which returns the correct output. ![image](https://www.epiusers.help/uploads/default/original/3X/9/e/9e7d0235e01c8135e270739b4c56ba2e060fe53d.png) If anyone has any idea where I’m going wrong, please let me know! Thanks, Eric ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [July 11, 2022, 5:49pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/2 "2022-07-11T17:49:18Z")
BAQs often return multiple rows with the same data that is then filtered down by the SSRS. Is it possible that your BAQ results are returning multiple rows that are getting hidden in your BAQ view, but are not ignored in your SSRS? Look around using your example, are there 8 or 12 releases for that order? If you can find out what is causing the 8-12x value, then you can figure out what to fix. It may be a grouping in your SSRS, or a formula hiding rows in your SSRS. ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 6:01pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/3 "2022-07-11T18:01:31Z")
> [@NateS](#): > > BAQs often return multiple rows with the same data that is then filtered down by the SSRS. This is great information. Thanks for sharing! Sadly, there is only 1 release per line. I’ve also tried playing around with the groupings in SSRS without luck. I’ll look into ways to filter the data coming into the SSRS report. Thanks! ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [July 11, 2022, 6:05pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/4 "2022-07-11T18:05:31Z")
Post up your SSRS if you can’t figure it out. I bet we can get it working. Things like this happen all the time in SSRS reports. ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 6:16pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/5 "2022-07-11T18:16:27Z")
Here’s the SSRS query: > =“SELECT T1.Company,T1.LegalNumber,T1.PackNum,CAST( T1.CustNum as nvarchar ) as CustNum,T1.ShipComment,T1.ShipDate,T1.Calc\_BilContct,T1.Calc\_BillToAddress,T1.Calc\_SoldToAddress,T1.Calc\_Contct,T1.Calc\_CustPartOpts,T1.Calc\_FOBDescription,T1.Calc\_LegalNum,T1.Calc\_NumRecordPerPage,T1.Calc\_NumTotalParts,T1.Calc\_SalesRepName,T1.Calc\_ShipToAddress,T1.Calc\_ShipViaDescription,T1.Calc\_stPhone,T1.Calc\_CusPhone,T1.Calc\_stFax,T1.Calc\_EMailAddress,T1.Calc\_FaxNum,T1.Calc\_SEmailAddr,T1.Calc\_CarrierDesc,T1.Calc\_ShipViaSCAC,T1.Calc\_CarrierSCAC,T1.Calc\_CompanyEORINumber,T1.Calc\_CustShipToEORINumber, T1.Pallet1Dims\_c, T1.Pallet2Dims\_c, T1.Pallet3Dims\_c, T1.Pallet4Dims\_c, T1.Pallet5Dims\_c, T1.Pallet6Dims\_c, T1.Pallet7Dims\_c, T1.Pallet8Dims\_c,T2.PackLine,T2.Discount,T2.ExtPrice,T2.HeaderShipComment,T2.LineDesc,T2.OrderLine,T2.OrderNum,T2.OrderRelNum,T2.PartNum,T2.RevisionNum,T2.ShipComment as ShipDtl\_ShipComment,T2.UnitPrice,T2.XPartNum,T2.XRevisionNum,T2.Calc\_DspBackOrdQty,T2.Calc\_DspBackOrdQtyUom,T2.Calc\_DspLabDur,T2.Calc\_DspLabMod,T2.Calc\_DspLineDesc,T2.Calc\_DspLineShpQty,T2.Calc\_DspLineShpQtyUom,T2.Calc\_DspMatDur,T2.Calc\_DspMatMod,T2.Calc\_DspMiscDur,T2.Calc\_DspMiscMod,T2.Calc\_DspPlannedQty,T2.Calc\_DspPlannedQtyUom,T2.Calc\_DspSerialNumber,T2.Calc\_DspShipRouting,T2.Calc\_DspSubShipTo,T2.Calc\_GetNextLegalNum,T2.Calc\_LegalText,T2.Calc\_LinChangd,T2.Calc\_NextLegalNumID,T2.Calc\_NumLineByInv,T2.Calc\_OrdRelRef,T2.Calc\_POLine,T2.Calc\_SalesRepName as ShipDtl\_Calc\_SalesRepName,T2.Calc\_SerialNumLabl,T2.Calc\_TotalLineCost,T2.Calc\_TotalTax,T2.Calc\_HidePackLine,T2.OrderLine\_KitFlag,T2.OrderLine\_KitParentLine,T2.OrderLine\_KitPrintCompsPS,T2.OrderLine\_DisplaySeq,T2.OrderLine\_KitShipComplete,T2.Calc\_MarkForAddress,T2.OrderNum\_PONum,T2.PartNum\_PartDescription,T2.PCID,T2.Calc\_CommodityCode,T2.Calc\_AttributeSetShortDesc, > T3.RptLiteralsLblDescription,T3.RptLiteralsLblFax, T3.RptLiteralsLblEmail,T3.RptLiteralsLblRel,T3.RptLiteralsLblPh,T3.RptLiteralsLblPO,T3.RptLiteralsLblRev,T3.RptLiteralsHComponents,T3.RptLiteralsLblBckOrQty,T3.RptLiteralsLblCustPart,T3.RptLiteralsLblDiscAmt,T3.RptLiteralsLblExtendedPrice,T3.RptLiteralsLblFOB,T3.RptLiteralsLblHdngPckSlip,T3.RptLiteralsLblLbDu,T3.RptLiteralsLblLine,T3.RptLiteralsLblMiDu,T3.RptLiteralsLblMtDu,T3.RptLiteralsLblOurPrt,T3.RptLiteralsLblPackSlip,T3.RptLiteralsLblPage,T3.RptLiteralsLblPlndQty,T3.RptLiteralsLblPoLine,T3.RptLiteralsLblPrtNum,T3.RptLiteralsLblReference,T3.RptLiteralsLblSalesOrder,T3.RptLiteralsLblSalesperson,T3.RptLiteralsLblShipDt,T3.RptLiteralsLblShipRouting,T3.RptLiteralsLblShipTo,T3.RptLiteralsLblShipVia,T3.RptLiteralsLblShpdQty,T3.RptLiteralsLblSoldTo,T3.RptLiteralsLblSrlNum,T3.RptLiteralsLblSrlNums,T3.RptLiteralsLblSubShipTo,T3.RptLiteralsLblTotalLineCost,T3.RptLiteralsLblTotalTax,T3.RptLiteralsLblUnitPrice,T3.RptLiteralsLblYourPO,T3.RptLiteralsLLegalText,T3.RptLiteralsLLegNum,T3.RptLiteralsLSalesKit,T3.RptLiteralsLCarrierSCAC,T3.RptLiteralsLCarrier,T3.RptLiteralsLblShipViaSCAC,T3.RptLiteralslblInvNumber,T3.RptLiteralslblInvDueDate,T3.RptLiteralsLblOf, T3.RptLiteralsLblContinueOnNext, T3.RptLiteralsLblComeFrom, T3.RptLiteralsLblPCID, T3.RptLiteralsLCommodityCode, T3.RptLiteralsLEORINumber, T3.RptLiteralsLblAttributeSet, T6.ResaleID, T1.Weight, T1.PkgLength, T1.PkgWidth, T1.PkgHeight, T1.PkgSizeUOM, T1.WeightUOM, T7.Name, T7.Address1, T7.Address2, T7.Address3, T7.City, T7.State, T7.Zip, T7.ManagerName, T7.PhoneNum, T9.Description as CommodityCodeDescription, T10.UnitPrice as OrderLinePrice, T10.Rpt1ListPrice > FROM ShipHead\_” + Parameters!TableGuid.Value + " T1 > LEFT OUTER JOIN ShipDtl\_" + Parameters!TableGuid.Value + " T2 > ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum > LEFT OUTER JOIN RptLabels\_" + Parameters!TableGuid.Value + " T3 > ON T1.RptLanguageID = T3.RptLanguageID > LEFT OUTER JOIN CustomerShipTo\_" + Parameters!TableGuid.Value + " T6 > ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum > LEFT OUTER JOIN Plant\_" + Parameters!TableGuid.Value + " T7 > ON T1.Company = T7.Company AND T1.Plant = T7.Plant > LEFT OUTER JOIN Part\_" + Parameters!TableGuid.Value + " T8 > ON T2.Company = T8.Company AND T2.PartNum = T8.PartNum > LEFT OUTER JOIN ICommCode\_" + Parameters!TableGuid.Value + " T9 > ON T8.Company = T9.Company AND T8.CommodityCode = T9.CommodityCode > LEFT OUTER JOIN OrderDtl\_" + Parameters!TableGuid.Value + " T10 > ON T2.Company = T10.Company AND T2.OrderNum = T10.OrderNum AND T2.OrderLine = T10.OrderLine" And here’s the .rdl [PackSlip.rdl](https://www.epiusers.help/uploads/short-url/9mOzYXtDBIjFMujFqGa7DA1AuPg.rdl) (382.1 KB) If you can point me in any direction it would be greatly appreciated. I am currently hitting a brick wall 😃 ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [July 11, 2022, 6:33pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/6 "2022-07-11T18:33:08Z")
I couldn’t open your file. Seems there is a version mismatch. I would keep looking at the groupings. Are there multiple orders/lines/releases in your packing slip? If you haven’t made any other changes from the base rdl, then I would suggest going back to the base packing slip and try to create your subtotals again. Sometimes if you edit the same file over and over some changes can get buried and forgotten about, causing unforeseen issues in your final report. Sorry I couldn’t open the file on my side. Good luck! Nate ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 6:38pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/7 "2022-07-11T18:38:20Z")
Thanks for the help! I’ll keep this thread updated when I (hopefully) find the solution. ---
**Author:** ![CSmith](https://www.epiusers.help/user_avatar/www.epiusers.help/csmith/32/50752_2.png) [@CSmith](https://www.epiusers.help/u/CSmith)\ **Post date:** [July 11, 2022, 8:19pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/8 "2022-07-11T20:19:19Z")
Did you set a scope limit on the sum? ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 9:04pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/9 "2022-07-11T21:04:59Z")
I had an issue doing this due to report styles throwing an error every time I tried to upload it. I believe it was due to the expression attempting to sum an aggregate. ---
**Author:** ![DaveOlender](https://www.epiusers.help/user_avatar/www.epiusers.help/daveolender/32/260_2.png) [@DaveOlender](https://www.epiusers.help/u/DaveOlender)\ **Post date:** [July 11, 2022, 9:29pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/10 "2022-07-11T21:29:21Z")
Could there be 8 commodity Codes? Instead of adding the table i would try using the built in “LInked Tables” in the RDD. You then just have to add a field in the select query that would look like T8.CommodityCode\_Description. I suppose another strategy would be to code in a subquery for the CommodityCode Table in the select that would use a Min or Max to make sure you are only getting one record. DaveO ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 11, 2022, 9:43pm UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/11 "2022-07-11T21:43:46Z")
Only using two comm codes… But I’ll give the linked tables and Max a try. Thanks! ---
**Author:** ![oxygen8](https://www.epiusers.help/user_avatar/www.epiusers.help/oxygen8/32/47930_2.png) [@oxygen8](https://www.epiusers.help/u/oxygen8)\ **Post date:** [July 12, 2022, 12:27am UTC](https://www.epiusers.help/t/seemingly-simple-ssrs-sum-is-not-so-simple/92312/12 "2022-07-12T00:27:37Z")
Alright I think I’ve found a solution - thank you to everyone for the help! What a great community 🙂 Turns out that you can get rid of the multiple data rows that are hiding in the BAQ view, but are selected by the SSRS query (kudos to @NateS for the info), by adding a `DISTINCT` after the `SELECT` statement. See below for the beginning of my SQL statement. `="SELECT DISTINCT T1.Company,T1.LegalNumber,T1.PackNum ...` This was the only code I needed to essentially filter out the additional/duplicate lines that were throwing my `SUM` off. Thanks again to everyone for the helpful tips and info!