topic Re: Paginated Reports selecting dataset "An item with the same key has already been added." SQL Union..? in Report Server

Paginated Reports selecting dataset "An item with the same key has already been added." SQL Union..?

EdgeExtension35 — Tue, 25 Jun 2024 14:45:06 GMT

I'm building a report in PowerBI paginated reports, fed by a dataset that that is tied to a SQL stored procedure. Everything was working fine, until I modified the SP to include a UNION to another table. The query runs fine in SSMS and I get the intended results, however when I try to validate the procedure in Report Builder I get the error:
"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.
An item with the same key has already been added."

I've tried creating a new blank report, readding my Datasource and trying to add the dataset again with the same results. I've looked over the SP and I cannot for the life of me figure out why it would be throwing that error, however all of the obscure posts & whatnot I've found in my research seem to suggest this happens on a table join/merge, so more than likely it's something with how PowerBI is handling the UNION portion of my query. Any thoughts? Im attaching a slightly scrubbed version of my SP query below:

SELECT E.ID, E.EstimateNumber, E.divisionNumber, E.deptNumber, E.JobName, E.JobLocationCity, E.JobLocationState, E.EstimateDateDue, E.EstimateTimeDue, E.ExtendedDate, E.EstimateNotes, E.OutstandingDate, E.GCorCMorOwner, E.NameOfGCorCMorO, IIF(E.EstimateDateDue<E.ExtendedDate,E.ExtendedDate,E.EstimateDateDue) AS DateDue, IIF(LEN(DATEPART(HH,EstimateTimeDue))<2,CONCAT('0',STR(DATEPART(HH,EstimateTimeDue))),STR(DATEPART(HH,EstimateTimeDue))) AS HourDue, DATEPART(N,EstimateTimeDue) AS MinuteDue, E.JobWorkType, E.ServiceCommercial, E.BidBond, E.SiteVisit, E.WageType, CONCAT(TRIM(Estimators.EstimatorFirstName),' ', LEFT(Estimators.EstimatorLastName,1), '.') AS EstimatorNm, E.BudgetEstimate, STUFF((SELECT ', ' + RS.[SystemType] FROM [dbo].[EstRoofSystems] RS WHERE RS.[EstimateID] = E.[ID] FOR XML PATH('')), 1, 2, '') AS SystemTypes, STUFF((SELECT ', ' + CAST(RS.[Price] AS VARCHAR) FROM [dbo].[EstRoofSystems] RS WHERE RS.[EstimateID] = E.[ID] FOR XML PATH('')), 1, 2, '') AS Prices, STUFF((SELECT ', ' + R.[RoofSystemDesc] FROM [dbo].[EstRoofSystems] RS INNER JOIN [dbo].[RoofSystems] R ON RS.[RoofSysID] = R.[ID] WHERE RS.[EstimateID] = E.[ID] FOR XML PATH('')), 1, 2, '') AS RoofSystems, NULL AS EndDate, --Adding column to accommodate any scheduled event's ending dates from the SchedEvents table. E.EstimateNotes, NULL AS eventType, --eventType NULL AS who --who FROM Estimates E LEFT JOIN Estimators ON Estimators.EstimatorID = E.EstimatorID WHERE (LEFT(E.EstimateNumber,1)='E') AND E.RemoveFromSchedule = 0 AND ((((E.WonLost) Not In ('W','L'))) OR (((E.WonLost) Is Null)) OR (((E.WonLost) In ('W','L')) AND ((E.KeepOnEstSchedule)=1))) UNION ALL -- Selecting all records from SchedEvents table SELECT S.ID, S.estimateNum, S.divisionNumber, S.deptumber, S.eventType, -- JobName NULL, -- JobLocationCity NULL, -- JobLocationState S.startDate, -- EstimateDateDue S.eventTime, -- EstimateTimeDue NULL, -- ExtendedDate NULL, -- EstimateNotes NULL, -- OutstandingDate NULL, -- GCorCMorOwner NULL, -- NameOfGCorCMorO S.startDate, -- DateDue NULL, -- HourDue NULL, -- MinuteDue NULL, -- JobWorkType NULL, -- ServiceCommercial NULL, -- BidBond NULL, -- SiteVisit NULL, -- WageType NULL, -- EstimatorNm NULL, -- BudgetEstimate NULL, -- SystemTypes NULL, -- Prices NULL, -- RoofSystems CASE WHEN S.startDate = S.endDate THEN NULL ELSE S.endDate END AS EndingDate, NULL, -- EstimateNotes S.eventType, S.who FROM SchedEvents S;

Thanks in advance.

Re: Paginated Reports selecting dataset "An item with the same key has already been added." SQL Union..?

amitchandak — Tue, 25 Jun 2024 15:14:11 GMT

EdgeExtension35 , small change in datepart for datatype,

try now

UNION ALL

-- Selecting all records from SchedEvents table
SELECT
S.ID,
S.estimateNum AS EstimateNumber,
S.divisionNumber,
S.deptNumber AS deptNumber, -- Corrected deptumber to deptNumber
S.eventType AS JobName, -- JobName
NULL AS JobLocationCity,
NULL AS JobLocationState,
S.startDate AS EstimateDateDue, -- EstimateDateDue
S.eventTime AS EstimateTimeDue, -- EstimateTimeDue
NULL AS ExtendedDate,
NULL AS EstimateNotes,
NULL AS OutstandingDate,
NULL AS GCorCMorOwner,
NULL AS NameOfGCorCMorO,
S.startDate AS DateDue, -- DateDue
NULL AS HourDue,
NULL AS MinuteDue,
NULL AS JobWorkType,
NULL AS ServiceCommercial,
NULL AS BidBond,
NULL AS SiteVisit,
NULL AS WageType,
NULL AS EstimatorNm,
NULL AS BudgetEstimate,
NULL AS SystemTypes,
NULL AS Prices,
NULL AS RoofSystems,
CASE
WHEN S.startDate = S.endDate THEN NULL
ELSE S.endDate
END AS EndDate,
NULL AS EstimateNotes,
S.eventType,
S.who
FROM
SchedEvents S;

Re: Paginated Reports selecting dataset "An item with the same key has already been added." SQL Union..?

EdgeExtension35 — Tue, 25 Jun 2024 17:46:31 GMT

I'm still getting the same error message about the duplicate item key, unfortunately.