March 3, 2011 at 4:38 am
I've plenty of experience using SSRS 05 but this is the first time i've used SSRS 08.
I'm trying to create a report and I've created my SP which works fine as a standalone query and also as an SP. It doesn't require any parameters.
The issue I have is that when I try to add it to the report, I get an error which is "An item with the same key has already been added."
There are no other datasets in the report so i'm not sure what its getting at. I have been messing about with this for most of the morning and have tried various things, including rewriting the SP, renaming it etc even deleting the report and starting again, but I keep getting this error. For all those of you who will say its my SP, the SQL code is below. FYI, I've tried it with temp tables and allsorts of things like that and have got errors all along the way.
CREATE PROCEDURE [dbo].[SP_RPT_OutstandingTasksList]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-----------------------------------------------------------------------------------
DECLARE @dteToday DATETIME
SET @dteToday = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))
-----------------------------------------------------------------------------------
SELECT DISTINCT
t.task_id
,t.Task_Name
,t.Task_Description
,t.Task_Start_Date
,t.Task_End_Date
,CASE
WHEN t.Task_End_Date < GETDATE() THEN 'Overdue'
WHEN t.Task_End_Date BETWEEN @dteToday AND DATEADD(DAY,1,@dteToday) THEN 'Due Today'
WHEN t.Task_End_Date BETWEEN DATEADD(DAY,1,@dteToday) AND DATEADD(DAY,3,@dteToday) THEN 'Due In Next 2 Days'
ELSE 'Due in 2 Days Plus'
END AS TaskStatusDescription
,t.Task_Percent_Complete
,ut.task_id
,u3.FullName as AssignedTo
,(CASE WHEN t.Task_End_Date < GETDATE() THEN 1 ELSE 0 END) AS Overdue
,(CASE WHEN t.Task_End_Date BETWEEN @dteToday AND DATEADD(DAY,1,@dteToday) THEN 1 ELSE 0 END) AS DueToday
,(CASE WHEN t.Task_End_Date BETWEEN DATEADD(DAY,1,@dteToday) AND DATEADD(DAY,3,@dteToday) THEN 1 ELSE 0 END) AS DueInNext2Days
,(CASE WHEN t.Task_End_Date > DATEADD(DAY,3,@dteToday) THEN 1 ELSE 0 END) AS DueIn2DaysPlus
FROM (
SELECT *
FROM OPENQUERY(Project,'SELECT task_id,task_name,task_start_date,task_end_date,task_description,task_creator,task_owner,task_contacts,Task_Percent_Complete FROM tasks WHERE Task_Percent_Complete < 100')
) t
LEFT JOIN (
SELECT *
FROM OPENQUERY(Project,'SELECT User_ID,Task_ID FROM User_Tasks')
) ut ON ut.task_id=t.task_id
LEFT JOIN (
SELECT
[user_id]
,user_username as Username
,contact_first_name + ' ' + contact_last_name as FullName
FROM OPENQUERY(Project,'SELECT u.user_id,u.user_contact,u.user_username,c.contact_id,c.contact_first_name,c.contact_last_name
FROM Users u
INNER JOIN contacts c ON c.contact_id=u.user_contact')
) u3 ON u3.[user_id]=ut.[User_ID]
WHERE
t.Task_Percent_Complete < 100
AND ut.[User_ID] IS NOT NULL
AND ut.task_id NOT IN (1,5,14,24,30,33,37)
ORDER BY u3.FullName,t.Task_End_Date
I'd appreciate any ideas if anyone thinks the know what's going on here.
Thanks.
March 3, 2011 at 4:59 am
Problem solved. I had two fields of the same name in my query. Why can't Microsoft come up with more helpful error messages? Grrr.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply