Report Data Definition Error when Adding a Relationship and New Table

Kinetic 202X
, ,
1

Hello, I am in the process of updating a data definition we use to generate our BOLs by adding a new table.

I am able to add the ne table alright and get the fields to appear in our ssrs report without issues.

However the query does not appear to be actually populating the newly pulled table with results.

When I added a relationship for the new table and set the output type to definition only the report runs fine but does not populate the new table with results,

When I set the relationship type to Output I get an error in the system monitor saying the following:The dataset ‘RptParameter’ contains a definition for the Field ‘DecimalGeneral’.

Has anyone experienced this before and come up with a solution of how to resolve it?

2

This error can indicate there is an issue with your join.

Please share your TSQL syntax and a screenshot of your join within the RDD

Thank you

PS… Remember to wrap any code in three backticks.

3

Thanks for the response. Below is how the query looks copied from the ssrs report.

SELECT (Select IsNull(Sum(T5.OurJobShipQty),0) From ShipDtl_" + Parameters!TableGuid.Value + " T5 Where T5.Company=T3.Company AND T5.PackNum = T3.PackNum) OurJobShipQty, T3.PackNum as SH_PkgNum, T4.JobNum,T4.InventoryShipUOM, T3.PkgSizeUOM, T3.Weight AS SH_Weight, T3.WeightUOM AS SH_WghtUOM
, T3.PkgQty_c, T3.PackNum as SH_PackNum, T3.Calc_CarrierDesc, T3.Calc_PONum, T3.PkgHeight, T3.PkgLength, T3.PkgWidth, T1.DisplayPalletDims_c, T1.BHNPrint_c, T1.ThirdPartyPrint_c,T1.OuterDimPrint_c,T1.PackageDimPrint_c,T1.PartNumPrint_c,T1.NoDockPrint_c,T1.HeatNumPrint_c,T1.CustNum,T1.CustCntName_c,T1.FreightBillPrint_c, T1.FreightChargeType_c, T1.EntryPerson, T2.PkgClass, T2.PkgCode, CAST(T1.BOLNum AS NVARCHAR) AS BOLNum
	,T1.Carrier
	,T1.CODAmount
	,T1.Company
	,T1.CommentText
	,T1.FreightCharges
	,T1.ShipDate
	,T1.Plant
	,T1.Calc_CustomerAddrList
	,T1.Calc_PlantAddrList
	,T1.Calc_ShipToAddrList
	,T1.Plant_Name
	,T1.ToPlant_Name
	,CAST(T2.BOLLine AS NVARCHAR) AS BOLLine
	,CAST(T2.BOLNum AS NVARCHAR) AS BOLDetail_BOLNum
	,T2.Company AS BOLDetail_Company
	,CAST(T2.Packages AS NVARCHAR) AS Packages
	,T2.Rate
	,T2.Weight
	,T2.WeightUOM
	,T2.Calc_ClassDesc
	,T2.Calc_PkgDesc
	,T2.Calc_WeightDflt
	,T2.Calc_WeightUomDflt
	,T5.ShipHold_c
 FROM BOLHead_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN BOLDetail_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.BOLNum = T2.BOLNum 
  LEFT OUTER JOIN ShipHead_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.BOLNum = T3.BOLNum and T2.BOLLine = T3.BOLLine 
  LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company AND T3.PackNum = T4.PackNum AND T4.PackLine  =  1
  LEFT OUtER JOIN JobHead_" + Parameters!TableGuid.Value + " T5 ON T4.Jobnum=T5.Jobnum
4

What table did you add?

6

Use Linked Tables.

JobNum is available. You should be able to select your column, no join needed.

image

Picked

image
image755×454 37.8 KB

image
image723×508 36.6 KB

Your syntax would be under T2.Calc_WeightUomDflt

T2.JobNum_MyColumnName

7

Thanks for the response.

I was able to get data output with that combination but still run into an error when trying to view the ssrs report (seen below). Honestly I’m at a loss because that part of the report is not being touched. At least I am moving in the right direction. Thanks for the help with the previous issue…

image