Did you clean up the quotes that might have been converted to “fancy” quotes?
Darn, I bet it can’t find the ProjectID field.
I was hoping to tap into whatever magic Epicor does in the background on their custom RDDs.
Ok, how is the join to the InvcDtls table setup in the RDD Relationships?
Ah, I hadn’t but just fixed it! Now I am getting a “failed to save report” error:
The expression used for the parameter ‘ARDTLS’ in the dataset ‘ARDTLS’ includes an aggregate or lookup function. Aggregate and lookup functions cannot be used in query parameter expressions.
EDIT: scratch that. I re-copied from @jkane 's edited code and pasted. Allowed me to save the report, but now getting that “query expression failed…” error again when I run the report.
Here is the RDD file if that’s easier: RDDARAgngCTS.xml (284.1 KB)
The relationship has ARDtls as the parent and InvcDtl as child. Type is output. Joined on Company and InvoiceNum fields.
That’s the issue. You need a join on the invoice line.
Can you do a direct SQL query against the SSRS Report database?
Yes, are thinking just to confirm that the Project ID value will show up in the InvcDtl table?
I was more wondering what the table is that T5.InvoiceLine populates.
Alright, finally got this resolved… Thank you all for the recommendations, @Mark_Wonsil, @ckrusen, and @jkane !
Long story short, I changed up my approach and went with joining the InvcHead table to ARDtls. InvcHead is just a bit less reliable than OrderDtl because for the ProjectID field to be populated, it requires the invoice to be generated via the Projects module. Which is our case 95% of the time. Gave up on using OrderDtl after many hours of attempts… maybe I was overlooking something simple, but I’m happy enough with this solution.
I was also falling victim to making too many changes at once earlier in the week… when I slowed down, the solution became rather obvious 
2 Likes
General solution for future readers: modify the RDD to add InvcHead table with relationship to ARDtls, DON’T click sync dataset in Report Style, modify the query expression in the report, add the ProjectID field to the dataset, add said field to report.
In the query expression, I added this join clause to the end:
LEFT OUTER JOIN InvcHead_" +Parameters!TableGuid.Value + " T9 ON T9.Company = T2.Company AND T9.InvoiceNum = T2.InvoiceNum
and add the following at the end of the SELECT statement: T9.Company, T9.InvoiceNum, T9.PBProjectID
2 Likes