Embed Javascript functions in BIRT

One of the tasks I’ve taken on for my company is migrating our custom reports for Maximo from Crystal Reports to BIRT. I’ve written about this in the past and the primary reason is the ability to pass parameters from the Maximo environment into the report. Using BIRT allows end users to not have to re-enter a Work Order number or their Person ID in a report. Maximo can pass that information automagically to BIRT. One of the downsides I’ve run into with the move to BIRT is the loss of some of the built in functions in Crystal Reports.

I’ve been using Crystal Reports since it was called Seagate Reports. Crystal Reports is a Windows based application and has a strong background with Visual Basic. So with the use of Visual Basic, Crystal Reports has a lot of short cut functions built right in. The best example I have is getting the name of the day of the week from a record. With some very simple code, Crystal will return the day of the week. For example:

WeekdayName (DayOfWeek ({mxtable.mxfield}), true)

This code uses two Crystal functions:

DayOfWeek (date)

WeekdayName (weekday, abbreviate)

DayOfWeek will convert a date field into an integer (from 0-6) on what day of the week the date falls. The WeekdayName function takes an integer value of what day of the week it is and returns the name of the day. There is an argument value to verify if the name of the day should be abbreviated or not. So if the argument is set to ‘true’, then 0 = Sun, 1 = Mon, 2 = Tue, etc. So now using them together I can show the name of the day in a report, based on a date field from Maximo.

So how can you do that in BIRT? BIRT has a similar hook as Crystal Report for embedded functionality, but instead of using Visual Basic, BIRT uses Java and Javascript. So the best way to handle this is to create a Javascript function to transform a date field into the name of the day.  I’ve previously posted how you can embed an external Javascript [link] into a BIRT report. I received several comments on how could this be done without using an external file. This example will also show how to embed a Javascript function into the report file itself.

The first step is to see what tools does BIRT offer us natively. Knowing that Javascript has several ways to modify a date/time field, we’ll start there. Using the .getDay() function, BIRT can return a numeric value of the day of the week of a date/time field. To demonstrate, enter the following into a dynamic text field:

new Date(“January 1, 2012 13:00:00″).getDay();

Once the report is previewed in Report Designer, the dynamic text field will return a value of ’0′ because 2012-01-01 landed on a Sunday. Sunday is the first day of the week and therefore Sunday = 0, Monday =1, etc. Same results as the Visual Basic function in Crystal Reports.

So now that you can get a numeric value of what day of the week a date/time field value is, next step is to convert that value over to a name of the day. Looking through date/time functions in Javascript shows there aren’t any built-in functions. So now we get to build our own. Note: this post isn’t going to give details on how to build a Javascript function, but an example is provided below.

A Javascript function to return a string for the name of the day of the week, based on the integer value of the .getDay() function.

function dayofWeek(ddate) {     var weekday=new Array(7);
weekday[0]=”Sun”;
weekday[1]=”Mon”;
weekday[2]=”Tue”;
weekday[3]=”Wed”;
weekday[4]=”Thu”;
weekday[5]=”Fri”;
weekday[6]=”Sat”;

return weekday[ddate.getDay()];
}

So how do you use the Javascript function inside BIRT? Javascript can be embedded in the Open method of a data set of a BIRT report when you add a reportContext object to the function statement.

reportContext.setPersistentGlobalVariable(“func_str”, func_str);

This statement allows the Javascript to be used globally throughout the report. An example Open method script for a report, using the Javascript created above, would look like this:

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open(); var sqlText = new String();

var where = params["where"];
sqlText = “SELECT labor.laborcode, labor.orgid, labor.worksite, labor.status, personcal.shiftnum, workperiod.workhours, ”
+ ” workperiod.workdate, person.displayname, workperiod.starttime, workperiod.endtime, laborcraftrate.craft ”
+ ” FROM labor ”
+ ” INNER JOIN personcal ON (labor.laborcode=personcal.personid) AND (labor.orgid=personcal.orgid) ”
+ ” INNER JOIN person ON labor.personid=person.personid ”
+ ” LEFT OUTER JOIN laborcraftrate ON (labor.orgid=laborcraftrate.orgid) AND (labor.laborcode=laborcraftrate.laborcode) ”
+ ” INNER JOIN workperiod ON (personcal.calnum=workperiod.calnum) AND (personcal.orgid=workperiod.orgid) AND (personcal.shiftnum=workperiod.shiftnum) ”
+ ” where ” + params["where"]
+ ” where workperiod.workdate >= dateadd(day, 0, convert(varchar, getdate(), 101)) ”
+ ” AND workperiod.workdate <= dateadd(day, 6, convert(varchar, getdate(), 101)) ”
+ ” AND (laborcraftrate.craft=’AUTOTECH’ OR laborcraftrate.craft=’MECH’) ”
+ ” AND labor.status=’ACTIVE’ AND labor.worksite= ? ”
+ ” and ” + where + ” ORDER BY labor.laborcode ” ;

function dayofWeek(ddate) {
var weekday=new Array(7);
weekday[0]=”Sun”;
weekday[1]=”Mon”;
weekday[2]=”Tue”;
weekday[3]=”Wed”;
weekday[4]=”Thu”;
weekday[5]=”Fri”;
weekday[6]=”Sat”;

return weekday[ddate.getDay()];
}

reportContext.setPersistentGlobalVariable(“dayofWeek”, dayofWeek);

maximoDataSet.setQuery(sqlText);
maximoDataSet.setQueryParameterValue(1, params["site"].toUpperCase());

To use the Javascript function in the report, create a dynamic text or custom data field using the following statement in Expression Builder:

dayofWeek (row["workdate"]);

Now the date/time value returned in the report for the workperiod.workdate field will be converted to a short day name. So if the workperiod.workdate is for example, 2012-10-09, the report would show a string value of “Tue”.

 

Got any questions or comments? Hit me up on Twitter or App.Net at @MyGeekDaddy.

 

UPDATE (2012-10-09): I’ve received some direct feedback and comments below regarding the choice of Javascript in this post. BIRT (as of at least 2.3.2) has a built in DateTime function that will do the same thing as the Javascript above. I used this function as an example of something I believed would be easy to follow, not necessarily something that was lacking in BIRT.

4 thoughts on “Embed Javascript functions in BIRT

  1. Sean

    Thank you for the posts! I may be tasked soon with moving a huge batch of Crystal Reports to BIRT reports and your blog has been great for reading.

    One thought: if you have the licensing and a crystal reports server, you can give context to crystal reports though Maximo. The key is in Maximo’s “launch in context” feature (available as of 7.1; not sure about prior) — as long as you know how crystal reports forms its URL, you can create a link from a Maximo button, etc. that passes parameters through a URL. I think I have a blog post either written or drafted about this process; let me know if you’d like more information on it.

    Overall, it’s better IMO to go the BIRT route, though it does promise to be painful.

  2. admin

    Thanks for the feedback! Good luck with your migration and feel free to hit me up if you have any questions.

  3. Andrew Tess

    Jason,
    Thank you for the tutorial on embedding javascript in BIRT Reports. But I am wondering why you are not using the Format DateTime in the properties on the data element in the report design? Specifically the “EE” in the format as Custom.

  4. jason.verly Post author

    I’ve had a couple of direct comments just like yours, and you are correct, the simplest method would be to use the built in BIRT function. The goal of the script was something simple and easy to follow. I’ve added a clarification statement at the bottom regarding the confusion. Thanks for the feedback.

Comments are closed.