asked on

SSRS - Item with same key already added

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karl_mark

ASKER

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

Jim Horn

Thanks for the grade. Good luck with your project. -Jim