BIRT – Generate a separate chart for each group value

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:

Creating a multi column BIRT report

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!

BIRT drop group header property with DOC output type

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:

BIRT drop group header property and table border lines

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.

Percentages in BIRT crosstab

With a small example, based on the Classicmodels database, I demonstrate how to get percentages in a BIRT crosstab table.

In the first step I create a crosstab table that shows just numbers. In the second step I modify the crosstab so that it shows percentages.

Step 1: the Query, the Data Cube and the Crosstab
Suppose you want to build a report that shows each country (from the customers table) and the number of orders in that country by year. That’s not too difficult:

First write the query:

select c.country,
       o.ordernumber,
       o.orderdate
from   customers c,
       orders o
where  c.customernumber = o.customernumber

Then build the Data Cube. We have two dimensions and one summary field:

For the year dimension, create a Date Group on orderdate:

Finally create the crosstab by dragging the Data Cube to the report layout. The Country will be a row dimension (drag to the left side of the crosstab), the Year will be a column dimension (drag it to the top of the crosstab). The results now look like this:

Step 2: Turning the numbers to percentages
So, now I want to know the percentage of orders placed by each country by year. In order to be able to calculate the percentage, I need to know the total number of orders by year. This can be done by dragging an aggregation item to the crosstab aggregation cell. I make the aggregation item a sum of the measure field (measure["ORDERNUMBER"]) grouped by year (GrpYear/Year), like this:

Finally, drag a Data item to the crosstab aggregation cell and enter an expression:

In the Number format property, choose Format as “percent”.
After making the measure field and the aggregation field invisible, the final result of the report now looks like this:

Showing different data records in the same report table

Sometimes you just can’t get the looks of your report right when creating multiple tables within an outer table. Especially when the inner tables differ from each other in number or type of fields. The solution I describe here uses SQL to build a dataset that selects all the types of rows you wish to have in the report in one single record, and makes use of the visibility property of the table row.

This solution may not be ideal in all circumstances; if the query is already very complex or if it heavily impacts database performance, things might not work out.

Testdata
The script below is created for an Oracle database and creates the tables that are used in this example:

CREATE TABLE offices (
  officecode VARCHAR2(10),
  city VARCHAR2(50),
  country VARCHAR2(20),
  CONSTRAINT pk_off PRIMARY KEY (officecode)
);

CREATE TABLE employees (
  officecode VARCHAR2(10) REFERENCES offices (officecode),
  employeenumber NUMBER(10),
  lastname VARCHAR2(30),
  firstname VARCHAR2(30),
  extension VARCHAR2(10),
  CONSTRAINT pk_emp PRIMARY KEY (employeenumber)
);

CREATE TABLE customers (
  customernumber NUMBER(10),
  customername VARCHAR2(30),
  contactlastname VARCHAR2(30),
  contactfirstname VARCHAR2(30),
  phone VARCHAR2(20),
  salesrepemployeenumber NUMBER(10) REFERENCES employees (employeenumber),
  CONSTRAINT pk_cus PRIMARY KEY (customernumber)
);

INSERT INTO offices VALUES (’1′, ‘San Francisco’, ‘USA’);
INSERT INTO offices VALUES (’2′, ‘Boston’, ‘USA’);
INSERT INTO offices VALUES (’3′, ‘NYC’, ‘USA’);
INSERT INTO offices VALUES (’4′, ‘Paris’, ‘France’);
INSERT INTO offices VALUES (’5′, ‘Tokyo’, ‘Japan’);

INSERT INTO employees VALUES (’1′, 1002, ‘Murphy’, ‘John’, ‘x5800′);
INSERT INTO employees VALUES (’1′, 1166, ‘Thompson’, ‘Leslie’, ‘x4065′);
INSERT INTO employees VALUES (’2′, 1188, ‘Firrelli’, ‘Julie’, ‘x2173′);
INSERT INTO employees VALUES (’3′, 1286, ‘Tseng’, ‘Foon Yue’, ‘x2248′);
INSERT INTO employees VALUES (’3′, 1323, ‘Vanauf’, ‘George’, ‘x4102′);
INSERT INTO employees VALUES (’4′, 1337, ‘Bondur’, ‘Loui’, ‘x6493′);
INSERT INTO employees VALUES (’4′, 1702, ‘Gerard’, ‘Martin’, ‘x2312′);

