Modifying MtlTags Report - (Inclusion of PackSlip)

[GE-Chess (Gary Evans)]

Hello,

I’m working on the MtlTags report and i’m trying to pull the PackSlip field. I’ve done some digging around and testing and this is what i’ve found so far.

MtlQueue has the PackSlip field which has been excluded,

This is from a linked table that links back to RcvHead.

On the SSRS report MtlTags is the only dataset which contains fields such as PONum, JobNum etc… but MtlQueue does not exist. I’ve tried to link the two however, when trying to create a relationship between the two, there is no fields on MtlTags.

I’m confused to how MtlTags has all these fields yet it’s not linked to the MtlQueue which contains the data? Any help on how I can pull the PackSlip into this report would be much appretiated!

Thanks,
Gary

[dcamlin (David Camlin)]

I previously did some work on MtlTags, and agreed, it is an odd report to work with. I also wanted to pull in some receipt information.

MtlTags has a DataSource called “MtlTags” which is some kind of system query source, not a database table. That’s why there aren’t any fields listed in the RDD. However, if you look at the query in the RDL, “MtlTags” it includes PartNum and PO information… which is enough.

I would ignore MtlQueue.

Add RcvDtl as a data source to your custom RDD with the below relationship, linking it to MtlTags.

Then you can add that join to your RDL.

="SELECT T1.AsmSeq, T1.TagNum,T1.BCAsmSeq,T1.BCBinNum,T1.BCJobNum,
T1.BCLotNum,T1.BCOprSeq,T1.BCPartNum,T1.BCWhseCode,
T1.BinNum,T1.ItemQty,T1.JobNum,T1.LaborNote,T1.LotNum,
T1.NCComment,T1.NonConfTranID,T1.OpCode,T1.OprSeq,
T1.PartNum,T1.PartDesc,T1.POLine,T1.PONum,T1.PORel,
T1.Reason,T1.ReasonDesc,T1.ResGrpID,T1.TagFormat, T1.TagTitle,
T1.UM,T1.UM_UOMSymbol,T1.VendID, T1.VendName,T1.WhseCode,T1.QtyNum, 
T1.LegalNumber, T1.JobSeqType, T1.Revision, T1.BCPCID, T1.PCID, 
T1.DMRNum, T1.AttributeValueSeq, T1.AttributeSetShortDescription, 
T1.NumberOfPieces, T2.PackSlip 

FROM MtlTags_" + Parameters!TableGuid.Value + " T1 

LEFT OUTER JOIN RCVDTL_" + Parameters!TableGuid.Value + " T2 ON 
T1.Company = T2.Company AND 
T1.PartNum = T2.PartNum AND 
T1.PONum = T2.PONum AND 
T1.POLine = T2.POLine AND 
T1.PORel = T2.PORelNum"

Add PackSlip as a Query Field, add it to your report where you want it, and you should be good to go.

[GE-Chess (Gary Evans)]

Hi David,

Thank you for this information,

Our SSRS service is currently experiencing issues so I will try this out once it’s resolved. Definitely makes more sense though regarding the data and relationships!

Kind Regards,
Gary

[GE-Chess (Gary Evans)]

Hi David,

Epicor have finally got round to fixing our SSRS reporting system. I’ve given your guide a shot, however i’m a little bit stuck.

I’ve imported RcvDtl and excluded the required fields which is fine. Of course, with the information you provided on MtlTags it makes sense as to why there are no fields. But then the question leads to how do you make these fields accessible into MtlTags that can then be used for the relationship? Do you have to do this through a different menu?

Kind Regards,
Gary

[dcamlin (David Camlin)]

That’s odd. They all show up for me.

Can you right-click and see an option to “refresh list”?

Or, maybe save the RDD with the relationship but without any fields chosen and see if a screen refresh may help populate them?

Or… try typing in the values and see if it’ll accept them. You’ll just have to make sure they’re correct as they’re case-sensitive.

[GE-Chess (Gary Evans)]

Hi David,

In this case, I recreated the RDD and the fields have now appeared for MtlTags. I created the relationship with RcvDtl and excluded the required fields.

