Parameter Issue from Report Administration

Hi,
I have having trouble in passing parameter from report administration in BIRT report. Suppose when i am passing from report administration it automatically get = sign like =2012 when i will pass this value into my report query it will bring nothing, but when i will remove = sign from report parameter like 2012 it will bring results. Any idea how i can remove this = while running parameter from report request page. I am using BIRT 2.3.2 with Maximo 7.5.0.3. Cheers

Find more posts tagged with

Comments

micajblock

<blockquote class='ipsBlockquote' data-author="'rawal79'" data-cid="116928" data-time="1368967922" data-date="19 May 2013 - 05:52 AM">
I am using BIRT 2.3.2 with Maximo 7.5.0.3. Cheers </blockquote>
 
I thought Maximo 7.5.0.3 uses BIRT 3.7.1. What are you using BIRT 2.3.2? I do not know that this is the issue, but it might be (there might be differences in the Maximo data classes used).

rawal79

Hi,
Thanks for your email. I agree with you Maximo 7.5.0.3 uses latest version, but i am using BIRT 2.3.2 for BIRT report development for Maximo.

micajblock

Why?

In any case what does the code in the beforeOpen event of the data source look like?

wwilliams

Use replace and remove it, for example; params["site"].replace(/[=!,]/,"")
+ MXReportSqlFormat.createParamWhereClause("prl.siteid", params["site"].replace(/[=!,]/,"").toUpperCase())

rawal79

<blockquote class='ipsBlockquote' data-author="'wwilliams'" data-cid="117017" data-time="1369143051" data-date="21 May 2013 - 06:30 AM">
Use replace and remove it, for example; params["site"].replace(/[=!,]/,"") 
+ MXReportSqlFormat.createParamWhereClause("prl.siteid", params["site"].replace(/[=!,]/,"").toUpperCase()) </blockquote>

rawal79

Hi,
Thanks for your email. My code as following

var sqlText = new String();

var yearParam = new String();
var startYear = new String();
var endYear = new String();

var sqld= new String();
yearParam = params["FiscalYear"].value;
yearParam = yearParam.split("/");
startYear = yearParam[0];
endYear = yearParam[1];

// Add query to sqlText variable.
sqlText =" select pmnum,(select description from locations where pm.location = locations.location) "
+ "as site, "
+" (select description from commodities where commodities.commodity= "
+" pm.commoditygroup) as workcategory,description, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-01-01' and targstartdate "
+ "< '" + startYear + "-02-01') as jan, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-02-01' and targstartdate "
+ "< '" + startYear + "-03-01') as feb, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-03-01' and targstartdate "
+ "< '" + startYear + "-04-01') as mar, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-04-01' and targstartdate "
+ "< '" + startYear + "-05-01') as apr, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-05-01' and targstartdate "
+ "< '" + startYear + "-06-01') as may, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-06-01' and targstartdate "
+ "< '" + startYear + "-07-01') as jun, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-07-01' and targstartdate "
+ "< '" + startYear + "-08-01') as jul, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-08-01' and targstartdate "
+ "< '" + startYear + "-09-01') as aug, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-09-01' and targstartdate "
+ "< '" + startYear + "-10-01') as sep, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-10-01' and targstartdate "
+ "< '" + startYear + "-11-01') as oct, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-11-01' and targstartdate "
+ "< '" + startYear + "-12-01') as nov, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-12-01' and targstartdate "
+ "< '" + endYear + "-01-01') as dec, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-01-01' and targstartdate "
+ "<'" + endYear + "-02-01') as jannext, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-02-01' and targstartdate "
+ "<'" + endYear + "-03-01') as febnext, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-03-01' and targstartdate "
+ "<'" + endYear + "-04-01') as marnext, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-04-01' and targstartdate "
+ "<'" + endYear + "-05-01') as aprnext, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-05-01' and targstartdate "
+ "<'" + endYear + "-06-01') as maynext, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + endYear + "-06-01' and targstartdate "
+ "<'" + endYear + "-07-01') as junnext, "
+ "(select name from companies where companies.company = pm.vendor) as contractor "
+ " from pm where " + MXReportSqlFormat.createParamWhereClause("siteid", params["siteid"].toUpperCase())
//+" where " + params["where"]
//+ " and pm.siteid = " + MXReportSqlFormat.createParamWhereClause("pm.siteid", params["siteid"].replace(/[=!,]/,"").toUpperCase())
+ "and status = 'ACTIVE' "
+ "and pfmanticipated > 0 "
+ "ORDER BY pmnum "

maximoDataSet.setQuery(sqlText);

When i registered this report from PM module in Maximo there is no issue with or without = sign. This report runs data fine, but when i uploaded this reoprt in workorder tracking module in report administrationrder module and ran it from there with =siteid it brings no data even i have used
pm.siteid = " + MXReportSqlFormat.createParamWhereClause("pm.siteid", params["siteid"].replace(/[=!,]/,"").toUpperCase())

still it brings no data. Is this reason that my main query is pulling data from PM and i am running it from Workorder module thats why = sign makes issue. My problem is this that i have to run this report from Workorder not from PM. Any idea Cheers

micajblock

I have no idea why it does not work from Workorder Tracking. That being said why not use siteid directly instead of MXReportSqlFormat.createParamWhereClause?

so this line:

+ " from pm where " + MXReportSqlFormat.createParamWhereClause("siteid", params["siteid"].toUpperCase())

should be:

+ " from pm where siteid = " + params["siteid"].toUpperCase()

wwilliams

<blockquote class='ipsBlockquote' >
should be: 
 
+ " from pm where siteid = " + params["siteid"].toUpperCase() </blockquote>
 
actually 
+ " from pm where siteid = '" + params["siteid"].toUpperCase() + "'"

paulk

<blockquote class='ipsBlockquote' >+ " from pm where " + MXReportSqlFormat.createParamWhereClause("siteid", params["siteid"].toUpperCase()) 
+ "and status = 'ACTIVE' "</blockquote>
I think I see the problem with this. The createParamWhereClause statement is correct however the next statement added to the string is "and" when it should be " and". The missing space is creating a bad query. 
 
 
An unrelated opinion: 
<blockquote class='ipsBlockquote' >+ "(select wonum from workorder where " 
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-01-01' and targstartdate " 
+ "< '" + startYear + "-02-01') as jan, " 
+ "(select wonum from workorder where " 
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-02-01' and targstartdate " 
+ "< '" + startYear + "-03-01') as feb, "</blockquote>
I would recommend modifying all of these to the following: 
 
<blockquote class='ipsBlockquote' >+ "(select wonum from workorder where " 
+ "workorder.pmnum = pm.pmnum and year(targstartdate) = " + startYear + " and month(targstartdate) " 
+ "=1) as jan, " 
+ "(select wonum from workorder where " 
+ "workorder.pmnum = pm.pmnum and year(targstartdate) = " + startYear + " and month(targstartdate) " 
+ "=2) as feb, "</blockquote>
Much easier to read IMO. Plus your code will miss a workorder with targstartdate of '02-01-xxxx 12:00:00 am'. You could change the > to >= to capture these missing times also.

rawal79

Hi,
Thanks a lot for your reply.My issue resolved. Bundle of thanks .