I am attempting to add serial numbers from the SerialNo table into our Job traveler. Currently, I have the links on the rdd as:
JobHead > JobAsmbl inner joined on company and job
JobAsmbl > SerialNo inner joined on company, job, assemblyseq, and part number
When the report the serial numbers show, but are duplicated several times and blow up a five page report to roughly 2053 pages. What are some things I can try to get rid of these repeat values?
At first glance, it would seem to me that in that query described, that any serial numbers ever defined for every part would all be shown, and every time that part appears in the assembly.
Is this what you had in mind, or is the report output something different than that? If a Part did not have a SerialNo defined, wouldn’t you still want it to show the Part on the assembly in the traveler?
Offhand I do not know a lot about the SerialNo table, but it would seem that you would want some filters on it. Probably more efficient if the filtering can be done somehow in the RDD as opposed to the SSRS RDL.
That’s pretty much what we are trying to accomplish. However, the expectation was that when this was added to a job traveler report that we would only see the serial numbers related to that job. What we are getting instead is each of the serial numbers for that job repeated several times. These repeats add a total of nearly 2053 pages. When that number is divided by the 86 pages between each repeat of the actual traveler that is shown that results in 24. That number is representative of the number of serial numbers that should be shown. As for filtering I have added in rdd filters for assemblyseq 0 .
Ah, I see the ‘job’ in there now. Do you reckon that if you wrote up a BAQ, you could reproduce the issue? Perhaps the ‘jobnum’ to link is not populated for the serials?
On a BAQ
built using the same tables, I get the results we are after without repeats. I’m starting to think it’s because of the other tables I have in my SSRS and RDD. I’m going to try creating a copy of the default JobTraveler and just join in the SerialNo Table to see if it gives the same result.
Hi Dylan,
We have serial numbers on top level job part number. We do not get duplicates for jobs producing many serialized parts. Here’s the setup, in case it may be useful to you, first in Epicor, second in Crystal. I’m sorry to say it’s in Crystal… I only go kicking and screaming to SSRS as needed 
Nancy
That is how I had mine set up! Turns out the problem was I had put the results in a table and then put that table in a row of another table. This was causing the serial numbers to replicate bassed off of assembly number, bom item, and something else. As soon as I added groupings to the Serial Number Table it reduced the number of returns to what it should have been.
Thanks a ton to both @Michael_Ramsey and @Nancy_Hoyt! Got the creative juices flowing.
You are welcome.
