Build BI Pub for Representatives Details Information

Hi Everyone,

We are currently building a BI Publisher report to retrieve detailed information about Representatives.

The report and SQL query work as expected in our Test environment and return the correct data. However, after deploying the same report and query to Production, we are getting different/incorrect results.

Below is the query we are using:

WITH Representatives AS (    SELECT        ppnf.display_name,        par.WORKER_PERSON_ID,        par.responsibility_type,        par.representative_person_id,                -- This creates a ranking (1, 2, 3...) for each worker's rows, sorting the newest date to the top (1)        ROW_NUMBER() OVER (            PARTITION BY par.worker_person_id             ORDER BY pare.start_date DESC        ) as rn,