how can I update inventory abc.rptdesign report?

Hi all,

I have customized the standard report inventory_abc.rptdesign adding some parameters. My problem is that at the end of the report there is a link to update the database. This link invokes to another report that is called inventory_abc_update. rptdesign. The problem is that I can?t find the way this link works propertly, and update the databse, I only get a message that says 'problem encountered with database update, database update succesful'. I don?t understand this confusing message.

Find more posts tagged with

Comments

JasonW

Where are you seeing the message? Can you post the update report?

Jason

cmsanchez

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="91047" data-time="1323376112" data-date="08 December 2011 - 01:28 PM">
Where are you seeing the message? Can you post the update report? 
 
Jason </blockquote>
 
Hi Jason, 
 
The message appears at the end of the report, in the last page: 
 
Problem Encountered with DataBase Update 
 
DataBase Update Succesful 
 
Below I post the Report modified adding the parameter 'commoditygroup' 
 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="<a class='bbc_url' href='http://www.eclipse.org/birt/2005/design'>http://www.eclipse.org/birt/2005/design"</a> version="3.2.17" id="1"> 
<property name="createdBy">Eclipse BIRT Designer Version 2.3.2.r232_20090202 Build <2.3.2.v20090218-0730></property> 
<property name="units">in</property> 
<simple-property-list name="includeResource"> 
<value>inventory</value> 
</simple-property-list> 
<property name="comments">Copyright (c) 2006 <<Your Company Name here>></property> 
<html-property name="description">This template will create a report having a single header with multiple rows of data, and contains three styles of charts.</html-property> 
<method name="initialize"><![CDATA[importPackage(Packages.com.ibm.tivoli.maximo.report.script); 
 
var timeInMs = Date.now(); 
 
attkey = "st-inventor_abc_" + params["appname"]+ "_"+ timeInMs; 
dskey = "ds-inventor_abc_" + params["appname"]+ "_"+ timeInMs; 
 
mxReportScriptContext = MXReportScriptContext.initialize(reportContext); 
 
//mxReportScriptContext.setDefaultLogLevel("DEBUG"); 
 
mxReportScriptContext.setDefaultLogFile("./temp/inventory_abc.log"); 
 
scriptLogger = mxReportScriptContext.getReportScriptLogger(); 
 
function sumFunction(dataSet, field){ 
if(dataSet.isDB2()) 
return(" sum(decimal(" + field + ", 10, 5))"); 
else 
if(dataSet.isOracle()) 
return(" sum(" + field + ")"); 
else 
return(" sum(cast(( "+ field +") as decimal)) "); 
}]]></method> 
<method name="afterFactory"><![CDATA[MXReportScriptContext.close();]]></method> 
<property name="theme">MaximoSystemLibrary.maximoTheme</property> 
<text-property name="displayName">Tivoli Maximo List Chart Template</text-property> 
<list-property name="libraries"> 
<structure> 
<property name="fileName">MaximoSystemLibrary.rptlibrary</property> 
<property name="namespace">MaximoSystemLibrary</property> 
</structure> 
</list-property> 
<parameters> 
<scalar-parameter name="where" id="25"> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<property name="isRequired">false</property> 
<property name="controlType">text-box</property> 
<property name="defaultValue">1=1</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="appname" id="200"> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<property name="isRequired">false</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="paramdelimiter" id="202"> 
<property name="hidden">true</property> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<property name="isRequired">false</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="paramstring" id="201"> 
<property name="hidden">true</property> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<property name="isRequired">false</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="storeroom" id="536"> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<text-property name="promptText">storeroom</text-property> 
<property name="isRequired">true</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="site" id="537"> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<text-property name="promptText">site</text-property> 
<property name="isRequired">true</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
<scalar-parameter name="commoditygroup" id="538"> 
<property name="valueType">static</property> 
<property name="dataType">string</property> 
<text-property name="promptText">commoditygroup</text-property> 
<property name="isRequired">true</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
</parameters> 
<data-sources> 
<script-data-source name="maximoDataSource" id="64" extends="MaximoSystemLibrary.maximoDataSource"/> 
</data-sources> 
<data-sets> 
<script-data-set name="dataSet" id="5"> 
<list-property name="resultSetHints"> 
<structure> 
<property name="position">1</property> 
<property name="name">itemnum</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">description</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">abctype</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">abcafter</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">ccf</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">6</property> 
<property name="name">ccfafter</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">7</property> 
<property name="name">issueytd</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">8</property> 
<property name="name">lastcost</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">9</property> 
<property name="name">ytd_issuecost</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">10</property> 
<property name="name">tot_ytdissuecost</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">11</property> 
<property name="name">total_a</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">12</property> 
<property name="name">total_b</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">13</property> 
<property name="name">total_c</property> 
<property name="dataType">integer</property> 
</structure> 
</list-property> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">itemnum</property> 
</structure> 
<structure> 
<property name="columnName">description</property> 
</structure> 
<structure> 
<property name="columnName">abctype</property> 
</structure> 
<structure> 
<property name="columnName">abcafter</property> 
</structure> 
<structure> 
<property name="columnName">ccf</property> 
</structure> 
<structure> 
<property name="columnName">ccfafter</property> 
</structure> 
<structure> 
<property name="columnName">issueytd</property> 
</structure> 
<structure> 
<property name="columnName">lastcost</property> 
</structure> 
<structure> 
<property name="columnName">ytd_issuecost</property> 
</structure> 
<structure> 
<property name="columnName">tot_ytdissuecost</property> 
</structure> 
<structure> 
<property name="columnName">total_a</property> 
</structure> 
<structure> 
<property name="columnName">total_b</property> 
</structure> 
<structure> 
<property name="columnName">total_c</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">itemnum</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">description</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">abctype</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">abcafter</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">ccf</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">6</property> 
<property name="name">ccfafter</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">7</property> 
<property name="name">issueytd</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">8</property> 
<property name="name">lastcost</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">9</property> 
<property name="name">ytd_issuecost</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">10</property> 
<property name="name">tot_ytdissuecost</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">11</property> 
<property name="name">total_a</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">12</property> 
<property name="name">total_b</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">13</property> 
<property name="name">total_c</property> 
<property name="dataType">integer</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">maximoDataSource</property> 
<list-property name="parameters"> 
<structure> 
<property name="name">myUpd</property> 
<property name="dataType">string</property> 
<property name="position">1</property> 
<property name="isInput">true</property> 
<property name="isOutput">true</property> 
</structure> 
</list-property> 
<method name="open"><![CDATA[maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
maximoDataSet.open(); 
 
****_ytd = 0; 
temp_ytd = 0; 
 
var request = reportContext.getHttpServletRequest(); 
 
if( BirtComp.notEqual(request, null) ) { 
var session = request.getSession(); 
session.setAttribute(dskey, this.getDataSource().getName()); 
session.setAttribute("where", params["where"].value); 
} 
 
params["paramdelimiter"] = "||"; 
params["paramstring"] = attkey + params["paramdelimiter"] + dskey; 
 
if( BirtComp.notEqual(params["storeroom"],null) && BirtComp.notEqual(params["storeroom"], "") ) { 
params["where"]+= " and " + MXReportSqlFormat.createParamWhereClause("inventory.location", params["storeroom"]); 
} 
 
if( BirtComp.notEqual(params["site"], null) && BirtComp.notEqual(params["site"], "") ) { 
params["where"]+= " and " + MXReportSqlFormat.createParamWhereClause("inventory.siteid", params["site"]); 
} 
 
if( BirtComp.notEqual(params["commoditygroup"], null) && BirtComp.notEqual(params["commoditygroup"], "") ) { 
params["where"]+= " and " + MXReportSqlFormat.createParamWhereClause("item.commoditygroup", params["commoditygroup"]); 
} 
 
var sqlJoin = " inner join item on inventory.itemnum = item.itemnum and inventory.itemsetid = item.itemsetid " 
+ " and (inventory.abctype not in('N', 'NA') or inventory.abctype is null) " 
+ " inner join invcost on inventory.location = invcost.location and inventory.itemnum = invcost.itemnum " 
+ " and inventory.itemsetid = invcost.itemsetid and inventory.siteid = invcost.siteid " 
; 
 
var sqlTotalIssueCost = " ( select sum(inventory.issueytd * invcost.lastcost) as tot_ytdissuecost " 
+ " from inventory " + sqlJoin + " where " + params["where"] + " ) " 
; 
 
 
if(!(maximoDataSet.isOracle())) sqlTotalIssueCost += " as totytdissuecost "; 
 
var sqlText = " select inventory.itemnum, item.description, inventory.abctype, inventory.ccf, " 
+ " inventory.issueytd, invcost.lastcost, (inventory.issueytd * invcost.lastcost) as ytd_issuecost, " 
+ " tot_ytdissuecost, inventory.itemsetid from inventory " 
+ sqlJoin + "," + sqlTotalIssueCost + " where " + params["where"] 
+ " order by ytd_issuecost desc " 
; 
 
scriptLogger.debug("sql Query ==> " + sqlText); 
 
maximoDataSet.setQuery(sqlText); 
 
 
orgIdDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "orgIdDataSet"); 
orgIdDataSet.open(); 
var orgIdSql = " select site.orgid from site where siteid = '" + (params["site"].replace(/[=!,]/,"")) + "'"; 
 
orgIdDataSet.setQuery(orgIdSql); 
 
if(orgIdDataSet.fetch()){ 
orgid = orgIdDataSet.getString("orgid"); 
} 
orgIdDataSet.close(); 
 
 
abcAfterDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "abcAfterDataSet"); 
abcAfterDataSet.open(); 
 
 
 
