user2040021 user2040021 - 5 months ago 25
SQL Question

Getting error: An item with the same key has already been added - error/warning in SSRS report

I tried a lot to dig into this issue. finally came down to point where it's causing this "An item with the same key has already been added.", which I belive same key already present, for an example a.field01 and b.field02.

Here is the code

ALTER PROCEDURE [dbo].[Payroll_Report]

@Office NVARCHAR(4000),
@Servicetype NVARCHAR(4000),
@Start DATETIME,
@End DATETIME
AS
BEGIN
SET NOCOUNT ON;

Select
a.owneridname
,a.[serviceidname]
,count(case
when
a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.serviceid
end) As 'Total# Confirmed Activities'

,CONVERT( NVARCHAR( 16 ),
FLOOR( ( sum(case
when
a.serviceidname not like 'Indirect' and a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.[scheduleddurationminutes]
end) ) / 60.0 ) )
+ N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
FLOOR( (sum(case
when
a.serviceidname not like 'Indirect'
then
a.[scheduleddurationminutes]
end)) % 60.0 ) )) as 'Total Direct Activites HH:MM'

,CONVERT( NVARCHAR( 16 ),
FLOOR( ( sum(case
when
a.serviceidname like 'Indirect' and a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.[scheduleddurationminutes]
end) ) / 60.0 ) )
+ N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
FLOOR( (sum(case
when
a.serviceidname like 'Indirect'
then
a.[scheduleddurationminutes]
end)) % 60.0 ) )) as 'Total Indirect Activites HH:MM'


,CONVERT( NVARCHAR( 16 ),
FLOOR( ( sum(case
when
a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.[scheduleddurationminutes]
end) ) / 60.0 ) )
+ N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
FLOOR( (sum(case
when
a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.[scheduleddurationminutes]
end)) % 60.0 ) )) as 'Total Direct Activites HH:MM'

,Sum(Case
when a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved' and a.serviceidname not like 'Indirect'
then
b.new_approvedmileage
end
) as 'Total Approved Mileage (KMs)'


,CONVERT( NVARCHAR( 16 ),
FLOOR( ( sum(case
when
b.new_expensestatusname like 'Approved'
then
b.new_approvedtravel
end) ) / 60.0 ) )
+ N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
FLOOR( (sum(case
when
b.new_expensestatusname like 'Approved'
then
b.new_approvedtravel
end)) % 60.0 ) )) as 'Total Approved Travel Time HH:MM'


,count(Case
when b.new_expensestatusname like 'Approved' and b.new_expensetypeidname like 'Stipends'
then
b.new_expensetypeidname
end
) As 'Total #Approved Stipends'

from
[dbo].[FilteredServiceAppointment] a
join
FilteredSystemUser fu
on a.ownerid = fu.systemuserid
join
.[dbo].[FilteredNew_expenses] b
on
fu.systemuserid = b.new_provider
where
a.scheduledstart BETWEEN @Start AND @End
AND b.new_serviceactivityid is null
AND a.siteidname IN (SELECT value FROM dbo.udf_Split(@Office, ','))
AND a.serviceidname IN (SELECT value FROM dbo.udf_Split(@Servicetype, ','))
group by
a.owneridname
,a.[serviceidname]


END


Everythign loads in when I call above SP into SSRS except 4th column "'Total Direct Activites HH:MM'" which is below from above code.

,CONVERT( NVARCHAR( 16 ),
FLOOR( ( sum(case
when
a.serviceidname not like 'Indirect' and a.[statuscodename] like 'Confirmed' or a.new_approvalstatusname like 'Approved'
then
a.[scheduleddurationminutes]
end) ) / 60.0 ) )
+ N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
FLOOR( (sum(case
when
a.serviceidname not like 'Indirect'
then
a.[scheduleddurationminutes]
end)) % 60.0 ) )) as 'Total Direct Activites HH:MM'


As soon as I uncomment this portion of code it gives me "An item with the same key has already been added." error when I refresh in dataset of SSRS.

PLEASE HELP.

Answer

You have 2 columns with the same exact name Total Direct Activites HH:MM. SSRS cannot deal with that. Change one, then this should work.