[Rajesh Kakawat]

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

[mwilliams]

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.

[Rajesh Kakawat]

Thanks i have solved
but anyway thanks for precious reply
thank you very much

[Rajesh Kakawat]

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]

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.

[Rajesh Kakawat]

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]

Well, let me know if you get stuck and would like an example.

Good luck!

[shrif]

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]

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]

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]

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.

[shrif]

can u write to me an example show me how to do this in details please
thanks alot...

[mwilliams]

What is your BIRT version? I'll make a quick example.

[shrif]

my birt version GANYMEDE
thx for your reply...

[mwilliams]

Ok. Check out the attached report and let me know if you have questions.

Attached File(s)

•  TestReport.rptdesign (36.25K)
  Number of downloads: 90

[shrif]

thx for your reply that's help me alot

[mwilliams]

Not a problem. Always glad to help. Let us know whenever you have questions!

[DispatchMG]

I'm attempting to do the same thing as the original poster, but I have a slightly different issue. My query is as follows:

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 <= ?

Then, on the data set's 'beforeOpen' script, i have this:

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;";

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.

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.

Attached File(s)

•  OrdersByProductIDInDateRange-optional.rptdesign (67.38K)
  Number of downloads: 10

[mwilliams]

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.

[ocremedios]

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.