SSRS Job Traveler (Joining to UD table in data set expression, UD field is not returning data in report)

tfenwick11 · February 19, 2018, 6:31pm

Currently I am attempting to modify a custom job traveler report in which the “JobHead” data set will be updated to include two LEFT OUTER JOINS to retrieve a UD field from Part_UD. With the SQL Query below for my data set I was able to retrieve the UD field "SkidProgram_c. With running the query I received my job information from the database and the “SkidProgram_c” field is populated. But when running the report the field displays no value. Looking at the “JobHead_GUID” table the field is blank as the example below. My question is how can the JobHead data set return the correct information I need but the report displays no value?

Title Field Expression:

JobHead Dataset:

=“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
,T7.SkidProgram_c FROM JobHead_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
LEFT OUTER 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 OUTER 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 OUTER JOIN [EpicorPOC10].[Erp].[Part] T6 ON T1.PartNum = T6.PartNum LEFT OUTER JOIN [EpicorPOC10].[Erp].[Part_UD] T7
ON T6.SysRowID = T7.ForeignSysRowID"

mlrainsford · February 19, 2018, 6:38pm

I believe that you do not need to join on the UD table directly in the SSRS dataset. You can reference the UD field from the Part table directly. Simply call T6.SkidProgram_c after removing the

LEFT OUTER JOIN [EpicorPOC10].[Erp].[Part_UD] T7 ON T6.SysRowID = T7.ForeignSysRowID

rbucek · February 19, 2018, 6:57pm

make sure you didn’t exclude the sysrowid columns for the ud table

tfenwick11 · February 19, 2018, 7:34pm

I didn’t add PART or PART UD to the RDD, figured i could just reference it from the expression. Do i have to add the tables to the RDD even though I’m pulling the information in the expression?

tfenwick11 · February 19, 2018, 7:44pm

Removed that section and received this.

Query execution failed for dataset ‘JobHead’. —> System.Data.SqlClient.SqlException: Invalid column name ‘SkidProgram_c’.

tfenwick11 · February 19, 2018, 7:46pm

Didn’t have PARTUD or PART in the RDD so nothing to exclude.

shobdy · February 19, 2018, 7:48pm

I noticed that you didn’t link the companies when you created T6.

Wonder if it would help to do this:

LEFT OUTER JOIN [EpicorPOC10].[Erp].[Part] T6 ON T1.Company = T6.Company AND T1.PartNum = T6.PartNum

Also, why don’t you bring the tables in to the report data definition? Linking to them directly is not usually the best way, IMO.

You should be able to bring in the UD field that way instead of another join.

mlrainsford · February 19, 2018, 7:57pm

I’m linking directly to the RDD instead of just referencing from the expression. I believe a combination of adding the table to the RDD and then referencing the UD field directly from that link should work.

tfenwick11 · February 19, 2018, 8:02pm

@shobdy Tried out the company addition to the join and had no luck. I am going to clear the expression and go back to the RDD and add PART and PART UD and add new relations and exclude everything but the skid program and sysrow and see if that works real quick.

shobdy · February 19, 2018, 8:05pm

@tfenwick11 - If you do RDD, make sure in Jobhead, uncheck exclude column for partnum, then add part table only. You don’t need to add the part_ud separately. The UD fields will show in exclude in Part table. Then add a relationship between JobHead and Part like this:

I think that should get you near where you want to go.

tfenwick11 · February 19, 2018, 8:19pm

@shobdy With adding PART and creating a relationship. With reviewing the “exclusions” tab I don’t see the SkidProgram_c field. I went and reviewed UD maintenance and everything looks to be in sync. I did not setup this field, is there a map maybe someone missed back to the PART Table?

shobdy · February 19, 2018, 8:22pm

Just to be safe, before you clicked the exclusions tab, you went to the Report Table -> List tab and selected the row with the new Part table on it? Then clicked on the Exclusions tab? You can click the column name ‘Field Name’ to sort alphabetically, if they are not, then look again.

As you can see below, some of my UD fields are listed in the Part table. (Commision_Code_c, etc…).

tfenwick11 · February 19, 2018, 8:31pm

@shobdy I’m rather stumped as to why I don’t see the field. Maybe they changed some of the logic in 10.2.1.800? I feel like I remember in version 10.1.600 you could not reference the UD tables when adding a new table to the RDD.

tfenwick11 · February 19, 2018, 8:44pm

@shobdy Never mind I was wrong, Looks like in version 10.1.600 you could reference the UD table. So looks like i’m stuck trying to figure out why my UD field is not showing up under PART.

shobdy · February 19, 2018, 8:58pm

@tfenwick11 - Unfortunately, I am still running 10.1.500.xx, so I don’t know what changes they made in 10.2.xxx.xx. Hopefully someone out here running 10.2 can help with that part. It really doesn’t make sense to me why they would rip that part out of it but what do I know .

tfenwick11 · February 19, 2018, 9:04pm

Yea its just really weird, the UD maintenance shows everything in Sync. I just read a few posts about users having trouble with the UD fields not showing in the RDD like how @rbucek described. So i included (SysRevID and SysRowID) in the PART table in the RDD. Cleared out my client cache and fully closed E10 and logged back in. I still don’t see the field in the PART table in the RDD. Really Odd.

tfenwick11 · February 20, 2018, 1:33pm

@shobdy Found a post this morning, and I didn’t realize that PART and PART_UD can be referred to by a VIEW. In my expression I tried referencing the VIEW and my query returned data like the last trials but the report still displays no info. I think at this point I am just going to have to create a ticket

tfenwick11 · February 21, 2018, 8:22pm

To give an update while waiting for support, I figured I would create a new UD field in 10.2 for the PART_UD table and regenerate the data model. Navigating back to my custom RDD the PART datasource now displays my new UD field but still does not display the old UD fields.

tfenwick11 · February 23, 2018, 7:46pm

Here is the solution I found that resolved my UD problems in 10.2

Steps to add UD fields if they are not recognized in the data source exclusions:

Navigate to RDD and expand data sources
Click on the data source (table) that you would originally link your secondary table to (PART)
With the parent data source selected you need to drill down to “description fields” and select a direct link to your secondary table (My example Jobhead and Part had “PartNum” in common.)
Chose to bring over “SkidProgram_c and IndivSkidLabel_c”
Saved the data set and navigated to report styles
Unfortunately I had to sync the report style so my dataset would see the UD changes (This will overwrite your data set expressions, so I suggest you always make copies doing this)
After syncing my RDD to the report I confirmed my dataset had the fields in the expression and in the field section.

T1.[PartNum_IndivSkidLabel_c], T1.[PartNum_SkidProgram_c] FROM JobHead_" + Parameters!