Jump to content

BIRT Developer Center Download


Photo
- - - - -

Displaying variable value in the report

variable fetching variable displaying variable

  • Please log in to reply
4 replies to this topic

#1 greyzie14dev

greyzie14dev

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 11 January 2017 - 03:23 AM

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:

 

Attached File  Hierarchy.PNG   10.4KB   2 downloads

 

I'm using Eclipse v.3.4.2, is there any restriction? If none, how do I get it displayed?

 

Thanks a lot guys!



#2 mblock

mblock

    Expert Member

  • BIRT Experts
  • 1872 posts

Posted 11 January 2017 - 02:34 PM

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


  • Shelley likes this

Regards,

Mica J. Block

Principal Analytics Architect

OpenText Corporation


#3 wwilliams

wwilliams

    Junior Member

  • Members
  • 320 posts

Posted 12 January 2017 - 07:42 AM

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



#4 jperras

jperras

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 24 March 2017 - 05:19 AM

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?


#5 wwilliams

wwilliams

    Junior Member

  • Members
  • 320 posts

Posted 24 March 2017 - 05:44 AM

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







Also tagged with one or more of these keywords: variable, fetching variable, displaying variable