Dynamic data in BIRT data set column

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:

Advertisement