locked
An item with the same key has already been added RRS feed

  • Question

  • When I try to create a new dataset with the query below I get an error message. After looking through different forums the most likely cause of the error message is that I have two or more columns with the same name. I have tried to index all fields but the message still occurs. The query runs fine in query designer and Server Management Studio. I also get the message that Query Designer cannot parse the query.

    WITH CTE AS (SELECT     MONTH(oms.[Posting Date]) AS 'MonthNo',
                            CASE MONTH(oms.[Posting Date])
                                 WHEN 1 THEN 'Jan'
                                 WHEN 2 THEN 'Feb'
                                 WHEN 3 THEN 'Mar'
                                 WHEN 4 THEN 'Apr'
                                 WHEN 5 THEN 'Maj'
                                 WHEN 6 THEN 'Jun'
                                 WHEN 7 THEN 'Jul'
                                 WHEN 8 THEN 'Aug'
                                 WHEN 9 THEN 'Sep'
                                 WHEN 10 THEN 'Okt'
                                 WHEN 11 THEN 'Nov'
                                 WHEN 12 THEN 'Dec' END AS EXPR1,
                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) - 1 THEN oms.[Oms sek] ELSE 0 END) AS 'CY1',
                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) THEN oms.[Oms sek] ELSE 0 END) AS CY, 0 AS Budget
    FROM          Vy_omsättning AS oms
    WHERE oms.Marknad=1
    GROUP BY MONTH(oms.[Posting Date])
                                   UNION ALL
    SELECT     Month(bud.[Datum]) AS 'MonthNo',
                            CASE MONTH(bud.[Datum])
                                WHEN 1 THEN 'Jan'
                                WHEN 2 THEN 'Feb'
                                WHEN 3 THEN 'Mar'
                                WHEN 4 THEN 'Apr'
                                WHEN 5 THEN 'Maj'
                                WHEN 6 THEN 'Jun'
                                WHEN 7 THEN 'Jul'
                                WHEN 8 THEN 'Aug'
                                WHEN 9 THEN 'Sep'
                                WHEN 10 THEN 'Okt'
                                WHEN 11 THEN 'Nov'
                                WHEN 12 THEN 'Dec' END AS EXPR1,
                                0 AS 'CY1',
                                0 AS CY,
                         SUM(CASE WHEN YEAR(bud.[Datum]) = YEAR(GETDATE()) THEN bud.[Dagsbudget_LC] ELSE 0 END) AS 'Budget'
    FROM         DagsBudget AS bud
    WHERE bud.Marknad=1
    GROUP BY MONTH(bud.[Datum]))
    SELECT    cte.MonthNo, cte.EXPR1, SUM(cte.CY1), SUM(cte.CY), SUM(cte.Budget)
    FROM         CTE as cte
        
    GROUP BY cte.MonthNo, cte.EXPR1
    ORDER BY cte.MonthNo

    Thursday, January 26, 2012 2:23 PM

Answers

    • Edited by Ursa Pangos Thursday, January 26, 2012 3:12 PM
    • Marked as answer by SPEHE Friday, January 27, 2012 7:42 AM
    Thursday, January 26, 2012 3:00 PM
  • Hi SPEHE,

    Thanks for your post.

    I would suggest you modify your query string like below to have a try:
    WITH CTE AS (SELECT     MONTH(oms.[Posting Date]) AS MonthNo,

                            CASE MONTH(oms.[Posting Date])

                                 WHEN 1 THEN 'Jan'

                                 WHEN 2 THEN 'Feb'

                                 WHEN 3 THEN 'Mar'

                                 WHEN 4 THEN 'Apr'

                                 WHEN 5 THEN 'Maj'

                                 WHEN 6 THEN 'Jun'

                                 WHEN 7 THEN 'Jul'

                                 WHEN 8 THEN 'Aug'

                                 WHEN 9 THEN 'Sep'

                                 WHEN 10 THEN 'Okt'

                                 WHEN 11 THEN 'Nov'

                                 WHEN 12 THEN 'Dec' END AS EXPR1,

                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) - 1 THEN oms.[Oms sek] ELSE 0 END) AS CY1,

                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) THEN oms.[Oms sek] ELSE 0 END) AS CY, 0 AS Budget

    FROM          Vy_omsättning AS oms

    WHERE oms.Marknad=1

    GROUP BY MONTH(oms.[Posting Date])

                                   UNION ALL

    SELECT     Month(bud.[Datum]) AS MonthNo,

                            CASE MONTH(bud.[Datum])

                                WHEN 1 THEN 'Jan'

                                WHEN 2 THEN 'Feb'

                                WHEN 3 THEN 'Mar'

                                WHEN 4 THEN 'Apr'

                                WHEN 5 THEN 'Maj'

                                WHEN 6 THEN 'Jun'

                                WHEN 7 THEN 'Jul'

                                WHEN 8 THEN 'Aug'

                                WHEN 9 THEN 'Sep'

                                WHEN 10 THEN 'Okt'

                                WHEN 11 THEN 'Nov'

                                WHEN 12 THEN 'Dec' END AS EXPR1,

                                0 AS CY1,

                                0 AS CY,

                         SUM(CASE WHEN YEAR(bud.[Datum]) = YEAR(GETDATE()) THEN bud.[Dagsbudget_LC] ELSE 0 END) AS Budget

    FROM         DagsBudget AS bud

    WHERE bud.Marknad=1

    GROUP BY MONTH(bud.[Datum]))

    SELECT    cte.MonthNo, cte.EXPR1, SUM(cte.CY1) as sumCY1, SUM(cte.CY) as sumCY, SUM(cte.Budget) as sumBudget

    FROM         CTE as cte

        

    GROUP BY cte.MonthNo, cte.EXPR1

    ORDER BY cte.MonthNo

     

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support

    • Proposed as answer by Sandip Shinde Friday, January 27, 2012 6:23 AM
    • Marked as answer by SPEHE Friday, January 27, 2012 7:41 AM
    Friday, January 27, 2012 5:25 AM

