Pass Optional parameter in Report

Rajesh Kakawat
edited February 11, 2022 in Analytics #1
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

  • mwilliams
    edited December 31, 1969 #2
    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.
    Warning No formatter is installed for the format ipb
  • Rajesh Kakawat
    edited December 31, 1969 #3
    Thanks i have solved
    but anyway thanks for precious reply
    thank you very much
  • Rajesh Kakawat
    edited September 25, 2010 #4
    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
  • mwilliams
    edited December 31, 1969 #5
    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.
    Warning No formatter is installed for the format ipb
  • Rajesh Kakawat
    edited December 31, 1969 #6
    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
  • mwilliams
    edited December 31, 1969 #7
    Well, let me know if you get stuck and would like an example. :)

    Good luck!
    Warning No formatter is installed for the format ipb
  • shrif
    edited December 31, 1969 #8
    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 .....
  • Rajesh Kakawat
    edited December 31, 1969 #9
    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.
  • shrif
    edited December 31, 1969 #10
    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
  • mwilliams
    edited December 31, 1969 #11
    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.
    Warning No formatter is installed for the format ipb
  • shrif
    edited December 31, 1969 #12
    can u write to me an example show me how to do this in details please
    thanks alot...
  • mwilliams
    edited December 31, 1969 #13
    What is your BIRT version? I'll make a quick example.
    Warning No formatter is installed for the format ipb
  • shrif
    edited December 31, 1969 #14
    my birt version GANYMEDE
    thx for your reply...
  • mwilliams
    edited December 31, 1969 #15
    Ok. Check out the attached report and let me know if you have questions.
    Warning No formatter is installed for the format ipb
  • shrif
    edited December 31, 1969 #16
    thx for your reply that's help me alot
  • mwilliams
    edited December 31, 1969 #17
    Not a problem. Always glad to help. Let us know whenever you have questions!
    Warning No formatter is installed for the format ipb
  • DispatchMG
    edited September 9, 2011 #18
    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.
  • mwilliams
    edited December 31, 1969 #19
    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.
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #20
    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.
  • mwilliams
    edited December 31, 1969 #21
    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.
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #22
    Michael,
    Thanks for the reply and you are correct about the date parameter thing. Appreciate your help
  • mwilliams
    edited December 31, 1969 #23
    Ok. Take a look at this report. If you run it in the web viewer, be sure to add the URL parameter &__parameterpage=true.
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #24
    I am getting an error on the report i have attached the report would really appreciate your help.
    Oscar
  • mwilliams
    edited December 31, 1969 #25
    What is the error? Can you post the error stack in here? Thanks!
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #26
    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
  • mwilliams
    edited December 31, 1969 #27
    Can you click the '+' sign and post the entire error stack? Thanks!
    Warning No formatter is installed for the format ipb
  • mwilliams
    edited December 31, 1969 #28
    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?
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #29
    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.
  • mwilliams
    edited December 31, 1969 #30
    Can you expand the error by clicking on the '+' and post the error stack in here? Thanks!
    Warning No formatter is installed for the format ipb
  • ocremedios
    edited December 31, 1969 #31
    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