Dynamic data in BIRT data set column

2012/11/16

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: