0

Hoping someone smarter than me can help me out here...

I am getting the error SSRS item with the same key has been added with the below SQL I have tried fining what the issue is with the code, but haven't been able to find it.

Can someone help me out :) 

SELECT 
    Name.ID, Name.COMPANY, Name.CATEGORY, 
    HOUSEPOP.HH_INV AS 'Current Year HH_INV',
    HOUSEPOP_1.HH_INV AS 'Last Year HH_INV', 
    name.MEMBER_TYPE, name.CATEGORY, 
    HOUSEPOP.TOTAL_POP,
    (HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV) AS 'Difference', 
    ((HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV)/HOUSEPOP_1.HH_INV) AS '% Change'
FROM            
    Name 
INNER JOIN
    HOUSEPOP ON Name.ID = HOUSEPOP.ID 
INNER JOIN
    HOUSEPOP AS HOUSEPOP_1 ON Name.ID = HOUSEPOP_1.ID
WHERE
    HOUSEPOP.BILL_YEAR = @Bill_Year 
    AND HOUSEPOP_1.BILL_YEAR = @Bill_Year -1
ORDER BY   
    ID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Taun Brown
  • 55
  • 1
  • 1
  • 8
  • Possible duplicate of [In SSRS, why do I get the error "item with same key has already been added" , when I'm making a new report?](https://stackoverflow.com/questions/14466874/in-ssrs-why-do-i-get-the-error-item-with-same-key-has-already-been-added-wh) – ViKiNG Sep 29 '17 at 02:10

2 Answers2

1

You have 2 CATEGORY columns. SQL Server/SSMS will allow duplicate columns in a query, but SSRS cannot have duplicates. You need to alias it.

SELECT 
        Name.ID, 
        Name.COMPANY, 
        Name.CATEGORY,                --CATEGORY column 1 here
        HOUSEPOP.HH_INV AS 'Current Year HH_INV', 
        HOUSEPOP_1.HH_INV AS 'Last Year HH_INV', 
        name.MEMBER_TYPE, 
        name.CATEGORY AS 'CATEGORY2', --I aliased your 2nd CATEGORY column here
        HOUSEPOP.TOTAL_POP,
        (HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV) as 'Difference',
        ((HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV)/HOUSEPOP_1.HH_INV) as '% Change'
FROM           Name 
INNER JOIN     HOUSEPOP 
ON             Name.ID = HOUSEPOP.ID 
INNER JOIN     HOUSEPOP AS HOUSEPOP_1 
ON             Name.ID = HOUSEPOP_1.ID
WHERE          HOUSEPOP.BILL_YEAR = @Bill_Year 
AND            HOUSEPOP_1.BILL_YEAR = @Bill_Year -1
ORDER BY       ID

I also took a bit of time to format your query so it is readable. This will help you debug (it helps me at least).

edit: Now that I look at your query again, CATEGORY just appears to be duplicated so the answer may just be to remove the second one instead of aliasing it.

Jacob H
  • 2,455
  • 1
  • 12
  • 29
0

You have two fields called "Category" - one after "Company" and one after "Member_Type". SSRS will only allow unique column names.

BishNaboB
  • 1,047
  • 1
  • 12
  • 25