I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]
@.Question char(80)
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]
(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo'
AND REGION_KEY=@.Region_Key
AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)
AND Qry_Questions.Question=@.Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
Looks to me like when you're calling the SPROC, you're not passing in all of the Region_Key's or all of the possible Question_Code's. The SPROC syntax looks fine, assuming you're sure that the LEFT function is working correctly.
To find out exactly what's wrong, I would recommend isolating your changes one by one. Test the SPROC with only the Region_Key param and see what you get. If it's right, test with only the Question_Code param. Then try together. Problems like this are frequently caused by you not realizing that there are NULLs or blanks in your data, and so when you pass certain parameters, it will exclude other rows and therefore throw off your totals.
Hope that helps.
|||Just to add to above post, you have defined QuestionCode as char(5) in the param list, yet inside the proc you are doing left(question code, 2). You may be truncating some data and comparing with incorrect data.
|||
It's actually giving me a number way too big. A Sum way more then when i take the paramaters out. The Stored procedure works fine for another report i have with the same detail. But when i added these two additional parameters, for some reason it gives me a number way higher then if it didnt have any parameters at all.
|||Could you post the DDL and input params for the updated SP. I am hoping RegionKey is coming out of Qry_sales_Group and is it part of key or does it have multiple rows for the same value?
|||
I got it to work. I figured out that the sub query needed to be changed for this report because i was totaling based on region and question this time.
Code Snippet
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
INNER JOIN Qry_Sales_Group
ON Qry_Sales_Group.SalesPerson_Purchaser_Code = qq.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS
Where qq.response in ('Yes','No')and Region_Key=@.Region_Key
GROUP by qq.[Question] , qq.Date,qq.response ) B
Thanks for the help. I went and piece every little piece til i relized it wasnt seperating them by regions.