INSERT INTO customers VALUES (112, ‘Signal Gift Stores’, ‘King’, ‘Jean’, ’7025551838′, 1166);
insert into customers values (131, ‘Land of Toys Inc.’, ‘Lee’, ‘Kwai’, ’2125557818′, 1323);
INSERT INTO customers VALUES (146, ‘Saveley & Henriot, Co.’, ‘Saveley’, ‘Mary’, ’78.35555′, 1337);
insert into customers values (168, ‘American Souvenirs Inc’, ‘Franco’, ‘Keith’, ’2035557845′, 1286);
INSERT INTO customers VALUES (172, ‘La Corne D”abondance, Co.’, ‘Bertrand’, ‘Marie’, ‘(1) 42.32555′, 1337);
INSERT INTO customers VALUES (173, ‘Cambridge Collectables Co.’, ‘Tseng’, ‘Jerry’, ’6175555555′, 1188);
insert into customers values (181, ‘Vitachrome Inc.’, ‘Frick’, ‘Michael’, ’2125551500′, 1286);
INSERT INTO customers VALUES (202, ‘Canadian Gift Exchange Network’, ‘Tamuri’, ‘Yoshi’, ‘(604) 553392′, 1323);
insert into customers values (204, ‘Online Mini Collectables’, ‘Barajas’, ‘Miguel’, ’6175557555′, 1188);
insert into customers values (298, ‘Vida Sport, Ltd’, ‘Holz’, ‘Mihael’, ’089 034555′, 1702);
INSERT INTO customers VALUES (319, ‘Mini Classics’, ‘Frick’, ‘Steve’, ’9145554562′, 1323);
insert into customers values (320, ‘Mini Creations Ltd.’, ‘Huang’, ‘Wing’, ’5085559555′, 1188);

COMMIT;

The query
Let’s say you want a report that selects these data from the tables created in the previous step:
- all offices (code / city) in the USA
- all employees (last name / first name / extension) within each office
- all customers (contact last name / contact first name / phone) with each employee

First step is to build a query that selects all the fields in one record:

WITH sel_offices AS (
   SELECT o.officecode,
          o.city
   FROM   offices o
   WHERE  o.country = 'USA'
),
sel_employees AS (
   SELECT e.officecode,
          e.employeenumber,
          e.lastname,
          e.firstname,
          e.extension
   FROM   sel_offices o,
          employees e
   WHERE  e.officecode = o.officecode
)
SELECT 1 AS recordtype,
       o.officecode,
       o.city,
       -1 AS employeenumber,
       NULL AS lastname,
       NULL AS firstname,
       NULL AS extension,
       NULL AS contactlastname,
       NULL AS contactfirstname,
       NULL AS phone
FROM   sel_offices o
UNION  ALL
SELECT 2 AS recordtype,
       e.officecode,
       NULL AS city,
       e.employeenumber,
       e.lastname,
       e.firstname,
       e.extension,
       NULL AS contactlastname,
       NULL AS contactfirstname,
       NULL AS phone
FROM   sel_employees e
UNION  ALL
SELECT 3 AS recordtype,
       e.officecode,
       NULL AS city,
       e.employeenumber AS employeenumber,
       NULL AS lastname,
       NULL AS firstname,
       NULL AS extension,
       c.contactlastname,
       c.contactfirstname,
       c.phone
FROM   sel_employees e,
       customers c
WHERE  e.employeenumber = c.salesrepemployeenumber
ORDER  BY officecode, employeenumber, recordtype

As you can see, three types of records are created:

• 1: an office record
• 2: an employee record
• 3: a customer record

