Solved

SSRS - Item with same key already added

Posted on 2014-03-20
3
459 Views
Last Modified: 2014-03-20
Using BIDS in Visual Studio 2012. I'm building a report based on a stored procedure. In order to reduce the number of stored procedures in the database (there are already many hundres, many of which are unused and have unknown effects! We're in the process of migrating to a new DB) I've added a parameter to the stored procedure which returns a slightly different result set depending on requirements.
This means that the same field name appears twice in the stored procedure which I understand is what causes this error, even thought the same field is never actually returned twice due to the SP parameter. So the question I have is whether there is any way to get around this on SSRS without having to split my code into two SPs? For reference, here is the stored procedure:

ALTER PROCEDURE [dbo].[usp_Students_AttainedLessThanTwoDs] @Letters BIT AS
BEGIN
SET NOCOUNT ON

IF @Letters = 0
BEGIN
SELECT
	SCT.[Admission Number],
	SPT.Surname,
	SPT.[Forename(s)] AS Forename,
	SPT.Surname + ' ' + SPT.[Forename(s)] AS StudentName,
	SPT.[Tutor Group],
	SPT.[Parents/Guardians],
	ISNULL(SPT.Address1, '') AS Address1,
	ISNULL(SPT.Address2, '') AS Address2,
	ISNULL(SPT.Address3, '') AS Address3,
	ISNULL(SPT.Postcode, '') AS Postcode,
	SPT.[Phone Home],
	SCT.Course,
	SCT.Attain2,
	SCT.Target2,
	SCT.Effort2,
	SCT.Attendance2,
	TGT.TTL,
	SCT.BenchMark,
	CASE RIGHT(SCT.[Subject Code], 1)
		WHEN 'D' THEN CASE WHEN SCT.Attain2 = 'PaPa'
			THEN 2
		END
		WHEN 'C' THEN CASE WHEN SCT.Attain2 = 'Pa' 
			THEN 1
		END
		ELSE 0
	END AS Mkr,
	CDT.Department,
	DT.HoD,
	SCT.Consult1,
	SCT.Consult2,
	CASE SPT.Sex
		WHEN 'M' THEN 'he'
		ELSE 'she'
	END AS HeShe,
	CASE SPT.Sex
		WHEN 'M' THEN 'his'
		ELSE 'her'
	END AS HisHer
FROM
	(
		SELECT
			SCT.[Admission Number],
			SUM(CASE WHEN RIGHT(SCT.[Subject Code], 1) IN ('B','D') 
					THEN 2
					ELSE 1
				END) AS Num2Ds
		FROM 
			StudentCoursesTable SCT JOIN
				StudentPersonalTable SPT ON
				SCT.[Admission Number] = SPT.[Admission Number]
		WHERE
			SCT.[Course End Date] IS NULL AND
				LEFT(SCT.Course, 1) = '1' AND
					SPT.[Level] = 1 AND
						SCT.Attain2 IN('U','E','EE','Pa','PaPa') AND
							RIGHT(SCT.[Subject Code], 1) <> 'S'
		GROUP BY
			SCT.[Admission Number]
	) TwoD JOIN
		StudentPersonalTable SPT ON
		TwoD.[Admission Number] = SPT.[Admission Number] JOIN
			TutorGroupTable TGT ON
			SPT.[Tutor Group] = TGT.TutorGroup JOIN
				StudentCoursesTable SCT ON
				SPT.[Admission Number] = SCT.[Admission Number] JOIN
					CourseDescriptionTable CDT ON
					SCT.[Subject Code] = CDT.[Course Code] JOIN
						DepartmentTable DT ON
						CDT.Department = DT.Department JOIN
							dbo.udf_Students_CourseCount(NULL) SCC ON
							SPT.[Admission Number] = SCC.AdmissionNumber
WHERE
	SCT.Attain2 IS NOT NULL AND
		LEFT(SCT.Course, 1) = '1' AND
			SCC.CourseCount - TwoD.Num2Ds < 2 AND
				SCT.[Course End Date] IS NULL
