APPENDING QUERY TO DATASET'S DYNAMICALLY

AAUMBI038

<div>Hello Everybody,</div>
<div> </div>
<div>I am working as a BIRT Report developer for last one year. In the following cases feeling very difficult to resolve. Kinldy help me to get out of this.</div>
<div> </div>
<div>Here we are having J2EE web application. Every report which I developing would be integrated with Application. Calling application to report is by URL. So, I always hidden the report paramters while deploying to server.</div>
<div> </div>
<div>Ex : </div>
<div> </div>
<div><a data-ipb='nomediaparse' href='http://****.****.****.****/reports/frameset?__report=Example.rptdesign'>http://****.****.****.****/reports/frameset?__report=Example.rptdesign</a>and parameters would get append with this URL by using JSP's.</div>
<div> </div>
<div>In application, If customer click Exampledata link that redirecting to one JSP page. There we are having Parameters like the attached JSP form.</div>
<div> </div>
<div> </div>
<div>After submit from JSP, that URL calling goes to report.</div>
<div> </div>
<div>In reprot side I 've one data set query like below,</div>
<div> </div>
<div>SELECT </div>
<div>SB.*, VAL.*</div>
<div>FROM </div>
<div> </div>
<div>SALES_BOOK SB LEFT JOIN </div>
<div> </div>
<div>(</div>
<div>-- set 1</div>
<div>SELECT * FROM INVOICE WHERE INVOICE.CREATION_TIME BETWEEN ? AND ? -- CreationFrom and CreationTo params used</div>
<div> </div>
<div>UNION ALL</div>
<div>-- set 2</div>
<div>SELECT * FROM RECEIPT WHERE RECEIPT.CREATION_TIME BETWEEN ? AND ?</div>
<div> </div>
<div>UNION ALL</div>
<div>-- set 3</div>
<div>SELECT * FROM PAYMENT WHERE PAYMENT.CREATION_TIME BETWEEN ? AND ?</div>
<div> </div>
<div>) AS VAL</div>
<div> </div>
<div>ON SB.FILE_NO=VAL.FILE_NO</div>
<div> </div>
<div>WHERE</div>
<div> </div>
<div>IF (?='' OR ?='ALL',TRUE,SB.BRANCH=?)</div>
<div> </div>
<div>But here I've only Creation_From and Ceation_To date parameters passing to the sets. That set tables also have Branch and zones. But, in query I've not used that filter so those sets producing very high junks. That causing report taking too much time. I used  filter Branch, out of the result data set. The branch parameter is mandatory in JSP side. Any way having other filter option ZONE which is multiselect in JSP page. Customer can select many values for ZONES.</div>
<div> </div>
<div> </div>
<div>That paramter I declared by following query,</div>
<div> </div>
<div>In beforeOpen() on the data set I've written the script,</div>
<div> </div>
<div> </div>
<div>if(params["Zone"].value != null && params["Zone"].value != "" && params["Zone"].value != "ALL"){</div>
<div>this.queryText = this.queryText + "AND SB.ZONE IN (" + reportContext.getParameterValue("Zone") + ")"</div>
<div> </div>
<div>}</div>
<div>else</div>
<div>{</div>
<div>params["Zone"].value=true</div>
<div>}</div>
<div> </div>
<div>If customer selects Zones -- South& North </div>
<div> </div>
<div>That query would be, AND ZONE IN  ('South','North')</div>
<div> </div>
<div>That would be append to the Data Set last line.. <strong>My doubt here started can we append this parameter inside of every sets like below query,</strong></div>
<div> </div>
<div> </div>
<div>SELECT </div>
<div>SB.*, VAL.*</div>
<div>FROM </div>
<div> </div>
<div>SALES_BOOK SB LEFT JOIN </div>
<div> </div>
<div>(</div>
<div>-- set 1</div>
<div>SELECT * FROM INVOICE WHERE INVOICE.CREATION_TIME BETWEEN ? AND ? -- CreationFrom and CreationTo params used</div>
<div>IF (?='' OR ?='ALL',TRUE,BRANCH=?) and IF (?='' OR ?='ALL',TRUE,zone=?)</div>
<div> </div>
<div>UNION ALL</div>
<div>-- set 2</div>
<div>SELECT * FROM RECEIPT WHERE RECEIPT.CREATION_TIME BETWEEN ? AND ?</div>
<div>IF (?='' OR ?='ALL',TRUE,BRANCH=?) and IF (?='' OR ?='ALL',TRUE,zone=?)</div>
<div> </div>
<div>UNION ALL</div>
<div>-- set 3</div>
<div>SELECT * FROM PAYMENT WHERE PAYMENT.CREATION_TIME BETWEEN ? AND ?</div>
<div>IF (?='' OR ?='ALL',TRUE,BRANCH=?) and IF (?='' OR ?='ALL',TRUE,zone=?)</div>
<div> </div>
<div>) AS VAL</div>
<div> </div>
<div>ON SB.FILE_NO=VAL.FILE_NO</div>
<div> </div>
<div>This makes report effective. But, ZONE IS MULTISELECT SO THAT COULD BE WRITEN BY "IN" CONDTION.</div>
<div> </div>
<div><strong>Kindly answer me for the below points,</strong></div>
<div> </div>
<div><strong>1. Please advice how I need to write IN condition inside the Data set. I already explained Zone is multiselect from JSP form. How could I receive the values to data set via 'in' condition from JSP.</strong></div>
<div> </div>
<div><strong>2. How dynamically add query inside data set. </strong></div>
<div> </div>
<div>Ex:</div>
<div> </div>
<div>and IF (?='' OR ?='ALL',TRUE,zone=?) This need to be inside of every set with customer selected values. If customer wouldn't select zone, that paramter statement wouldn't come to data set.</div>
<div> </div>
<div> </div>
<div> </div>
<div>Kindly help me for this issue. Eagerly waiting.</div>
<div> </div>
<div>Thank in advance.</div>
<div> </div>
<div>By </div>
<div> </div>
<div>Ananth</div>