Replacing where clause dynamically in query

In my report query I have a where clause that needs to be replaced dynamically based on the data chosen in the front end.

The query is something like :


<select statement>

where ?=?



I already have a code to replace the value - I created report parameter and linked to the value ? in the query. 

<select statement>

where name=?


Any value of name that comes from front end replaces the ? in the where clause - this works fine.



But now I need to replace the entire clause. Should I create two parameters and link them to both the '?' ?

Can anyone help with this?

Thanks !


  • edited February 19, 2016

    One option is to change your query to:


    select x from y where myplaceholder1 = 'myplaceholder2'


    Then add the parameter values in code.  That is done by selecting the data set and clicking the Script tab.  Go to the beforeOpen event and enter the following:


    this.queryText = this.queryText.replace("myplaceholder1", params["p1"].value).replace("myplaceholder2", params["p2"].value);


    (replace "p1" and "p2" with the names of your two parameters)

    Jeff F.
    Customer Support Engineer I
  • Thanks for responding.

    But in my report the input parameters are not fixed, as in there could be one or two or even no input parameter. How do I alter the query based on the number of input parameters? 

  • Hi New_Bee,


    defining a query dynamically is done with coding i.e. you can add or replace part of the query depending on many paramters.

    for instance you can have things like

    var myWhere = " where (1=1) ";
    if (params["p1"] == 'A' && params["p2"] == 'B') var myWhere = " and field1=" + params["p3"];
    if (params["p1"] == 'A' && params["p2"] != 'B') var myWhere = " and field1=" + params["p4"];
    if (params["p1"] != 'A' && params["p2"] == 'B') var myWhere = " and field1=" + params["p5"];
    if (params["p1"] != 'A' && params["p2"] != 'B') var myWhere = " and field1=" + params["p6"];
    this.queryText += myWhere; 

    Take a look on this thread here

    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
Sign In or Register to comment.