The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.

Pass Optional parameter in Report

Hi ,
I am new to birt, I have made reports which 4 to 6 parameter and i have integrated the report in jsp .
I am using hibernate connection for database
all thing are working well .But the problem i want to pass optional parameter to report .how can i do this staff
I have changed the Parameter and have unchecked IsRequired but Still it not working. it doesn't take any null value and throw error
and then tried it writing Dynamic Query in database Script on BeforeOpen but still it no working
Please somebody help me
sorry for poor english
Thanx
«1

Comments

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    Thanks i have solved
    but anyway thanks for precious reply
    thank you very much
  • edited September 25, 2010
    But i think dateTime does not work in this manner
    The parameter doesn't take null value
    i am using hql query like this
    hql query + where invoiceDate between ? and ?

    where invoiceDate is field of my Database


    For the above problem i am providing default parameter with wide range of Date if user does not input any Date

    Is ther any other way to do this stuff ?
    Sorry for poor English

    thanks
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    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.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    hi,
    Thanks for ur reply , i haven't that what u reply last time but i will surely going to try
    I am using Birt 2.5 from eclipse openSource

    thanx
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Well, let me know if you get stuck and would like an example. :)

    Good luck!

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    anyone can tell me about this problem i didn't understand what can i do
    i make the report parameter and enter ? in the sql query what can i do else
    please tell me .....
  • edited December 31, 1969
    Above problem was related with dynamic query which was created according to parameter passed by the user.
    If you writing simple query than you have to just put ? in sql query and create a parameter.
  • edited December 31, 1969
    but i want to know if i didn't entered value in the parameter the result should be all the data
    how can i make it ??
    please answer me and thanks for your reply
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    You could set up the basic select/from query in your dataSet. If a value is selected, you can add the where clause in the beforeOpen script of your dataSet. If no value is selected, you can not edit th queryText and the entire result set will be called. There is also the possibility that you can add "All" to your parameter list and make the parameter required so that the user either selects All or one of the individual parameters.

    Hope this helps.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    can u write to me an example show me how to do this in details please
    thanks alot...
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    What is your BIRT version? I'll make a quick example.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    my birt version GANYMEDE
    thx for your reply...
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Ok. Check out the attached report and let me know if you have questions.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    thx for your reply that's help me alot
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Not a problem. Always glad to help. Let us know whenever you have questions!

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited September 9, 2011
    I'm attempting to do the same thing as the original poster, but I have a slightly different issue. My query is as follows:<br />
    <br />
    <pre class='_prettyXprint _lang-auto _linenums:0'>
    select
    o.orders_id,
    o.orders_number,
    o.date_purchased,
    o.customers_name,
    o.customers_street_address,
    o.customers_city,
    o.customers_state,
    o.customers_postcode,
    o.customers_telephone,
    o.customers_email_address,
    op.products_id,
    op.products_name,
    opa.products_options_values_id,
    opa.products_options_values,
    ot.value
    from orders o
    inner join orders_products op on o.orders_id = op.orders_id
    left join orders_products_attributes opa on op.orders_products_id = opa.orders_products_id
    inner join orders_total ot on o.orders_id = ot.orders_id
    where o.orders_status = 5
    and ot.class = 'ot_total'
    and o.date_purchased >= ?
    and o.date_purchased <= ?
    </pre>
    <br />
    Then, on the data set's 'beforeOpen' script, i have this:<br />
    <br />
    <pre class='_prettyXprint _lang-auto _linenums:0'>
    if (params["productIdParam"] != null)
    {
    this.queryText += " and op.products_id = " + params["productIdParam"];
    }

    this.queryText += " order by op.products_id, opa.products_options_values_id, o.date_purchased;";
    </pre>
    <br />
    When I run this query, if I do not supply a product id, I get nothing back; contrarily, if I do enter a product id, the report runs just as expected. I cannot see anything that's wrong with what I've done, but obviously, something is. I've attached my report design file. Any help on this would be greatly appreciated, thanks.<br />
    <br />
    Oh, also, I cannot get logging to work. I am using BIRT Report Designer (i.e. stand-alone - I think this is what the RCP refers to?), version 2.6.1. My goal would be to enable SQL logging so that I can see what BIRT is using to query the database; I would think this would help me debug these types of issues on my own. Again, any help with this would be great. Lastly, I have removed database connection details from the uploaded report, so just be aware of that.
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    You might try checking the parameter for an empty string value as well, i.e. "". One thing you can do to check your query text that is passed is to assign queryText to a variable in your beforeOpen script and then recall this variable in a text box in your report. I believe this will still show the '?' dataSet parameters, but it will show what is passed in the line in question from your beforeOpen script. So, that might help.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    Mike how do i get your example files to open up with the BIRT version 3.6.1 i use. I got two Date parameters Start date and End Date and i want to uncheck "Is Required" field in the parameter. The purpose is either to get all the All dates or Data within the Date range and the onus is on the end user to determine what he prefers. I hope the info.. is suffice and it makes sense.
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    There were some major changes between 2.3 and 2.5, I believe, so some reports will not open when created in 2.3 and opened in a newer version. That's probably the reason you're unable to open the design. I'll just make a new one. So, you've got a start date and an end date and you want the user to be able to either select to enter a date or select to pass a null value, which will return all dates? Is this correct? Let me know and I'll make an example.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    Michael,
    Thanks for the reply and you are correct about the date parameter thing. Appreciate your help
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Ok. Take a look at this report. If you run it in the web viewer, be sure to add the URL parameter &__parameterpage=true.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    I am getting an error on the report i have attached the report would really appreciate your help.
    Oscar
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    What is the error? Can you post the error stack in here? Thanks!

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    The error is
    "The following items have errors:


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


    Thanks
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Can you click the '+' sign and post the entire error stack? Thanks!

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Do your query results show up under preview results in the dataSet editor? Also, what is the scenario in which you receive that error? With the date parameters empty, with parameters entered, or both?

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    My results show up under the preview results dataSet editor. I receive the error in both scenarios with parameters entered and with data set empty.
    No idea what i am doing wrong here, done exactly what you have in your example.
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Can you expand the error by clicking on the '+' and post the error stack in here? Thanks!

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • edited December 31, 1969
    I got it working something to do with the BIRT version i use. When you make changes to the data set it throws thar error no idea why had to re do the report.

    Thank you,
    Oscar
Sign In or Register to comment.