How do I split birt dataset column into multiple rows

Question

My datasource has a column that contains a comma-separated list of numbers.

I want to create a dataset that takes those numbers and turns them into groupings to use in a bar chart.

requirements

1. numbers will be between 0-17 inclusive
2. groupings: 0-2,3-5,6-10,11-17
3. x-axis labels have to be the groupings
4. y-axis is the percent of rows that contain that grouping
   • note that because each row can contribute to multiple columns the percentages can add up to > 100%

any help you can offer would be awesome... i'm very new to BIRT and have been stuck on this for a couple days now

Answer 1

Not sure that I understand the requirements exactly, but your basic question "split dataset column into multiple rows" can be solved either using a scripted dataset or with pure SQL (depending on your DB).

Either way, you will need a second dataset (e.g. your data model is master-detail, and in your layout you will need something like

Table/List "Master bound to master DS Table/List "Detail" bound to detail DS

The detail DS need the comma-separated result column from the master DS as an input parameter of type "String".

Doing this with a scripted dataset is quite easy IFF you understand Javascript AND you understand how scripted datasets work: Create a report variable "myValues" of type object with a default value of null and a second report variable "myValuesIndex" of type integer with a default value of 0.

(Note: this is all untested!)

Create the dataset "detail" as a scripted DS, with one input parameter "csv" of type String and one output parameter "value" of type String.

In the open event of the scripted DS, code:

  vars["myValues"] = this.getInputParameterValue("csv").split(",");
  vars["myValuesIndex"] = 0;

In the fetch event, code:

  var i = vars["myValuesIndex"];
  var len = vars["myValues"].length;
  if (i < len) {
    row["value"] = vars["myValues"][i];
    vars["myValuesIndex"] = i+1;
    return true;
  } else {
    return false;
  }

For example, for the master DS result row with csv = "1,2,3-4,foo", the detail DS will result in 4 rows with value = "1" value = "2" value = "3-4" value = "foo"

Using an Oracle DB, this can be done without Javascript. The detail DS (with the same input parameter as above) would then look like:

  select t.value as value from table(split(?)) t

For the definition of the split function, see RedFilter's answer on Is there a function to split a string in PL/SQL?

If you get ORA-22813, you should change the original definition

  create or replace type split_tbl as table of varchar2(32767);

to

  create or replace type split_tbl as table of varchar2(4000);

as mentioned on https://community.oracle.com/thread/2288603?tstart=0

It's also possible with pure SQL in 11g using regexp_substr (see the same page).