Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In SSRS, why do I get the error "item with same key has already been added" , when I'm making a new report?

I'm getting the following error in SSRS:

An error occurred while the query design method was being saved.
An item with the same key has already been added

What does an "item" denote, though? I even tried editing the RDL and deleting all references to the Stored Procedure I need to use called prc_RPT_Select_BI_Completes_Data_View.

Could this possibly have to do with the fact that the Stored Procedure uses Dynamic SQL (the N' notation)?

In the stored procedure I have:

SET @SQL +=  N' SELECT   bi.SupplierID as ''Supplier ID''         ,bi.SupplierName as ''Supplier Name''          ,bi.PID as ''PID''         ,bi.RespondentID as ''Respondent ID''          ,lk_slt.Name as ''Entry Link Type''          ,ts.SurveyNumber as ''Initial Survey ID''' 

enter image description here

like image 230
Caffeinated Avatar asked Jan 22 '13 19:01

Caffeinated


People also ask

How do I refresh data in SSRS report?

In the Report Data pane, right-click the dataset, and then click Query. Click Refresh Fields.

How do I make changes to an existing SSRS report?

Right click Reports and add an existing item. Select an existing RDL file to add it to the project. Double click it to open it. You will now be able to see and edit the report in Visual Studio.

How do I refresh a stored procedure in SSRS?

While creating any SSRS report using stored procedure, you will first create dataset for it. Select stored procedure in dropdown and click refresh fields.


1 Answers

It appears that SSRS has an issue(at leastin version 2008) - I'm studying this website that explains it

Where it says if you have two columns(from 2 diff. tables) with the same name, then it'll cause that problem.

From source:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99 FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

like image 72
Caffeinated Avatar answered Oct 07 '22 05:10

Caffeinated