Job Traveller Report - Serial Number Data Output

[GE-Chess (Gary Evans)]

Hey All,

I am fairly new to Epicor (1 Month so Far) and been working within SSRS reports recently to connect and display data. Currently I am on Job Traveller and I’m trying to pull SerialNo.SerialNumber from Job Entry → Job **** → Activity → Serial Numbers.

I’ve been able to find Serial Number within the JobOper data source and include this however, I’m not having much luck with pulling this into the Query successfully and getting the data to show.

Would anyone be able to point me in the right direction to how I would be able to display this value within the report?

[dcamlin (David Camlin)]

Hi Gary, welcome to the forum!

Just poking around… it looks like the SerialNumber field on JobOper may not be the same. The description I found is “Serial Number of the asset”. Which sounds more like a serial number for the scheduled resource vs the serial number of a part.

You can add SerialNo table to your query and potentially join it to JobAsmbl or JobMtl (depending on what you’re trying to see).

Are you looking to show the Serial Numbers of Materials being issued to the job?

[jkane (John Kane)]

Welcome @GE-Chess !!

Quick question, how familiar are you with the RDD concept?

[GE-Chess (Gary Evans)]

Thanks for the welcome!

Fairly familiar with the RDD concept now, I’ve gone through the definitions and relationships with my colleague who has been involved in the Epicor implementation for a year now.

[GE-Chess (Gary Evans)]

Thanks for the welcome David!

Yea I suspected this may be the case.

Essentially our users are inputting the Serial Numbers of Part’s into the Serial Number Assignment → Job **** → Assigned Serial Numbers.
For a job I can see the serial numbers inputted in there, for example 009023412.

Then within Job Entry → Job **** → Activity → Serial Numbers this shows that same data from the Serial Number Assignment. I need to drag that field into the report.

Without SerialNo/SerialNumber being apart of JobAsmbl or JobMtl would there be a way to connect this directly? We are not sure what route we can take to somehow join this in.

Thanks again!

[jkane (John Kane)]

That is good.

So, what level do your serial numbers exist? Are they on materials, assemblies, and/or Asm 0 only?

[dcamlin (David Camlin)]

Well, if you want to give it a shot (I haven’t done this SerialNumber add specifically, but below is what I would attempt)…

You would need to open up your RDD (JobTrav). I don’t know if you’ve modified it in the past or not for other things… but if not, you need to “Duplicate Report” from the Actions menu as you cannot modify the base RDDs.

Once you have your own copy of it and can modify it, you’ll want to add a New Table. In this case the SerialNo table.

Save.

You’ll then want to review the “Exclusions” for that table and turn on the fields you want/need access to from that table. You’ll want to make sure you include Company, JobNum, AssemblySeq, and MtlSeq. We’re going to use those to join SerialNo to JobMtl. You also want to uncheck any fields you want to use in your report (SerialNumber for example).

Once you have your Exclusions unchecked, you need to add a New Relationship. This is where you tell it to join the SerialNo table on the JobMtl table.

The Parent table would be JobMtl… the Child is SerialNo. Set the Relationship Type to Output (as we want data output so we can use it).

Next, below, you add your relationship Fields.

So you’ll add:
Company to Company
JobNum to JobNum
AssemblySeq to AssemblySeq
JobMtl to JobMtl

That should be it for the RDD. Save, obviously.

You’ll now need to go to your Report Style. Here, again, if you haven’t already modified it in the past (have your own customizable version), you’ll need to copy the report style (in the actions menu) so you can make edits.

Even if you do already have a customized style, you need to make sure it is pointing towards your newly customized RDD.

Save.

In the report itself (the RDL file in Report Builder)… you’ll need to add the new table join to your main query, using the same relationships we defined in the RDD.

Then you can add the “fields” you want to show in your report in the upper portion of the query.

You’ll also need to add any added fields as query fields in the query properties.

At that point, you can add them to your report layout… save, upload, and test.

A lot of steps… feel free to ask questions if/when you run into any unknowns.

