Displaying variable value in the report

Hi people of great minds!

 

I would like to ask for your help, please...

 

In my project "open" script, I've declared 2 variables (StartDate, EndDate):

 

//




 

var sqlText = new String();


sqlText =


DECLARE @StartDate as DATETIME

DECLARE @EndDate as DATETIME


SET @StartDate = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

SET @EndDate = convert(datetime,convert(varchar(10),(getdate()-1),120) + ' 23:59:59')

 

< select >

< from >

where

workorder.actfinish >= @StartDate

and workorder.actfinish <= @EndDate

 

 

maximoDataSet.setQuery(sqlText);

 

//


 

Now, how do I fetch the values of StartDate and EndDate for them to get displayed in my report?

I've read through few forums and articles and most of those suggest declaring a global variable, but "Variables" is not available in my hierarchy:

 


Comments

  • mblockmblock Expert Member

    Easiest would be to do the same calculation in an expression in the report. Using JavaScript instead of Oracle SQL


    Regards,

    Mica J. Block

    Principal Analytics Architect

    OpenText Corporation

  • wwilliamswwilliams Junior Member

    Try something this in your beforeFactory


    startdate= Formatter.format(BirtDateTime.addMonth(BirtDateTime.firstDayOfMonth(BirtDateTime.today()),-1), "MM-dd-yyyy") ;


    enddate= Formatter.format(BirtDateTime.addDay(BirtDateTime.today(),-1), "MM-dd-yyyy") + " 23:59:59"  ;


     


    then format those dates for SQL Server


  • I am running into a similar issue. I have built a multitude of reports with date ranges so that they can be scheduled. If a user enters a report parameter date, it will display fine in the layout. However, if they don't select a value on the report, it will display nothing on the layout. A requirement is that if a date is selected then display that, however, if they do not select a value the layout should display to the previous Sunday or Saturday. My variable is set up like this in the open statement:


     


    var start_date = new String();

    var end_date = new String();

     


    if(params["startdate"].value) {

    start_date = " and invtrans.transdate >= " + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);

    }

    else { start_date = " and invtrans.transdate >= DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, getdate()) - 1, 0)) "; }

     

    if(params["enddate"].value) {

    end_date = " and invtrans.transdate <= " + MXReportSqlFormat.getEndDayTimestampFunction(params["enddate"]);

    }

    else { end_date = " and invtrans.transdate < DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, getdate()) - 1, 0)) "; }

     

    I attempted to add the same code to the expression builder since this is javascript but I am getting a date conversion issue on the second sql statement. 

     

    Does anyone have any thoughts on displaying the start_date or end_date values either in the sql script or in the layout view?

  • wwilliamswwilliams Junior Member

    Duplicate the SQL Server logic with BIRTfunctions\BIRTDateTime.. for  display


Sign In or Register to comment.