A number is used to describe a record type, so that it can easily be used to order the records. Also, a lot of fields are left empty, but officecode and employeenumber are selected in all three parts of the unioned query as they are needed to put records in the right order. In the office type record (recordtype = 1) employee number is empty, as it is unknown at that level. But Oracle always puts nulls first by default when ordering, so that shouldn’t be a problem.

The report
The next step is to create a report table that can hold all three record types. So the table has three detail rows: one for each record type. The visibility property expression of each row will return false for just one record type. For an office type of record, the visibility property looks like this:

For the second and the third row, the same needs to be done with row(“RECORDTYPE”) == 2 and row(“RECORDTYPE”) == 3

And with the Office records in grey, the Employee records in light grey and the customer records in white, the final result looks like this:

All data properly ligned out!

Oracle SQL with clause to create a BIRT crosstab dimension

In this example I’ll show how to create a report that counts the number of numerical values in a dynamically created category. The number of categories is variable as they are created by two parameters: the max value of the categories and the size of each category. Categories are created in the SQL With Clause and they are put in the BIRT report by creating a crosstab dimension for it.

Some parts of the query, like ROWNUM and the CONNECT BY clause,  are Oracle specific.

Creation of the table and the testdata
Run this script to create the table that is used in the report:

CREATE TABLE payments (
customernumber NUMBER(15),
paymentdate DATE,
amount NUMBER(16,2)
);

INSERT INTO payments VALUES (10000, to_date(’04012011′, ‘ddmmyyyy’), 6985.55);
INSERT INTO payments VALUES (10000, to_date(’08062010′, ‘ddmmyyyy’), 2258);
INSERT INTO payments VALUES (20000, to_date(’09042011′, ‘ddmmyyyy’), 26985.09);
INSERT INTO payments VALUES (20000, to_date(’11042011′, ‘ddmmyyyy’), 22255);
INSERT INTO payments VALUES (20000, to_date(’08042010′, ‘ddmmyyyy’), 21652.69);
INSERT INTO payments VALUES (20000, to_date(’09092010′, ‘ddmmyyyy’), 52690);
INSERT INTO payments VALUES (30000, to_date(’22012011′, ‘ddmmyyyy’), 89630.45);
INSERT INTO payments VALUES (30000, to_date(’15032011′, ‘ddmmyyyy’), 23500);
INSERT INTO payments VALUES (30000, to_date(’30032011′, ‘ddmmyyyy’), 10000);
INSERT INTO payments VALUES (40000, to_date(’30052011′, ‘ddmmyyyy’), 11000.11);
INSERT INTO payments VALUES (40000, to_date(’02052010′, ‘ddmmyyyy’), 9855);
INSERT INTO payments VALUES (50000, to_date(’11052010′, ‘ddmmyyyy’), 49867.25);
INSERT INTO payments VALUES (50000, to_date(’02052010′, ‘ddmmyyyy’), 32654);

COMMIT;

Creation of the categories – the Query
The boundaries of a category are calculated by two parameters:

• category size
• category max value

If the size = 10000 and the max values = 30000, then we‘ll have four categories:

• 0 – 10000
• 10000 – 20000
• 20000 – 30000
• >= 30000

The full query looks like this:

Let’s take a look at it step by step:

With params as
In the first with clause I take the parameters and give them an alias. Without it, the query would have many many “?” in it and I would need to create the same dataset parameters several times. (see http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/)

connect by
This construction is needed to build recursion into the query: categories will be added as long as the rownumber is smaller than the category max value divided by category size. In the previous example, this will be 30000 / 10000, so 3 categories will be created by this part of the query

union all select…
The select statement that comes after this part, creates the last category, which is the “>= max size” category

main query
The amount column in the payments table is joined to the categories table using the cat_from and the cat_to fields. Each payment will belong to exactly one category.

Creation of the crosstab
First the datacube needs to be created. There are two dimensions, Year and Category, and 1 summary field that uses the COUNT function:

To get the sorting of the categories in the crosstab right (small -> big), I have added a field cat_num. Using the label only didn’t work out as the largest category has a > sign in it, which breaks the ordering. So in the definition of the Category dimension I use the cat_num as key field and cat_label as display field:

