Nowadays with Oracle Open World, twitter is buzzing with words like “Forms Modernization”, “Forms isn’t dead” and so on. In these tweets and posts people are encouraging Forms builders to modernize their Forms applications by introducing some features that enhance the looknfeel and usage of Forms. Another way of modernizing Forms, they say, is to migrate to the Oracle ADF framework, which is Oracle’s development framework flagship. If it were up to Oracle they would like to see all Forms applications migrated to ADF.

Luckily, there is another way, and a very smart one indeed! Why not migrate your Forms application to the industry-proven Eclipse RCP platform. As you may or may not know, with the Eclipse RCP framework it is possible to create full blown desktop applications. I know many Forms developers would welcome back the possibility of a client/server application, because, let’s face it, it’s much much faster than a web application (even with ajax).

To demonstrate the migration of Oracle Forms to Eclipse RCP, I have created a little demo. This demo follows a scenario that a company could follow if they decide to migrate Oracle Forms.

In the scenario, our company owns an Oracle Forms application to manage HR data. Lately, they have deployed an application to manage the company’s car fleet, which was built with Eclipse RCP. Of course, there must be some integration because our car fleet application needs data from the HR database. Instead of writing a bunch of routines to deal with the HR database and data, the company decided to simply embed the Oracle Forms HR application inside the car fleet application and to have them “talk” to each other. This way, our company is able to quickly and cheaply integrate both applications and to postpone the migration of the Oracle Forms HR application to the new platform or to gradually migrate piece by piece.

Advertisement

This is a followup on a comment I received on an earlier post about generating a chart in a grouping header: Generate a separate chart for each group value.

If you take a look at the screenshot of the resulting report in that post, you see that for the year 2005 there’s only a bar for the months that have records in the data set. The commenter asked for what needs to be done to show all months of the year, also the ones that don’t have data in the dataset.

In the chart edit screen, on the Format Chart tab, select the X-axis on the left side and uncheck the “Is Category Axis”:

Then click the Scale button and modify it like this:

This is what the report looks like now:

The last couple of months I noticed that people often struggle with using dates in BIRT, especially in the context of datasets. So I decided to write a few words about it.

First of all
Dates are not saved in a database in some date format. They are saved as a date and that’s it. Dates do get formatted when they are presented to you, for example on your screen. The format then depends on the settings of the software program you are using or on your machine settings.

Also, if a user enters a date that needs to be saved in a database, you’ll have to let the database know in what format the value is entered, so it can be converted to the correct date.

To achieve all this, you could rely on default date format settings, but that doesn’t make your software very portable, does it?

Dataset parameters of date type
In a typical BIRT reporting situation, you have a report parameter that is bound to a dataset parameter that is used in your SQL query.

If you make sure that:

  • the report parameter is of type date
  • the dataset parameter is of type date
  • the parameter value is compared to a date type column in the query

then there shouldn’t be any problem at all.

In this case, there’s only one place where the date format is important: the user has to enter a parameter value. This format can easily be adapted to your needs in the Edit Parameter dialog. In this example I created a rather exotic custom format “MM-yyyy-dd”:

Now the user has to enter the date in the custom format:

And when shown in the report using the default date format settings, the parameter value looks – at least on my machine – like this:

Concatenating a date parameter to queryText
Though in my opinion the use of concatenating to queryText should be avoided whenever possible, a lot of developers seem to like it. When concatenating a date type parameter to the queryText in the beforeOpen event of the dataset, the parameter value is implicitly converted to a string, which also means that a format is applied to it.

The best way to make sure your database will accept the concatenated value as a date, is to add a date formatting construction in the query, as well as at the parameter value that is implicitly converted into a string. The message is: do explicit conversion on both sides.

When using the Apache Derby sample database, this can be done like this:

importPackage( Packages.java.text );

// get the value of parameter dateParameter as entered by the user
var dateParamVal = params["dateParameter"];

// transform the date into a String using the yyyy-MM-dd format 
var dateFormatOut = new SimpleDateFormat("yyyy-MM-dd");
var formattedDate = dateFormatOut.format(dateParamVal);

// the database will convert the string value into a date value
// yyyy-MM-dd is one of the formats the Apache Derby database recognizes as a date
this.queryText = this.queryText + " AND orderdate = Date('" + formattedDate + "')";

