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).

Answer 2

create parameters in the scripted data set. we have to pass or link actual dataset values to scripted dataset parameters through DataSet parameter Binding after assigning the scripted data set to Table.