Finally the crosstab is put into the report. The Year dimension appears at the left (row level), the category dimension appears at the top (column level):

So, if we run the report with category size = 25000 and category max value = 50000, then this will be the output:

If we run the report with category size = 10000 and category max value = 50000, then this will be the output:

As you can see: works perfectly!

Forms Modernization Seminar 6/6: Eclipse RCP anyone?

In many back-office applications, in Belgium and The Netherlands at least, Oracle Forms is a much seen framework. Praised for the stability and speed of development Oracle Forms was a major player in it’s time. However, today, these Oracle Forms applications get more and more outdated and end users and companies are requiring 21st century features like web service-, desktop- and social media-integration or an intuitive and feature-packed UI.

Often, companies that own such forms apps are told to migrate to another framework like Oracle ADF, JEE or .NET. I ‘d like to add another framework to the list: Eclipse RCP.

Eclipse RCP is an very modern, widely used and stable framework which fulfills all of today’s requirements for applications. Why Eclipse RCP?

During the Forms Modernization seminar organized by iAdvise, I will give a demo on how to integrate an existing Oracle Forms application inside an Eclipse RCP application allowing a very smooth and cost-effective transition from Oracle Forms to Eclipse RCP by retaining parts of the old application inside the new one.

Screenshot of an Oracle form inside an Eclipse RCP view

The demo will show:

• Oracle Forms inside an Eclipse RCP page
• how to use Eclipse RCP commands to control Oracle Forms (enter-query, execute-query, save,…)
• how to pass values from Eclipse RCP to Oracle Forms and vice versa
• master-detail views (Oracle Forms being the master)
• how to use Oracle Forms commands to show Eclipse RCP views and dialogs

The seminar is held on the 6th of June, 2011 at Hof ter Delft (www.hofterdelft.be)

If you like to attend please register here

Adding custom checkboxes to JFace TreeViewer

Goal:

For an RCP application which we are building at the moment we were in need of a Tree with checkboxes. It ‘s not just a question of using the JFace CheckboxTreeViewer because we wanted to control on which level in the tree the checkbox was shown. As you know, the CheckboxTreeViewer shows a checkbox on every level.

An added bonus is that now, we can change the look of the checkbox by supplying our own images.

After some investigation, I found this solution to be the easiest:

Solution:

1.
The first thing you need are two custom icons, one for a checked and one for an unchecked checkbox. I’ve used these icons for this example:

 

2.
Create a JFace TreeViewer with one TreeViewerColumn and three TreeItems (don’t use TreeItems with a TreeViewer in your application, use ContentProvider and LabelProvider instead). Add the unchecked icon to the TreeItems on the second level.

Example:

Composite compositeExample = new Composite(composite, SWT.NONE);
compositeExample.setLayoutData(new GridData(SWT.FILL, SWT.FILL, true, true, 1, 1));
TreeColumnLayout tcl_compositeExample = new TreeColumnLayout();
compositeExample.setLayout(tcl_compositeExample);

TreeViewer treeViewerExample = new TreeViewer(compositeExample, SWT.BORDER | SWT.FULL_SELECTION);
treeExample = treeViewerExample.getTree();
treeExample.setHeaderVisible(true);
treeExample.setLinesVisible(true);

TreeViewerColumn treeViewerColumnName = new TreeViewerColumn(treeViewerExample, SWT.NONE);
TreeColumn trclmnName = treeViewerColumnName.getColumn();
tcl_compositeExample.setColumnData(trclmnName, new ColumnWeightData(100, ColumnWeightData.MINIMUM_WIDTH, true));
trclmnName.setText("Name");

TreeItem trtmParent = new TreeItem(treeExample, SWT.NONE);
trtmParent.setImage(ResourceManager.getPluginImage("your_plugin_name", "your_image_name"));
trtmParent.setFont(FontConstants.FONT_NORMAL);
trtmParent.setText("Parent");

