Report Data - Not pulling through

[Samcox]

HI all, apologies for the long post this may be. I have amended the OrderAck report to include the customer table (This was included previously and is working) and CustIC table as i need to have the “ICCode” & “ICTypeID” in the report.

So i have the below within the report but it doesn’t bring any information through from the CustIC table. i have put CustIC bits in bold, can anyone see anything wrong with what i’ve done, that seems to be the only table i can’t get any information out of.

="SELECT T1.RptLanguageID,T1.CustNum,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal,T1.Customer_CustID,T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.DiscountPercent,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc, T2.Calc_FSAInstallationDesc, T2.Calc_FSAInstallationType, T2.Calc_FSAInstallPrice,T2.Calc_LineTaxAmt,T2.Calc_FSAInstallationRequired, T3.NeedByDate AS OrderRel_NeedByDate,T1.DiscountPercent,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter, T3. Calc_AttributeSetShortDesc, T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow, T1.Calc_TaxMethod, T4.RptLiteralsLTotalTax, T1.DocTotalTax, T1.DocOrderAmt, T1.Calc_TotalTaxAmt, T4.RptLiteralsLInstallation, T4.RptLiteralsLInstallTotal, T4.RptLiteralsLAttributeSetShortD, T5.Company, T5.CustNum, T5.EORINumber, **T6.Company, T6.CustNum, T6.ICCode, T6.ICTypeID**
 FROM OrderHed_" + Parameters!TableGuid.Value  + "  T1
 LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + "  T2 
	ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum 
 LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value  + " T3 
	ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value   + "  T4  
	ON T1.RptLanguageID = T4.RptLanguageID
 LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value   + "  T5  
    ON T1.Company = T5.Company and T1.CustNum = T5.CustNum
 **LEFT OUTER JOIN CustIC_" + Parameters!TableGuid.Value   + "  T6 
 ON T5.Company = T6.Company and T5.CustNum = T6.CustNum"**

[dcamlin (David Camlin)]

Change this to Output:

You need data from the CustIC table… so you need this relationship to “output” data.

[Samcox]

Thanks David, but no luck with this

[Dragos (Dragos Ene)]

A couple of things:

• you already have T2.Company and T1.CustNum in your SQL query, you need to rename the T6 ones to avoid any issues. You can use ‘T6.Company as Company2’ or something similar
• did you add the fields in the SSRS field list after you updated the query? In dataset properties, go to Fields section and press Add

[Samcox]

@Dragos

you already have T2.Company and T1.CustNum in your SQL query, you need to rename the T6 ones to avoid any issues. You can use ‘T6.Company as Company2’ or something similar

Thank you for this i have changed the T6 part to the below
T6.Company as Company2, T6.CustNum As CustNum2, T6.ICCode, T6.ICTypeID
Yeah i have them in the Fields, still nothing

[Samcox]

I have also tested it by removeing the company&CustNum from T6. all let the report run but it doesnt bring back the T6 info

[jkane (John Kane)]

Never select a Key when creating a join. I have no idea why, I just know it has never worked when I select one.

[Samcox]

Still nothing

thank you tho

[jkane (John Kane)]

I’ve never worked with the CustIC table, but I see that ShipToNum is in it. Do you need to include that in the join?

[Samcox]

Errrm i dont think so as there would be nothing to link it to, as its just a sales order?

When i pull the all the fields into a BAQ theres nothing there for the CustIC table

[jkane (John Kane)]

There actually is something there, that is the auto created Ship To that Epicor does. It is an empty field when they auto create it, but it is still a key field.

[Samcox]

T6.Company as Company2, T6.CustNum As CustNum2, T6.ICCode, T6.ICTypeID, T6.ShipToNum

Added it in as a field and into the query removed it from the exclusions, still nothing

The report runs which is more annoying, just none of the T6 fields come back with info in them

[jkane (John Kane)]

Did you add it in as a join on the RDD and RDL? ShipToID = ShipToID

[Samcox]

The ShipToNum is only on the CustIC table so not sure thats possible?

[jkane (John Kane)]

Then join it to OrderHed (or OrderRel if you change the ship to at the release level).