END
ELSE

SELECT DISTINCT
	SCT.[Admission Number],
	SPT.Surname,
	SPT.[Forename(s)] AS Forename,
	SPT.Surname + ' ' + SPT.[Forename(s)] AS StudentName,
	SPT.[Tutor Group],
	TGT.TTL,
	SPT.[Parents/Guardians],
	ISNULL(SPT.Address1, '') AS Address1,
	ISNULL(SPT.Address2, '') AS Address2,
	ISNULL(SPT.Address3, '') AS Address3,
	ISNULL(SPT.Postcode, '') AS Postcode,
	SPT.[Phone Home],
	CASE SPT.Sex
		WHEN 'M' THEN 'he'
		ELSE 'she'
	END AS HeShe,
	CASE SPT.Sex
		WHEN 'M' THEN 'his'
		ELSE 'her'
	END AS HisHer
FROM
	(
		SELECT
			SCT.[Admission Number],
			SUM(CASE WHEN RIGHT(SCT.[Subject Code], 1) IN ('B','D') 
					THEN 2
					ELSE 1
				END) AS Num2Ds
		FROM 
			StudentCoursesTable SCT JOIN
				StudentPersonalTable SPT ON
				SCT.[Admission Number] = SPT.[Admission Number]
		WHERE
			SCT.[Course End Date] IS NULL AND
				LEFT(SCT.Course, 1) = '1' AND
					SPT.[Level] = 1 AND
						SCT.Attain2 IN('U','E','EE','Pa','PaPa') AND
							RIGHT(SCT.[Subject Code], 1) <> 'S'
		GROUP BY
			SCT.[Admission Number]
	) TwoD JOIN
		StudentPersonalTable SPT ON
		TwoD.[Admission Number] = SPT.[Admission Number] JOIN
			TutorGroupTable TGT ON
			SPT.[Tutor Group] = TGT.TutorGroup JOIN
				StudentCoursesTable SCT ON
				SPT.[Admission Number] = SCT.[Admission Number] JOIN
					CourseDescriptionTable CDT ON
					SCT.[Subject Code] = CDT.[Course Code] JOIN
						DepartmentTable DT ON
						CDT.Department = DT.Department JOIN
							dbo.udf_Students_CourseCount(NULL) SCC ON
							SPT.[Admission Number] = SCC.AdmissionNumber
WHERE
	SCT.Attain2 IS NOT NULL AND
		LEFT(SCT.Course, 1) = '1' AND
			SCC.CourseCount - TwoD.Num2Ds < 2 AND
				SCT.[Course End Date] IS NULL
SET NOCOUNT OFF
RETURN
END

Open in new window

0
Comment
Question by:Karl_mark
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39942229
>I've added a parameter to the stored procedure which returns a slightly different result set depending on requirements.
>This means that the same field name appears twice in the stored procedure

Bad idea.  Bad bad bad.   SSRS, like SSIS, requires a 'contract' with a data source where the schema does not change.  Any changes result in breaking the stored mapping of columns, and throw an error.

I recommend that whatever columns are 'conditionally' returned based on the parameter, change it so that the column is always returned, but only populated based on the condition.

Also, column names need to be unique.
0
 

Author Closing Comment

by:Karl_mark
ID: 39942321
I already know that column names need to be unique! That is always the first port of call so to speak and was already mentioned. My question was whether it was possible to do the above in SSRS and it seems not.
Your recommendation is interesting however. By adding CASE statements to each of the fields I do not want to see I can return NULL and therefore remove the duplicates with DISTINCT. A useful idea so thanks for that!
For reference, the new SP looks like:

ALTER PROCEDURE [dbo].[usp_Students_AttainedLessThanTwoDs] @Letters BIT AS
BEGIN
SET NOCOUNT ON


