Referencing the same table more than once on a report - Help Needed

Kinetic ERP
, ,
1

I have a need to pull the EmpBasic table into the labor edit report twice. Once for the employee information and once for the supervisor’s information. If I go to add the EmpBasic table into the RDD more than once it says that the table id has to be unique. If I reference the EmpBasic table more once on the SSRS side it doesn’t return any additional information. What can I do to reference the same table twice?

2

What supervisor information are you returning? You could just create one or more calculated fields based on Supervisor ID, like

case SupervisorID
when ‘100’ then ‘John Smith’
…

etc.

3

They still don’t allow this? Even in Kinetic?

1 Like
4

@Matthew_Morgan,

What I would like is to get their email addresses. This would allow me to break route based on supervisor email. Doing that through a Calculated field would mean I would have to update every time someone shuffled around.

@utaylor,

As of right now no. I still get the angry Epicor message:

image

Thank you both for the responses!

5

Yeah Dylan, I have also wanted to do this for quite some time as the sales rep is used on multiple tables and thus the need to join it in more than one way is needed.

I thought this was an epicor Idea at one point. I have to imagine that it has strong support.

6

Is this a BAQ report?

If not, an old Crystal trick may work. Relate to the table with just Company and then do the relations in the Dataset query inside SSRS.

3 Likes
7

@Randy,
It’s a regular report. I’ll give that a shot later today. Thanks for the idea!

8

Still not possible to my knowledge…

9

@hkeric.wci,

I’ve seen you and Jose do some miracles in the past…why isn’t this done yet? XD

10

One caveat with that trick is, at least back in the Crystal days, is this trick pulls all the records of that table. The EmpBasic shouldn’t be too heavy of a table and with the report data now in the SSRS database it should be pretty quick if it works.

Another option is to build another data source into your SSRS and have it point to your database to get the data. I’ve not had to do this yet but IIRC there is a thread here about it.

11

@dgreenEA Ways that I think could possibly do this. All are a bit hacky, but would work.

  1. EmpBasic has PerConID and PerCon has ReportsTo
  2. Use a UD table to build Employee and supervisor records on update of employee.
  3. Add the supervisor info into UD fields in EmpBasic and let Epicor bring that to the report.
1 Like
12

Just include the table once with no relations. The RDD will create a copy of the EmpBasic table as EmpBasic_GUID. Then refernce it twice in the RDL’s query expression.

I’d only suggest doing this tables that you know are limited in size (Country, EmpBasic, etc …) Definitely don’t do it with PartTran.

2 Likes
13

However you can include the table twice if it exists twice.

I actually use JobAsmbl twice by simply including a different alias, yet it is the same DB Table behind the scenes. But before you do that, make sure you have exhausted the Linked Tables options first. Sometimes you can just use that in conjunction with an additional Relationship.

For example for JobHead I simply used the other one, it worked exactly like the regular one, since it was the same Schema.

image
image

Maybe the List one didnt work well, but for sure the other one if I recall.

I thought you could start mixing BAQs and RDDs now… @JeffLeBert maybe knows more of a same-table relationship trick :slight_smile:

2 Likes
14

https://epicor-manufacturing.ideas.aha.io/ideas/ERP-I-731 Looks like someone made an epicor idea but it doesn’t have many upvotes

2 Likes
15

This is a great idea but I think more votes are going to using BAQs for report sources which would also mitigate this problem.

1 Like
16

I voted for it Chance.

17

Would also vote for this.

18

It’s been a long time since I “owned” the reporting framework. I didn’t do much with this issue and so never had to find workarounds.

It looks like others in this thread came to the same conclusion that I did. Making the reporting framework allow mixing BAQs as data sources in “regular” reports would solve this problem. It would also give a bunch of other benefits.

That’s about all I can say. Try what @hkeric.wci suggested and vote for BAQ support in “regular” reports. I will continue to push for this as well.

5 Likes
19

I am not seeing the idea for that. However would love that feature as well! Anyone that could link it will get my vote.

1 Like
20

I dont have enough information to know what exactly your are trying to achieve, but I would suggest you change your approach:

  • Select the EmpBasic and link it only by Company. This will return all the Employees (provided you dont have thousands, it should not affect the performance).
  • In SSRS create two datasets both using the EmpBasic. the first one selects non-suppervisors employees, the other does.
  • You can use a different tablix to pool the information from each table or
  • you could use a formula using LOOKUP

Hope that helps.

1 Like