[GE-Chess (Gary Evans)]

Thank you for such a detailed and easily structured guide to this. I can confirm that this did pull the Serial Number of the Job Entry!

I did some testing with this and used a Job with a quantity of 10 Parts. I then assigned 10 serial numbers to the Job and found a slight complication with pulling all 10 serial numbers to the field. I messed around with the relationship and Query and did a bit of digging but unfortunately, I’m not having much luck.

Would this require a lot of adjusting/changes in order to make this work? If it’s possible then this would be perfect!

Just wanted to say again that I appreciate your help on this, definitely helped me to understand the links between the tables.

[GE-Chess (Gary Evans)]

I have had a look through the Assemblies tab of the Job Entry but I haven’t been able to actually find a Serial Number within this area.

The only field I have been able to find this within is Activity → Serial Numbers.

David’s guide below has been able to pull the Serial Number field from here.

[dcamlin (David Camlin)]

@GE-Chess

found a slight complication with pulling all 10

Can you explain what didn’t work? Are you only getting 1 serial number to display?

It may be WHERE you’re placing your field within the report. I’ll have to look again when I’m in front of my machine, but i remember JobTrav being full of sub reports. If there’s a JobMatl sub report, that could be where you’d need to add this field.

[GE-Chess (Gary Evans)]

Yes, only 1 serial number is displaying (Seems like the first serial number of the list).

I did think it might have been where, but I extended the field to ensure there is plenty of room for more serial numbers.

[jkane (John Kane)]

Depending on where in the process you use SNs will dictate how you need to retrieve them. You can issue SNs for materials and you can match and assign SNs to assemblies and the top level. Each instance of these requires different joins to pull them, which is why I was asking. @dcamlin only gave you instructions for pulling SNs of issued materials. If you need SNs at different levels, it will require different configurations.

[dcamlin (David Camlin)]

@GE-Chess

I’m trying to pull SerialNo.SerialNumber from Job Entry → Job **** → Activity → Serial Numbers.

@jkane is correct, I made the assumption you were after the serial numbers pre-assigned to issued raw materials. If that’s what you’re after… I set up a quick test this morning and below should get you there. If you’re after Serial Numbers of assemblies and/or Finished Parts, we’ll need to revisit.

~~ * ~~

I entered a test job, added a serial tracked part and issued qty (5) of that part with different serial numbers:

@dcamlin

i remember JobTrav being full of sub reports.

Like I said, many little subreports going on in here. I highlighted the “RawMaterialComponents” report in the image below. Again, I assume this is what you’re looking for:

When you download the JobTrav report style (RDL), it pulls down several reports and puts them in the folder.

So, in this case, you’ll want to open and edit the RawMaterialComponents.rdl

This is the report (subreport) where you would want to add your new table/relationship and field:

When you add it to the subreport form, make sure it is in the “Detail” Group (highlighted green below). Group 1 - gives you information on the the specific raw material part. The “Details” group is where the details of each transaction would go.

But, in cases where you issue multiple parts with Serial Numbers and want to see all of them, you’d get an output like this in the final report:

Which, again, matches up with the Serial Numbers displayed in JobEntry for this raw material:

[GE-Chess (Gary Evans)]

Hi David,

I’ve gathered more information to provide the exact information we are trying to output.

We are trying to export all Serial Numbers assigned to the Part within a Job. So within Job there is one Part that can be assigned. From there of course, the sub-assemblies are then assigned which are located within the RawMatieralComponents Report.

I got confirmation that they are not assigning Serial Numbers to any of the materials within Assemblies. They are just assigning Serial Numbers to the Part of the Job. However, what you provided has been quite helpful to demonstrate to the user.

So if I go to Serial Number Assignment and enter the Job number there is the Part assigned (Which can only be the one Part). The users are then adding multiple Serial Numbers of this part within this menu.

So if we had a Part called ‘Mobile System’ assigned to Job ‘876456’ then we would have maybe 5 serial numbers attached to this one Part.

