|
|
| (no subject) [message #683231 is a reply to message #683125] |
Mon, 13 June 2011 09:29   |
Eclipse User |
|
|
|
Originally posted by: Donkey Hottie
13.6.2011 6:45, jyo kirjoitti:
> Hi,
>
> I am working on a report design which has 3 report parameters; all three
> report parameters are optional. Now my question is, when no report
> parameters are selected full report should be generated, how that can be
> achieved (i.e., the where clause on the dataset sQL query should be set
> as optional)?
>
> I have removed the where clause from the Data Set SQL query, and I think
> I need to filter the data, but so far not successful. Is there any
> example that someone can point me to? Any input on this is appreciated.
> Thanks in advance,
>
> Jyo
You can do something like this
SELECT A,B,C
FROM ABBA
WHERE A = IFNULL(param1,A)
AND B = IFNULL(param2,B)
AND C = IFNULL(param3,C) ;
If Oracle, change IFNULL() to NVL().
--
You will have a long and boring life.
|
|
|
| Re: How to make the where clause optional on dataset SQL query?? [message #683326 is a reply to message #683149] |
Mon, 13 June 2011 14:21 |
 jyo 
Messages: 34
Registered: June 2011 |
Member |
|
|
Thank you for the reply.
Initially I had query like below:
select A, B, C from table where A=? or B=? or C=?
And I defined 3 optional report parameters rp_A, rp_B, rp_C that can enter on the parameter Page, and I linked the dataset paramters to report parameters and report rendering fine if user selects/enters values for rp_A, rp_B and rp_C. Now my question is, if user doen't select/enter any value for the report parameter; all the data from the table should be dsiplayed on the report;
I tried below two options Thomas and Donkey Hottie provided; but still not working for me, maybe they should be at specific places during report design that I am missing, can yu point me some detail example?
|
|
|
| (no subject) [message #683427 is a reply to message #683326] |
Mon, 13 June 2011 17:21 |
|
In this case I would not use dataset parameters. I would put my query
in like select A, B, C from table
I would then write a beforeOpen script on the dataset that checked the
values of A, B and C and modify the query based on those values.
if( params["A"].value != null ){
this.queryText = this.queryText + " where A="+params["A"].value;
}
Jason
On 6/13/2011 10:21 AM, jyo wrote:
> Thank you for the reply.
>
> Initially I had query like below:
> select A, B, C from table where A=? or B=? or C=?
>
> And I defined 3 optional report parameters rp_A, rp_B, rp_C that can
> enter on the parameter Page, and I linked the dataset paramters to
> report parameters and report rendering fine if user selects/enters
> values for rp_A, rp_B and rp_C. Now my question is, if user doen't
> select/enter any value for the report parameter; all the data from the
> table should be dsiplayed on the report;
> I tried below two options Thomas and Donkey Hottie provided; but still
> not working for me, maybe they should be at specific places during
> report design that I am missing, can yu point me some detail example?
>
|
|
|
|
|
| Re: (no subject) [message #684023 is a reply to message #683949] |
Tue, 14 June 2011 21:20 |
|
I have done it this way before:
var parmcount = params["parmorders"].value.length
var whereclause = "";
if( parmcount > 0 ){
whereclause = " where customernumber in ( ";
}
for( i=0; i < parmcount; i++ ){
if( i == 0 ){
whereclause = whereclause + params["parmorders"].value[i];
}else{
whereclause = whereclause + " , " + params["parmorders"].value[i];
}
}
if( parmcount > 0 ){
this.queryText = this.queryText + whereclause + " ) ";
}
My suggestion is to write the final query into a global variable and
then display in on the report to verify it.
reportContext.setGlobablVariable("myquery", this.queryText);
Add a text item to the report after the dataset is used in a table with
this value:
<value-of>
reportContext.getGlobablVariable("myquery");
</value-of>
Make sure you set the type to html for the text element.
Jason
On 6/14/2011 2:05 PM, jyo wrote:
>
> Jason, I am using below snippet of javascript for building the where
> clause dynamically for list box mutil selected values; I have other two
> report parameters also, that's why I kept " where " outside the loop.
> but it is not working I am not sure where I am going wrong...
>
> var parmcount=reportContext.getParameterValue( "listparameter" ).length;
> printWriter.println("parmcount is: "+parmcount);
> whereclause=whereclause + " where ";
>
> if( parmcount > 0 ){
> whereclause =whereclause +"au.tran_au in ('";
> for( i=0; i < parmcount; i++ ){
> if( i == 0 ){
> whereclause = whereclause + params["listparameter"].value[i];
> }else{
> whereclause = whereclause + "' , '" + params["listparameter"].value[i]; }
> }
>
> whereclause = whereclause+"')";
>
> }
> this.queryText = this.queryText+whereclause+" ORDER BY au.tran_au";
|
|
|
|
| Re: (no subject) [message #684509 is a reply to message #684483] |
Wed, 15 June 2011 17:18 |
|
Are you sure this error is from the script?
Jason
On 6/15/2011 12:34 PM, jyo wrote:
> Thanks Jason, I am able to debug the javascript and build the dynamic
> where clause fine...
>
> but I am getting below warning each time when I run the report; can you
> tell me why Iam getting below error?
>
> RHINO USAGE WARNING: Missed Context.javaToJS() conversion:
> Rhino runtime detected object [Ljava.lang.Object;@31d0b9 of class
> [Ljava.lang.Object; where it expected String, Number, Boolean or
> Scriptable instance. Please check your code for missing
> Context.javaToJS() call.
>
>
> here is the part of snippet of code I am using:
>
> if((params["rp_tran_au"].value == null || params["rp_tran_au"].value == '')
> && (params["rp_net_user_id"].value == null ||
> params["rp_net_user_id"].value == '') && (params["rp_usr_id"].value ==
> null || params["rp_usr_id"].value == '')){
> this.queryText = this.queryText + " ORDER BY au.tran_au";
> }else{
> //whereclause=whereclause + " where ";
> var parmcount = params["rp_tran_au"].value.length;
>
>
> if((parmcount == 1)&& (params["rp_tran_au"].value[0] == null |
> params["rp_tran_au"].value[0] == '') ){
> //printWriter.println("No Au seleted");
> }else if((parmcount == 1)&& (params["rp_tran_au"].value[0] != null |
> params["rp_tran_au"].value[0] != '') ){
> // printWriter.println("Single Au seleted");
> whereclause =whereclause +" where au.tran_au in ('"
> +params["rp_tran_au"].value[0] +"')";
> au_appended=true;
|
|
|
|
|
|
| Re: (no subject) [message #684958 is a reply to message #684941] |
Thu, 16 June 2011 14:17 |
|
When you run the attached report do you get the error?
Jason
On 6/16/2011 9:59 AM, jyo wrote:
> Hi Jason, yes, if I remove the script I won't get that error. I am not
> sure how big that error is and can it ignored or not. If I let the
> report run with that error will there be any performance issues? And
> also not sure why that is happening. I was doing regular javascript...
>
> And also when I deploy the webapp in the tomcat which runs on linux
> server, the logs are writing to the catalina.out even though the
> ReportEngine<timestamp>.log (which gets created each time I restart the
> server) file gets created under the directory BIRT_VIEWER_LOG_DIR I
> specified in web.xml; but those files remain empty. In my local machine
> (windows) the logs are writing to ReportEngine<timestamp>.log.
> Thanks in advance,
> Jyo
>
|
|
|
|
| Re: (no subject) [message #684964 is a reply to message #684941] |
Thu, 16 June 2011 14:17 |
|
When you run the attached report do you get the error?
Jason
On 6/16/2011 9:59 AM, jyo wrote:
> Hi Jason, yes, if I remove the script I won't get that error. I am not
> sure how big that error is and can it ignored or not. If I let the
> report run with that error will there be any performance issues? And
> also not sure why that is happening. I was doing regular javascript...
>
> And also when I deploy the webapp in the tomcat which runs on linux
> server, the logs are writing to the catalina.out even though the
> ReportEngine<timestamp>.log (which gets created each time I restart the
> server) file gets created under the directory BIRT_VIEWER_LOG_DIR I
> specified in web.xml; but those files remain empty. In my local machine
> (windows) the logs are writing to ReportEngine<timestamp>.log.
> Thanks in advance,
> Jyo
>
|
|
|
|
| Re: (no subject) [message #684981 is a reply to message #684974] |
Thu, 16 June 2011 14:49 |
|
I thought your script was similar to the one I used in that report.
On 6/16/2011 10:41 AM, jyo wrote:
> No, I am not getting that error with the attached report. So, issue is
> in my script??
|
|
|
|
| Re: (no subject) [message #685450 is a reply to message #685444] |
Fri, 17 June 2011 14:28 |
|
Can you trim this script a little at a time to determine which if
statement is causing the issue?
Jason
On 6/17/2011 10:21 AM, jyo wrote:
> Hi Jason, I wanted to touch base with you on this. Even I feel my
> script is very simple and similar to yours, but I am getting RHINO USAGE
> WARNING. Can you point me by seeing below why I am getting that warning?
>
> rp_tran_au - is list box field with multi selection
> rp_net_user_id - is a text box
> rp_usr_id - is a text box
>
> var whereclause = "";
> var au_appended=false;
> var netuserAppended=false;
>
> if((params["rp_tran_au"].value == null || params["rp_tran_au"].value == '')
> && (params["rp_net_user_id"].value == null ||
> params["rp_net_user_id"].value == '') && (params["rp_usr_id"].value ==
> null || params["rp_usr_id"].value == '')){
> this.queryText = this.queryText + " ORDER BY au.tran_au";
> }else{
> whereclause=whereclause + " where ";
> var parmcount = params["rp_tran_au"].value.length;
> //printWriter.println("The param count is: " +parmcount);
>
> if((parmcount == 1)&& (params["rp_tran_au"].value[0] == null |
> params["rp_tran_au"].value[0] == '') ){
> //printWriter.println("No Au seleted");
> }else if((parmcount == 1)&& (params["rp_tran_au"].value[0] != null |
> params["rp_tran_au"].value[0] != '') ){
> // printWriter.println("Single Au seleted");
> whereclause =whereclause +"au.tran_au in ('"
> +params["rp_tran_au"].value[0] +"')";
> au_appended=true;
> }else if ( parmcount > 1 ){
> whereclause =whereclause +"au.tran_au in ('";
> for( i=0; i < parmcount; i++ ){
> if( i == 0 && params["rp_tran_au"].value[i] != null){
> whereclause = whereclause + params["rp_tran_au"].value[i];
> }else{
> whereclause = whereclause + "' , '" + params["rp_tran_au"].value[i]; }
> }
> whereclause = whereclause+"')";
> au_appended=true;
> }
>
>
> if(params["rp_net_user_id"].value != null &&
> params["rp_net_user_id"].value != ''){
> if(au_appended){
> whereclause = whereclause + " and net_user_id='"
> +params["rp_net_user_id"].value+"'";
> netuserAppended=true;
> }else if(!au_appended){
> whereclause = whereclause + "net_user_id='"
> +params["rp_net_user_id"].value+"'";
> netuserAppended=true;
> } }
> if(params["rp_usr_id"].value != null && params["rp_usr_id"].value != ''){
> if(au_appended || netuserAppended){
> whereclause = whereclause + " and usr_id='" +params["rp_usr_id"].value+"'";
> }else if (!au_appended && !netuserAppended){
> whereclause = whereclause + " usr_id='" +params["rp_usr_id"].value+"'";
> }
> }
> this.queryText = this.queryText+whereclause+" ORDER BY au.tran_au";
> }
>
> //printWriter.println("The query is :" + this.queryText);
>
>
> Thank you!
> jyo
|
|
|
|
|
|
| Re: (no subject) [message #688536 is a reply to message #688515] |
Fri, 24 June 2011 21:45 |
|
What is the parameter type? Did you try removing each condition individually?
if((params["rp_tran_au"].value == null |){}
then
if((params["rp_tran_au"].value == '')){}
To see which condition is the issue.
Jason
|
|
|
|
| Re: (no subject) [message #691115 is a reply to message #691100] |
Thu, 30 June 2011 18:04 |
|
What data type is rp_tran_au?
Also do you check it for null first and then check the value?
Jason
On 6/30/2011 1:47 PM, jyo wrote:
> Jason Weathersby wrote on Fri, 24 June 2011 17:45
>> What is the parameter type? Did you try removing each condition
>> individually?
>> if((params["rp_tran_au"].value == null |){}
>> then
>> if((params["rp_tran_au"].value == '')){}
>> To see which condition is the issue.
>>
>> Jason
>
>
> Hi Jason, After debug I came to know that below condition causing the
> RHINO USAGE WARNING....And can you correct the syntax I am using?
>
> if((params["rp_tran_au"].value == '')){}
>
> Thx!
|
|
|
] 
Search
Help
Register
Login
Home
