Double Child on SSRS report

Epicor ERP 10
, , , ,
1

I’m running into the exception of not being able to have two relationships for the same child table. I’ve read the other threads and am not quite putting the pieces together.

Basically we keep track of what customer belongs to a part. Mainly for drawing and compliance purposes. If the job is MTS, the job traveler contains the part customer. If however, the job is MTO, we want to display the Sales Order Customer. Two different relationship paths. Clearly the RDD will not allow the second relationship.

If I go to Report Builder Designer and modify the query for the dataset, it appears to be pulling data from the temp tables. If I cannot get the second relationship into the temp tables, how can I modify the query to pull this information?

2

Steve,
I’ve come across this issue myself with customer contacts. Same exact situation. I needed to reference the Customer Contact table with regard to two different parents. My work around was to create a UD column on the OrderHed table and then included that in the RDD / report query. I then created a BPM to populate that field whenever the customer contact was updated on the order. I’m not saying it’s the best way or the only way… but it’s a way.

3

In these situations it is best to use a linked table for one relationship and add the customer table for the other. The Linked tables are a report writer’s best friend.

1 Like
4

What is a linked table? That’s what I am not understanding. Too use to writing native SQL.

5

Epicor builds extra relationships that you can add in as needed. You move a table from Available to Picked and then you can select the fields you want on the second tab. You don’t need to build the relationship as Epicor has already done it in the background.

image

6

Cool - found in another thread as well.

1 Like
7

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘JobHead’. —> System.Data.SqlClient.SqlException: Invalid object name ‘SOCustomer_e3f63cdc34fd45a89b7abba3337ab8cd’.)

So does it dump all data into the Customer table, even though the results are coming from two different paths?

1 Like
8

It dumps it all into the Dataset. Which one is that, the Linked or the added?

9

Wouldn’t it need both the linked child and the relationship child?

10

Do you understand how the dataset gets generated for the SSRS report? If not, I can explain it and that may help.

11

That is exactly what I am hoping to understand.

12

Ok.

When looking at an RDD, the Data Sources will be output to the report DB as tables with the following structure TableName_GUID. If you can write a SQL query against the report DB you can actually see the data output. In order to see these tables you need to set the Archive Period to something other than 0 Days. You get the GUID from the Report Monitor after it has successfully run. This is our Sales Order Acknowledgement.

image

image

image

image

Once the tables have been created in the DB, the SSRS Dataset Query takes over. At this point it is just SQL in the query. You can use any SQL tricks you know. One of my favorites is using CTEs in the Dataset Query.

image
image615×636 22 KB

image

At this point, you have your Tables being output to the SQL DB and the SSRS report querying those tables to create the dataset.

Now, if you want to use data from a Linked Table, you first need to copy the System RDD and create your own as you cannot modify the system ones. Select the Table that you want to use the Linked Data on, in my case it is OrderHed. Go to the Linked Tables tab and make sure the table you want is in the Picked list. Then go to the Description tab and select the fields from the table that you want.

image

image

Now, to get the field in the report Dataset, they get added as a field on the table that you are hanging off of. In my example I used the Plant table off of the OrderHed table. In my Dataset Query, OrderHed is T1 so I need to add the field T1.Plant_Address1 to the statement. You also need to add the field to the list of fields in the Dataset.

image

Now, to get a brand new table in the RDD, this is how you do it. One very important thing to note first though is that if the table you want is in the RDD, just expose the fields you need and edit the Dataset Query. I see a lot of people need a table that is already there in an RDD but does not have a Relationship, so they add a relationship and it will duplicate the data. If the table is already there in the System RDD with no entry in the Relationships, 99.9% of the time the data will be output to SQL as Epicor has already built the relationship in the background.

If you need a new table, you add it in your RDD and expose the fields you need for the report and the fields you need to create a relationship on. In my example, I added CustCnt to my RDD and created the relationship to OrderHed. Note: always leave Key field blank and make sure the Relation Type field is Output.

