Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Parse data into second Data Set
Parse data into second Data Set [message #868753] Wed, 02 May 2012 13:48 Go to next message
Michael Chambers is currently offline Michael ChambersFriend
Messages: 2
Registered: May 2012
Junior Member
Hi, I'm new to BIRT and am using 3.7.1.
I have an SQL Stored Procedure Data Set pulling data from Oracle.
The procedure's output (now) returns one row per entity. One of the columns is a bunch of joined detail data that my report needs to split into multiple detail rows per entity.

The field is named CODING and the data comes back in the format:
Type1~~ID1~~Name1~~~~~;~Type1~~ID2~~Name2~~0~~0~;~Type2~~ID3~~Name3~~100~~25083

I plan to parse on ~~ and ~;~ into a per-row detail table of:
Type || ID || Name || Percent || Dollars
Type1 | ID1 | Name1 | |
Type1 | ID2 | Name2 | 0 | 0
Type2 | ID3 | Name3 | 100 | 25083

First, I'm looking for feedback on the right approach. I have seen the example of nesting the Order Details table in the main Orders row. That looks like what I want, so I'm trying to figure out how to get from a parsed collection local variable (in javascript or java) into a new data set. Or perhaps I should handle it differently.

Your thoughts?

Also, in which (data set?) event would you write the function to deal with each master row of data. I'm guessing I wouldn't iterate over all of the master rows at once.

(Reqs - I want to only hit the database once per report - the procedure takes a while, and the grouped data returned doesn't lend itself to further queries for details. As the output includes CLOBs, I only want one row per entity, so have already adjusted my SQL to mash the many-to-one detail data together to allow one returned row per entity.)

(First post, so I apologize if I'm not following preferred conventions.)
Thanks,
Michael
Re: Parse data into second Data Set [message #869010 is a reply to message #868753] Wed, 02 May 2012 21:09 Go to previous messageGo to next message
Michael Chambers is currently offline Michael ChambersFriend
Messages: 2
Registered: May 2012
Junior Member
The solution was to add a scripted data set, sending the long data string as a parameter "codingparam".
Data Set's open:
i=0;
coding = inputParams["codingparam"];
arrCoding = coding.split("~;~");

Data Set's fetch:
if(i>=arrCoding.length){
return false;
}
codingRow=arrCoding[i];
arrCodingRow=codingRow.split("~~");
row["CODE_TYPE"] = arrCodingRow[0];
row["CODE_ID"] = arrCodingRow[1];
row["CODE_NAME"] = arrCodingRow[2];
if(arrCodingRow.length>3){
row["PERCENT_RELEVANCE"] = arrCodingRow[3];
row["PERCENT_DOLLARS"] = arrCodingRow[4];
}
++i;
return true;
Re: Parse data into second Data Set [message #869672 is a reply to message #868753] Thu, 03 May 2012 16:02 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Michael,

You could have an outer table tied to the stored proc and and an inner
table that is a scripted data set. The scripted data set could take the
outer table row value as an input parameter that you could then parse.
I am not certain how fast this would be though.

I created a simple example with your data and attached it.

Jason


On 5/2/2012 9:48 AM, Michael Chambers wrote:
> Hi, I'm new to BIRT and am using 3.7.1.
> I have an SQL Stored Procedure Data Set pulling data from Oracle.
> The procedure's output (now) returns one row per entity. One of the
> columns is a bunch of joined detail data that my report needs to split
> into multiple detail rows per entity.
>
> The field is named CODING and the data comes back in the format:
> Type1~~ID1~~Name1~~~~~;~Type1~~ID2~~Name2~~0~~0~;~Type2~~ID3~~Name3~~100~~25083
>
>
> I plan to parse on ~~ and ~;~ into a per-row detail table of:
> Type || ID || Name || Percent || Dollars
> Type1 | ID1 | Name1 | |
> Type1 | ID2 | Name2 | 0 | 0
> Type2 | ID3 | Name3 | 100 | 25083
>
> First, I'm looking for feedback on the right approach. I have seen the
> example of nesting the Order Details table in the main Orders row. That
> looks like what I want, so I'm trying to figure out how to get from a
> parsed collection local variable (in javascript or java) into a new data
> set. Or perhaps I should handle it differently.
>
> Your thoughts?
>
> Also, in which (data set?) event would you write the function to deal
> with each master row of data. I'm guessing I wouldn't iterate over all
> of the master rows at once.
>
> (Reqs - I want to only hit the database once per report - the procedure
> takes a while, and the grouped data returned doesn't lend itself to
> further queries for details. As the output includes CLOBs, I only want
> one row per entity, so have already adjusted my SQL to mash the
> many-to-one detail data together to allow one returned row per entity.)
>
> (First post, so I apologize if I'm not following preferred conventions.)
> Thanks,
> Michael
>
Re: Parse data into second Data Set [message #869674 is a reply to message #869010] Thu, 03 May 2012 16:32 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Sorry I did not see this.

Jason

On 5/2/2012 5:09 PM, Michael Chambers wrote:
> The solution was to add a scripted data set, sending the long data
> string as a parameter "codingparam".
> Data Set's open:
> i=0;
> coding = inputParams["codingparam"];
> arrCoding = coding.split("~;~");
>
> Data Set's fetch:
> if(i>=arrCoding.length){
> return false;
> }
> codingRow=arrCoding[i];
> arrCodingRow=codingRow.split("~~");
> row["CODE_TYPE"] = arrCodingRow[0];
> row["CODE_ID"] = arrCodingRow[1];
> row["CODE_NAME"] = arrCodingRow[2];
> if(arrCodingRow.length>3){
> row["PERCENT_RELEVANCE"] = arrCodingRow[3];
> row["PERCENT_DOLLARS"] = arrCodingRow[4];
> }
> ++i;
> return true;
Previous Topic:bar chart with literal array for Category (x) series?
Next Topic:Report Parameter values size
Goto Forum:
  


Current Time: Fri Jan 24 04:16:19 GMT 2020

Powered by FUDForum. Page generated in 0.01714 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top