I am using the ISCountryOrigin field to pull the value of the part country of origin on my invoice ssrs form. For some reason it is giving me the numeric value instead of the description. How can I get the country name and not the id?
You should be able to pull in the Country table to the RDD and then setup a relationship between the tables. Then youâd need to add the Description field from the Country table to your RDD and report.
You might also look around in the RDD because often times thereâs a value already stored in the dataset for you. Iâm not aware off the top of my head, though, if itâs in there.
I added the field Country_Description to my dataset and the query is updated as such:
=âSELECT DISTINCT T1.DocInvoiceBal,T1.RptLanguageID,T1.Company,T1.CreditMemo,T1.Calc_ExtPriceTotal,T1.CustNum,T1.DocDepositCredit,T1.DocInvoiceAmt,T1.Calc_TotalMiscChrg,T1.DocRounding,T1.DocumentPrinted,T1.Posted,T1.InvoiceComment,T1.InvoiceDate,T1.InvoiceNum,T1.InvoiceType,T1.LegalNumber,T1.PONum,T1.SoldToInvoiceAddress,T1.Calc_BillToAddressList,T1.Calc_BottomAddress,T1.Calc_CompanyAddressList,T1.Calc_CurrDocDesc,T1.Calc_CurSymbol,T1.Calc_CustContactName,T1.Calc_CustPartOpts,T1.Calc_DteOrdrd,T1.Calc_fFOB,T1.Calc_MulPackNum,T1.Calc_mulponum,T1.Calc_MulShipDate,T1.Calc_MulShipTo,T1.Calc_MulShipVia,T1.Calc_MultSoldTo,T1.Calc_MultTaxID,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_PackNum,T1.Calc_PrintBottomAddress,T1.Calc_SalesPerson,T1.Calc_SalesTerms,T1.Calc_ShipDate,T1.Calc_ShipToAddressList,T1.Calc_ShipToContactName,T1.Calc_ShipVia,T1.[Calc_Voucher-String] as Calc_Voucher_String,T1.CurrencyCode_CurrencyID,T1.CurrencyCode_DecimalsGeneral,T1.CurrencyCode_DecimalsPrice,T1.Calc_SEBankRef,T1.Calc_CustResaleID,T1.BranchID,T1.CustAgentTaxRegNo,T1.CHISRCodeLine,T1.Calc_CHBankAcctIBANCode,T1.Calc_CHBankAcctISRPartyID, T1.InvoiceRef, T1.CMReason,T1.Calc_DocDspTaxAmt, T1.RevisionNum AS InvcHead_RevisionNum, T1.RevisionDate AS InvcHead_RevisionDate, T1.Calc_IsDraftCopy, T1.FirstPrintDate, T1.Calc_ReverseChargeTax as Calc_ReverseChargeTaxTotal, T1.DocCopyNum, T1.PayDiscDays, T2.AdvanceBillCredit,T2.Company as InvcDtl_Company,T2.DocAdvanceBillCredit,T2.DocDiscount,T2.DocUnitPrice,T2.InvoiceComment as InvcDtl_InvoiceComment,T2.InvoiceLine,T2.InvoiceNum as InvcDtl_InvoiceNum,T2.PackLine,T2.PackNum,T2.DropShipPackSlip,T2.PartNum,T2.POLine,T2.PricePerCode,T2.RevisionNum,T2.SalesUM,T2.SellingOrderQty,T2.SellingShipQty,T2.ShipDate,T2.XPartNum,T2.XRevisionNum,T2.Calc_ActDate,T2.Calc_BackOrdQty,T2.Calc_UnitPrice,T2.Calc_ExtPrice,T2.Calc_Duration,T2.Calc_GetNextLegalNum,T2.Calc_InvcComment,T2.Calc_InvoiceDisplayLine,T2.Calc_IsKitParent,T2.Calc_DspDocLineTax,T2.Calc_JobNumber,T2.Calc_LabDur,T2.Calc_LabMod,T2.Calc_Labor,T2.Calc_LineDesc,T2.Calc_LineSoldToAddressList,T2.Calc_MatDur,T2.Calc_Mate,T2.Calc_MatMod,T2.Calc_Misc,T2.Calc_MiscDur,T2.Calc_MiscMod,T2.Calc_Modifier,T2.Calc_NextLegalNumID,T2.Calc_NumLineByInv,T2.Calc_ponum,T2.Calc_PSLegalNum,T2.Calc_Reference,T2.Calc_SerialNumber,T2.Calc_SerialNumber2,T2.Calc_SerialNumber3,T2.Calc_SerialNumber4,T2.Calc_SerialNumber5,T2.Calc_SerialNumber6,T2.Calc_SerialNumber7,T2.Calc_ShipToAddressList as InvcDtl_Calc_ShipToAddressList,T2.Calc_ShipToContactName as InvcDtl_Calc_ShipToContactName,T2.Calc_ShipToShipVia,T2.Calc_StateTaxID,T2.Calc_WarrDesc,T2.Calc_WhseCode,T2.OrderLine_DisplaySeq,T2.OrderLine_KitFlag,ISNULL(T2.OrderLine_KitPricing,ââ) AS OrderLine_KitPricing,ISNULL(T2.OrderLine_KitPrintCompsInv,0)AS OrderLine_KitPrintCompsInv,T2.OrderLine_KitsLoaded,T2.OrderLine_OrderLine,T2.PartNum_PartDescription, T2.EpicorFSA, T3.CallComment,T3.CallQty,T3.PartNum as FSCallDt_PartNum,T3.RevisionNum as FSCallDt_RevisionNum,T3.XPartNum as FSCallDt_XPartNum,T3.XRevisionNum as FSCallDt_XRevisionNum,T3.Calc_CProb,T3.Calc_fCallLine,T3.Calc_fCallNum,T3.Calc_InvoiceLine,T3.Calc_InvoiceNum, T4.Calc_InvoiceNum as FSCallMt_Calc_InvoiceNum,T4.Calc_MatNum,T4.Calc_MtPaNum,T4.Calc_MtQty,T4.Calc_MtBillPrice,T4.Calc_MtExtPrice,T4.Calc_MtLinedesc,T4.Calc_MtRevNum,T4.ResReasonCode, T4.Calc_MtCount,T5.RptLanguageID as Label_RptLanguageID,T5.Calc_ActDate as Label_Calc_ActDate,T5.Calc_CreditMemo as Label_Calc_CreditMemo,T5.Calc_Duration as Label_Calc_Duration,T5.Calc_JobNumber as Label_Calc_JobNumber,T5.Calc_LabDur as Label_Calc_LabDur,T5.Calc_Labor as Label_Calc_Labor,T5.Calc_MatDur as Label_Calc_MatDur,T5.RptLiteralsLMaterial as Label_Calc_Mate,T5.Calc_Misc as Label_Calc_Misc,T5.Calc_MiscDur as Label_Calc_MiscDur,T5.Calc_MtExtPrice as Label_Calc_MtExtPrice,T5.Calc_SerialNumber as Label_Calc_SerialNumber,T5.DepositCredit as Label_DepositCredit,T5.DocUnitPrice as Label_DocUnitPrice,T5.InvoiceLine as Label_InvoiceLine,T5.PONum as Label_PONum,T5.ProbReasonCode as Label_ProbReasonCode,T5.Reference as Label_Reference,T5.RptLiteralsLBckOrd,T5.RptLiteralsLBillTo ,T5.RptLiteralsLTWTotalTax ,T5.RptLiteralsLComeFrom ,T5.RptLiteralsLContinueInNext ,T5.RptLiteralsLCusPart , T5.RptLiteralsLDate ,T5.RptLiteralsLEMaila ,T5.RptLiteralsLExtPrice,T5.RptLiteralsLTax ,T5.RptLiteralsLFax ,T5.RptLiteralsLFOB ,T5.RptLiteralsLHdng ,T5.RptLiteralsLInvoice ,T5.RptLiteralsLLegNum ,T5.RptLiteralsLLine ,T5.RptLiteralsLLineRef ,T5.RptLiteralsLof ,T5.RptLiteralsLOurPart ,T5.RptLiteralsLPackSlp ,T5.RptLiteralsLPage ,T5.RptLiteralsLPartDesc ,T5.RptLiteralsLPartRev ,T5.RptLiteralsLPhone ,T5.RptLiteralsLPONum ,T5.RptLiteralsLAULessDeposit ,T5.RptLiteralsLQty ,T5.RptLiteralsLQtyOrd ,T5.RptLiteralsLRev ,T5.RptLiteralsLLinesSubtotal ,T5.RptLiteralsLRounding ,T5.RptLiteralsLMiscChgs, T5.RptLiteralsLSalesKit ,T5.RptLiteralsLSeeBelow ,T5.RptLiteralsLShipTo ,T5.RptLiteralsLShpVia ,T5.RptLiteralsLSlsTxID ,T5.RptLiteralsLSoldToL ,T5.RptLiteralsLTotal ,T5.RptLiteralsLWarrantyA ,T5.RptLiteralsLWHCode ,T5.ShipDate as Label_ShipDate,T5.UnitPrice as Label_UnitPrice,T5.RptLiteralsLSEOCR ,T5.RptLiteralsLNOKID ,T5.RptLiteralsLDiscountPercent ,T5.RptLiteralsLVatNr ,T5.RptLiteralsLlessAdvBill, T5.RptLiteralsLFSAEquipmentPartNum, T5.RptLiteralsLFSAServiceOrderNum, T1.Calc_CustFax, T6.EMailAddress, T6.FaxNum, T6.ResaleID, T6.CustID, T5.RptLiteralsLOrdered as Label_Calc_DteOrdrd,T5.RptLiteralsLSalsRep as Label_Calc_SalesPerson,T5.RptLiteralsLTrms as Label_Calc_SalesTerms, T1.Calc_CustEMailAddress, T1.Calc_DropShipPackSlip, T7.InvoiceComment as FSContDt_InvoiceComment ,T7.ContractQty,T7.PricePerUnit,T7.DocPricePerUnit,T7.Calc_ContPrice, T5.PricePerCode as Label_PricePerCode, T5.PartNum as Label_PartNum,T5.ExtPrice as Label_ExtPrice, T1.Calc_PELegalText, T1.Calc_DocInvoiceAmt, T5.RptLiteralsLAmt, T5.Calc_SEBankRef as Label_Calc_SEBankRef, T1.Calc_TaxMethod, T1.SEBankRef, T1.Calc_LessPrepaidDeposits, T5.RptLiteralsLLessPrepDeposits, T5.Calc_ReverseChargeTax as RptLiteralsLReverseChargeTax, T7.ContractLine, T7.PartNum as ContractPartNum, T7.PartNum_PartDescription as ContractPartDescription, T7.ContractNum as ContractNum ,(CASE WHEN EXISTS(SELECT 1 FROM InvcChrg_â + Parameters!TableGuid.Value + " Tmp WHERE Tmp.Company = T1.Company AND Tmp.InvoiceNum= T1.InvoiceNum AND Tmp.InvoiceLine = T2.InvoiceLine) THEN 1 ELSE 0 END) AS âHasFinChargesâ, T5.RptLiteralsLMiscChgs, T5.[Description] AS Label_Description,0 AS DisplayLineInfo, T5.RptLiteralsLCopy, T5.RptLiteralsLPrintedAt, T5.RptLiteralsLFirstPrinted, T5.RptLiteralsLDraftCopy, T5.RptLiteralsLInternalUse, T10.FSAEquipmentPartNum, T10.FSAServiceOrderNum,T5.Calc_PLWasteRegisterNum as Label_Calc_PLWasteRegisterNum, T12.ConvFactor, T12.ConvOperator, T12.UOMCode,IIF(T2.SalesUM = âYDâ AND ISNULL(T12.UOMCode, ââ) <> ââ, (CASE T12.ConvOperator WHEN â/â THEN T2.SellingShipQty * T12.ConvFactor WHEN ââ THEN T2.SellingShipQty / IIF(T12.ConvFactor = 0, 1, T12.ConvFactor) END), 0) AS Calculated_Meters, CASE T14.ConvOperator WHEN â/â THEN T2.SellingShipQty * T14.ConvFactor WHEN 'â THEN T2.SellingShipQty / IIF(T14.ConvFactor = 0, 1, T14.ConvFactor) ELSE 0 END AS Calculated_Rolls , T13.Plant, T13.Calc_AddressList AS PlantAddressList, T1.OrderNum AS InvcHead_OrderNum, T11.NetWeight, T11.NetWeightUOM, T11.ISOrigCountry, T11.CommercialStyle, T15.TrackingNumber, T15.ShipToNum, T15.CustomPackNum_c, T16.Description AS Country_Description, T17.XPartNum AS CustXPrt_XPartNum
FROM InvcHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN InvcDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
LEFT OUTER JOIN FSCallDt_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.InvoiceLine = T3.Calc_InvoiceLine AND T2.InvoiceNum = T3.Calc_InvoiceNum
LEFT OUTER JOIN FSCallMt_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.CallNum = T4.CallNum AND T3.Calc_fCallLine = T4.Calc_fCallLine AND T3.Calc_InvoiceLine = T4.Calc_InvoiceLine AND T3.Calc_InvoiceNum = T4.Calc_InvoiceNum
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5
ON T1.RptLanguageID = T5.RptLanguageID LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum AND T1.InvoiceNum = T6.Calc_InvoiceNum
LEFT OUTER JOIN FSContDt_" + Parameters!TableGuid.Value + " T7
ON T2.Company = T7.Company AND T2.InvoiceLine = T7.Calc_InvoiceLine AND T2.InvoiceNum = T7.Calc_InvoiceNum
LEFT OUTER JOIN FSAExtData_" + Parameters!TableGuid.Value + " T10
ON T2.Company = T10.Company AND T2.SysRowID = T10.ForeignSysRowID
LEFT OUTER JOIN ( SELECT temp.Company, temp.InvoiceNum, temp.InvoiceLine, MIN(temp.SeqNum) as MinSeqNum
FROM InvcMisc_" + Parameters!TableGuid.Value + " temp
GROUP BY temp.Company, temp.InvoiceNum, temp.InvoiceLine) T9
ON T1.Company = T9.Company
AND T1.InvoiceNum = T9.InvoiceNum
AND T2.InvoiceLine = T9.InvoiceLine
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T11
ON T2.Company = T11.Company
AND T2.PartNum = T11.PartNum
LEFT OUTER JOIN UOMConv_" + Parameters!TableGuid.Value + " T12
ON T11.Company = T12.Company
AND T11.UOMClassID = T12.UOMClassID
AND T12.UOMCode = âMâ
LEFT OUTER JOIN Plant_" + Parameters!TableGuid.Value + " T13
ON T1.Company = T13.Company
AND T1.Plant = T13.Plant
LEFT OUTER JOIN PartUOM_" + Parameters!TableGuid.Value + " T14
ON T11.Company = T14.Company
AND T11.PartNum = T14.PartNum AND T14.UOMCode = âRollâ
LEFT OUTER JOIN ShipHead_" + Parameters!TableGuid.Value + " T15
ON T1.Company = T15.Company
AND T1.Calc_PackNum = T15.PackNum /AND T14.UOMCode = âRollâ/ LEFT JOIN Country_" + Parameters!TableGuid.Value + " T16 ON T11.Company = T16.Company AND T11.ISOrigCountry = T16.CountryNum
LEFT JOIN CustXPrt_" + Parameters!TableGuid.Value + " T17 ON T2.Company = T17.Company AND T2.CustNum = T17.CustNum AND T2.PartNum = T17.PartNum
ORDER BY T1.InvoiceNum, T2.Calc_NextLegalNumID, T2.InvoiceLine "Does this look right? If so, how would I use that field in my expression? I tried just calling that value, but it comes back as empty
Iâm not familiar with Intrastat. Iâve noticed that in the One Time Ship (OTS) Country itâs stored as the CountryNum. And I had to link up to the Country table on the CountryNum field to get the Country name (Description). As far as the ISOrigCountry, Iâm not sure what that is or what itâs tied to. But youâre setting it up correctly from what I can tell. you said LEFT JOIN and I think that should be LEFT OUTER JOIN.
What part of the query are you referring to that should be LEFT OUTER JOIN and not LEFT JOIN?
Is there a way in the expression to say something similar to: represent the ID field as the description so it replaces it?
Unfortunately that did not change anything
Should I still use the ISOrigCountry field or would adding the OUTER mean I need to use the new Country_Description field?
I guess LEFT JOIN should work just the same as LEFT OUTER JOIN. I didnât realize that. I just noticed it was different than the other joins in that query. So my apologies for that.
As I mentioned earlier, I donât know what ISOrigCountry is referring to. Whatever youâre trying to do in the RDD, you should be able to do the exact same query in BAQ Designer to confirm that youâre seeing values youâd expect to see. Do the country nums agree with the numeric value youâre getting for ISOrigCountry? If so, I think you just need to change the field from ISOrigCountry to CountryNum on the relationship in the RDD. If that RDD relationship is not setup properly, it doesnât matter what you put for your query in the report.
1 Like
Seems like I just needed ISOrigCountry to CountryNum and not ISOrigCountry to ISOrigCountry. Thanks for the help
Hi.
Iâm just on it and found out something. It seems those two columns have different data types.
ISOrigCountry is nvarchar(6) and CountryNum is int.
It will work in SQL as long as you will have numeric values in ISOrigCountry but if you want to use it in EDI then it wonât work. I will simply not let you generate EDI definition in for the Report Style
So the relation like this will not work
1 Like
Interesting! Good find. I was recently trying to query the Country of Origin and I found that PartCOO is a different table altogether where Country of Origin is defined on the part. Perhaps it might be better to query from there (where it is CountryNum to CountryNum) and set a criteria on the PartCOO table so that Primary = 1 (true). Or, if you need ISOrigCountry (I donât know what itâs used for), you will need to fill that field in on the Country table and then you could join to that column in the Country table.