Hello All,
I am trying to join the Part Table on the JobMtl table in the JobTrav Report data definition. After I add the table and create the relationship the report runs extremely slow and the grouping is way off.
I am joining it with the company and partnum fields so I don’t see why I would get duplicates especially since the jobmtl table is joined to the job by jobnum so for every material there should only be one part number based on that join.
Any ideas?
The Part table is already linked in the RDD through Linked Tables on JobHead. Just add the Part fields you want to the Picked List…
Be sure you are joining to the material part number and not the job parent
part number. I believe both are in the job material table.
Brad
@bmgarver @BoostERP What I don’t understand is the Pick Links, how can I see what they are joining to? A PartNum as a Pick link to the Job head table to me would mean that it is joined to the Part Num on the Job Head. I am looking to join the Part table on the Job Mtl table. The JobMtl table also has a pick link to the part table, but when I choose additonal fields the report starts duplicating data. I would expect it to behave the same since it is the same join…
Why does data start duplicating just because I add another field from the pick link table?
Not sure if this applies here but how many warehouses do you have? If more than one, each part that is available in multiple warehouses may be duplicated.
This will happen if there is more than one Material Part tied to the job. May need to add a group to the SSRS report grouping by MtlSeq field.
@bmgarver I made a group on the material sequence earlier and I was happy, but as soon as I ticked the “show sub assemblies” as a print option when printing the report then all of a sudden everything ran SUPER slow and the grouping fell apart.
Did you find a solution to this? I am running into very similar situation for my JobTrav.
I gave up on this report because they didn’t really need it customized. They can use the standard report.
BUT for all newer reports where I have had to add tables, I use stored procedures. Create the stored procedure in your SQL database (we use a reporting database separate from our production database) that returns whatever results you are looking for. Then in the report create another datasource that connects to that SQL database. Then create a dataset that uses that stored procedure. @Koecher.
In our environment our SSRS reporting databases are on a separate server than our Epicor databases, but if they were on the same server (which might be true in your case) I believe you can actually modify the query in your dataset (the default dataset created form the report data definition) and join whatever tables you want using the query editor.
