We are in the process of implementing Epicor to replace our in-house ERP system. One of the customization requests I received was to change the data that lists on the PO Form as Mfg / Mfg Part Number. Currently, Mfg lists MfgID, not the actual name. This results in the customer seeing an 8 character ID that is not as useful as just seeing the name of the company.
I have been messing around in the SSRS editor and the Report Data Designer and have not been able to find a way to pull that Manufacturer.Name field into the report.
Does anyone have any suggestions on how to do this? Please see the attached picture to clarify.
I’m not at a computer so I can’t say for sure. But I bet on the PO Detail data source you can go to linked tables and select the manufacturer ID, Then on the picked columns you should be able to see the manufacturer columns including Name. You can select it. Then it will be available in your report to add. See if that helps. If not, I’m glad to look into it when I get back to my machine.
I did what you asked, but there was only MfgNum available. I have created a BAQ that translates MfgNum to Manufacturer Name, but also unsure how to pull that into here, if that is possible.
The RDD needs to have the Manufacturer table added then a relationship from PartXRefVend or PartXRefMfg to the manufacturer. I did a mockup.
Assuming you already have duplicated the POFORM you also need a duplicate RDD.
make a copy of the RDD with duplicate report, unless you already have.
Add table, pick from scheme
add an Output relationship
I don’t do RDDs a lot, but I had to clear and reload the POFORM to see the fields. Uncheck the Name and label.
I think at this point in the report style you need to upload the report to the server and then redownload it.
This is a word doc I did for our report person back when we started. Sorry, too lazy to do the rest of this. SSRS add new data fields to report.docx (358.1 KB)
I will try this. I had attempted doing what you mentioned here, but maybe my relationship was incorrect. I did duplicate the RDD and create my custom one. Thank you for your help
I’m guessing MfgNum is like MfgID except the Num is not editable - similar to VendorNum vs VendorID.
When you use MfgNum, you get the Name to link in. This is going to be the easiest thing I promise.
So in the RDL, you’d update your query to pull in the field MfgNum_Name from the PODetail table and then add MfgNum_Name to your fields for the dataset.
I suppose I just assumed that the linked table in PO Detail is going to give the Manufacturer that he’s looking for. I shouldn’t have assumed. Both ways are useful. I try to use Linked tables if I can… then if I can’t I add a new data source and relationship.
I was able to get up to this point, but not sure what you mean by “update your query etc”. I saved the RDD, duplicated the exists SSRS and changed the data definition to the RDD I just made and downloaded the SSRS report. On POForm.rdl I don’t see PODetail as a dataset, and even so, the field I indicated in the inital post is buried in a subquery.
Haha this is where @gpayne’s solution comes into play. I saw it on the Detail on your printed Report but with it being in a subreport, I see there is a separate data source just for that subreport. So linking to it in PODetail may not be the answer. You may have to go Greg’s path.
After setting up the RDD, you need to make a corresponding edit to the RDL. Check out this video and see if that helps explain what needs to happen. (Note this is assuming you added a data source and relationship like Greg pointed out. It would be similar if you used a linked table - but you’d not need to join a table in the query, you’d just add the column to the select statement in the SQL and then add the field.
@dr_dan Is there a way to shove something into both of those calculated fields or even the area to be sure which of those is the one showing on the report?
And I’ll try to explain what we’re doing… for me, I followed someone’s tips just like this and I didn’t really understand why or what the purpose was.
Essentially, the RDD is sending a bunch of tables to the ReportServer. The tables are named POHeader_SOMEUNIQUEGUID, PODetail_SOMEUNIQUEGUID, etc. Those tables are only used to store data for the report you’re generating. Once the report is generated, those tables get deleted. So the SQL query we’re writing in the RDL file is querying these “temp” tables to get the data. It’s just basic SQL though (if you speak that language). So the bit about the Guid parameter, that’s getting the parameter generated for that specific report and it is concatenating it into the SQL query.
Once you understand this, it becomes much easier to maneuver in the RDL files. You can do all the stuff you can normally do with SQL like grouping, aggregates, sorting, etc. By default, all the RDLs come pre-loaded with the SQL already in there for their stock reports. So as you go to edit them, you need to edit the SQL also to pull the data you want to display in the report. I hope that made sense.
Thank you for your guys’ help on this. I will be attempting this in the next couple days. I’m going to try @gpayne 's idea. I am very new to this and I appreciate all your help. What are some resources you guys used when getting started that helped you become so proficient? I feel it is a very complex thing to be doing the reports, and BAQs, and BPMS, and application studio, but all the trainings so far are so superficial it doesn’t help when the problem becomes more complicated like these.
a lot of searching and just trying things. That is what I do most on this site is search. I would look for rdd and rdl and read thru them. Use EpicCare and EpicWeb for searches also.
Then open system monitor report tab and find the filename column
copy the file name and then open ssms and expand the ssrs report database then filter tables with the table guid id
This will show the list of tables generated for the report and you can query them for the data generated.
In my case the manufacture table is empty.
But the mfg ID is showing on the report.
I went to the field help and there is a PODetail.MfgNum that @dr_dan bet would be there.
I went back to the rdd and in PODetail went to linked tables and moved MfgNum to the right.
In the main POForm rdl. PODetail is already joined as T2, so you just need to add the field we saw in the data.
Then add the field to the field list.
We need to update this field
