Pass Optional parameter in Report

2»

Comments

  • mwilliams
    edited December 31, 1969 #32
    Hmm. Well, glad you got it working! Let us know whenever you have issues! :)
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #33
    Now since you have asked i have a question, i want to know how do i get a calendar to pop up on date parameters so customers can select the date instead of typing/writing it in.
    Is it something simple or involves a lot of javascript?
  • mwilliams
    edited December 31, 1969 #34
    Sorry for the delay. Take a look at this devShare post:

    http://www.birt-exchange.org/org/devshare/designing-birt-reports/1348-adding-a-calender-widget-to-reports/

    Hope this helps.
    Warning No formatter is installed for the format ipb
  • AnnibalS
    edited December 31, 1969 #35
    <blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="101555" data-time="1338566853" data-date="01 June 2012 - 09:07 AM"><p>
    Sorry for the delay. Take a look at this devShare post:<br />
    <br />
    <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1348-adding-a-calender-widget-to-reports/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/1348-adding-a-calender-widget-to-reports/</a><br />
    <br />
    Hope this helps.<br /></p></blockquote>
    <br />
    Good morning, mwilliams.<br />
    <br />
    I'm trying to use the datatime param but the following error occours:<br />
    <br />
    The following items have errors: <br />
    <br />
    <span style='color: #FF0000'><br />
    Table (id = 10): <br />
    + Cannot execute the statement.<br />
    SQL statement does not return a ResultSet object.<br />
    SQL error #1: The syntax of the string representation of a datetime value is incorrect.<br />
    </span><br />
    <br />
    My script used is:<br />
    <pre class='_prettyXprint _lang-auto _linenums:0'>
    if (params["PARAMDATETIME"].value!=null){
    this.queryText = " select ****.PRODUCT.ID_PRODUCT, ****.PRODUCT.NAME, ****.PRODUCT.MARCA, ****.PRODUCT.DATE_VALIDADE"+
    " from ****.PRODUCT" +
    " where ****.PRODUCT.DATE_EXPIRED<='"+params["PARAMDATETIME"].value+"'";
    } else {
    this.queryText = " select ****.PRODUCT.ID_PRODUCT, ****.PRODUCT.NAME, ****.PRODUCT.MARCA, ****.PRODUCT.DATE_EXPIRED "+
    " from ****.PRODUCT";
    }
    </pre>
    <br />
    I inserted the following values for it, but occours error above:<br />
    - 13-09-2012 09:22:49.001-0300<br />
    - 2012-11-10 00:00:00.000<br />
    - 2012-11-10 00:00:00<br />
    - 2012-11-10 00:00<br />
    - 12-11-10 00:00<br />
    - 11-10-2012 00:00<br />
    - 11-10-12 00:00<br />
    - 10-11-2012 00:00<br />
    - 10-11-12 00:00<br />
    <br />
    Could you help me, please?<br />
    <br />
    Thanks.
  • mwilliams
    edited December 31, 1969 #36
    What format is PRODUCT.DATE_EXPIRED, in your dataSet?
    Warning No formatter is installed for the format ipb
  • Rajesh Kakawat
    edited December 31, 1969 #37
    Use same format in which your date is stored in database
  • AnnibalS
    edited December 31, 1969 #38
    <blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109331" data-time="1347572802" data-date="13 September 2012 - 02:46 PM"><p>
    What format is PRODUCT.DATE_EXPIRED, in your dataSet?<br /></p></blockquote>
    <br />
    I add into "Report Parameters" how "Date Time" format.<br />
    <br />
    In may database the column is Timestamp.
  • AnnibalS
    edited December 31, 1969 #39
    <blockquote class='ipsBlockquote' data-author="'AnnibalS'" data-cid="109366" data-time="1347635846" data-date="14 September 2012 - 08:17 AM"><p>
    I add into "Report Parameters" how "Date Time" format.<br />
    <br />
    In may database the column is Timestamp.<br /></p></blockquote>
    <br />
    I no longer need the datetime parameter. <br />
    <br />
    Thanks for help.
  • mwilliams
    edited December 31, 1969 #40
    Ok. Let us know whenever you have questions!

    :)
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #41
    I am developing a BIRT report with a cascading parameter multiple values for e.g.(country, city). Do you have an example out there with you where i can choose either multiple cities or ALL cities based on country.

    Oscar
  • <p>Hi,</p><p> </p><p>I´m having problems here with optional parameters in Birt. I tried out the solution described at the beginning of the topic using a script in the Before method and it worked fine. But... In my report I am using the concept of sub-reports and I have a table inside another table.</p><p>That means I have some parameters I need to pass to the query of the external table that can be null or not. These same parameters need to achieve the nested table (that is the final goal) so it can generate the report properly.</p><p> </p><p>The problem is that I succeed in passing "optional" parameters to the external table using a script in before method, it uses some parameters in it´s query, but when I try to pass these parameters to the nested tables it fails. I tried making a binding between these two tables, but in this case I need to configure the parameters in Birt and they cannot be null.</p><p> </p><p>Is there a solution for this case? Do I need to redesign my report to use optional parameters?</p><p> </p><p>I appreciate any help. Thanks very much!</p><p> </p><p>Rodrigo.</p>
  • sumathra
    edited December 20, 2013 #43
    <p>i had  searched a web for this requirement  optional parameter.....but i cant found a example i too have 5 to 8 parameter user may select any parameter ...how to achieve this ?????i already post the thread but no reply ..  <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/31203-optional-parameter/'>http://developer.actuate.com/community/forum/index.php?/topic/31203-optional-parameter/</a> kindly help on this??please</p>
  • <blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="74184" data-time="1299082139">
    <div>
    <p>Ok. Check out the attached report and let me know if you have questions.</p>
    </div>
    </blockquote>
    <p>Excelente aporte, me sirvio un monton para despejar mis dudas que tenia al pasar mis parametros.</p>
  • <p>I am using Excel Data Source and I have created two report parameters(i.e RP_SupplierName & RP_Metal). To make one of them optional, I unchecked the isRequired(unchecked isRequired for RP_SupplierName report parameter). But I am getting blank report. Please help me where I am doing wrong???</p>
  • <p>How are you applying the parameters to the report design?</p>
    <p>Are you using a filter in the data set?</p>
    <p>Could you provide a sample of your report design?</p>
    <p> </p>
    <p>I've attached a simple sample that shows to create a filter that will only apply to the data set if the parameter value is not null.</p>
    Warning No formatter is installed for the format ipb
  • <blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="68781" data-time="1285353965">
    <div>
    <p>Hi Rajesh007, If you make the parameter optional, you'll need to add the portion of your where statement that uses this parameter using script. In your beforeOpen method of your dataSet, you'd use a statement like: if (params["paramName"] != null){ this.queryText = this.queryText + " where dbTable.dbStringField = '" + params["stringParam"] + "'"; } If it's an integer or float value, you wouldn't need the single tick quotes(') surrounding the parameter value. Hope this helps.</p>
    </div>
    </blockquote>
    <p>
    When there is no value passed tot he parameter "paramName" , then my if condition not null check is not happening. no data is displayed in the report. The query still gets appendned with " where dbTable.dbStringField = '" + params["stringParam"] + "'"</p>
  • Guido
    edited December 7, 2016 #48
    <p>Hi,</p>
    <p> </p>
    <p>I have a problem with optional parameters, using BIRT with MongoDB.</p>
    <p> </p>
    <p>I have two parameters (ip and admState).</p>
    <p> </p>
    <p>My "$query expression" is:</p>
    <p> </p>
    <p>{ip:"paramIp"}</p>
    <p> </p>
    <p>paramIp is required, no optional.</p>
    <p> </p>
    <p>Then, in beforeOpen script I write this:</p>
    <pre class="_prettyXprint">
    this.queryText = this.queryText.replace("paramIp", params["ip"].value);

    if (params["admState"].value == "UP" || params["admState"].value == "DOWN"){
        this.queryText = this.queryText + ", administrative_state:" + params["admState"].value;
    }</pre>
    <p>IP param work fine, but admState filter no work. Always return the same results.</p>
    <p> </p>
    <p><u>For example:</u></p>
    <p>Entry params:</p>
    <p>ip = 10.0.0.1</p>
    <p>admState = UP</p>
    <p>Table Result => ip: 10.0.0.1 | admin_state: UP | country: Argentina</p>
    <p> </p>
    <p>Entry params:</p>
    <p>ip = 10.0.0.1</p>
    <p>admState = DOWN</p>
    <p>Table Result => ip: 10.0.0.1 | admin_state: UP | country: Argentina</p>
    <p>This result should be empty.</p>
    <p> </p>
    <p><strong>The question is: how I write the query for optional parameters??</strong></p>
    <p> </p>
    <p>Really appreciate your help!!</p>
    <p>Sorry for my basic English, hope you understand me. ^_^</p>
  • @mwilliams said:
    With a date time parameter, you'll need to include the '' surrounding the parameter in your script and make sure that the dateTime value is entered in the correct format for your database. If you're doing optional parameters, you'll need to do what I described above if the user chooses to leave the parameter blank. If you want to put a default range in, you'll just add your where clause in script similar to the above script only with static values rather than a parameter. I can provide a simple example if needed. Just let me know your BIRT version if you'd like an example.

    Can any one give me an example for this... I am using BIRT version 4.6

  • Hi,

    I am currently new to birt.
    I have made one report using sql query for displaying date parameters as optional parameter.

    I am currently using eclipse version 4 (Indigo version) .

    In my query i am having 4-5 different parameters out of which two are date parameters both are fetching from and to dates from database for. Respective columns but in that i need to make second date parameter (both from and to) to be as optional

    I tried handling it through sql itself by giving nvl for the second date parameter values.

    select * from table_name
    where trunc(date_column) between ? And?
    And nvl(date_column, 'X') between nvl(?, 'X') and nvl(?, 'X').

    It works fine when i am generating my report from eclipse birtviewer but it shows error when i am deploying on server and regenerating the same report from server.

    I am getting this error only when i am making this date parameter as optional unchecking 'isrequired' box.

    I am Getting error message as

    Table (id = 5700069):
    + Can not load the report query: 5700069. Errors occurred when generating the report document for the report element with ID 5700069. "

    Please anyone assist me to solve this issue.
    Thanks in advance for your help.
  • jfranken
    edited December 28, 2019 #51

    If the query runs in the designer and connects to the same database used on the server, then the issue is related to the server installation/configuration or the database drivers on the server. You said that you are running Eclipse Indigo. The best option is to upgrade to the latest version of Eclipse BIRT. Your old reports should still run.

    If I understand correctly, the query runs on the server when the dates are not null. In that case, a possible workaround is to replace or remove the null dates in the beforeOpen of the data set. Here is an old thread that provides more detail:

    https://forums.opentext.com/forums/discussion/comment/218385

    Warning No formatter is installed for the format ipb