How to pass blank value for Report parameter

Hi,

I am building a sql query based on report parameter,But I want that if the report paramter is blank then my query should bring all the data.
For example:
select * from abc where param1< ? but if value is blank for param1 then all the values should be fetched.

I tried using beforeOpen () Script of Data set that

if (params["param1"].value==null){
this.queryText = "select * from abc";
}

But it didn't wrked .Can you please suggest me how should I proceed.?

Regards
Shilpa

Find more posts tagged with

Comments

bhanley

The query did not work because the parameter binding was removed when you modified the query and the engine did not know how to proceed. 
 
Try this: 
 
1) Set up your Data Set's query to NOT feature a parameter binding: select * from abc 
 
2) In the beforeOpen script, check your parameter and modify the query if necessary: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (params["param1"].value != null){
this.queryText = this.queryText + " WHERE param1 < " + params["param1"].value;
}
</pre>
 
Essentially you are doing the parameter binding manually to allow for the fact that there may be no parameter to bind to at run-time. 
 
Good Luck!

Shilpa

Thanks Brian,

It worked,but do you have any idea what to do if the parameter is in inner query

bhanley

You could still do the same kind of operation you did here, but rather than simply appending a where clause onto the end of the default query, you would need to do a replace inside the string itself with your dynamic value.

Shilpa

Thanks Brian ,

It helped ,I was not doing some mistake for inner query.

Regards
Shilpa