image
image750×565 18.5 KB

Finally, add the new table and fields to your Dataset Query just like the existing ones and you will now have that data available for your report.

Hope this helps.

13

I believe I did this… Primarily through trial and error yesterday. At least the error messages in the System Monitor were close enough to give me a clue.

Frustration Point: Just venting for now. I have virtually no access to the database. We are dedicated cloud and have access to a replication of production only. No access to the reporting database. No access to the Pilot database. No access to the Pilot reporting database. No access to the schema in SSMS. I don’t understand the reason we cannot have a read only SQL account for debugging purposes, especially as we are dedicated. Of course I’m making the assumption dedicated has their own database vs. multi-tenant. I came from an on prem environment, where SSMS was the go to tool to understand what was happening and for debugging purposes. Anyway… That and $6 will get me that cup of coffee I want from Starbucks.

In our situation we have 2 paths to get to the Customer Name and CustID, which we want to print on the Job Traveler. Since we manufacturer parts specific to customers, we’ve added a ud field to the part table for custid (PartNum_CustId_c - I do not know why they didn’t use custnum - before my time). If the part is make to stock or just make to job, we want the part customer on the report.

If the job is a make to order, then we want the customer the order is for to be listed.

image
image701×614 13.1 KB

I understand the process of pulling from the db and creating the guid temp tables in the reporting db, but I have no idea how it would handle the multi path to the customer table. Would it create just one Customer_guid table or would it create a customer and a customer2 table based on path. At this time, based on your feedback, it appears to be just one table Customer_guid. If I had access to the schema it would answer this question. Then the next question is it adding both paths worth of information. Really wished I could dump the table somehow to see.

As of right now, MTS & MTJ’s are correctly being produced - as they were before I headed down this path. MTO’s are still not showing any customer information. If I could see the OrderHed_guid table I’d know if it was at least getting the ordernum.

Here is the defined relationship to the Customer table:

image
image680×533 44.6 KB

Here is the linked table to the Customer table:

image
image793×503 47.1 KB

And here are the fields from the linked table:

image
image775×368 38.8 KB

Here is the query in the RDL:

, T5.WhatIf
, T6.Name AS Customer, T6.CustID as CustomerId
, T7.OrderNum
, T8.CustNum
, T9.CustID as SOCustId
, T9.Name as SOCustName

FROM JobHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.AssemblySeq = T4.AssemblySeq AND T3.OprSeq = T4.OprSeq
LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq AND T4.OpDtlSeq = T5.OpDtlSeq
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T2.Company = T6.Company AND T2.PartNum_CustId_c = T6.CustID
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T7
ON T1.Company = T7.Company AND T1.JobNum = T7.JobNum
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T8
ON T7.Company = T8.Company AND T7.OrderNum = T8.OrderNum
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T9
ON T8.Company = T9.Company AND T8.CustNum = T9.CustNum"

14

You are close. If you are using the Customer table that is hanging off of the OrderHed, it would look like this.

, T5.WhatIf
, T6.Name AS Customer, T6.CustID as CustomerId
, T7.OrderNum
, T8.CustNum
, T8.Customer_CustID as SOCustId
, T8.Customer_Name as SOCustName

FROM JobHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.AssemblySeq = T4.AssemblySeq AND T3.OprSeq = T4.OprSeq
LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq AND T4.OpDtlSeq = T5.OpDtlSeq
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T2.Company = T6.Company AND T2.PartNum_CustId_c = T6.CustID
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T7
ON T1.Company = T7.Company AND T1.JobNum = T7.JobNum
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T8
ON T7.Company = T8.Company AND T7.OrderNum = T8.OrderNum"
15

Woot!!! Schweng!!! We’re in the FRONT row!

Many Many Thanks for the details. I never would have gotten through linked tables if you hadn’t laid out specific details.

image