maximo 76 Birt 4.3.1 Report Scheduling

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) + ")"  

Comments

  • wwilliamswwilliams Junior Member

    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

  • 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 !!!!!


  • wwilliamswwilliams Junior Member

    what is?


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


     


    what is your database?


  • wwilliamswwilliams Junior Member

    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


  • edited May 11

    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 


Sign In or Register to comment.