I added a UD Field to the JobHead table called FAQty_c. I added it to the screen and entered data in the field for a job. I then added the JobHead table to our custom Job Pick List RDD by linking it to the JobAsmbl table by the Company and JobNum fields, and then chose to include the Company, JobNum, and FAQty_c fields.
I then modified the SQL expression in our custom Job Pick List to pick up the changes and include the JobHead info. When I ran the report, the fields from the main JobHead table (Company and JobHead) had data but the FAQty_c did not. I found the GUID in my SSRS database and found that the FAQty_c field said NULL.
I decided to stop and start the Task Agent (not sure if that does anything) and then I recycled the IIS App Pool that the Task Agent is tied too. Now the FAQty_c shows 0 instead of NULL, but it should say 1 which is what I entered into the field in the Job Entry screen.
If I check the data with a BAQ, the data is there. I am currently on 10.1.400.14. I am having this problem on this report and the MtlTag. Consultants have created custom forms for us on 10.1.400.12 and the UD field data came in no problem. Is it possible a bug is causing this problem for this version? Am I missing a step to get the UD Fields to display data in the SSRS database?
When you are addressing UD fields at the SQL level, you have to make a join to the UD version of the table. In your case JobHead_UD needs joining to JobHead. Inside Epicor the UD field integrates automatically for you. In SQL it doesn’t.
Here is an example of the join you need:
select Part_UD.MarkupOverride_c, Part.PartNum from Epicor10.Erp.Part_UD INNER JOIN Epicor10.Erp.Part on Part_UD.ForeignSysRowID = Part.SysRowID
Ben: I can give that a shot, however, I was under the impression that the RDD took care of that for me. I’m looking at forms consultants have created for me and they did not join the UD version of the table in the SQL expression. I’m not trying to say one way is better than the other, I’m just trying to understand why there is a difference.
Ken: I did all of those steps. The data isn’t making it to the SSRS database which makes me wonder if it’s an RDD problem.
great. Can you post your join. Is this a new report? Check other reports that use the table, see if the field is there. Then, like you are thinking it is the RDD or how the system is creating the report table.
The JobHead table is already linked to the RDD through Linked Tables. Add JobNum to the Picked Links and then on the Description Fields sheet, select the UD field to add to the report.
One last comment, if you’re in SQL you don’t have to join to the _UD table. Just query from the VIEW instead, which is prefixed dbo.TableName rather than ERP.TableName. The view contains all the fields from both tables.
This worked great for a custom SSRS report. I add table JobHead and JobHead_UD to my RDD for the SGMargin report. Add the relationship of InvoiceDtl to JobHead (company and jobnum) using the output type. However, you would need to add the relationship for JobHead to JobHead_UD using the definition type not output type.
I know this is an old post - but I’m having a very similar issue with the RMAForm report. I’ve added a new table to the RDD and I can see that this table gets created in SQL Server when I generate the report. However, the table has no data. I’m not using any filtering criteria or report relationships and the table I’m pulling from does have data in the Epicor database, but it’s just not populating. Was anyone able to determine what caused @tmcmullen’s original issue?
Hey Calvin, unfortunately this is what I had originally but the data definition doesn’t pull in any values. I actually tried deleting all report parameters and relationships so I would bring in ALL records from the target table (just as a test). But it doesn’t pull anything. If you have time, here’s a link to the original post I made about it a few days ago: Added "HDCase" table to RMAForm RDD but no HDCase records are populated in the temp tables
Original issue was resolved - I’m still a little confused on how Epicor fills the temp tables with data, but I found what I was missing in my original issue.
The temp tables contain just the data that the RDD (or BAQ in a BAQ Report), based on the parameters at runtime. If there is a Part_guid temp table created for your report, it will only contain the records required, based on the input.
One last thing. RDD’s often create table’s that don’t exist in the DB. The RDD often combines tables. For example, the ARForm RDD makes about 7 temp tables, but has like 20 DB tables.
Since a single Invoice will have info just one customer, no need to make separate temp tables. So you might see fields like Customer_Name in a temp table named InvcHead.