var abcAfterSql=new String(); 
 
if(abcAfterDataSet.isOracle()){ 
abcAfterSql = " select * from " 
+ " (select " + sumFunction(abcAfterDataSet, "maxvars.varvalue") + " as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) "; 
abcAfterSql += ", (select " + sumFunction(abcAfterDataSet, "maxvars.varvalue") + " as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) "; 
abcAfterSql += ", (select " + sumFunction(abcAfterDataSet, "maxvars.varvalue") + " as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) " 
; 
} 
 
if(abcAfterDataSet.isDB2()){ 
abcAfterSql = " select a_abcafter, b_abcafter, c_abcafter from "; 
abcAfterSql += " (select sum(decimal((a_abcafter),10,5)) as a_abcafter from ( select(maxvars.varvalue) as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) as a) atable, "; 
abcAfterSql += " (select sum(decimal((b_abcafter),10,5)) as b_abcafter from ( select(maxvars.varvalue) as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) as

btable, "; 
abcAfterSql += " (select sum(decimal((c_abcafter),10,5)) as c_abcafter from ( select(maxvars.varvalue) as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) as c) ctable "; 
} 
 
if(abcAfterDataSet.isSQLServer()){ 
abcAfterSql = " select a_abcafter, b_abcafter, c_abcafter from "; 
abcAfterSql += " (select sum(cast((a_abcafter) as float)) as a_abcafter from ( select(maxvars.varvalue) as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) as a) atable, "; 
abcAfterSql += " (select sum(cast((b_abcafter) as float)) as b_abcafter from ( select(maxvars.varvalue) as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) as

btable, "; 
abcAfterSql += " (select sum(cast((c_abcafter) as float)) as c_abcafter from ( select(maxvars.varvalue) as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) as c) ctable "; 
} 
 
