CODEWITHSUNDEEP
sqlt-sqlstored-proceduresreporting-services
user2040021
user2040021

Reputation: 309

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.

Upvotes: 0

Views: 890

Answers (1)

R. Richards
R. Richards

Reputation: 25151

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.

Upvotes: 2

Related Questions