I then changed the Dataset query for MtlTags within the report with the one you provided,

="SELECT T1.AsmSeq, T1.TagNum,T1.BCAsmSeq,T1.BCBinNum,T1.BCJobNum,
T1.BCLotNum,T1.BCOprSeq,T1.BCPartNum,T1.BCWhseCode,
T1.BinNum,T1.ItemQty,T1.JobNum,T1.LaborNote,T1.LotNum,
T1.NCComment,T1.NonConfTranID,T1.OpCode,T1.OprSeq,
T1.PartNum,T1.PartDesc,T1.POLine,T1.PONum,T1.PORel,
T1.Reason,T1.ReasonDesc,T1.ResGrpID,T1.TagFormat, T1.TagTitle,
T1.UM,T1.UM_UOMSymbol,T1.VendID, T1.VendName,T1.WhseCode,T1.QtyNum, 
T1.LegalNumber, T1.JobSeqType, T1.Revision, T1.BCPCID, T1.PCID, 
T1.DMRNum, T1.AttributeValueSeq, T1.AttributeSetShortDescription, 
T1.NumberOfPieces, T2.PackSlip 

FROM MtlTags_" + Parameters!TableGuid.Value + " T1 

LEFT OUTER JOIN RCVDTL_" + Parameters!TableGuid.Value + " T2 ON 
T1.Company = T2.Company AND 
T1.PartNum = T2.PartNum AND 
T1.PONum = T2.PONum AND 
T1.POLine = T2.POLine AND 
T1.PORel = T2.PORelNum"

Unfortunately, tried running Print Tags and it generated an error,

Ice.Core.SsrsReporting.SsrsCaller.SsrsException: The SSRS server returned the status code 500 (InternalServerError) with the following error text:
An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'MtlTags'. ---> System.Data.SqlClient.SqlException: Invalid column name 'PackSlip'.

If this sounds more like an issue within the report then I will just scrap and restart from scratch with this process.

[dcamlin (David Camlin)]

@GE-Chess

I recreated the RDD

Did you repoint your report style to your new RDD? If it is still looking at the old one, it won’t see PackSlip.

@GE-Chess

I created the relationship with RcvDtl and excluded the required fields

I’m assuming you un-excluded PackSlip?

[dcamlin (David Camlin)]

One other thing to try… I used all caps when I added the table to my RDD.

I always use ALL CAPS when I add a table to an RDD so I can quickly tell which tables I added vs what was there originally. Not required… just something I picked up from a consultant many moons ago and I stuck with the practice.

Anyway… you used mixed case in your RDD. So, you may need to change the table to mixed case in your RDL query.

[GE-Chess (Gary Evans)]

Yea seems like changing RCVDTL to RcvDtl did the trick. Nice thing to note down for future!

The Packing Slip is now correctly outputting to the report, which is great! However, there’s a slight issue: if the Packing Slip is not received, the report pulls a random number from a received one instead that is within the same PO number.

To show as an example,

We have two packing slips under one PO,

In this case, we have two Packing Slips under one PO. If I go to 415287 and print the tags, it shows Packing Slip 142536 (because it’s received) instead of 415287 (which is not received).

I considered adding “Received” into the relationships, but unfortunately, the MtlTags table doesn’t include this field.

Sorry for the trouble—any ideas on the best approach to resolve this?

[dcamlin (David Camlin)]

Well, crap.

Yeah, the 1-to-many relationship is giving issues, which I was never told about by my receivers. Apparently they never looked that closely and caught this issue. But I checked this morning and am seeing the same thing.

I don’t think its a matter of received or not, it is just displaying the first PackSlip value that matches the relationship criteria (PartNum, PONum, POLine, PORel).

I think this can be gotten around with a different approach. Using CallContextBpmData… the problem is, there’s a known bug with Kinetic SSRS not receiving the CallContextBPMData values. The approach has worked for years in Classic, but not in Kinetic. Supposedly, they (Epicor) have a fix going through development right now to correct the issue.

I can walk through that set-up with you… but until they have this fixed, it won’t show any results, so its difficult to know if it will work or not.