Add new field from another table in Epicor 10.1.500

Epicor ERP 10
1

I am so confused.

I need to add a custom UD field from the ERP.Part table (PartDecor_c) to our Slow Moving Stock Report. I have the RDD (SlowMoveStock - SlowMoveStk2) copied and added the Part table and created a relationship between this table and SlowMovingPartSummary. I removed the exclusions for Company, Part (for the relationship only) and the PartDecor_C field I want to use in the report. I created a second Report Style that calls on this RDD and that points to the Custom Reports directory of SSRS. I did initial try to Sync the Dataset. It butchered the report (not surprised). This told me I would have to add the query manually to the report. I deleted and the report in the Customer Report folder and deleted the RDD then re-created and deleted and re-created the Report Style. I then re-ran the this report to regenerate the report in the Custom Reports folder. It ran less the new content being placed in the dataset as expected. These are done correctly to the best of my skills.

The problem I seem to be having is confusion on how to add the LEFT JOIN into the dataset query based on lack of understanding of what the T# values should be. I am assuming I need to change the expression on SlowMovingPartSummary to include this LEFT JOIN based on above information. Are T# values based on the sequence number assigned in the RDD? When I added Part Table I assigned sequence 50 and SlowMovingPartSummary is sequence 20. Does this mean the LEFT JOIN query should be written to associate T2 to T5 using Company and PartNum as the relationship values? If so, what should code look like as current code does not make me comfortable in writing this correctly. I know I add the field as well after I put the relationship in but until I get the JOIN correct I cannot do this. What am I missing?

2

The T# are aliases assigned to the tables in the FROM clause of the dataset query. If you look in the FROM clause, you’ll see it is of the form “… FROM TableName_GUID T# …”. From the JobTraveler report:

FROM JobHead_" + Parameters!TableGuid.Value + " T1 LEFT JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company  AND T1.JobNum = T2.JobNum

As far as I’m aware, the T# are just the order they show up in the RDL query. They aren’t necessarily tied to the Sequences in the RDD. Look in the query for

...FROM SlowMovingPartSummary_" + Parameters!TableGuid.Value + " T# ...

Take a note of which T# the SlowMovingPartSummary is assigned, then add the left join with your Part table. The alias you assign to the Part table doesn’t matter. You can make it a T# value if you want to be consistent, but you can just as easily name it “Part” or “P”. See below for the full query from our JobTraveler that has some added tables in it.

JobTraveler Query 
="SELECT T1.DueDate, T1.IUM, T1.ProdQty, T1.ReqDueDate, T1.StartDate, T1.Calc_MultiJobParts, T1.Calc_OrderQty, T1.Calc_StockQty, T2.AssemblySeq, T2.BomSequence, T2.Company, T2.[Description], T2.DrawNum, T2.IUM AS JobAsmbl_IUM, T2.JobNum, T2.OverRunQty, T2.PartNum, T2.PullQty, T2.RequiredQty, T2.RevisionNum, T2.Calc_BCAsmSeq, T2.Calc_BCJobNum, T2.Calc_BCPartNum, T2.Calc_BCRevNum, T2.Calc_comment, T3.CommentText, T3.Instructions, T3.DaysOut, T3.DueDate AS JobOper_DueDate, T3.EstProdHours, T3.EstSetHours, T3.Machines, T3.OpCode, T3.OpDesc, T3.OprSeq, T3.PrimaryProdOpDtl, T3.PrimarySetupOpDtl, T3.ProdStandard, T3.RunQty, T3.StartDate AS JobOper_StartDate, T3.StdFormat, T3.Calc_BCOpCode, T3.Calc_BCOprSeq, T3.Calc_DispStatus, T3.Calc_OPText, T3.Calc_OPType, T3.Calc_PurPoint, T3.Calc_VendorId, T3.Calc_VendorName, T3.OpCode_OpDesc, T4.CapabilityID, T4.ConcurrentCapacity, T4.DailyProdRate, T4.OpDtlSeq, T4.ProdCrewSize, T4.ResourceGrpID, T4.ResourceID, T4.SetUpCrewSize, T4.SetupOrProd, T4.Calc_BCCapabilityID, T4.Calc_BCResGrpID, T4.Calc_BCResourceID, T4.Calc_CapbltyDesc, T4.Calc_ResDesc, T4.Calc_ResGrpDesc, T4.Calc_SchedResDesc, T4.Calc_SchedResGrpDesc, T4.Calc_SchedResGrpID, T4.Calc_SchedResID, T5.ResourceGrpID AS ResourceTimeUsed_ResourceGrpID, T5.ResourceID AS ResourceTimeUsed_ResourceID, T5.WhatIf, Part.Number01 as 'QtyPerBox', UD02.Character01 as 'BoxName', UDCodes.CodeDesc as 'InvType', UD14.Number01 as 'PieceRate', T1.Character01 as 'AltMethod', UD14.Number02 as 'PieceRateInd', UD14.Number03 as 'PieceRate02', UD14.Number04 as 'PieceRate03', UD14.Number05 as 'PieceRate04', UD14.Number06 as 'PieceRate05', UD14.Number07 as 'PieceRate06', UD14.Number08 as 'PieceRate07', UD14.Number09 as 'PieceRate08', UD14.Number10 as 'PieceRate09', UD14.Number11 as 'PieceRate10' FROM JobHead_" + Parameters!TableGuid.Value + " T1 LEFT JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company  AND T1.JobNum = T2.JobNum LEFT JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company  AND T2.JobNum = T3.JobNum  AND T2.AssemblySeq = T3.AssemblySeq LEFT JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company  AND T3.JobNum = T4.JobNum  AND T3.AssemblySeq = T4.AssemblySeq  AND T3.OprSeq = T4.OprSeq LEFT JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company  AND T4.JobNum = T5.JobNum  AND T4.AssemblySeq = T5.AssemblySeq  AND T4.OprSeq = T5.OprSeq  AND T4.OpDtlSeq = T5.OpDtlSeq LEFT JOIN Part_" + Parameters!TableGuid.Value + " Part ON T1.PartNum = Part.PartNum  AND T1.Company = Part.Company LEFT JOIN UD02_" + Parameters!TableGuid.Value + " UD02 ON Part.Company = UD02.Company  AND Part.Character01 = UD02.Key1 LEFT JOIN UDCodes_" + Parameters!TableGuid.Value + " UDCodes ON UDCodes.Company = Part.Company  AND UDCodes.CodeID = Part.ShortChar02 LEFT JOIN UD14_" + Parameters!TableGuid.Value + " UD14 ON UD14.Company = T3.Company  AND UD14.Key1 = T1.PartNum  AND UD14.Key2 = T1.RevisionNum  AND UD14.Key4 = T3.OprSeq"
5

