Hey there, let's break down your problem step by step and fix those issues with your SSRS report for Epicor!

First, let's clarify the two key issues you're facing:

• Your textbox expression was displaying raw SQL instead of the actual PersonID value
• Now that you've merged the subquery into your main SQL, you need to fix table join errors to get the correct PersonID

1. Why was the textbox showing raw SQL? #

SSRS textbox expressions treat string values as literal output—so when you pasted your entire SQL query as a string (wrapped in "), it just printed the query text instead of executing it.

If you didn't want to merge the query into your main dataset, a better approach here would be using the Lookup function (SSRS built-in) to pull PersonID from a separate dataset. For example:

=Lookup(Fields!PONum.Value, Fields!PONum.Value, Fields!PersonID.Value, "PlannerDataset")

This would match the PONum from your main POHeader dataset to a secondary dataset containing PONum and PersonID, then return the corresponding PersonID. But since you've already moved the query into your main SQL, let's focus on fixing that.

2. Fixing Table Join Errors in Your Main SQL #

Looking at your SQL snippet, there are a few critical syntax and logic issues that are preventing it from returning the correct PersonID:

Issue 1: Alias Conflicts #

Your subquery uses T1 as an alias, but T1 is already the alias for your main POHeader_xxx table. This creates ambiguity for the SQL engine. Always use unique aliases in subqueries.

Issue 2: Invalid Column Reference #

You're trying to reference PlannerID.T5.PersonID—but PlannerID is the alias for your subquery. Once you alias the subquery, you should reference columns using the subquery alias directly (e.g., PlannerID.PersonID), not the internal table aliases from the subquery.

Issue 3: Broken JOIN Syntax #

Your RIGHT OUTER JOIN placement is incorrect, and re-aliasing the main POHeader table as POHeader1 creates unnecessary confusion. Since you want to keep all POHeader records and pull in PersonID where available, a LEFT JOIN (instead of RIGHT JOIN) is the logical choice here.

Corrected SQL Example #

Here's a cleaned-up version of your query with these fixes applied (fill in the missing join logic with your actual Epicor table relationships):

SELECT 
    T1.*, -- Include all POHeader fields you need
    PlannerID.PersonID -- Pull PersonID from the subquery
FROM POHeader_" + Parameters!TableGuid.Value + " T1
LEFT JOIN (
    -- Subquery with unique aliases to avoid conflict
    SELECT 
        SubPO.PONum,
        T5.PersonID,
        T5.JobNum
    FROM POHeader_" + Parameters!TableGuid.Value + " AS SubPO
    -- Add your actual join logic to link POHeader to the table containing PersonID
    INNER JOIN SomeEpicorTable T2 ON SubPO.RelevantField = T2.RelevantField
    INNER JOIN AnotherTable T3 ON T2.AnotherField = T3.AnotherField
    -- Final join to the Person table (T5) to get PersonID
    INNER JOIN Person T5 ON T3.PersonID = T5.PersonID
) AS PlannerID ON T1.PONum = PlannerID.PONum

Step-by-Step Validation #

1. Test the Subquery First: Run just the subquery in Epicor's SQL tool (replace Parameters!TableGuid.Value with a real table name like POHeader_12345abc) to confirm it returns PONum and matching PersonID correctly.
2. Verify Join Keys: Double-check that PONum is the correct field to link POHeader to your other tables. In Epicor, sometimes you might need to use Company as an additional join key (e.g., ON T1.Company = SubPO.Company AND T1.PONum = SubPO.PONum) to avoid cross-company mismatches.
3. Confirm PersonID Source: Make sure T5 is the correct table for Project Lead—Epicor's Person table stores general person info, while Emp is for employees. Confirm which table has the PersonID linked to your project lead data.

3. Final Tips #

• After fixing the SQL, update your SSRS dataset to use this corrected query.
• In your report's textbox, simply reference the field directly with =Fields!PersonID.Value instead of any SQL expression.
• If you still get null values, check if some POs don't have a linked Project Lead (which is expected) or if your join logic is excluding valid records (try replacing INNER JOIN with LEFT JOIN in the subquery if needed).

内容的提问来源于stack exchange，提问作者MOnik