Seemingly Simple SSRS Sum is not so Simple

Kinetic ERP
1

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
image816×437 21.3 KB

If I try it on each line, I get the same issue.

=Sum(Fields!OrderLinePrice.Value)

image
image816×441 21.2 KB

This is the table relationship in the Data Definition.

image
image980×722 31.8 KB

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

If anyone has any idea where I’m going wrong, please let me know!

Thanks,
Eric

2

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.

3

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!

4

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.

1 Like
5

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 (382.1 KB)

If you can point me in any direction it would be greatly appreciated. I am currently hitting a brick wall :smiley:

6

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

1 Like
7

Thanks for the help! I’ll keep this thread updated when I (hopefully) find the solution.

8

Did you set a scope limit on the sum?

1 Like
9

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.

10

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

1 Like
11

Only using two comm codes…

But I’ll give the linked tables and Max a try. Thanks!

12

Alright I think I’ve found a solution - thank you to everyone for the help! What a great community :slight_smile:

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!

2 Likes