How to get the same name of field from different dataset?

Epicor ERP 10
1

My Report Builder SSRS problem is I want to use Name in ship to table, but there is Name of Salesperson in Quote dataset already. Do I have to do manual query or can I modify things in data definition? I already added table ShipTo to data definition, The other fields come out but the Name not.

I’m new to Epicor, Thank you

2

The field name might automatically be changed (to avoid conflicts), by prepending the table name. So you might need to use
ShipTo_Name

1 Like
3

In the SSRS query dataset you should be able give your fields aliases so you can uniquely identify the fields in your report. So if you had the Address1 field from Customer and the Address1 field from CustomerBillTo, they could be differentiated like so

SELECT T1.Address1 as Customer_Address1, T2.Address1 as BillTo_Address1 
FROM Customer_" + Parameters!TableGuid.Value + " T1
INNER JOIN CustomerBillTo_" + Parameters!TableGuid.Value + " T2

In your dataset fields you reference them by the alias
image

1 Like
4

Thank you Calvin

Where can I check the field name which be changed? It always be like “table” + _ + “Field” or not?

Many Thanks

5

Did you update the RDL’s query expression to join the table you added in the RDD? Then you just need to reference the field like @cfinley mentions above.

Also, make sure that field isn’t excluded in the RDD.

The basic steps of modifying a epicor supplied report are:

  1. Duplicate the RDD
  2. Modify the duplicated RDD (add tables, fields, etc…)
  3. Duplicate the Report Style
  4. Modify the RDL of the new style:
    a. Update the query expression to join any new tables added to the RDD, and add the fields in the SELECT clause
    b. Update the RDLs list of query fields to add the new fields
6

The Expression, copy pasted into a Text Editor can make editing more efficient. Not for the faint of heart. You’ve got a bit of a learning curve ahead. Review other examples of editing reports in this forum, Try some things, have backups, iterate, gain confidence, you’ll get it, faith in you.

image
image882×574 30.7 KB

1 Like
7

And a hint when looking at the Datasets in RDL… They often appear to mirror the tables in an RDD, but this is only a “coincidence”. Each time a report is run, the RDD generates temporary tables with common suffix (a GUID).

These tables created by the RDD are often named like the tables used by the RDD, but aren’t exact copies. Some RDD’s may have 20+ tables, but only generate 3 or 4 temp tables. Take a look at the RDD of the OrderAck and then compare that to the Datasets in the RDL used for the Sales Order Ack report.

And one last word of advice:

NEVER, EVER USE THE “SYNC DATA” BUTTON ON A REPORT STYLE!!!

(well there are some very rare times you can use it, but using it when you shouldn’t cause very bad things to happen)