What we are trying to succeed in, is exporting all 5 of these Serial Numbers to the report, outside of the sub-reports.

We have the below currently which is where the placement of this field is,

Your previous guide was very helpful as we were able to output one of the 5 serial numbers as you can see in the image. However, we are trying to accomplish outputting all 5 alongside each other. This may not be possible but I wanted to check with the community to check first.

Again, really appreciate all you time and effort into helping with this!

[dcamlin (David Camlin)]

I gotcha. (See, @jkane was right, I was making bad assumptions) And since i made a mess… I’ll try to help clean it up.

So, yes, I think you can get there.

Not sure where you are at this point… but I used the below “relationship” in my RDD… joining JobAsmbl to SerialNo (instead of JobMtl to SerialNo) because we want the Serial Numbers for the assembly level parts this time around:

This was my corresponding addition to the rdl query:

LEFT OUTER JOIN SerialNo_" + Parameters!TableGuid.Value + " T8 ON T2.Company = T8.Company AND T2.JobNum = T8.JobNum AND T2.AssemblySeq = T8.AssemblySeq

Now… as far the report only returning the first row and not multiple… I initially got the same thing. Since we’re not in a “details” group, its just going to report the “first” result.

So, instead of add Serial Number as a textbox, I added a new tablix instead. Doing it this way gives it its own “details” group so it will supply all rows.

So, at the top of the screen in Report Builder, just click on the Insert tab. Insert a new Table, I used the “Wizard”.

Choose JobHead as the source query and I just pulled “SerialNumber” over to the Values box:

Next > Next > Finish

It’ll drop the matrix on your screen and you just drag it where you want it.

I deleted the header row and created my own label textbox, so I ended up with this.

And this uploaded test resulted in the below:

[GE-Chess (Gary Evans)]

Hi David,

I’ve applied the above and it does output all serial numbers. However, it likes to output them several times . I’ve double checked the changes and I can’t see anything that i’ve missed/done differently.

For one of the job numbers it outputted the same serial number 16 times. This job had only 1 serial number applied to it.

For another job I tried, it outputted the same number 12 times. This job had 5 serial numbers applied to the job.

I have screenshots of the configuration I set below,

Would you happen to have any idea of where this may be relating to? Sorry to be a pain, but I do appreciate the support!

[jkane (John Kane)]

For the Job and Asm that only has one SN, just run a query against JobAsm and see how many records are in the table. If the amount of records equals the amount of SNs returned, that is the issue. You might need to use the PartTranSNTran table in your report.

[dcamlin (David Camlin)]

Yeah, I agree with @jkane. Those returns don’t make much sense to me. Doing a BAQ against those JobAsmb and SerialNo and seeing what is returned is a good sanity check.

Curious if we can find some kind of correlation. For the job the job where it repeated 16 times. Is there 16 of anything? Like, 16 total Assemblies on the job? 16 total Materials in the Assembly? I’m just wondering if the relationship needs tuned.

Or, we may be able to put a filter on the added matrix somehow.

For the job that had (5) serial numbers… were they ALL repeated 12 times? Or just the first one and the rest showed up once?

[GE-Chess (Gary Evans)]

Apologies for the delay, I won’t be back on Epicor until next week so I will investigate this further then and run a BAQ query against JobAsmb.

For the job that had 5 serial numbers it repeated all 5 numbers 12 times,

I will get back to you once I have the results and info.

Thanks again!
Gary

[GE-Chess (Gary Evans)]

Okay so I created a BAQ on JobAsm & PartSN and no mismatches were found. Everything appeared to add up okay between the data.

As i’ve now advanced in my EpiHelp level I can now provide further screenshots!

In this screenshot below you can see the example of where the serial number was outputted 12 times (There is another page but I didn’t feel the need to provide this)

Then in this image below you can see it’s duplicated 16 times on this report, yet there is only one serial number.

Again apologies for my lack of knowledge in this area, however your support is greatly appreciated.

Kind Regards,
Gary