0

I am building report in SSRS 2008 which queries Oracle. I have 3 text parameters build into dataset query - they should work in a way that if left blank they should include everything. If I put a value in each parameter - report returns value. But as soon as I remove one of the values - leaving box blank - report returns nothing (where in fact it should return more resulting rows). See query below. I was trying to troubleshoot it for some last 2 days , trying to find some onswers in the internet, looking at session details - no luck. I would appreciate any help, thanks.

select  WI_NUM , COMPONENT_NUM , LOT_NUM 
from CONS 
where  
(:WI_NUM = '' OR WI_NUM = :WI_NUM) 
and 
(:COMP_NUM  = '' OR COMPONENT_NUM = :COMP_NUM) 
and 
(:LOT_NUM  = '' OR LOT_NUM = :LOT_NUM)

I also tried that but same problem:

where  
(:WI_NUM is NULL OR WI_NUM = :WI_NUM) 
and 
(:COMP_NUM  is NULL OR COMPONENT_NUM = :COMP_NUM) 
and 
(:LOT_NUM  is NULL OR LOT_NUM = :LOT_NUM)

All parameters are set as data type text, allow blanks, not allow nulls(not ticked).

wiciuo
  • 95
  • 1
  • 9
  • The parameters must not actually be blank. Have you tried displaying the values for the parameters in the report or pulling them into the dataset? I'd also check the length of the parameter value to ensure it isn't containing multiple spaces. I've never used SSRS with an Oracle database so I don't know if there would be any differences but your code looks like it should work to me. – Mike D. Sep 23 '14 at 13:04
  • possible [duplicate](http://stackoverflow.com/questions/17681428/issue-with-oracle-bind-variables-not-using-index-properly/17702034) – tbone Sep 23 '14 at 13:31
  • Hi. I build dataset which checks if parater is blank: CASE when :WI_NUM is NULL THEN 1 ELSE 0 end FIELD1 and it is showing 1 when parater is empty, yet report doesn't return anything. This is depressing. – wiciuo Sep 23 '14 at 14:12
  • @tbone - Thanks, I checked the link, although it is interesting idea I cannot apply it in my situation , I cannot create procedures on DB. Instead I would rather like to find out why the basic approach I applied does not work. – wiciuo Sep 23 '14 at 14:45

1 Answers1

0

The report was build by modyfying previous report. I decided to build report from scratch and it worked straight away. Must have been something wrong with the template. Thanks @Mike. D , @Aftab and @tbone for all your suggestions - they were good pieces of advice.

wiciuo
  • 95
  • 1
  • 9