Adding a field on PO but PO shows shipping release line twice

Hi all,

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!

Best,
YP

Try adding a Distinct right after the Select in you RDL Dataset

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.

Hi Calvin,

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.

Forgot to tag you in my reply. Sorry.

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).

That’s right.

Also on the printed form, the subtotal that shows at the bottom is doubled as well.

You added the field to the query expression on the report, right? Are you sure you attached it to the right table?

Hi Alex,

Just saw your reply. I did not see Distinct in Epicor Report Data Definition. Could you elaborate a little more about this?

Thank you!

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
image

This is what I got.

Alex,

I followed your suggestion but unfortunately it did not work. I got an error message when I ran the report.


image

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?

I meant in the Query Expression.

Open the Datasets folder, right click on POHeader and select properties
image

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.

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?

2 Likes

hahahaha! I will remember it now for sure!