abcAfterDataSet.setQuery(abcAfterSql); 
 
if(abcAfterDataSet.fetch()){ 
a_abcafter = abcAfterDataSet.getFloat("a_abcafter"); 
b_abcafter = abcAfterDataSet.getFloat("b_abcafter"); 
c_abcafter = abcAfterDataSet.getFloat("c_abcafter"); 
} 
abcAfterDataSet.close(); 
 
ccfAfterDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "ccfAfterDataSet"); 
ccfAfterDataSet.open(); 
 
var ccfAfterSql = " select * from " 
+ " (select " + sumFunction(ccfAfterDataSet, "maxvars.varvalue") + " as a_ccfafter from maxvars where maxvars.varname in ('A_CCF') and orgid = '" + orgid + "' ) "; 
 
if(!(ccfAfterDataSet.isOracle())) ccfAfterSql += " as a, "; 
else ccfAfterSql += " , "; 
 
ccfAfterSql += " (select " + sumFunction(ccfAfterDataSet, "maxvars.varvalue") + " as b_ccfafter from maxvars where maxvars.varname in ('B_CCF') and orgid = '" + orgid + "' ) "; 
 
if(!(ccfAfterDataSet.isOracle())) ccfAfterSql += " as b, "; 
else ccfAfterSql += " , "; 
 
