nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Replace SQL text with Parameter

maximoadmin

Hi All, I have an SQL query like below in one of the sub dataset SELECT pmsequence.pmnum, pmsequence.jpnum, PMSEQUENCE.INTERVAL, jobplan.description, jobplan.pluscrevnum, jobplan.orgid jorgid, jobplan.siteid jsiteid, jobplan.status,
jobplan.jobplanid,pmsequence.orgid, pmsequence.siteid
FROM MAXIMO.pmsequence, MAXIMO.jobplan
WHERE pmsequence.jpnum = jobplan.jpnum
and pmsequence.siteid=jobplan.siteid
AND UPPER(jobplan2.status) = NVL(UPPER(:prmjpstatus), UPPER(jobplan2.status))
 I created parameter prmjpstatus . What is the process to replace :prmjpstatus with parameter value at run time... Any help... Thanks.

Find more posts tagged with

Comments

griniland

You need to script Data Set event "beforeOpen", there You can get your query text var queryText=this.queryText and replace it with anything You want. this.queryText= newQueryText Another way is to use binding parametres, I guess.

maximoadmin

HiHere is my test example I am getting errors in beforeOpen method. [color=#ff0000;]SQL query in my open method:[/color]maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());maximoDataSet.open(); var sqlText = new String(); var siteid=params["siteid"]; // Add query to sqlText variable.sqlText = "select assetnum,location,status,siteid,failurecode"+ " from asset "+ "where"+ " asset.siteid= ':site' " + "order by asset.assetnum "; maximoDataSet.setQuery(sqlText); [color=#ff0000;]beforeOpen method:[/color] ã€€var queryText=this.queryTextthis.queryText = this.queryText.replaceAll(":site", params["siteid"]); Error: [color=#FF0000;][font="verdana, sans-serif;"]The following items have errors: [/color][/font] [color=#FF0000;][font="verdana, sans-serif;"]ReportDesign (id = 1): [/color][/font][color=#FF0000;][font="verdana, sans-serif;"]+ There are errors evaluating script " 
 
var queryText=this.queryText 
this.queryText = this.queryText.replaceAll(":site", params["siteid"]); 
 
": 
Fail to execute script in function __bm_beforeOpen(). Source:

" + 
 
var queryText=this.queryText 
this.queryText = this.queryText.replaceAll(":site", params["siteid"]); 
 
+ "

A BIRT exception occurred. See next exception for more information. 
Error evaluating Javascript expression. Script engine error: TypeError: Cannot call method "replaceAll" of null (/report/data-sets/script-data-set[@id="5"]/method[@name="beforeOpen"]#4) 
Script source: /report/data-sets/script-data-set[@id="5"]/method[@name="beforeOpen"], line: 0, text: 
__bm_beforeOpen(). (Element ID:1) [/color][/font]

griniland

If you set your SQLText in OPEN event, its obvious, that in BEFORE_OPEN event this property is still null, so You can't call it's methods. Try to fix your script sequence. Haven't worked with SQL queries, but with XML queries I do what I need in DataSet.event beforeOpen();

wwilliams

So have you created a parameter? Data Explorer, Report Parameters, then create a site parameter?Then in you query + " and siteid ='" + params["site"].replace(/[=!,]/,"") + "'"or just + " and siteid ='" +params["site"] + "'"EvenBirtStr.toUpper(params["site"])...