(I’m not specialized in Apache Derby databases, I learned about date functions here: http://db.apache.org/derby/papers/JDBCImplementation.html#Conversion+of+a+string+type+to+a+JDBC+java.sql.Date)

To do the same for an Oracle database, it looks like this

importPackage( Packages.java.text );

// get the value of parameter dateParameter as entered by the user
var dateParamVal = params["dateParameter"];

// transform the date into a String using the yyyy-MM-dd format 
var dateFormatOut = new SimpleDateFormat("yyyy-MM-dd");
var formattedDate = dateFormatOut.format(dateParamVal);

// the database will convert the string value into a date value
// yyyy-MM-dd is the format that Oracle will use to convert the string into a date datatype
this.queryText = this.queryText + " AND orderdate = to_date('" + formattedDate + "', 'yyyy-mm-dd')";

Our Birt guru, Hans van Dorst, will be presenting at the Actuate Birt Day on 25th of April, 2012. He will be talking about best practices on report architecture such as libraries, templates, sql statements … and system architecure.

Please join  us on the Birt Day in Utrecht, The Netherlands

More info: http://www.actuate.nl/birtday/BIRTDay2012.pdf

Finally, after a lot of work, our new website is now live!
Please take a look at our website and let us know what you think!

In an earlier post I explained how to use a crosstab item to create a multi column BIRT report. But when there are one or more groups in your report, that approach does not suffice. In this post I’ll describe a slightly different way to determine row and column numbers, so that they can be used in the crosstab together with a grouping on the data.

In this example I use the PRODUCTS table from the Classicmodels database. My goal is to show products in three columns grouped by productline.

Report variables
First I create 5 report variables. They’ll be used in the onFetch event of the dataset:

The query
This is the query I used:

 SELECT productline,
       productname
FROM   products
ORDER  BY productline, productname

The “order by productline” is important here, as you’ll see when the onFetch event script is discussed.

Computed columns
Add four computed columns to the dataset. In the expression you can enter a dummy value, I entered zero for all of them:

Dataset onFetch event script
In the onFetch script of the dataset the report variables that were described earlier will be filled and assigned to the computed columns in the dataset:

 if (vars["groupName"] != row.PRODUCTLINE) {
   vars["groupName"] = row.PRODUCTLINE;
   vars["groupNum"]++;
   vars["groupRownum"] = 1;
   vars["cubeColnum"] = 0;
   vars["cubeRownum"] = 3;
}
else {
   vars["groupRownum"]++;
   vars["cubeColnum"] = BirtMath.mod(vars["groupRownum"]-1, 3)
   vars["cubeRownum"] = BirtMath.ceiling(vars["groupRownum"], 3)
}

row.groupNum = vars["groupNum"];
row.groupRownum = vars["groupRownum"];
row.cubeColnum = vars["cubeColnum"] + 1;
row.cubeRownum = vars["cubeRownum"];

The datacube
In the datacube I create two dimensions. The row dimension combines the PRODUCTLINE column and the cubeRownum computed column. The column dimension uses the cubeColumn computed column.

The PRODUCTNAME column will be the summary field. Don’t forget to change the datatype of this field to String and the function to FIRST.

The crosstab
This is what the crosstab should initially look like:

To get a decent layout, you can execute these simple steps:

  • select the crosstab and then select the “Hide Measure Header” checkbox in the general properties tab
  • select the cell that contains the cubeRownum data element and set the width property to zero

    If you execute the report after these modifications, it will look like this:

    As you can see, the data is nicely spread over three columns until the next group comes up. Exactly how we wanted it.

In BIRT it is possible to create a table grouping and have a chart generated for each group value. In this blog I explain how it can be done using the sample database.

The Query
Create a dataset dsOrders on the ORDERS table from the sample database with this query:

select year(orderdate) order_year,
       month(orderdate) order_month,
       status
from   orders

The Report Table and the Chart
Drag a table element (1 row, 1 column) to the report design section and bind it to your data set dsOrders. Insert a group into the table where you Group On order_year. In the group header appears a datafield order_year.
Now add a second group header row underneath the first one and drag a chart item in it. Select Stacked Bar Chart as the chart subtype. On the Select Data tab, make sure to choose “use Data from dsOrders”, instead of the default selection which is “Inherit data from container”. Enter the dialog like this:

The number of orders will be counted grouped by month and status.

To make sure that every chart shows data from the corresponding year only, select the chart and add this filter:

The row.outer[“ORDER_YEAR”] part can be constructed through the expression.

Results
So if we run the report, a chart will be shown for each year:

Up to now, BIRT has no feature to show records in multiple columns. In the following step by step instructions I explain how you can create a multi column report by using a crosstab.

The query
Create a very simple query to show employees from the sample database:

SELECT lastname||' '||firstname as name,
       email
FROM   employees

Computed columns
Add three computed columns to the dataset:

  • rownum: This one is created for nothing but serving the next two computed columns. It’s a runningcount aggregation.
  • colnum: this will be the column dimension. It’s a mod calculation of rownum against the number of columns you want in the report. To make sure that (if there is a odd number of records in the dataset) the last record is put in the first column, substract the mod calculation value from the number of columns
  • cubeRownum: this column will serve as the row dimension; it gives two, or whatever number of columns you want in your report, consecutive records the same numeric value

The cube
There are two dimensions in the cube:

  • rownum
  • colnum

And two summary fields:

  • name
  • email

Don’t forget to change the datatype of the summary fields to String and the function to FIRST, like this:

The crosstab
Follow these steps to create the crosstab:

  • drag a crosstab item from the palette to the report layout
  • drag the colnum dimension to the top area of the crosstab
  • drag the cuberownum dimension to the left area of the crosstab
  • drag the summary fields to the data field area of the crosstab
  • finally, to create a clean layout, drag a 3 line, 1 column grid to the data field area and drag the name summary field to the first row and the email summary field to the second row of it
  • by dragging the summary fields into the data field area, two columns are created. You can remove one of them by right-clicking it and choose: remove.
  • you can remove the Name and Email labels from the measure header

The crosstab should look like this now:

If you run the report, it should look like this:

That’s probably not how you want it to be, so a few extra steps are needed to tweak the layout.

Layout issues
First remove the column header by checking the Hide Measure Header checkbox in the crosstab properties:

As there is no such property for the column measure, select both the left measure column and the column above the left measure column like this:

Now go to advanced properties section and set the following properties:

  • Width = 0
  • Border Left style = No Line Style (same for right, top and bottom)
  • Border Left width = 0 (same for right, top and bottom)

Next, select the entire crosstab table and set this properties:

  • Border Left style = No Line Style (same for right, top and bottom)

Finally, the report will look like this:

Just perfect!

In an earlier post I wrote about the drop property and how to make it look decent when it is used in a table that has border lines set. Now it turns out that the drop property does not work properly when the output type is DOC or XLS.

Drop property result in DOC output
This is what the report from the earlier post looks like with output type set to DOC:

The cells are merged correctly but the headers are not dropped.

Workaround
To work around this issue, I started with removing all grouping headers, then I dragged all fields into the detail section and I added two aggregations: a RUNNINGCOUNT in the Country column that aggregates by the Country grouping and similar a RUNNINGCOUNT in the Manager lastname column that aggregates by the Manager grouping. I set both aggregation fields invisible. The aggregation on the Country grouping looks like this:

Next I set the borders of the grouping columns like this:

And then I added some scripts to the report.
In the report’s Initialize event:

var showTopBorderCountry = "1";
var showTopBorderManager = "1";

In the onCreate event of the Detail row:

if (this.getRowData().getExpressionValue("row[AggCountry]") == "1")
   showTopBorderCountry = "1";
else
   showTopBorderCountry = "0";
    
if (this.getRowData().getExpressionValue("row[AggManager]") == "1")
   showTopBorderManager = "1";
else
   showTopBorderManager = "0";

In the onCreate event of the Country header cell:

if (showTopBorderCountry == "1"){
    this.getStyle().borderTopColor="Black";
    this.getStyle().borderTopStyle="Solid";
	this.getStyle().borderTopWidth="Thin";
}

In the onCreate event of the Manager header cells:

if (showTopBorderManager == "1"){
    this.getStyle().borderTopColor="Black";
    this.getStyle().borderTopStyle="Solid";
	this.getStyle().borderTopWidth="Thin";
}

Finally, in the visibility property of the header cells, I wrote this expression:

if (row["AggManager"] > 1) true; else false; 

Now the DOC output looks like this:

Only one week ago I learned about the group header’s drop property to drop header columns to the detail line. Several questions about it came across on the BIRT forums and I decided to take a closer look at it.

Find the drop property
The drop property is not in any right-click menu nor does it have it’s own section in the properties tab. To find it, you have to select the header cell – not the header row as you might expect – of which you want the value to drop to the detail row and go to the “Advanced” properties:

Yes, it works!
From the drop down you can choose “All” or “Detail”. I selected “Detail” and the results look like this:

As you can see, the Country, a header field, is shown on the same line as the first detail line. Exactly what we were looking for.

Now I create a second grouping, on manager. I drag the managers’s lastname and firstname to the new header row and for both cells I set the drop property to “Detail”. Now the results look like this:

Again: exactly what we were looking for!

No it doesn’t…
What? Why is that? Well, take a look at what the report looks like when I add table border lines to the table:

The linesizes got all messed up. Apparently the borders of the header cells show up on the detail level cells, but not on the header level cells.

…Or does it?
To solve this “problem”, I started playing with border width properties of individual cells, and after trying lots of possible combinations I could get a satisfying result. This is what I did:

  • detail border top width = 0 (red cells)
  • manager header border top width = 0 (green cells + yellow cells)
  • manager header border bottom width = 0 (yellow cells)
  • country header border bottom width = 0 (all cells except for the one in the Country column)

The colors point out the cells that need above property changes:

The resulting report now looks like this:

So it did work out eventually, but it sure took a lot of time, not to mention the blood, sweat and tears…

If anybody knows a more clever workaround for this, please leave a comment.