Grouping within a BIRT data set
In the context of the Plug In 2 BIRT Contest for Autumn 2012, I created the Group Functions plugin. You can find the plugin, documentation and a sample report on birt-exchange Devshare. The group function aggregations in the plugin make it possible to generate the SUM, COUNT or ROWNUMBER by a group of data within a BIRT data set. With the right combination of these functions and the use of filters you can create many-to-many relations in a joint data set by first applying grouping in your data sets. You can read more on this in the pdf that you can download from the link mentioned earlier.

Screenshots
Here’s a screenshot of how a computed column is created with the GROUPSUM function:
grpfUse

And this is the result in the preview results area of the data set:
grpfResult

You can vote
The contest closed on november 30 and now registered birt-exchange users can vote for the plugin they like most on birt-exchange (the poll is in the sidebar on the right). So if you like this feature, give it a vote. Thanks!

In this post a small report is built that selects a text from the database. This text contains codes which need to be replaced by data from another data set.

Let’s say you have the texts that need to appear on a letter in the database and one part of it is this salutation: “Dear <title> <name>,”.

There are two datasets in the report:

  • letter_text, that selects the salutation text
  • customer, that selects the title and the name of the customer the letter will be sent to

Build the report layout following these steps:

  • create a table element with data set = letter_text
  • in the detail row of the table, create a second table with data set = customer
  • in the detail row of the customer table, create a Dynamic Text item with expression = eval(row._outer[“TEXT”])

So it comes down to save the text parts in the database as javascript that can be executed in the Dynamic Text item expression.  This is how the database tables are created:


CREATE TABLE letter_texts (text VARCHAR2(2000));
CREATE TABLE customers (cust_id NUMBER(9), title VARCHAR2(20), cust_name VARCHAR2(200));
INSERT INTO letter_texts VALUES ('"Dear " + row["TITLE"] + " " + row["CUST_NAME"] + ","');
INSERT INTO customers VALUES (1, 'Mr', 'Smith');

COMMIT;

The report now looks like this:

And this should be the result:

We had to build an Eclipse RCP application where SQL statements could be edited. At first, we used a StyledText widget to do this. Soon, we were in need of extra features like syntax coloring, content assist etc. Why not use a ‘ready to use’ SQLEditor to open a SQL file you say? Well, we wanted to ’embed’ the area in our own editor. The SQL area should be part of the editor and not the only thing in the editor!

After a while we discovered a normal StyledText was not the way to do this. After some investigation we found a widget which fits our needs perfectly.. The SQLStatementArea widget which is part of the Eclipse Data Tools Platform (DTP). After installing all necessary plugins, coding could begin..

The SQLStatementArea widget can be used like any other widget in a Editor.

ISQLSourceViewerService viewerService = new CustomSQLSourceViewerService();
SQLStatementArea sta = new SQLStatementArea(this, SWT.BORDER, viewerService, true);
sta.setEditable(true);
sta.setEnabled(true);
  • this: the SWT Composite where the area should be in
  • SWT.BORDER: we want a border to be visible, other styles can be added as usual
  • viewerService: an instance of a class implementing ISQLSourceViewerService (talk about this in a minute)
  • true: when this is true, line numbers are visible

When instantiating the SQLStatementArea a class implementing ISQLSourceViewerService is needed as mentioned earlier. In this class a help method needs to be implemented which will define the PartitionScanner for the document. This method determines where the text has to be scanned for.

This is the code of the method we used for this class:

@Override
public void setUpDocument(IDocument doc, String dbType) {
SQLPartitionScanner sqlPartitionSanner = new SQLPartitionScanner();
if(doc instanceof IDocumentExtension3)
{
IDocumentExtension3 extension3 = (IDocumentExtension3) doc;
FastPartitioner _partitioner = new FastPartitioner(sqlPartitionSanner, new String[]
{
SQLPartitionScanner.SQL_CODE,
SQLPartitionScanner.SQL_COMMENT,
SQLPartitionScanner.SQL_MULTILINE_COMMENT,
SQLPartitionScanner.SQL_STRING,
SQLPartitionScanner.SQL_DOUBLE_QUOTES_IDENTIFIER
});
_partitioner.connect(doc);
extension3.setDocumentPartitioner(ISQLPartitions.SQL_PARTITIONING,     _partitioner);
}
}

Next in line is the SourceViewerConfiguration. This class is responsible for syntax coloring, content assist etc. In our application we’ve used the code from here, because it is more advanced and extensive (multi-line comments for example). However , the code in the available plugins is sufficient for this tutorial. The configuration class to use for a SQL configuration is the SQLSourceViewerConfiguration which can be found in the org.eclipse.datatools.sqltools.sqlbuilder.views.source package.

SQLSourceViewerConfiguration sqlSourceViewerConfiguration = new SQLSourceViewerConfiguration();
sta.configureViewer(sqlSourceViewerConfiguration);

There are still a few things that need to be added in order for the SQLStatementArea to work. As most widgets, it needs a layoutdata, but it also needs a document which holds the actual input. Because were going to add databinding later, we’re setting up an empty document for now.

sta.setLayoutData(new GridData(GridData.FILL_BOTH));
document = new Document();
document.set("");
sta.getViewer().setDocument(document);

Our next step is to add databinding so the text is displayed correctly and the object is updated immediately  This is easy because the SQLStatementArea holds a StyledText widget which we can use to create the binding. When you’re not familiar with databinding you can check out this great tutorial or just set the text of the document for now (instead of empty).

IObservableValue observeTextObserveWidget = SWTObservables.observeText(sta.getViewer().getTextWidget(), SWT.Modify);
IObservableValue sqlSql_statementObserveValue = EMFEditProperties.value(editingDomain, Literals.DOCUMENT_SQL_STATEMENT__SQL_STATEMENT).observe(sql);
bindingContext.bindValue(styledTextStatementObserveTextObserveWidget, sqlSql_statementObserveValue, null, null);
  • editingDomain: we’re connecting the binding to our editingdomain for undo/redo functionality and dirty state (use EMFProperties when not using an editingdomain)
  • sql: the EMF object which holds our SQL statement

When running the application, the result is an editor which holds a great area for editing SQL.

SQLStatementArea integrated in Eclipse Editor

SQLStatementArea integrated in Eclipse Editor

The only thing is, when hitting CTRL+SPACE now, content assist doesn’t work. This is because we have to ‘bind’ the content assist of our SQLStatementArea with the Eclipse content assist.

handlerService = (IHandlerService) editor.getSite().getService(IHandlerService.class);
IHandler cahandler = new AbstractHandler() {
public Object execute(ExecutionEvent event) throws ExecutionException {
sta.getViewer().doOperation(ISourceViewer.CONTENTASSIST_PROPOSALS);
return null;
}
};
if(contentAssistHandlerActivation != null){
handlerService.deactivateHandler(contentAssistHandlerActivation);
}
contentAssistHandlerActivation = handlerService.activateHandler(ITextEditorActionDefinitionIds.CONTENT_ASSIST_PROPOSALS,
cahandler);

Now, when starting the application again, content assist will work!

SQLStatementArea integrated in Eclipse Editor with content assist

SQLStatementArea integrated in Eclipse Editor with content assist

When using this in an application we recommend to use the code mentioned earlier or to write your own. This way you can have far more keywords, multi-line comments, content formatting (uppercase/lowercase) and so on. But we hope this tutorial gives you a great start!

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.

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