March 16, 2020, 9:37pm
I am trying to add an “Approved By” field in PO. I changed the data definition POForm by including the column “ApprovedBy”. I then tried to added this field in POForm report in SSRS. Then the weird thing happened–for each PO line, the same shipping release information showed up twice (see below). I really could not figure out why this happened. These repeated release lines also caused line subtotal doubled than what it is supposed to be.
Does anyone have an idea about this?
Thank you so much!
Try adding a Distinct right after the Select in you RDL Dataset
March 17, 2020, 5:25pm
Did you add any data sources to the RDL? Or just enable a field currently being excluded?
Also, does the PO above have 2 lines with one release each? Or does each line have 2 releases?
If the PO lines were created from PO Suggestions or Requisitions, different comments would create separate releases.
March 17, 2020, 6:40pm
I did not add any data resource. I just simply enable column “ApproveBy” in POHeader (see below).
For your 2nd and 3rd question–Each line has 2 repeated releases.Those PO are not created from PO suggestions.
March 17, 2020, 6:41pm
Forgot to tag you in my reply. Sorry.
March 17, 2020, 6:45pm
So each PO line actually has 1 release, that shows up twice on the printed form (and doubles the order qty), correct?
Meaning if you look at the PO in PO Entry or PO Tracker, It shows the order qty for line 1 is (5), and there is just one release (also Qty 5).
March 17, 2020, 7:23pm
Also on the printed form, the subtotal that shows at the bottom is doubled as well.
March 17, 2020, 7:33pm
You added the field to the query expression on the report, right? Are you sure you attached it to the right table?
March 17, 2020, 7:34pm
Just saw your reply. I did not see Distinct in Epicor Report Data Definition. Could you elaborate a little more about this?
OK, open the main RDL, POForm.rdl
Double Click on the main Dataset (POHeader)
Open the dynamic Query and add the Word “Distinct” after the Select and before the first field.
See if that helps
March 17, 2020, 7:59pm
I followed your suggestion but unfortunately it did not work. I got an error message when I ran the report.
Did you click the sync dataset button? Adding distinct would not increase the amount of columns returned. Were you getting this error before the distict?
March 17, 2020, 8:07pm
I meant in the Query Expression.
Open the Datasets folder, right click on POHeader and select properties
In the Properties window, select Query Expression Function editor button
You added the new fields in to the query expression, right?
Or did you click the Sync Dataset button on the report style Maintenance? Hopefully you
did not do this.
March 17, 2020, 8:28pm
Oh my god, I did click Sync Dataset button on the report style maintenance. I first clicked this button, downloaded the report, and tried to change the report. Should I redo everything?
March 17, 2020, 8:55pm
hahahaha! I will remember it now for sure!