Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Date Parameter
Date Parameter [message #364113] Tue, 22 July 2008 15:39 Go to next message
Eclipse User
Originally posted by: mrong.facilities.rutgers.edu

Greeting,

I try to use a parameter to filter date field in Oracle.

I created a dynamic dataset for lookup column. It shows the following
format on the report parameter: June 25, 2008 10:10AM. But when I select
this value and run the report, it doesn't give me any record.

Thanks in advance.

-M
Re: Date Parameter [message #364139 is a reply to message #364113] Wed, 23 July 2008 15:07 Go to previous messageGo to next message
Eclipse User
Originally posted by: jasonweathersby.alltel.net

Mark,

Can you post the report?

Jason

Mark wrote:
> Greeting,
>
> I try to use a parameter to filter date field in Oracle.
>
> I created a dynamic dataset for lookup column. It shows the following
> format on the report parameter: June 25, 2008 10:10AM. But when I select
> this value and run the report, it doesn't give me any record.
>
> Thanks in advance.
>
> -M
>
Re: Date Parameter [message #786540 is a reply to message #364139] Mon, 30 January 2012 14:52 Go to previous messageGo to next message
Rahul Sawant is currently offline Rahul Sawant
Messages: 1
Registered: January 2012
Junior Member
Hi,

I have pasted my code below... Please let me know how should I declare the date parameter.

Your help is much appreciated.

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

if(maximoDataSet.isOracle()){
dbClause = "nvl";
asClause = "";
db2QueryAlias = "";
}
else{
dbClause = "coalesce";
}

if(params["glaccount"].value) {
params["glaccount"] = params["glaccount"].toUpperCase();
//params["where"]+= " and w.glaccount like '"+params["glaccount"]+"'";toDateString()
}

//df = new Packages.java.text.SimpleDateFormat("MM-dd-yyyy");
//begindate = df.parse(row["reportdate"]);
//begindate;

if (params["begindate"].value){
//params["begindate"]=params["begindate"].toDateString();
//params["where"]+= " and w.reportdate >= "
// + MXReportSqlFormat.getDateFunction(params["begindate"]);
}

if (params["enddate"].value){
//params["enddate"]=params["enddate"].toDateString();
//params["where"]+= " and w.reportdate <= '"
//+ MXReportSqlFormat.getDateFunction(params["enddate"])+ "'";
}



var sqlText = new String();
// Add query to sqlText variable.
sqlText = " select pl.refwo, pl.ponum,pl.polinenum,"
+" pl.unitcost * (sum(distinct pl.orderqty)-isnull(sum(m.quantity/m.conversion),0))* (1+(isnull(t.taxrate,0)/100)) obnr into #polinetemp"
+" from matrectrans m, workorder w,"
+" (select taxcode,taxrate from tax t1 where effective = (select max(effective) "
+" from tax t2 where t2.taxcode = t1.taxcode group by taxcode)) t,"
+" (select pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,pl.orderqty,pl.tax1code,pl.receiptscomplete"
+" from poline pl, po where pl.ponum = po.ponum and po.status not in ('CAN','CLOSE')) pl "

+" where w.glaccount like '"+params["glaccount"]+"'"
//+" and w.reportdate >= '"+ params["begindate"] +"'"
//+" and w.reportdate <= '"+ params["enddate"] + "'"+"'23:59:59'"
//+" and w.reportdate <= '"+params["enddate"]+"' + '23:59:59'"
+" and m.ponum =* pl.ponum and m.polinenum =* pl.polinenum and pl.refwo = w.wonum and pl.tax1code *= t.taxcode "
+" and pl.receiptscomplete <> '1' " //+ params["where"]
+"group by pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,t.taxrate"

+" select refwo wonum, sum(obnr) obnr into #temp from #polinetemp where isnull(obnr,0) >= 0 group by refwo"

+" SELECT distinct workorder.glaccount, workorder.wonum, workorder.description, workorder.actmatcost,"
+" workorder.acttoolcost, workorder.actlabcost, workorder.esttoolcost, workorder.estlabcost, workorder.wopm4,"
+" workorder.wopm2, workorder.reportdate, workorder.worktype, workorder.wopm5, workorder.targstartdate,"
+" workorder.actlabhrs, workorder.wo5, isnull(obnr,0) obnr"

+" FROM workorder workorder, stard.dbo.billing_source_dept b, #temp t"

+" where workorder.glaccount LIKE '"+params["glaccount"]+"'"
//+" and w.reportdate >= '" + MXReportSqlFormat.getDateFunction(params["begindate"])+"'"
//+" and w.reportdate <= '" + MXReportSqlFormat.getDateFunction(params["enddate"])+ "'"+"'23:59:59'"
+" and workorder.reportdate >= '"+ params["begindate"] +"'"
+" AND workorder.reportdate <= '"+params["enddate"]+"' + ' 23:59:59'"
+" and workorder.wonum not in (select distinct BILLING_NUMBER from facbilld.dbo.billing_detail "
+" where source in ('max','sts','vit','sec')"
+" and bill_date_time is not null and bill_date_time <> '')"
+" and workorder.glaccount is not null and workorder.status not in ('CAN', 'CLOSE') "
+" and workorder.worktype not in ('BY','RO') and ((b.source in ('max','sts','vit','sec') "
+" and left(workorder.glaccount, 5) = b.deptid) or left(workorder.glaccount, 5) = '55352') "
+" AND workorder.wopm2 not in ('4') AND workorder.wopm2 is not null"
+" AND workorder.wopm2 <> '' AND (substring(workorder.wopm4, 1, 3) <> '160' "
+" or (substring(workorder.wopm4,1,5) >= '16062' and substring(workorder.wopm4,1,5) <= '16099') "
+" or substring(workorder.woPM5,1,2) in ('43','44','46')) AND (substring(workorder.wopm4, 1, 5)) not in ('53250')"
+" and workorder.wonum *= t.wonum " //+ params["where"]
+" ORDER BY workorder.wonum"
;

maximoDataSet.setQuery(sqlText);
Re: Date Parameter [message #786670 is a reply to message #786540] Mon, 30 January 2012 17:35 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Rahul,

I am not to familiar with Maximo, but you should be able to set the date
format in the design in the parameters editor. What you may want to do
is to print out the sql that you are generating in the script and verify
it. Add something like:

importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/temp/myquery.txt", true ) );
out.println( "Query passed to Maximo is " + sqlText);
out.close();

to the bottom of the script and then check the txt file to see what is
being generated.

Jason

On 1/30/2012 9:52 AM, Rahul Sawant wrote:
> Hi,
>
> I have pasted my code below... Please let me know how should I declare
> the date parameter.
>
> Your help is much appreciated.
>
> maximoDataSet =
> MXReportDataSetProvider.create(this.getDataSource().getName(),
> this.getName());
> maximoDataSet.open();
>
> if(maximoDataSet.isOracle()){
> dbClause = "nvl";
> asClause = "";
> db2QueryAlias = "";
> }
> else{
> dbClause = "coalesce";
> }
>
> if(params["glaccount"].value) {
> params["glaccount"] = params["glaccount"].toUpperCase();
> //params["where"]+= " and w.glaccount like
> '"+params["glaccount"]+"'";toDateString()
> }
>
> //df = new Packages.java.text.SimpleDateFormat("MM-dd-yyyy");
> //begindate = df.parse(row["reportdate"]);
> //begindate;
>
> if (params["begindate"].value){
> //params["begindate"]=params["begindate"].toDateString();
> //params["where"]+= " and w.reportdate >= " // +
> MXReportSqlFormat.getDateFunction(params["begindate"]);
> }
>
> if (params["enddate"].value){
> //params["enddate"]=params["enddate"].toDateString();
> //params["where"]+= " and w.reportdate <= '" //+
> MXReportSqlFormat.getDateFunction(params["enddate"])+ "'";
> }
>
>
>
> var sqlText = new String();
> // Add query to sqlText variable.
> sqlText = " select pl.refwo, pl.ponum,pl.polinenum,"
> +" pl.unitcost * (sum(distinct
> pl.orderqty)-isnull(sum(m.quantity/m.conversion),0))*
> (1+(isnull(t.taxrate,0)/100)) obnr into #polinetemp"
> +" from matrectrans m, workorder w," +" (select taxcode,taxrate from tax
> t1 where effective = (select max(effective) "
> +" from tax t2 where t2.taxcode = t1.taxcode group by taxcode)) t,"
> +" (select pl.refwo,
> pl.ponum,pl.polinenum,pl.unitcost,pl.orderqty,pl.tax1code,pl.receiptscomplete"
>
> +" from poline pl, po where pl.ponum = po.ponum and po.status not in
> ('CAN','CLOSE')) pl "
>
> +" where w.glaccount like '"+params["glaccount"]+"'"
> //+" and w.reportdate >= '"+ params["begindate"] +"'"
> //+" and w.reportdate <= '"+ params["enddate"] + "'"+"'23:59:59'"
> //+" and w.reportdate <= '"+params["enddate"]+"' + '23:59:59'" +" and
> m.ponum =* pl.ponum and m.polinenum =* pl.polinenum and pl.refwo =
> w.wonum and pl.tax1code *= t.taxcode " +" and pl.receiptscomplete <> '1'
> " //+ params["where"]
> +"group by pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,t.taxrate"
>
> +" select refwo wonum, sum(obnr) obnr into #temp from #polinetemp where
> isnull(obnr,0) >= 0 group by refwo"
>
> +" SELECT distinct workorder.glaccount, workorder.wonum,
> workorder.description, workorder.actmatcost,"
> +" workorder.acttoolcost, workorder.actlabcost, workorder.esttoolcost,
> workorder.estlabcost, workorder.wopm4,"
> +" workorder.wopm2, workorder.reportdate, workorder.worktype,
> workorder.wopm5, workorder.targstartdate,"
> +" workorder.actlabhrs, workorder.wo5, isnull(obnr,0) obnr"
>
> +" FROM workorder workorder, stard.dbo.billing_source_dept b, #temp t"
>
> +" where workorder.glaccount LIKE '"+params["glaccount"]+"'"
> //+" and w.reportdate >= '" +
> MXReportSqlFormat.getDateFunction(params["begindate"])+"'"
> //+" and w.reportdate <= '" +
> MXReportSqlFormat.getDateFunction(params["enddate"])+ "'"+"'23:59:59'"
> +" and workorder.reportdate >= '"+ params["begindate"] +"'"
> +" AND workorder.reportdate <= '"+params["enddate"]+"' + ' 23:59:59'"
> +" and workorder.wonum not in (select distinct BILLING_NUMBER from
> facbilld.dbo.billing_detail "
> +" where source in ('max','sts','vit','sec')"
> +" and bill_date_time is not null and bill_date_time <> '')"
> +" and workorder.glaccount is not null and workorder.status not in
> ('CAN', 'CLOSE') "
> +" and workorder.worktype not in ('BY','RO') and ((b.source in
> ('max','sts','vit','sec') "
> +" and left(workorder.glaccount, 5) = b.deptid) or
> left(workorder.glaccount, 5) = '55352') "
> +" AND workorder.wopm2 not in ('4') AND workorder.wopm2 is not null"
> +" AND workorder.wopm2 <> '' AND (substring(workorder.wopm4, 1, 3) <>
> '160' "
> +" or (substring(workorder.wopm4,1,5) >= '16062' and
> substring(workorder.wopm4,1,5) <= '16099') "
> +" or substring(workorder.woPM5,1,2) in ('43','44','46')) AND
> (substring(workorder.wopm4, 1, 5)) not in ('53250')"
> +" and workorder.wonum *= t.wonum " //+ params["where"]
> +" ORDER BY workorder.wonum"
> ;
>
> maximoDataSet.setQuery(sqlText);
Previous Topic:BIRT basics
Next Topic:Gracefully Handle Bad DB Login
Goto Forum:
  


Current Time: Tue Sep 23 16:32:14 GMT 2014

Powered by FUDForum. Page generated in 0.01601 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software