Javascript Date Functions in BIRT

One of the common tasks a report writer can be asked to do is sort or limit information based on certain dates or date ranges. They could use SQL functions like:

matusetrans.actualdate >= dateadd(day, -7, convert(varchar, getdate(), 101))
workorder.reportdate >= getdate() - 7
 
po.followupdate - getdate() >= 0
matusetrans.actualdate >= dateadd(month,datediff(month,1,getdate()) -1,0) and matusetrans.actualdate < dateadd(month,datediff(month,1,getdate()) +0,0)

As part of the report output, the report writer could be asked to display how the date range in the report has been curated. Now the first three are relatively easy to do with built in BIRT date/time functions. But what about the last one? That SQL query is looking at the first and last day of the previous month. How can you dynamically display those kinds of dates in a Maximo BIRT report?

The best way to display a specific date is to use an equivalent Javascript function [1] in BIRT to the SQL date function that was used in the data selection. The common dates report writers are asked to use include:

  • First/Last day of the previous month
  • First/Last day of the current month
  • First/Last day of the next month

Each of these date functions are shown below with the SQL Server query function [2] and the equivalent Javascript function that can be used in BIRT.


Background:
One background item that needs to be covered first is how Javascript counts compared to how normal people count. In Javascript the first object in a list starts at zero 0, and not at one 1. So when calculating/counting months in Javascript, January = 0, February = 1, ..., December = 11.

So when you look at some of the functions below, the following comparison is looking at January.

getMonth() == 0

Other functions, like addition or subtraction, work as expected.


First day of the previous month:
Functions look for the first day of the previous month, based on what the date is today.

SQL Server Function:

dateadd(month,datediff(month,1,getdate()) -1,0)

Javascript Function:

now = new Date();
if (now.getMonth() == 0) {
    current = new Date(now.getFullYear() - 1, 11, 1);
} else {
    current = new Date(now.getFullYear(), now.getMonth() - 1, 1);
}

Last day of the previous month:
Functions look for the last day of the previous month, based on what the date is today.

SQL Server Function:

dateadd(day, -1, dateadd(month,datediff(month,1,getdate()) +0,0))

Javascript Function: 

now = new Date();
if (now.getMonth() == 0) {
    current = new Date(now.getFullYear() - 1, 11, 31);
} else {
    current = new Date (new Date(now.getFullYear(), now.getMonth(), 1) - 1);
}

First day of the current month:
Functions look for the first day of the current month, based on what the date is today.

SQL Server Function: 

dateadd(month,datediff(month,1,getdate()) +0,0)

Javascript Function: 

now = new Date();
new Date(now.getFullYear(), now.getMonth(), 1);

Last day of the current month:
Functions look for the last day of the current month, based on what the date is today.

SQL Server Function:

dateadd(day, -1, dateadd(month,datediff(month,1,getdate()) +1,0))

Javascript Function: 

now = new Date();
new Date (new Date(now.getFullYear(), now.getMonth() +1 , 1) - 1);

First Day of Next Month:
Functions look for the first day of the next month, based on what the date is today.

SQL Server Function:

dateadd(month,datediff(month,1,getdate()) +1,0)

Javascript Function: 

now = new Date();
if (now.getMonth() == 11) {
    current = new Date(now.getFullYear() + 1, 1, 1);
} else {
    current = new Date(now.getFullYear(), now.getMonth() +1 , 1);
}

Last Day of Next Month:
Functions look for the last day of the next month, based on what the date is today.

SQL Server Function:

dateadd(day, -1, dateadd(month,datediff(month,1,getdate()) +2,0))

Javascript Function: 

now = new Date();
if (now.getMonth() == 11) {
    current = new Date(now.getFullYear() + 1, 1, 31);
} else {
    current = new Date(new Date(now.getFullYear(), now.getMonth() +2 , 1) -1 );
}

Implementing in BIRT Report:
To get these date fields into BIRT, the best way is to put the Javascript function into a DATA element and not in a Dynamic Text element. Why? A DATA element allows for formatting changes, which means we can modify the date format of the date field. A Dynamic Text element assumes the information will be just text and therefore has no formatting options.

In the BIRT report right click and insert a DATA element.

Once the DATA element is created, insert the applicable Javascript function from above into the Expression field for the element. As stated before, because the Javascript function has been entered as a DATA element, the date field can be formatted any way the report writer needs.

Comments

Top