Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Multiple values in a parameter
megabri
<p>I have a report that's using the following query:</p><p> </p><div>select * from ptsadmin.vr_claimsrpt</div><div>WHERE STATUS_DTM >= :fromTimeframe</div><div>and STATUS_DTM <= :toTimeframe</div><div> </div><div>Everything works fine with that but now I want to add something to narrow down my result by a list of employees that's provided by a parameter. I have a field called PERSONID that would check the employee numbers. I want to pass an array with something like '1024913', '1027937'. How do I do that?</div>
Find more posts tagged with
Comments
micajblock
<p><a data-ipb='nomediaparse' href='
http://developer.actuate.com/community/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause'>http://developer.actuate.com/community/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause</a></p>
;
megabri
<p>This is what I changed my query to:</p><div>select * from ptsadmin.vr_claimsrpt</div><div>WHERE STATUS_DTM >= :fromTimeframe</div><div>and STATUS_DTM <= :toTimeframe</div><div>and personid in (****).</div><div> </div><div>I should also mention it's an array of integers.</div><div> </div><div>and i added this to the beforeOpen:</div><div>this.queryText = this.queryText.replace("****", params["Employees"].value.join(", "));</div><div> </div><div>I'm getting this error...</div><div><div>The following items have errors: </div><div> </div><div> </div><div> </div><div>ReportDesign (id = 1): </div><div> </div><div> + There are errors evaluating script "this.queryText = this.queryText.replace("****", params["Employees"].value.join(", "));":</div><div>Fail to execute script in function __bm_beforeOpen(). Source:</div><div>
</div><div>" + this.queryText = this.queryText.replace("****", params["Employees"].value.join(", ")); + "</div><div>
</div><div>A BIRT exception occurred. See next exception for more information.</div><div>TypeError: Cannot call method "join" of null (<inline>#1). </div><div> </div><div> </div><div>Table (id = 138): </div><div> </div><div> + Cannot get the result set metadata.</div><div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div><div>SQL error #1:ORA-00904: "****": invalid identifier</div><div> </div><div> ;</div><div> java.sql.SQLSyntaxErrorException: ORA-00904: "****": invalid identifier</div></div>
micajblock
<p>What the error is telling you is that there is no value to the parameter '<span>Employees'. Are you sure that is the name of the parameter? Note parameter names are case sensitive.</span></p>
megabri
<p>Attached you'll find employeesParam.jpg which is a screen shot of my parameter set up for the Employees parameter. I've also attached the error I get when i try to add the 'and personid in (****)' part of the query to my data set. Any ideas?</p>
micajblock
<p>what is the data type of the parameter? personid?</p>
megabri
<p>Sorry, forgot to attach the error in my last post.</p><p> </p><p>Employees is a list box of integers. Personid is saved as a number in my database.</p>
micajblock
<p>Is this in the designer when you try to preview the results of the data set? If yes, try to have one of the values set to default.</p>
megabri
<p>That did it! Thank you so much!</p>
cmkatt
<p>Hi Expert,</p>
<p> </p>
<p> i'm newbie in BIRT.</p>
<p> </p>
<p> i'm having the similar issue but i just can't make it works. here attached with my rptdesign file. Kindly assist.</p>
<p> </p>
<p> </p>
micajblock
<p>Is this in the designer or in Maximo? What is the error you are getting? </p>
cmkatt
<p>Hi Mic,</p>
<p> </p>
<p>It is in designer. below are the error message when i preview the report.</p>
<p> </p>
<div>The following items have errors: </div>
<div> </div>
<div> </div>
<div>ReportDesign (id = 1): </div>
<div>+ There are errors evaluating script "</div>
<div>//var param=params["prmStatus"].toString().split(",");</div>
<div> </div>
<div>//this.queryText = this.queryText.replaceAll("Statuslist", prmStatus[0]);</div>
<div> </div>
<div>this.queryText = this.queryText.replaceAll('Statuslist',"'" + params["prmStatus"].value.join("','") + "'");":</div>
<div>Fail to execute script in function __bm_beforeOpen(). Source:</div>
<div>
</div>
<div>" + </div>
<div>//var param=params["prmStatus"].toString().split(",");</div>
<div> </div>
<div>//this.queryText = this.queryText.replaceAll("Statuslist", prmStatus[0]);</div>
<div> </div>
<div>this.queryText = this.queryText.replaceAll('Statuslist',"'" + params["prmStatus"].value.join("','") + "'"); + "</div>
<div>
</div>
<div>A BIRT exception occurred. See next exception for more information.</div>
<div>TypeError: Cannot call method "replaceAll" of null (/report/data-sets/script-data-set[
@id="
;5"]/method[
@name="
;beforeOpen"]#6). </div>
micajblock
<p>Get rid of the beforeOpen of the StatusList data set. You are trying to change the query used for the parameter with the same parameter.</p>
<p> </p>
<p>P.S. I think Maximo might deal with a multi-select differently.</p>
cmkatt
<div>Hi MBlock,</div>
<div> </div>
<div> i'm still getting error below.</div>
<div> </div>
<div> </div>
<div>ReportDesign (id = 1): </div>
<div>+ There are errors evaluating script "</div>
<div>//var param=params["prmStatus"].toString().split(",");</div>
<div> </div>
<div>//this.queryText = this.queryText.replaceAll("Statuslist", prmStatus[0]);</div>
<div> </div>
<div>this.queryText = this.queryText.replaceAll('Statuslist',"'" + params["prmStatus"].value.join("','") + "'");":</div>
<div>Fail to execute script in function __bm_beforeOpen(). Source:</div>
<div>
</div>
<div>" + </div>
<div>//var param=params["prmStatus"].toString().split(",");</div>
<div> </div>
<div>//this.queryText = this.queryText.replaceAll("Statuslist", prmStatus[0]);</div>
<div> </div>
<div>this.queryText = this.queryText.replaceAll('Statuslist',"'" + params["prmStatus"].value.join("','") + "'"); + "</div>
<div>
</div>
<div>A BIRT exception occurred. See next exception for more information.</div>
<div>TypeError: Cannot call method "replaceAll" of null (/report/data-sets/script-data-set[
@id="
;5"]/method[
@name="
;beforeOpen"]#6). </div>
micajblock
<p>you are using a scripted data set. In your case this.queryText is null! Just us the parameter to replace the string sqltext in the open event.</p>
<p> </p>
<p>Again once you import to Maximo it might behave differently.</p>
cmkatt
<p>Hi MBlock,</p>
<p> </p>
<p> It works as per your suggestion. Will test it out in Maximo. Many thanks.</p>
<p> </p>
<div>+ " and po.status in ('" + params["prmStatus"].value.join("','") + "' ) "</div>
<div>+ " and " + params["where"]</div>
cmkatt
<p>Hi MBlock,</p>
<p> </p>
<p> Like what you expected, Maximo has its own methodologies, this doesn't work in Maximo. Thanks.</p>
micajblock
<p>I think Maximo will send a comma delimited list for their multi-select.</p>
wwilliams
<span><a class="" href="
http://developer.actuate.com/community/forum/index.php?/user/59481-cmkatt/"
; title=""><span>cmkatt</span></a></span>
<p>I looked at your report, what are you trying to accomplish with the statuslist dataset that you can't accomplish with a lookup?</p>
<p>If I understand, you have a parameter prmStatus. You'd like to use a multiselect for PO.status?</p>
<p>What does your request page report lookups look like?</p>
<p> </p>
