| Report Parameters [message #1739676] |
Wed, 03 August 2016 17:34  |
 Aaron Easton
Messages: 4
Registered: August 2016 |
Junior Member |
|
|
I have a report that i did not build. The issue that i have is how it looks at the parameter date and time when it looks back a month ago. I just need it to look at the 1st and the last day. I am not worry about the time stamp.
Here is the report parameters:
importPackage( Packages.java.text );
var sqlText = new String();
var myDF1 = new SimpleDateFormat("MM/dd/yyyy", reportContext.getLocale());
var cMonth = new String();
var cMonth2 = new String();
var cMonth3 = new String();
var cMonth6 = new String();
var cMonth8 = new String();
var nDate1 = new Date();
var nDate2 = new Date();
var nDate3 = new Date();
nDate1=BirtDateTime.addMonth(BirtDateTime.now(),-params["MonthAgo"].value);
nDate1=BirtDateTime.addDay(nDate1,1-BirtDateTime.day(nDate1));
if(params["MonthAgo"].value==0) {
nDate2=BirtDateTime.addDay(BirtDateTime.now(),1);
cMonth3=" ";
cMonth6=" AND (1=2) ";
cMonth8="CURRENT TIMESTAMP";
} else {
nDate2=BirtDateTime.addMonth(BirtDateTime.now(),2-params["MonthAgo"].value);
nDate2=BirtDateTime.addDay(nDate2,1-BirtDateTime.day(nDate2));
nDate3=BirtDateTime.addDay(nDate2,-BirtDateTime.day(nDate2));
cMonth3=" AND (READINGDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
cMonth6=" AND (READINGDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
cMonth8="DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate3)+" 23:59:59','MM/DD/YYYY HH24:MI:SS'))";
};
cMonth= " (REPORTDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY'))) AND (REPORTDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
cMonth2=" (ft.DATEFLT>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY'))) AND (ft.DATEFLT<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
I need some help on getting the last month only. Thanks for any help.
|
|
|
| Re: Report Parameters [message #1739679 is a reply to message #1739676] |
Wed, 03 August 2016 18:25  |
Ed Merks
Messages: 33140
Registered: July 2009 |
Senior Member |
|
|
Best to ask on the BIRT forum, though answers are a little sparse there...
https://www.eclipse.org/forums/index.php/f/2/
On 03.08.2016 20:08, Aaron Easton wrote:
> I have a report that i did not build. The issue that i have is how it
> looks at the parameter date and time when it looks back a month ago.
> I just need it to look at the 1st and the last day. I am not worry
> about the time stamp.
>
>
> Here is the report parameters:
>
> importPackage( Packages.java.text );
> var sqlText = new String();
> var myDF1 = new SimpleDateFormat("MM/dd/yyyy",
> reportContext.getLocale());
> var cMonth = new String();
> var cMonth2 = new String();
> var cMonth3 = new String();
> var cMonth6 = new String();
> var cMonth8 = new String();
>
> var nDate1 = new Date();
> var nDate2 = new Date();
> var nDate3 = new Date();
>
> nDate1=BirtDateTime.addMonth(BirtDateTime.now(),-params["MonthAgo"].value);
>
> nDate1=BirtDateTime.addDay(nDate1,1-BirtDateTime.day(nDate1));
>
> if(params["MonthAgo"].value==0) {
> nDate2=BirtDateTime.addDay(BirtDateTime.now(),1);
> cMonth3=" ";
> cMonth6=" AND (1=2) ";
> cMonth8="CURRENT TIMESTAMP";
> } else {
> nDate2=BirtDateTime.addMonth(BirtDateTime.now(),2-params["MonthAgo"].value);
>
> nDate2=BirtDateTime.addDay(nDate2,1-BirtDateTime.day(nDate2));
> nDate3=BirtDateTime.addDay(nDate2,-BirtDateTime.day(nDate2));
> cMonth3=" AND
> (READINGDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY')))
> ";
> cMonth6=" AND
> (READINGDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY')))
> ";
> cMonth8="DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate3)+"
> 23:59:59','MM/DD/YYYY HH24:MI:SS'))";
> };
>
> cMonth= "
> (REPORTDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY')))
> AND
> (REPORTDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY')))
> ";
> cMonth2="
> (ft.DATEFLT>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY')))
> AND
> (ft.DATEFLT<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY')))
> ";
>
>
>
> I need some help on getting the last month only. Thanks for any help.
Ed Merks
Professional Support: https://www.macromodeling.com/
|
|
|
Powered by
FUDForum. Page generated in 0.02800 seconds
] 
Search
Help
Register
Login
Home