ccfAfterSql += " (select " + sumFunction(ccfAfterDataSet, "maxvars.varvalue") + " as c_ccfafter from maxvars where maxvars.varname in ('C_CCF') and orgid = '" + orgid + "' ) "; 
 
if(!(ccfAfterDataSet.isOracle())) ccfAfterSql += " as c "; 
 
ccfAfterDataSet.setQuery(ccfAfterSql); 
 
if(ccfAfterDataSet.fetch()){ 
a_ccfafter = ccfAfterDataSet.getFloat("a_ccfafter"); 
b_ccfafter = ccfAfterDataSet.getFloat("b_ccfafter"); 
c_ccfafter = ccfAfterDataSet.getFloat("c_ccfafter"); 
} 
ccfAfterDataSet.close();]]></method> 
<method name="fetch"><![CDATA[if (!maximoDataSet.fetch()) 
return (false); 
 
tot_ytdissuecost = maximoDataSet.getFloat("tot_ytdissuecost") 
ytd_issuecost = maximoDataSet.getFloat("ytd_issuecost"); 
 
****_ytd = (ytd_issuecost/tot_ytdissuecost) + temp_ytd; 
temp_ytd = ****_ytd; 
 
if(****_ytd <= a_abcafter){ 
abcafter = "A"; 
ccfafter = a_ccfafter; 
} 
else if (****_ytd >= b_abcafter){ 
abcafter = "C"; 
ccfafter = c_ccfafter; 
} 
else{ 
abcafter = "B"; 
ccfafter = b_ccfafter; 
} 
 
row["itemnum"] = maximoDataSet.getString("itemnum"); 
row["description"] = maximoDataSet.getString("description"); 
//row["abctype"] = maximoDataSet.getString("abctype"); 
row["abcbefore"] = maximoDataSet.getString("abctype"); 
row["abcafter"] = abcafter; 
row["ccf"] = maximoDataSet.getString("ccf"); 
row["ccfafter"] = ccfafter ; 
row["issueytd"] = maximoDataSet.getString("issueytd"); 
row["lastcost"] = maximoDataSet.getFloat("lastcost"); 
row["ytd_issuecost"] = ytd_issuecost; 
row["tot_ytdissuecost"] = tot_ytdissuecost; 
 
 
var request = reportContext.getHttpServletRequest(); 
 
if(BirtComp.notEqual(request,null)) { 
var session = request.getSession(); 
var updStmtList = session.getAttribute(attkey); 
 
if (scriptLogger.isDebugEnabled()) { 
scriptLogger.debug("["+ maximoDataSet.getName() + "] >>> BirtComp.equalTo(updStmtList,null) : '" + BirtComp.equalTo(updStmtList,null) + "'"); 
} 
 
if(BirtComp.equalTo(updStmtList,null)) { 
updStmtList = new Array; 
} 
 
var updateQuery = "update inventory set inventory.abctype = '" + abcafter + "', inventory.ccf = " + ccfafter 
+ " where " + params["where"] + " and (inventory.abctype not in('N', 'NA') or inventory.abctype is null) " 
+ " and inventory.itemnum = '" + maximoDataSet.getString("itemnum") + "'" 
+ " and inventory.itemsetid = '" + maximoDataSet.getString("itemsetid") + "'" 
; 
 
updStmtList.push(updateQuery); 
 
session.setAttribute(attkey, updStmtList); 
} 
 
return (true);]]></method> 
</script-data-set> 
<script-data-set name="graphDataSet" id="535"> 
<list-property name="resultSetHints"> 
<structure> 
<property name="position">1</property> 
<property name="name">abctype</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">sumbefore</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">sumafter</property> 
<property name="dataType">integer</property> 
</structure> 
</list-property> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">abctype</property> 
</structure> 
<structure> 
<property name="columnName">sumbefore</property> 
</structure> 
<structure> 
<property name="columnName">sumafter</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">abctype</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">sumbefore</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">sumafter</property> 
<property name="dataType">integer</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">maximoDataSource</property> 
<method name="open"><![CDATA[graphDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
graphDataSet.open(); 
 
 
var where = new String(); 
where= " 1=1 "; 
 
if( BirtComp.notEqual(params["storeroom"], null) && BirtComp.notEqual(params["storeroom"], "") ) { 
where += " and " + MXReportSqlFormat.createParamWhereClause("inventory.location", params["storeroom"]); 
} 
 
if( BirtComp.notEqual(params["site"], null) && BirtComp.notEqual(params["site"], "") ) { 
where += " and " + MXReportSqlFormat.createParamWhereClause("inventory.siteid", params["site"]); 
} 
 
if( BirtComp.notEqual(params["commoditygroup"], null) && BirtComp.notEqual(params["commoditygroup"], "") ) { 
where += " and " + MXReportSqlFormat.createParamWhereClause("item.commoditygroup", params["commoditygroup"]); 
} 
 
var sqlText = new String(); 
var abcAfterSql = new String(); 
var sqlJoin = new String(); 
var sqlTotalIssueCost = new String(); 
 
 
var sqlJoin = " inner join item on inventory.itemnum = item.itemnum and inventory.itemsetid = item.itemsetid " 
+ " and (inventory.abctype not in('N', 'NA') or inventory.abctype is null) " 
+ " inner join invcost on inventory.location = invcost.location and inventory.itemnum = invcost.itemnum " 
+ " and inventory.itemsetid = invcost.itemsetid and inventory.siteid = invcost.siteid " 
; 
 
var sqlTotalIssueCost = " ( select sum(inventory.issueytd * invcost.lastcost) as tot_ytdissuecost " 
+ " from inventory " + sqlJoin + " where " + where + " ) " 
; 
 
 
if(!(graphDataSet.isOracle())) sqlTotalIssueCost += " as totytdissuecost "; 
 
var firstQuery = "select count(abctype) as totalbef, abctype, 0 as zerovalue from ( " 
+ " select inventory.itemnum, item.description, inventory.abctype, inventory.ccf, " 
+ " inventory.issueytd, invcost.lastcost, (inventory.issueytd * invcost.lastcost) as ytd_issuecost, " 
+ " tot_ytdissuecost, inventory.itemsetid from inventory " 
+ sqlJoin + "," + sqlTotalIssueCost + " where " + where 
+ " and inventory.abctype is not null "; 
 
var nullQuery = " union all select abctype, count(itemnum) as totalbef, 0 as totalaft from ( " 
+ " select inventory.itemnum, item.description, inventory.abctype, inventory.ccf, " 
+ " inventory.issueytd, invcost.lastcost, (inventory.issueytd * invcost.lastcost) as ytd_issuecost, " 
+ " tot_ytdissuecost, inventory.itemsetid from inventory " 
+ sqlJoin + "," + sqlTotalIssueCost + " where " + where 
+ " and inventory.abctype is null "; 
 
 
if(!(graphDataSet.isSQLServer())) firstQuery += " order by abctype, ytd_issuecost desc " 
; 
 
var abcAfterSql=new String(); 
 
if(graphDataSet.isOracle()){ 
abcAfterSql = " select * from " 
+ " (select " + sumFunction(graphDataSet, "maxvars.varvalue") + " as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) "; 
abcAfterSql += ", (select " + sumFunction(graphDataSet, "maxvars.varvalue") + " as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) "; 
abcAfterSql += ", (select " + sumFunction(graphDataSet, "maxvars.varvalue") + " as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) " 
; 
} 
 
if(graphDataSet.isDB2()){ 
abcAfterSql = " select a_abcafter, b_abcafter, c_abcafter from "; 
abcAfterSql += " (select sum(decimal((a_abcafter),10,5)) as a_abcafter from ( select(maxvars.varvalue) as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) as a) atable, "; 
abcAfterSql += " (select sum(decimal((b_abcafter),10,5)) as b_abcafter from ( select(maxvars.varvalue) as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) as

btable, "; 
abcAfterSql += " (select sum(decimal((c_abcafter),10,5)) as c_abcafter from ( select(maxvars.varvalue) as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) as c) ctable "; 
} 
 
if(graphDataSet.isSQLServer()){ 
abcAfterSql = " select a_abcafter, b_abcafter, c_abcafter from "; 
abcAfterSql += " (select sum(cast((a_abcafter) as float)) as a_abcafter from ( select(maxvars.varvalue) as a_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT') and orgid = '" + orgid + "' ) as a) atable, "; 
abcAfterSql += " (select sum(cast((b_abcafter) as float)) as b_abcafter from ( select(maxvars.varvalue) as b_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT') and orgid = '" + orgid + "' ) as

btable, "; 
abcAfterSql += " (select sum(cast((c_abcafter) as float)) as c_abcafter from ( select(maxvars.varvalue) as c_abcafter from maxvars where maxvars.varname in ('A_BREAKPOINT', 'B_BREAKPOINT', 'C_BREAKPOINT') and orgid = '" + orgid + "' ) as c) ctable "; 
} 
 
 
var secondQuery = " select (inventory.issueytd * invcost.lastcost/tot_ytdissuecost) as svalue, invcost.lastcost, inventory.itemnum, " 
+ " inventory.abctype, inventory.issueytd, (inventory.issueytd * invcost.lastcost) as ytd_issuecost, tot_ytdissuecost " 
+ " from inventory " 
+ sqlJoin + "," + sqlTotalIssueCost + " where " + where 
+ " group by inventory.itemnum, inventory.abctype, inventory.ccf, inventory.issueytd, invcost.lastcost, (inventory.issueytd * invcost.lastcost), tot_ytdissuecost, inventory.itemsetid "; 
 
if(!(graphDataSet.isSQLServer())) secondQuery += " order by ytd_issuecost desc "; 
 
if(graphDataSet.isOracle()){ 
 
var sqlText = "select abctype, totalbef, totalaft from ( " + firstQuery 
+ " ) group by abctype order by abctype) firstt, ( select count(newabctype) as totalaft, newabctype from ( " 
+ " select case when totalafter<=a_abcafter then 'A' when totalafter>b_abcafter then 'C' else 'B' end as newabctype " 
+ " from( select inv.itemnum, sum(inv2.svalue) as totalafter from ( " 
+ secondQuery + ") inv, ( "+ secondQuery + ") inv2 " 
+ " where inv.svalue<=inv2.svalue or inv.itemnum=inv2.itemnum " 
+ " group by inv.itemnum, inv.svalue " 
+ " order by inv.svalue desc " 
+ " ) cumultable, (" + abcAfterSql + " )) group by newabctype) secondt where abctype=newabctype " 
+ nullQuery + " ) group by abctype order by abctype " 
; 
} 
if(graphDataSet.isDB2()){ 
var sqlText = "select abctype, totalbef, totalaft from ( " + firstQuery 
+ " ) as firsttab group by abctype order by abctype) firstt, ( select count(newabctype) as totalaft, newabctype from ( " 
+ " select case when totalafter<=a_abcafter then 'A' when totalafter>b_abcafter then 'C' else 'B' end as newabctype " 
+ " from( select inv.itemnum, sum(inv2.svalue) as totalafter from ( " 
+ secondQuery + ") inv, ( "+ secondQuery + ") inv2 " 
+ " where inv.svalue<=inv2.svalue or inv.itemnum=inv2.itemnum " 
+ " group by inv.itemnum, inv.svalue " 
+ " order by inv.svalue desc " 
+ " ) cumultable, (" + abcAfterSql + " ) as sumtab) sumtab2 group by newabctype) secondt where abctype=newabctype " 
+ nullQuery + " ) nulltab group by abctype order by abctype " 
; 
 
} 
 
if(graphDataSet.isSQLServer()) { 
 
var sqlText = "select abctype, totalbef, totalaft from ( " + firstQuery 
+ " ) as firsttab group by abctype ) firstt, ( select count(newabctype) as totalaft, newabctype from ( " 
+ " select case when totalafter<=a_abcafter then 'A' when totalafter>b_abcafter then 'C' else 'B' end as newabctype " 
+ " from( select inv.itemnum, sum(inv2.svalue) as totalafter from ( " 
+ secondQuery + ") inv, ( "+ secondQuery + ") inv2 " 
+ " where inv.svalue<=inv2.svalue or inv.itemnum=inv2.itemnum " 
+ " group by inv.itemnum, inv.svalue " 
+ " ) cumultable, (" + abcAfterSql + " ) as sumtab) sumtab2 group by newabctype) secondt where abctype=newabctype " 
+ nullQuery + ") as nulltab group by abctype " 
; 
 
} 
 
 
graphDataSet.setQuery(sqlText);]]></method> 
<method name="fetch"><![CDATA[if (!graphDataSet.fetch()) 
return (false); 
 
row["abctype"] = graphDataSet.getString("abctype"); 
row["sumbefore"] = graphDataSet.getInteger("totalbef"); 
row["sumafter"] = graphDataSet.getInteger("totalaft"); 
 
return (true);]]></method> 
</script-data-set> 
</data-sets> 
<page-setup> 
<simple-master-page name="maximoLandscape" id="47" extends="MaximoSystemLibrary.maximoLandscape"> 
<overridden-values> 
<ref-entry baseId="304" name="NewGrid2" id="304"/> 
<ref-entry baseId="305" id="305"/> 
<ref-entry baseId="306" id="306"/> 
<ref-entry baseId="307" id="307"/> 
<ref-entry baseId="308" id="308"/> 
<ref-entry baseId="340" name="NewImage1" id="340"/> 
<ref-entry baseId="309" id="309"/> 
<ref-entry baseId="310" name="NewImage" id="310"/> 
<ref-entry baseId="322" name="NewGrid" id="322"/> 
<ref-entry baseId="323" id="323"/> 
<ref-entry baseId="324" id="324"/> 
<ref-entry baseId="325" id="325"/> 
<ref-entry baseId="326" id="326"/> 
<ref-entry baseId="328" name="NewText" id="328"/> 
<ref-entry baseId="327" id="327"/> 
<ref-entry baseId="329" name="NewGrid1" id="329"/> 
<ref-entry baseId="330" id="330"/> 
<ref-entry baseId="331" id="331"/> 
<ref-entry baseId="332" id="332"/> 
<ref-entry baseId="333" id="333"/> 
<ref-entry baseId="334" id="334"/> 
<ref-entry baseId="335" name="NewAutoText" id="335"/> 
<ref-entry baseId="336" id="336"/> 
<ref-entry baseId="337" name="NewText1" id="337"/> 
<ref-entry baseId="338" id="338"/> 
<ref-entry baseId="339" name="NewAutoText1" id="339"/> 
</overridden-values> 
</simple-master-page> 
</page-setup> 
<body> 
<data id="487"> 
<structure name="stringFormat"> 
<property name="category">></property> 
<property name="pattern">></property> 
</structure> 
<property name="resultSetColumn">abctype</property> 
</data> 
<table id="7"> 
<property name="width">100%</property> 
<property name="pageBreakInterval">50</property> 
<column id="83"> 
<property name="width">0.9in</property> 
</column> 
<column id="79"> 
<property name="width">2.6in</property> 
</column> 
<column id="91"> 
<property name="width">0.8in</property> 
</column> 
<column id="578"> 
<property name="width">0.9in</property> 
</column> 
<column id="568"> 
<property name="width">0.8in</property> 
</column> 
<column id="558"> 
<property name="width">0.9in</property> 
</column> 
<column id="548"> 
<property name="width">0.9in</property> 
</column> 
<column id="87"> 
<property name="width">0.9in</property> 
</column> 
<column id="17"> 
<property name="width">1.2in</property> 
</column> 
<column id="18"> 
<property name="width">0.1in</property> 
</column> 
<header> 
<row id="180"> 
<property name="style">title</property> 
<cell id="181"> 
<property name="colSpan">10</property> 
<property name="rowSpan">1</property> 
<label id="341"> 
<text-property name="text" key="inventory_abc.reportname"></text-property> 
</label> 
</cell> 
</row> 
<row id="173"> 
<property name="height">0.08in</property> 
<cell id="174"> 
<property name="colSpan">10</property> 
<property name="rowSpan">1</property> 
<property name="borderBottomColor">#000000</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">thin</property> 
</cell> 
</row> 
<row id="166"> 
<cell id="167"> 
<property name="colSpan">10</property> 
<property name="rowSpan">1</property> 
<property name="borderBottomColor">#000000</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">thin</property> 
<grid id="109"> 
<property name="width">100%</property> 
<column id="110"> 
<property name="width">1in</property> 
<property name="style">reportheaderlabel</property> 
</column> 
<column id="111"> 
<property name="width">8.875in</property> 
<property name="style">reportheaderdata</property> 
</column> 
<row id="141"> 
<property name="height">0.06in</property> 
<cell id="142"/> 
<cell id="143"> 
<property name="paddingLeft">2px</property> 
</cell> 
</row> 
<row id="112"> 
<cell id="113"> 
<label id="118"> 
<text-property name="text" key="storeroom_colon">Parameter 1:</text-property> 
</label> 
</cell> 
<cell id="114"> 
<property name="paddingLeft">2px</property> 
<data id="344"> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">storeroom</property> 
<expression name="expression">params["storeroom"].replace(/[=!,]/,"")</expression> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<property name="resultSetColumn">storeroom</property> 
</data> 
</cell> 
</row> 
<row id="115"> 
<cell id="116"> 
<label id="119"> 
<text-property name="text" key="site_colon">Parameter 2:</text-property> 
</label> 
</cell> 
<cell id="117"> 
<property name="paddingLeft">2px</property> 
<data id="532"> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">site</property> 
<expression name="expression">params["site"].replace(/[=!,]/,"")</expression> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<property name="resultSetColumn">site</property> 
</data> 
</cell> 
</row> 
<row id="825"> 
<cell id="826"> 
<label id="827"> 
<text-property name="text" key="commoditygroup_colon">Parameter 1:</text-property> 
</label> 
</cell> 
<cell id="828"> 
<property name="paddingLeft">2px</property> 
<data id="829"> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">commoditygroup</property> 
<expression name="expression">params["commoditygroup"].replace(/[=!,]/,"")</expression> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<property name="resultSetColumn">commoditygroup</property> 
</data> 
</cell> 
</row> 
<row id="126"> 
<property name="height">0.06in</property> 
<cell id="127"/> 
<cell id="128"> 
<property name="paddingLeft">2px</property> 
</cell> 
</row> 
</grid> 
</cell> 
</row> 
<row id="144"> 
<property name="height">0.06in</property> 
<cell id="145"> 
<property name="colSpan">10</property> 
<property name="rowSpan">1</property> 
</cell> 
</row> 
</header> 
<group id="364"> 
<property name="groupName">chartGroup</property> 
<property name="interval">none</property> 
<property name="sortDirection">asc</property> 
<expression name="keyExpr">row["chart_key"]</expression> 
<structure name="toc"> 
<expression name="expressionValue">""</expression> 
</structure> 
<property name="repeatHeader">false</property> 
<property name="hideDetail">false</property> 
<property name="pageBreakAfter">auto</property> 
<property name="pageBreakBefore">auto</property> 
<header> 
<row id="387"> 
<property name="style">chart</property> 
<cell id="388"> 
<property name="colSpan">10</property> 
<property name="rowSpan">1</property> 
<table id="637"> 
<property name="width">100%</property> 
<property name="dataSet">dataSet</property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">itemnum</property> 
<expression name="expression">dataSetRow["itemnum"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">description</property> 
<expression name="expression">dataSetRow["description"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">abctype</property> 
<expression name="expression">dataSetRow["abctype"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">abcafter</property> 
&

JasonW

Can you zip both reports and post it in the reply? Do you get anything in the log file?

Jason