Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Use computed column to concatenate data set data
Use computed column to concatenate data set data [message #872826] Wed, 16 May 2012 18:30 Go to next message
Neil Wang is currently offline Neil Wang
Messages: 103
Registered: July 2009
Senior Member
Hi,

I am trying to create a string which is a concatenation of all the data in a data set.
For example, I have a data set "row[FirstName]" and it has data of "John", "Alice" and "Mary". I would like to create a string which is "John, Alice, Mary" (with comma in between data).

I am thinking about using computed column function in data set to achieve this but don't know how to grab each data from the data set.

please advise.

Neil

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873026 is a reply to message #872826] Thu, 17 May 2012 06:52 Go to previous messageGo to next message
Tomas Greif is currently offline Tomas Greif
Messages: 52
Registered: September 2010
Member
Hi,

you can use "CONCATENATE" as agregation function. Create new computed column with row[FirstName] as Expression, in Aggregation choose "CONCATENATE" and type "," as Separator.

Tomas

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873188 is a reply to message #872826] Thu, 17 May 2012 12:42 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member
Neil

Use the concatenate aggregate function with an aggregation element.
Look at the attached example.

Jason

On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873191 is a reply to message #872826] Thu, 17 May 2012 12:42 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member
Neil

Use the concatenate aggregate function with an aggregation element.
Look at the attached example.

Jason

On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873193 is a reply to message #872826] Thu, 17 May 2012 12:42 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member
Neil

Use the concatenate aggregate function with an aggregation element.
Look at the attached example.

Jason

On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873194 is a reply to message #873026] Thu, 17 May 2012 12:43 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member
Sorry Tomas, I did not see that you had replied.

Jason

On 5/17/2012 6:52 AM, Tomas Greif wrote:
> Hi,
>
> you can use "CONCATENATE" as agregation function. Create new computed
> column with row[FirstName] as Expression, in Aggregation choose
> "CONCATENATE" and type "," as Separator.
> Tomas

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Use computed column to concatenate data set data [message #873654 is a reply to message #873026] Fri, 18 May 2012 15:14 Go to previous messageGo to next message
Neil Wang is currently offline Neil Wang
Messages: 103
Registered: July 2009
Senior Member
Hi Thomas,

Thank you for your comment and it works.

I have another question.

I have a data set with this information

Child data set
Key|Value|ID|Concatenation
AAA|BBBBB|01|AAABBBBB, CCCDDDDD
CCC|DDDDD|01|AAABBBBB, CCCDDDDD

The goal of the concatenation is to concatenate all key and value columns into one string which works fine.

I have another data set like this

Parent data set
Name|ID
Bill|01

I need to create a join data set with ID as the join key and the result looks like

Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
Bill| 01| CCC| DDDDD| 01| AAABBBBB, CCCDDDDD

However, I would like to have the result to look like
Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD

I am not gonna display Child:Key and Child:Value in my report; therefore, I don't mind having/not having them in the final result set. The whole purpose is to have one parent to have its "Key + Value" concatenated in one string; therefore, having two rows defeats the purpose as I put them in the detailed row of a table, both rows will show up. I am thinking I should keep just one row in the child result set; so that, the final result set will have one row only; however, I am not sure how to do it. Please advise.

Neil

Report message to a moderator

Re: Use computed column to concatenate data set data [message #876073 is a reply to message #873654] Wed, 23 May 2012 17:11 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member
Neil

Did you get this to work?

Jason

On 5/18/2012 3:14 PM, Neil Wang wrote:
> Hi Thomas,
>
> Thank you for your comment and it works.
>
> I have another question.
>
> I have a data set with this information
>
> Child data set
> Key|Value|ID|Concatenation
> AAA|BBBBB|01|AAABBBBB, CCCDDDDD
> CCC|DDDDD|01|AAABBBBB, CCCDDDDD
>
> The goal of the concatenation is to concatenate all key and value
> columns into one string which works fine.
> I have another data set like this
>
> Parent data set
> Name|ID
> Bill|01
>
> I need to create a join data set with ID as the join key and the result
> looks like
>
> Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
> Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
> Bill| 01| CCC| DDDDD| 01| AAABBBBB, CCCDDDDD
>
> However, I would like to have the result to look like
> Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
> Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
>
> I am not gonna display Child:Key and Child:Value in my report;
> therefore, I don't mind having/not having them in the final result set.
> The whole purpose is to have one parent to have its "Key + Value"
> concatenated in one string; therefore, having two rows defeats the
> purpose as I put them in the detailed row of a table, both rows will
> show up. I am thinking I should keep just one row in the child result
> set; so that, the final result set will have one row only; however, I am
> not sure how to do it. Please advise.
>
> Neil
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Use computed column to concatenate data set data [message #876274 is a reply to message #873654] Thu, 24 May 2012 05:37 Go to previous message
Tomas Greif is currently offline Tomas Greif
Messages: 52
Registered: September 2010
Member
Hi Neil,

I think you can do the following:

- add new computed column to the child dataset. Use aggregation "IS-TOP-N", in expression enter row["concatenation"], in N enter 1. This will return true only for one row. You can name the column like "test"
- add filter to the child dataset. Add condition that "test" column equals to "true"

Anyway I would suggest to do the data pre-processing in database if possible.

Tomas

Report message to a moderator

Previous Topic:Export feature in report
Next Topic:Not able to Install BIRT 3.7.2 on JVM 64 bit
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 20 03:02:33 EDT 2013

Powered by FUDForum. Page generated in 0.02313 seconds