SELECT DISTINCT
	SCT.[Admission Number],
	SPT.Surname,
	SPT.[Forename(s)] AS Forename,
	SPT.Surname + ' ' + SPT.[Forename(s)] AS StudentName,
	SPT.[Tutor Group],
	SPT.[Parents/Guardians],
	ISNULL(SPT.Address1, ''),
	ISNULL(SPT.Address2, ''),
	ISNULL(SPT.Address3, ''),
	ISNULL(SPT.Postcode, ''),
	SPT.[Phone Home],
	CASE @Letters
		WHEN 0 THEN SCT.Course
		ELSE NULL
	END AS Course,
	CASE @Letters
		WHEN 0 THEN SCT.Attain2
		ELSE NULL
	END AS Attain2,
	CASE @Letters
		WHEN 0 THEN SCT.Target2
		ELSE NULL
	END AS Target2,
	CASE @Letters
		WHEN 0 THEN SCT.Effort2
		ELSE NULL 
	END AS Effort2,
	CASE @Letters
		WHEN 0 THEN SCT.Attendance2
		ELSE NULL
	END AS Attendance2,
	TGT.TTL,
	CASE @Letters
		WHEN 0 THEN SCT.BenchMark
		ELSE NULL
	END AS Benchmark,
	CASE @Letters
		WHEN 0 THEN 
			CASE RIGHT(SCT.[Subject Code], 1)
				WHEN 'D' THEN CASE WHEN SCT.Attain2 = 'PaPa'
					THEN 2
				END
				WHEN 'C' THEN CASE WHEN SCT.Attain2 = 'Pa' 
					THEN 1
				END
				ELSE 0
			END 
		ELSE NULL
	END AS Mkr,
	CASE @Letters
		WHEN 0 THEN CDT.Department
		ELSE NULL 
	END AS Department,
	CASE @Letters
		WHEN 0 THEN DT.HoD
		ELSE NULL
	END AS HoD,
	CASE @Letters
		WHEN 0 THEN SCT.Consult1
		ELSE NULL
	END AS Consult1,
	CASE @Letters
		WHEN 0 THEN SCT.Consult2
		ELSE NULL
	END AS Consult2,
	CASE SPT.Sex
		WHEN 'M' THEN 'he'
		ELSE 'she'
	END AS HeShe,
	CASE SPT.Sex
		WHEN 'M' THEN 'his'
		ELSE 'her'
	END AS HisHer
FROM
	(
		SELECT
			SCT.[Admission Number],
			SUM(CASE WHEN RIGHT(SCT.[Subject Code], 1) IN ('B','D') 
					THEN 2
					ELSE 1
				END) AS Num2Ds
		FROM 
			StudentCoursesTable SCT JOIN
				StudentPersonalTable SPT ON
				SCT.[Admission Number] = SPT.[Admission Number]
		WHERE
			SCT.[Course End Date] IS NULL AND
				LEFT(SCT.Course, 1) = '1' AND
					SPT.[Level] = 1 AND
						SCT.Attain2 IN('U','E','EE','Pa','PaPa') AND
							RIGHT(SCT.[Subject Code], 1) <> 'S'
		GROUP BY
			SCT.[Admission Number]
	) TwoD JOIN
		StudentPersonalTable SPT ON
		TwoD.[Admission Number] = SPT.[Admission Number] JOIN
			TutorGroupTable TGT ON
			SPT.[Tutor Group] = TGT.TutorGroup JOIN
				StudentCoursesTable SCT ON
				SPT.[Admission Number] = SCT.[Admission Number] JOIN
					CourseDescriptionTable CDT ON
					SCT.[Subject Code] = CDT.[Course Code] JOIN
						DepartmentTable DT ON
						CDT.Department = DT.Department JOIN
							dbo.udf_Students_CourseCount(NULL) SCC ON
							SPT.[Admission Number] = SCC.AdmissionNumber
WHERE
	SCT.Attain2 IS NOT NULL AND
		LEFT(SCT.Course, 1) = '1' AND
			SCC.CourseCount - TwoD.Num2Ds < 2 AND
				SCT.[Course End Date] IS NULL



SET NOCOUNT OFF
RETURN
END

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39942415
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now