Pass Optional parameter in Report

Rajesh Kakawat · 2010-09-20T01:46:44+00:00

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…

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

Find more posts tagged with

Comments

mwilliams

Hmm. Well, glad you got it working! Let us know whenever you have issues!

ocremedios

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

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.

AnnibalS

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="101555" data-time="1338566853" data-date="01 June 2012 - 09:07 AM">
Sorry for the delay. Take a look at this devShare post: 
 
<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> 
 
Hope this helps. </blockquote>
 
Good morning, mwilliams. 
 
I'm trying to use the datatime param but the following error occours: 
 
The following items have errors: 
 
 
Table (id = 10): 
+ Cannot execute the statement. 
SQL statement does not return a ResultSet object. 
SQL error #1: The syntax of the string representation of a datetime value is incorrect. 
 
 
My script used is: 
<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>
 
I inserted the following values for it, but occours error above: 
- 13-09-2012 09:22:49.001-0300 
- 2012-11-10 00:00:00.000 
- 2012-11-10 00:00:00 
- 2012-11-10 00:00 
- 12-11-10 00:00 
- 11-10-2012 00:00 
- 11-10-12 00:00 
- 10-11-2012 00:00 
- 10-11-12 00:00 
 
Could you help me, please? 
 
Thanks.

mwilliams

What format is PRODUCT.DATE_EXPIRED, in your dataSet?

Rajesh Kakawat

Use same format in which your date is stored in database

AnnibalS

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109331" data-time="1347572802" data-date="13 September 2012 - 02:46 PM">
What format is PRODUCT.DATE_EXPIRED, in your dataSet? </blockquote>
 
I add into "Report Parameters" how "Date Time" format. 
 
In may database the column is Timestamp.

AnnibalS

<blockquote class='ipsBlockquote' data-author="'AnnibalS'" data-cid="109366" data-time="1347635846" data-date="14 September 2012 - 08:17 AM">
I add into "Report Parameters" how "Date Time" format. 
 
In may database the column is Timestamp. </blockquote>
 
I no longer need the datetime parameter. 
 
Thanks for help.

mwilliams

Ok. Let us know whenever you have questions!

ocremedios

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

rodrigofantinati

Hi, 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.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. 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. Is there a solution for this case? Do I need to redesign my report to use optional parameters? I appreciate any help. Thanks very much! Rodrigo.

sumathra

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

ronald_ecq

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="74184" data-time="1299082139">
<div>
Ok. Check out the attached report and let me know if you have questions.
</div>
</blockquote>
Excelente aporte, me sirvio un monton para despejar mis dudas que tenia al pasar mis parametros.

Jaya Patil

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

JFreeman

How are you applying the parameters to the report design?
Are you using a filter in the data set?
Could you provide a sample of your report design?
 
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.

New_Bee

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="68781" data-time="1285353965">
<div>
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.
</div>
</blockquote>

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"] + "'"

Guido

Hi,
 
I have a problem with optional parameters, using BIRT with MongoDB.
 
I have two parameters (ip and admState).
 
My "$query expression" is:
 
{ip:"paramIp"}
 
paramIp is required, no optional.
 
Then, in beforeOpen script I write this:
<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>
IP param work fine, but admState filter no work. Always return the same results.
 
For example:
Entry params:
ip = 10.0.0.1
admState = UP
Table Result => ip: 10.0.0.1 | admin_state: UP | country: Argentina
 
Entry params:
ip = 10.0.0.1
admState = DOWN
Table Result => ip: 10.0.0.1 | admin_state: UP | country: Argentina
This result should be empty.
 
The question is: how I write the query for optional parameters??
 
Really appreciate your help!!
Sorry for my basic English, hope you understand me. ^_^

ChowS

@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

Gaurav raj

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

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