According to the help and the Epoicor implementation guide, you can add an existing table to an RDD, as long as you give it a different name:
From the help:
Report Table
Displays the identifier for the selected table. You first populate this field by clicking the Schema Table… button to find and select a table you want to include on your custom report definition.
Typically the Report Table is the same as the Schema Table. However you can change the name of the table in this field. Use this feature to display data from the same physical table in multiple locations in the report. For example, you can use this field to rename the OrderMsc table to the OrderHeadMsc table, add the OrderMsc table again, and then rename it to the OrderDtlMsc table. Then while you modify the report format through a SQL Server report writer, you can place selected columns from these two tables in different locations on the custom report.
When I try to add a table With the Report Table: BTCustomer, and the Schema Table: Customer, I get the following error when it tries to save:
ZDataTableID field should be equal to RptTableID.
The help implies that I need to rename the original instance of the Customer table, but the Report Table field is read only for all tables.
Am I trying to do something that just can’t be done?
And since I’d eventually need to edit the RDL’s query expression, should I just add the Erp.Customer table as a join, right from the DB, bypassing having the report pull that data into the dataset passed to SSRS?
In 10.2.300.15, adding a second instance of a table works (kinda), if you name the second table the name of another existing table (I had to enter the alias name before searching for and selecting the table):
Adding a copy of the Vendor table and naming it Customer:
Thanks. But I can’t even get that far in 10.1.400.23.
Since I’d have to edit the RDL’s Query to add the fields I want, I might as well just pull in the second instance of customer there.
The only real danger of pulling data from directly from the DB, instead of the temp table with GUID suffix, is that the data in the temp table is a snapshot as of when the report was run. In other words, If the report was archived, the version using data only from the RDD (ie temp tables with the GUID), would always print the same. Where as the report with the direct link to the DB, will show the current values from the linked table, which may differ from their value when the report was run.
I’ve opened up a case with Epicor to see what their take is.
Not sure this will apply in your case but in earlier versions when I’ve needed to reuse a table,
I’ve directly edited the the query for a dataset in SSRS. (and manually adding the fields to the dataset).
Kind of a pain but has worked.
Example below, where I added another JobOper as T4
=“SELECT
T1.AssemblySeq,
T2.AssemblySeq as SubAsmbl_AssemblySeq,
T2.Company,
T2.JobNum,
T2.Parent,
T2.PartNum,
T2.RelatedOperation,
T3.OprSeq,
T3.OpCode,
T3.OpDesc, T4.OprSeq as AsmZero_OprSeq, T4.OpCode as AsmZero_OpCode, T4.OpDesc as AsmZero_OpDesc
FROM JobAsmbl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN SubAsmbl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Parent
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.Parent = T3.AssemblySeq
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.JobNum = T4.JobNum AND T2.Parent = T4.AssemblySeq AND T2.RelatedOperation = T4.OprSeq"
Hmmm… just rtied on a 10.1.600 test system & seems to be working?
May be another one of those things that I “learned” did not work but…was in earlier versions and I may need to do some “unlearning” and test again?
Isn’t your example of adding T4 as a second instance of the JobOper table, going to use the data that the RDD selected for the original instance of JobOper?
My OrderAck RDD needs two Customer table references, but different customer records for the one order.
If I added Customer to the RDD, with a Customer.CustNum = OrderHed.CustNum relationship, wont the only records in the temp table customer_[report_GUID] be the one where Customer.CustNum = OrderHed.CustNum ?
My second customer table was going to have the relationship
Customer.CustNum = OrderHed.BTCustNum
Or does the table customer_[report_GUID] contain all the customer records (as of the time the report was run)?
In my case yes… same JobOper reocords from the RDD selection, just aliased.
So wouldn’t help you.
but…now I’m really interested in testing that example of second instance in the RDD.
Pobably will be a couple days before I can get around to it…
Thinking it should work though… assuming there isn’t some underlying logic/joins that short circuit the second instance?
Now I wonder why you’re getting an error when you try adding a second instance to an RDD on your system…if a version bug or possibly your entry sequence?
Just heard back from Epicor support (actually got a phone call). They say that you cannot have a table in an RDD, more than once. That the documentation (Online Help, and the Implementation Guide) are incorrect.
I can confirm that you can’t. I’ve spend hours trying. That is what makes the RDD Engine blah a bit… The only work-around is to join on less keys the first time the table is used and apply filters in the RDL. You might end-up grabbing more than you need.
The other option which I haven’t used yet is the BAQ DataSet Section.
I wonder if @JeffLeBert out of curiosity has more insights on what one should do if you need to join the same table on 3 Parents in RDD Maintenance. It’s definitely a common request and a common problem.
What I always understood the RDD is what populates the temp-tables in your reporting database from Epicor database and the RDL is what merely uses the temp-tables and filters on that. But it’s also a bit different on BAQ Report, which has an empty RDD.
Just imagine needing to use PartTran on 3 Parents… definitely not a table you want to run open-wide.
You should NOT try to include a table twice in an RDD. This is like cleaning a gun while it is loaded. It looks like support has already said that, but I added a great and gruesome analogy.
The whole “3 Parents” thing is something I haven’t thought about much, but the limitations of “standard” RDDs I have thought about. That is why I push using BAQs as your data source. This gives you 100% control over the data you collect. Unfortunately, you can’t use this except on reports the ONLY have BAQ data sources.
To clarify the last bits of @hkeric.wci’s post, we need to get into the weeds of how RDDs and the Reporting Framework do their thing.
The RDD defines the table structures that the report data will be written into. It also defines some of the extra magic things it does for you like linked columns, but that is beyond what we are talking about today.
The Report Assembly, is an E10 assembly that queries data from the database and pushes it into the Reporting Framework, which writes it to the temporary report tables. It also builds stuff like the calculated columns. This is where the magic of the querying of the data actually happens.
Lastly, we have the Reporting Framework. It writes that data the the Report Assembly pushes to it. When all the data is collected, it runs the actual report. For SSRS, this entails calling the SSRS server and waiting for the PDF bytes to be returned.
If you think about using BAQs for your data source (again, only in new reports, sorry) you can get rid of the Report Assembly and define all of the magic yourself.
It is best to just add the UDCodes table, without a relationship and then post-filter in the RDL. Usually not a big deal because UDCodes doesn’t have 200K rows.
When you mention new reports and BAQ DataSets, does that mean a new report without a reference to a Report Assembly, or just merely a Blank Report Data Definition?
You said “BAQ DataSets”. I think you meant “BAQ data sources”.
Basically I’m talking about a RDD with no data sources specified. We have a rule in RDD maintenance that you can’t add a BAQ data source if you have a “regular” data source and visa-versa. Basically, you have to pick one or the other.
The Report Assembly is specified in the Report Style so that isn’t part of the equation. A report based on BAQ datasources has no need for a Report Assembly. Or, more specifically, it uses a common one similar to what BAQ Reports use.
Does a BAQ Report create the temp tables on the SSRS server, like RDD based reports do? If not, and it just runs the query when the report is run, then an archived BAQ Report wouldn’t necessarily be the same next that archive is printed or previewed.
If I may ask, what is the business case here for adding the customer table twice. You may not need to do it if it is to get Bill to address, by using “Allow Third party shipments”.
Its very common for us to have to change the Sold To at the last minute (or even after shipments have happened). Our customers often have specific “entities” setup for the project we’re supplying. And they (the customers) get very snooty if the Sold To info isn’t just right.
So our solution is to create a customer record with this specific entity info. Then make that customer an alternate Bill To for the customer specified on the Order.
But now we need to have that Bill To info appear in place of the Sold To info on Order Acks.
The first time customer is added to the OrderAck RDD is to get some UD fields from the customer record where customer.CustNum = OrderHed.CustNum.
The second instance of the customer table would be to get the customer info where customer.CustNum = OrderHed.BTCustNum
The RDL file has the address info in a field named Calc_BillToAddressList .
The fact that that feild has the words “BillTo” in it, I was hoping that it would contain the BillTo address when a different BillTo is specified on the order.
Set up a Bill To customer with “Allow Third party shipments” check box checked.
Then in the Order Entry you can select this customer as the Sold To and for Ship To you can use any other customer which has the correct ShipTo information.
This way you will have both the address and Epicor takes care of the print etc. For your UD field info you can either use lookup on the RDD or a BPM/customization to bring the info into the order.