Guess I am more than confused I am absolutely STUPID. In your example, I cannot identify what you are joining from your example.

I need to join a field PartDecor_C from the PART table (child) to SlowMovingPartSummary (Parent) in RDD.

Is this correct:

FROM SlowMovingPartSummary_ " + Parameters!TableGuid.Value + " T1 LEFT JOIN Part_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.partnum = T2.partnum

Here is the Expression now without adding the above line.

=“SELECT T1.PartDescription,T1.PartNum, CAST( T2.Bucket1Amount as float ) as Bucket1Amount,CAST( T2.Bucket2Amount as float ) as Bucket2Amount,CAST( T2.Bucket3Amount as float ) as Bucket3Amount,CAST( T2.Bucket4Amount as float ) as Bucket4Amount,CAST( T2.Bucket5Amount as float ) as Bucket5Amount,CAST( T2.Bucket6Amount as float ) as Bucket6Amount,T2.LastUsedDate,CAST( T2.ExtendedCost as float ) as ExtendedCost,T2.IUM,T2.LotNum,CAST( T2.OnHandQty as float ) as OnHandQty,T2.PartNum as SlowMovingStockDetail_PartNum,CAST( T2.TotalProvision as float ) as TotalProvision,CAST( T2.UnitCost as float ) as UnitCost,T2.WarehouseCode,T2.WhseDesc

FROM SlowMovingPartSummary_” + Parameters!TableGuid.Value + " T1

LEFT OUTER JOIN SlowMovingStockDetail_" + Parameters!TableGuid.Value + " T2

ON T1.Company = T2.Company AND T1.PartNum = T2.PartNum"

Sorry, I am so thick on this. It is just not sinking in how the TO and FROM of this query works.

6

Since there’s already a T2 in the existing query – SlowMovingStockDetail, I would make Part be T3. This query should work:

=“SELECT T1.PartDescription
	,T1.PartNum
	,CAST(T2.Bucket1Amount AS FLOAT) AS Bucket1Amount
	,CAST(T2.Bucket2Amount AS FLOAT) AS Bucket2Amount
	,CAST(T2.Bucket3Amount AS FLOAT) AS Bucket3Amount
	,CAST(T2.Bucket4Amount AS FLOAT) AS Bucket4Amount
	,CAST(T2.Bucket5Amount AS FLOAT) AS Bucket5Amount
	,CAST(T2.Bucket6Amount AS FLOAT) AS Bucket6Amount
	,T2.LastUsedDate
	,CAST(T2.ExtendedCost AS FLOAT) AS ExtendedCost
	,T2.IUM
	,T2.LotNum
	,CAST(T2.OnHandQty AS FLOAT) AS OnHandQty
	,T2.PartNum AS SlowMovingStockDetail_PartNum
	,CAST(T2.TotalProvision AS FLOAT) AS TotalProvision
	,CAST(T2.UnitCost AS FLOAT) AS UnitCost
	,T2.WarehouseCode
	,T2.WhseDesc
	,T3.PartDecor_c
FROM SlowMovingPartSummary_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN SlowMovingStockDetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PartNum = T2.PartNum
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T3
ON T1.Company = T3.Company AND T1.PartNum = T3.PartNum"
7

Note you’ll also have to add the PartDecor_c field as a Query Field in Dataset Properties > Fields tab. Just put both Field Name and Field Source as PartDecor_c:

image
image718×743 23.9 KB

8

That Part I do remember from adding fields from pre-existing tables in RDD. I just for the life of me couldn’t grasp the Left Outer Join. Thank you.

9

If I recall correctly (and I very well could be wrong), you don’t have to add any references to the table added to the RDD.

If you look at various reports, there isn’t always a 1-to-1 relationship between the RDD tables (now called data sources) and the tables that the RDL query refernces.

edit

Here’s the original RDD and RDL for Slow Moving Stock

image

10

??? are you saying I didnt need to modify the RDD?

11

You do need to modify the RDD. But the only mods you need to do to the RDL’s query expression is to add the fields to one of the existing “Tx” tables.

edit

You have to add the field to the existing Tx table that the relationship made in the RDD.

12

This is still giving me fits. HELP! The RDD has a relationship built correctly to Part table and Company, Part (both for relationship only are excluded) as is the field I want for the report Part.PartDecor_c. I have tried both methods suggested above and can add the field. It runs, but will NOT display this field added to the report.