Fetching Last 6 months data in SQL Server for Maximo

Below is one of the best ways to find number of tickets created in last 6 months in Maximo using SQL Server database.

Query is self explanatory

select month,countoftickets from (
SELECT DATENAME(MONTH,DATEADD(month, -5, GETDATE())) month,COUNT(*) AS countoftickets , 1 as fs FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, -5, GETDATE())) union
SELECT DATENAME(MONTH,DATEADD(month, -4, GETDATE())) month,COUNT(*) AS countoftickets , 2 as fs FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, -4, GETDATE())) union
SELECT DATENAME(MONTH,DATEADD(month, -3, GETDATE())) month,COUNT(*) AS countoftickets , 3 as fs  FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, -3, GETDATE())) union
SELECT DATENAME(MONTH,DATEADD(month, -2, GETDATE())) month,COUNT(*) AS countoftickets , 4 as fs  FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, -2, GETDATE())) union
SELECT DATENAME(MONTH,DATEADD(month, -1, GETDATE())) month,COUNT(*) AS countoftickets , 5 as fs  FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, -1, GETDATE())) union
SELECT DATENAME(MONTH,DATEADD(month, 0, GETDATE())) month,COUNT(*) AS countoftickets , 6 as fs  FROM ticket WHERE MONTH(creationdate) = MONTH(DATEADD(month, 0, GETDATE()))  ) a1
order by fs

output:

Month   Countoftickets
October 10
November 12
December   5
January   17
February 20
March 15











---------------------------------------------------------------------------- All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.

How to wrap attributes in Birt based on space.

In the attribute which you want to wrap add the following code which wraps after every space to next line

if(dataSetRow["description"]!=null){

  dataSetRow["description "].replace(/ /g,"\n")

 }

/ /g à means white space anywhere in the string

----------------------------------------------------------------------------

All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.

How to wrap data attributes in Birt Report

Below function would be useful for wrapping the data present in a field.

if(dataSetRow["description"]!=null){

function wrap(longStr,width){ length = longStr.length; if(length <= width) return longStr; return (longStr.substring(0, width) + "\n" + wrap(longStr.substring(width, length), width)); }wrap( dataSetRow["description"], 15 );

}

in the above function, i am trying to wrap description field to a size of 15.

Only disadvantage of this is that it includes hard stops so after every 15 characters which can be frustrating to a user who uses the report in a excel sheet.

----------------------------------------------------------------------------
All the messages below are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.

Maximo .. Update database using Birt report

Sometimes database has to be updated after each report run showing that a report  has been run(example like last run date).

In Birt simple way to achieve this is to execute update query in the 'beforeClose' method of dataset.

var myTxn = MXReportTxnProvider.create("maximoDataSource");


var updateSqlText = new String();

updateSqlText = " update poline set enterdate=SYSDATE  where enterdate is null'";

var apextractStmt = myTxn.createStatement();
apextractStmt.setQuery(updateSqlText);

myTxn.save();

---------------------------------------------------------------------------- All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.

How to convert a string value to double in Birt report.

In the below example i am converting a string(UPPER) attribute to double value to perform so math calculations.

row["ASSETNUM"]=Double.parseDouble(mainDataSet.getString("ASSETNUM"));


----------------------------------------------------------------------------
All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.

Configuring BIRT..issue "There are errors evaluating script"

The following items have errors:
ReportDesign (id = 1):
+ There are errors evaluating script "importPackage(Packages.com.ibm.tivoli.maximo.report.script);

mxReportScriptContext = MXReportScriptContext.initialize(reportContext);
mxReportScriptContext.setDefaultLogLevel("DEBUG");
mxReportScriptContext.setDefaultLogFile("c:/temp/asset.txt");".

Table (id = 7):
+ There are errors evaluating script "maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = "select assetid, siteid, assetnum, description, location, binnum, parent, assettype, "
+ " status, priority, itemnum, islinear, installdate, manufacturer, "
+ " assettag, budgetcost, ytdcost, totalcost, warrantyexpdate,purchaseprice, "
+ " replacecost, moved, changedate, failurecode, conditioncode from asset "
+ " where " + params["where"];

maximoDataSet.setQuery(sqlText);".
+ There are errors evaluating script "if (!maximoDataSet.fetch())
return (false);

We did some silly mistakes because of which we were getting this error.Our mistake was we were not using the recomended JDK version.Please use JJDK1.5 version to solve this issue