Home » Archived » BIRT » Date Parameter
| Date Parameter [message #364113] |
Tue, 22 July 2008 15:39  |
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  |
| 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 |
 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 |
|
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);
|
|
|
Goto Forum:
Current Time: Fri Apr 19 21:06:44 GMT 2024
Powered by FUDForum. Page generated in 0.03377 seconds |
] 
Search
Help
Register
Login
Home
