added two more user parameters

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.