question

Binod avatar image
Binod asked

SSRS Parameter Problem

Hello All, Please help me . i have designing a report with two parameter AND Used code as WHERE (Catalog_Code = @CatCode AND Part_type =@PartType) AND (catalog_code =@CatCode) AND (Part_type =@PartType) when i am supplying value for both parameter its giving correct output but when i supplied value for 1 keeping other as null give me blank result When i changed code as below WHERE (Catalog_Code = @CatCode AND Part_type =@PartType) OR (catalog_code =@CatCode) OR (Part_type =@PartType) getting correct result for Individual parameter but when supplying value for both parameter i am getting result for other parameter too. i need to get value only for the value supplied to the both parameter. or value supplied to the individual Parameter For Ex: When @catcode= BRU give result for BRU When @PartType =Nescafe give result for BRU but when supply value @catcode= BRU and @PartType =Nescafe then result set only contain data for BRU and Nescafe but in my case i am getting data Even for peanut with BRU and Nescafe Thanks a Lot..... kindly help.
ssrs
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Hi Binod The approach I use is like this: WHERE (@CatCode IS NULL OR Catalog_Code = @CatCode) AND (@PartType IS NULL OR Part_type = @PartType) The effect is to deal with each parameter separately and ignore that parameter if no value was provided.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank You Very Much David......
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
what happens when you change the query to simply WHERE (Catalog_Code = @CatCode OR Part_type =@PartType) I don't think you need the other clauses!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

then it gives result for other catcode and part type too because same catcode is also present with other part type, and same part type also present with other catcode. SO When applying "OR" will not work when value passed for both parameter.And applying "And" will not work when any one parameter is NULL. Please provide your valuable suggestion.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.