Jump to content

BIRT Developer Center Download


Photo
- - - - -

maximo 76 Birt 4.3.1 Report Scheduling


  • Please log in to reply
6 replies to this topic

#1 Vineet Joshi

Vineet Joshi

    Advanced Member

  • Members
  • PipPipPip
  • 92 posts

Posted 25 April 2017 - 11:49 AM

Hi All,

 

I want my report to run for today's date if date parameter is left blank(for scheduling in maximo). The below logic is working fine in  report designer and the report is working when user give the date manually or leave it blank. 

The isssue is when I import it in Maximo it gives me error when dates are left blank. Though it runs when i give it date from manually. Not sure what I am missing

 

Thanks in Advance !!!!!!

 

 

var start_date= new Date();
var end_date = new Date();
 
if( BirtComp.notEqual(params["p_startdate"], null)) {
 
start_date = params["p_startdate"]; 
}
else
{
start_date=BirtDateTime.today();
 
}
 
if( BirtComp.notEqual(params["p_enddate"], null)) {
 
end_date = params["p_enddate"]; 
}
else
{
//enddate=MXReportSqlFormat.getDateFunction(BirtDateTime.today());
end_date=BirtDateTime.today();
}
 
 
 
sqlText= " SELECT distinct "
+ " case when WORKORDER.istask=0 then WORKORDER.wonum  "
+ " else WORKORDER.parent "
+ " end as WONUM,WORKORDER.WORKTYPE,WORKORDER.description wodesc, WORKORDER.status,WORKORDER.OWNERGROUP,0 as hamdblinedt, "
+ " WORKORDER.woeq3, "
+ " labtrans.laborcode,person.displayname displayname_1,labtrans.skilllevel,labtrans.startdate,labtrans.STARTTIME,labtrans.finishdate,labtrans.FINISHTIME,WORKORDER.woeq1, "
+ " round(COALESCE(labtrans.regularhrs,0),2) regularhrs,(select round(sum(COALESCE(downtime,0)),2) AS MCDT From ASSETSTATUS ASSETSTATUS where "
+ " workorder.wonum=assetstatus.wonum and  workorder.siteid=assetstatus.siteid and (assetstatus.isrunning=1 or assetstatus.isrunning is NULL ) "
+ " ) MCDT,workorder.assetnum, labtrans.memo "
+ " from LABTRANS  "
+ " inner join WORKORDER  on workorder.wonum=labtrans.refwo and workorder.siteid=labtrans.siteid "
+ " inner join PERSON   on person.personid=labtrans.laborcode "
+ " where ( LABTRANS.startdate >=" + MXReportSqlFormat.getDateFunction(start_date) + " and " 
+ " labtrans.startdate<= " + MXReportSqlFormat.getDateFunction(end_date) + ")"  


#2 wwilliams

wwilliams

    Junior Member

  • Members
  • 331 posts

Posted 27 April 2017 - 03:45 AM

Maybe something like this

myWhere ="";

 

.

.

if(params["p_startdate"].value !=null && params["p_enddate"].value !=null){

myWhere =

 " where ( LABTRANS.startdate >=" + MXReportSqlFormat.getDateFunction(start_date) + " and " 
 " labtrans.startdate<= " + MXReportSqlFormat.getDateFunction(end_date) + ")"

} else {

 

myWhere =  "where LABTRANS.startdate >= trunc(sysdate) and trunc(labtrans.startdate)<= sysdate +1 "

}

 

replace

+ " where ( LABTRANS.startdate >=" + MXReportSqlFormat.getDateFunction(start_date) + " and " 
+ " labtrans.startdate<= " + MXReportSqlFormat.getDateFunction(end_date) + ")"
 
with
+ myWhere


#3 Vineet Joshi

Vineet Joshi

    Advanced Member

  • Members
  • PipPipPip
  • 92 posts

Posted 11 May 2017 - 04:40 AM

Hello W Williams,

 

Thank you for your reply !!! I tried the way you advised but it is not working from inside maximo though it is working in report designer. 

Can you please take a look at the xml file in the attachment and let me know what I am missing.

 

 

Thank you !!!!!

Attached Files



#4 wwilliams

wwilliams

    Junior Member

  • Members
  • 331 posts

Posted 11 May 2017 - 09:03 AM

what is?

(Current date - 30 DAYs) and (labtrans.startdate)<= (Current date) "

 

what is your database?



#5 Vineet Joshi

Vineet Joshi

    Advanced Member

  • Members
  • PipPipPip
  • 92 posts

Posted 11 May 2017 - 09:05 AM

DB2



#6 wwilliams

wwilliams

    Junior Member

  • Members
  • 331 posts

Posted 11 May 2017 - 10:01 AM

I know nothing about db2

to see sql in maximo

add

stext = new String();

in the beforeFactory

 

in your open

sqlText= " SELECT refwo, startdate from labtrans "
+  myWhere

stext = sqlText;

 

Then in your table header add a data column with the expression stext.

Hopefully the sql clause will appear and it should show you what is wrong



#7 Vineet Joshi

Vineet Joshi

    Advanced Member

  • Members
  • PipPipPip
  • 92 posts

Posted 11 May 2017 - 11:44 AM

Hello W Williams ,

 

This Issue is resolved . There was no error in Report design or Query syntax. 

All I had to do was to write "datelookup" in the look up field of Report Administration parameter page . 

 

Probably, Maximo Report engine was not able to decide what is the data type of parameters are,  making it not go in the else section of the Query .(Doesn't make sense to me though   :D:lol:

 

Thanks for help