| Parse data into second Data Set [message #868753] |
Wed, 02 May 2012 13:48  |
 Michael Chambers
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 #869672 is a reply to message #868753] |
Thu, 03 May 2012 16:02  |
|
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 |
|
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;
|
|
|
Powered by
FUDForum. Page generated in 0.02867 seconds
] 
Search
Help
Register
Login
Home
