I’ve been tasked with printing sortof a breadcrumb trail on traveler headers that show parent, parent’s parent, etc. leading to top level (0). In other words, Asm 53 has parent=7, Asm 7 has parent=1, Asm 1 has parent=0 – We want something like “Parentage: 7 / 1 / 0” to print on the traveler for Asm 53.
I can build this string with a clever BPM, but I don’t want to rebuild the already heavily customized traveler into a BPM report. I only have the SSRS data set to work with (right?), which will only have the Parent up to the Asm being printed. Right now I’m thinking a custom field on JobAsmbl populated at some point after job creation and before subs are printed for production.
Anyone have any great ideas or advice to share as I undertake this?
I’ve done stuff like this where I’ve used a recursive query or a loop to concat sequences into a breadcrumb. I did that entirely in SSRS using the Epicor-provided data. I did have to yank and replace the canned query in the report and re-write it as dynamic SQL so as to work with the report DB tables. Not sure if that was the best route, but it worked.
I’ll have to see if I can get more JobAsmbl data into the RDD “above” the scope of the print. Makes sense to try that first.
I don’t think it will be possible to do this in SSRS. I am now working on custom code for some kind of BPM that will trigger off JobReleased=TRUE. Code executes, builds the delineated string, and writes it into a custom field that I’ve already sync’d. I’m a bit of a novice here and I don’t have much code base to extrapolate from. Anyone willing to help me with some broad strokes?
You can use CTEs in the RDL query of your report. If all of the data is in the SSRS tables, you could build a recursive CTE and create it that way. There is one caveat, and I would have to look this back up, but there is a symbol you need to put at the beginning of a CTE statement for the RDL to accept it.
Do SQL coders get CTEs if they recursively bang their heads on the desk long enough? 
I do get befuddled at least a few times before it’s all working, but I usually arrive where I need to be. That said, the data I need is not in the report tables. When you filter the JobTrav by assembly, you only get data for the sub you select (and beneath, if you check that box). I need to trace parent assemblies back to 0. The only way I’ve been able to imagine that with my tiny brain is recording it in a custom field on JobAsmbl when the Job is “done” (released).
I goofed here - I meant to type BAQ in place of both of those BPMs. Short of converting our Traveler to a BAQ report, I need the data written to a cell that I can actually select into the SSRS data. I tried adding another instance of JobAsmbl to the RDD, but that’s not allowed.
Are you offering your brain to science? 
Ah - I missed the “CTE” double entendre. I thought there was doubt that I would “get” common table expressions with my feeble intellect. I honestly just need some help learning how to teach myself to get and set values from/to the data structures exposed in BPMs using a custom code widget. I’m equally terrible at C# and VB.
My joke landings are worse than Ted Williams’s batting average. It doesn’t stop me though, sorry about knocking the thread off track.
@jfajen , if this were me, I would copy the Job Traveler RDD so you could expose more fields. The JobAsmbl data source does have the Parent field in it and it is not being output. Once you do that, you should be able to get it in your report.
Parent field is there, but I need the parent field in rows that are not present in the filtered dataset.
I’ll go as far as you want with this. I am confident that what you are asking can be done with the existing RDD and RDL. But, if you want to do your own BAQ, let me know and I’ll stop responding. 
Are you saying that when a Job Traveler print dialog is filtered for a particular subassembly (with or without the “print subs” checkbox filled), the JobAsmbl table in the RDD will have rows all the way up to 0? I tested that with a stubbed off report style and I could only get the parent of the included subs. I need to be able to track it all the way back to the top even when we’re printing subs way down in the weeds.
And, to be clear, I could beat my head against the table and come up with a CTE BAQ that will show me this trail … but I am looking to compute it and save it in the ERP data, and the only way I can figure to do that is with custom code. Asking for some examples of reading from built-in fields and writing to a custom field within a BPM. I probably should have started a new thread specific to that question.
Well we’re here now, go ahead and ask. Need more info.
Ah, so you do want it in a field in Epicor. That should be fairly easy to do as I believe that when you get details, it starts at the 0 and goes down. I would need to trace it again to know for sure. But I imagine a Parantage field that when a new Asm is added to the Job, it records the Parent Asm in the Parantage field and then when an Asm is added to that Asm, it grabs the Parent Parantage and adds the Parent Asm to the field.
This is as far as I’ve gotten with a custom code widget in a post-proc MD (ChangeJobHeadReleased)
The logic isn’t quite there yet, but I’m mainly concerned with figuring out how to read/write and I think you can see what I’m aiming for.
// csharp
string parentage = "";
int currentAsm = 0;
int currentParent = 0;
Ice.IIceTable myTable = ds.JobAsmbl.Tableset.Tables[0];
foreach(var ttRow in (from row in ds.JobAsmbl select row))
{
parentage = ttRow.Parent.ToString(); // initialize string
currentAsm = ttRow.AssemblySeq; // grab Assembly number of each row
currentParent = ttRow.Parent; // grab Parent seed of each row
do
{
parentage = parentage + "~"; // add delineator
// Use currentAsm value to find next currentParent value
parentage = parentage + currentParent.ToString(); // add next non-zero parent to string
// set currentAsm to currentParent value and repeat until we find 0
}while(currentParent != 0);
// write delineated Parentage to custom field in each row
}
That do while loop looks dangerous