All replies

    • Edited by Ursa Pangos Thursday, January 26, 2012 3:12 PM
    • Marked as answer by SPEHE Friday, January 27, 2012 7:42 AM
    Thursday, January 26, 2012 3:00 PM
  • Hi Spehe,

    In the above pic click on Fields and see which field is repeating  and delete the repeating field


    Hope this will help you !!!
    Sanjeewan
    Thursday, January 26, 2012 5:04 PM
  • Spehe,

    In your top query inside the cte, you are missing column CY. add that and it should be fine.

    good luckkkkk

    Edit: Also, when declaring column name, you don't need to add the quote, like 'MonthNo', just MonthNo. or [MonthNo].
    • Edited by i_h Thursday, January 26, 2012 5:25 PM
    Thursday, January 26, 2012 5:24 PM
  • Same name for more than columns is almost always the culprit. If that's not the case for you, try deleting and recreating the data set. No valid explanation but it might work. :-)

    http://svangasql.wordpress.com/2011/11/10/ssrs-an-item-with-the-same-key-has-already-been-added/

    Thursday, January 26, 2012 9:17 PM
  • Hi SPEHE,

    Thanks for your post.

    I would suggest you modify your query string like below to have a try:
    WITH CTE AS (SELECT     MONTH(oms.[Posting Date]) AS MonthNo,

                            CASE MONTH(oms.[Posting Date])

                                 WHEN 1 THEN 'Jan'

                                 WHEN 2 THEN 'Feb'

                                 WHEN 3 THEN 'Mar'

                                 WHEN 4 THEN 'Apr'

                                 WHEN 5 THEN 'Maj'

                                 WHEN 6 THEN 'Jun'

                                 WHEN 7 THEN 'Jul'

                                 WHEN 8 THEN 'Aug'

                                 WHEN 9 THEN 'Sep'

                                 WHEN 10 THEN 'Okt'

                                 WHEN 11 THEN 'Nov'

                                 WHEN 12 THEN 'Dec' END AS EXPR1,

                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) - 1 THEN oms.[Oms sek] ELSE 0 END) AS CY1,

                             SUM(CASE WHEN YEAR(oms.[Posting Date]) = YEAR(GETDATE()) THEN oms.[Oms sek] ELSE 0 END) AS CY, 0 AS Budget

    FROM          Vy_omsättning AS oms

    WHERE oms.Marknad=1

    GROUP BY MONTH(oms.[Posting Date])

                                   UNION ALL

    SELECT     Month(bud.[Datum]) AS MonthNo,

                            CASE MONTH(bud.[Datum])

                                WHEN 1 THEN 'Jan'

                                WHEN 2 THEN 'Feb'

                                WHEN 3 THEN 'Mar'

                                WHEN 4 THEN 'Apr'

                                WHEN 5 THEN 'Maj'

                                WHEN 6 THEN 'Jun'

                                WHEN 7 THEN 'Jul'

                                WHEN 8 THEN 'Aug'

                                WHEN 9 THEN 'Sep'

                                WHEN 10 THEN 'Okt'

                                WHEN 11 THEN 'Nov'

                                WHEN 12 THEN 'Dec' END AS EXPR1,

                                0 AS CY1,

                                0 AS CY,

                         SUM(CASE WHEN YEAR(bud.[Datum]) = YEAR(GETDATE()) THEN bud.[Dagsbudget_LC] ELSE 0 END) AS Budget

    FROM         DagsBudget AS bud

    WHERE bud.Marknad=1

    GROUP BY MONTH(bud.[Datum]))

    SELECT    cte.MonthNo, cte.EXPR1, SUM(cte.CY1) as sumCY1, SUM(cte.CY) as sumCY, SUM(cte.Budget) as sumBudget

    FROM         CTE as cte

        

    GROUP BY cte.MonthNo, cte.EXPR1

    ORDER BY cte.MonthNo

     

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support

    • Proposed as answer by Sandip Shinde Friday, January 27, 2012 6:23 AM
    • Marked as answer by SPEHE Friday, January 27, 2012 7:41 AM
    Friday, January 27, 2012 5:25 AM
  • Thank you all!

    Bill, I copied your suggestion and it worked fine, but I am not sure what you changed?

    Friday, January 27, 2012 7:41 AM
  • SPEHE,

    Reporting services is poor on naming the unnamed column with identifer, whenever the select statement contains more than one unnamed columns, the reporting services dataset fields name generater may name them with the same field name, which would fire the error you encountered above.

    SELECT cte.MonthNo, cte.EXPR1, SUM(cte.CY1) as sumCY1, SUM(cte.CY) as sumCY, SUM(cte.Budget) as sumBudget

    FROM  CTE as cte

    GROUP BY cte.MonthNo, cte.EXPR1

    ORDER BY cte.MonthNo

    Addtionally, I don’t know why you mark Ursa’s reply as answer, it seems like your situation is completely different with the link’s scenario she provided, if I misunderstand, please not hesitate to correct me.

    Thanks,
    Bill Lu

     

    Bill Lu

    TechNet Community Support



    • Edited by Zilong Lu Friday, January 27, 2012 8:31 AM
    Friday, January 27, 2012 8:31 AM
  • OK, then I understand more.

    In my point of view both you and Ursa provided the right answer, even if your answer was more exact. I do not really know what marking answers mean and if I have done it in a wrong way I apologies.

    Thank you again for your valuable help. I have now posted a follow up to this problem, since I get an error message when I execute the report. :o(

    Friday, January 27, 2012 8:39 AM