TreeItem trtmChild = new TreeItem(trtmParent, SWT.NONE);
trtmChild.setFont(FontConstants.FONT_NORMAL);
trtmChild.setImage(imgUnChecked);
trtmChild.setText("Child");

TreeItem trtmChild_1 = new TreeItem(trtmParent, SWT.NONE);
trtmChild_1.setFont(FontConstants.FONT_NORMAL);
trtmChild_1.setImage(imgUnChecked);
trtmChild_1.setText("Child");
trtmParent.setExpanded(true);

Once you have filled your tree and have a screen similar to below, you can add the real functionality.

3.
Now the icon has to change when the user clicks on it. We can achieve this by adding the following MouseListener to the tree. It will listen to clicks inside the tree and compare it with the items inside the tree. Once you know the clicked item, you can change his image.

tree_1.addMouseListener(new MouseListener(){

	@Override
	public void mouseDoubleClick(MouseEvent e) {
	}

	@Override
	public void mouseDown(MouseEvent e) {
		for(TreeItem item : tree_1.getSelection()) {
			if(item.getImage() != null) {
				if((e.x > item.getImageBounds(0).x) && (e.x < (item.getImageBounds(0).x + item.getImage().getBounds().width))) {
					if((e.y > item.getImageBounds(0).y) && (e.y < (item.getImageBounds(0).y + item.getImage().getBounds().height))) {
						setChecked(item);
					}
				}
			}
		}
	}

	@Override
	public void mouseUp(MouseEvent e) {
}});

private void setChecked(TreeItem item) {
	if(item.getImage().equals(imgUnChecked)) {
		item.setImage(imgChecked);
	}
	else {
		item.setImage(imgUnChecked);
	}
}

4.
Run you program and click on the icons, you will see that they really act like a checkbox. Now all you have to do is customize it!

Oracle Stored Procedure Data Set in BIRT

In BIRT reports it is possible to create a data set that is based on a stored procedure in the database.  In this post is discussed in detail how this needs to be done.  As an example I’ll show how to create a stored procedure in Oracle that meets the requirements to act as a data set and how to create the data set in BIRT.  As a bonus you’ll learn how to show data on a BIRT report from a temporary table.

Temporary table
First of all, create a temporary table in Oracle.  Feel free to skip this step if you are not interested in working with a temporary table.

CREATE GLOBAL TEMPORARY TABLE tmp_objects (object_name VARCHAR2(30), status VARCHAR2(20))
ON COMMIT PRESERVE ROWS;

As you can see, we have the ON COMMIT PRESERVE ROWS clause.  This is necessary because the default value of the auto-commit property of the JDBC datasource is set to TRUE (or auto, which also means TRUE).

If you really insist on having the ON COMMIT DELETE ROWS clause in the temporary table definition, or if you need to reuse an existing table that has this clause, you’ll need to change to auto-commit property to false.

Stored procedure
The stored procedure needs to return data in some way.  To serve as a data set in BIRT, an Oracle stored procedure needs to have an output parameter of type SYS_REFCURSOR.  The procedure in this example performs two steps:

• inserting all Oracle objects of a certain type in the temporay table
• open the cursor – select all records from the temporary table

CREATE OR REPLACE PROCEDURE refcur_test (
p_refcur OUT SYS_REFCURSOR,
p_type IN VARCHAR2
)
IS
BEGIN
-- pre-processing steps here
INSERT INTO tmp_objects (
object_name, status
)
SELECT o.object_name,
o.status
FROM all_objects o
WHERE o.object_type = p_type;
-- open the cursor
OPEN p_refcur FOR SELECT * FROM tmp_objects;
END;

BIRT data set
To create the data set in BIRT, start with selecting ‘SQL Stored procedure Query’ from the Data Set Type dropdown box, after clicking ‘new data set’

In the query area, make a call to the stored procedure. Don’t forget to mention the database owner in your call. Without it, things won’t work. (I’m not sure, but I think this is a JDBC issue)

Now create an output parameter to correspond with the refcursor parameter in the Oracle procedure.

And finally, take a look at the Preview Results to see if everything works fine. In my case, it does:

Et voila!