Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Grouping multiple rows from result into single cell(Grouping multiple rows from result into single cell)
icon5.gif  Grouping multiple rows from result into single cell [message #907455] Tue, 04 September 2012 03:25 Go to next message
Simon Yeh is currently offline Simon YehFriend
Messages: 4
Registered: September 2012
Junior Member
Hi,There:
Rolling Eyes

Here is my result table in the preview of DataSet
===================
| Name | Group |
===================
Clark Admin
Clark Financial
Clark Staff
John Staff
John Engineer


The requirement is to generate report as blow table, combine all group into one cell.
===================
| Name | Group |
===================
Clark Admin, Financial, Staff
John Staff,Engineer

Could anyone help me what is the best way to do in the report design?

Re: Grouping multiple rows from result into single cell [message #908054 is a reply to message #907455] Tue, 04 September 2012 15:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Absolutely. Just add a group on Name and in the group footer use an
aggregation element with the concatenate function. Look at the attached
example. Note that I used a visibility expression to hide all rows
except the group footer. I could have just deleted them instead.

Jason

On 9/3/2012 11:25 PM, Simon Yeh wrote:
> Hi,There:
> :roll:
> Here is my result table in the preview of DataSet
> ===================
> | Name | Group |
> ===================
> Clark Admin
> Clark Financial
> Clark Staff
> John Staff
> John Engineer
>
>
> The requirement is to generate report as blow table, combine all group
> into one cell.
> ===================
> | Name | Group |
> ===================
> Clark Admin, Financial, Staff
> John Staff,Engineer
>
> Could anyone help me what is the best way to do in the report design?
>
>
Re: Grouping multiple rows from result into single cell [message #908056 is a reply to message #907455] Tue, 04 September 2012 15:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Absolutely. Just add a group on Name and in the group footer use an
aggregation element with the concatenate function. Look at the attached
example. Note that I used a visibility expression to hide all rows
except the group footer. I could have just deleted them instead.

Jason

On 9/3/2012 11:25 PM, Simon Yeh wrote:
> Hi,There:
> :roll:
> Here is my result table in the preview of DataSet
> ===================
> | Name | Group |
> ===================
> Clark Admin
> Clark Financial
> Clark Staff
> John Staff
> John Engineer
>
>
> The requirement is to generate report as blow table, combine all group
> into one cell.
> ===================
> | Name | Group |
> ===================
> Clark Admin, Financial, Staff
> John Staff,Engineer
>
> Could anyone help me what is the best way to do in the report design?
>
>
Re: Grouping multiple rows from result into single cell [message #908058 is a reply to message #907455] Tue, 04 September 2012 15:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Absolutely. Just add a group on Name and in the group footer use an
aggregation element with the concatenate function. Look at the attached
example. Note that I used a visibility expression to hide all rows
except the group footer. I could have just deleted them instead.

Jason

On 9/3/2012 11:25 PM, Simon Yeh wrote:
> Hi,There:
> :roll:
> Here is my result table in the preview of DataSet
> ===================
> | Name | Group |
> ===================
> Clark Admin
> Clark Financial
> Clark Staff
> John Staff
> John Engineer
>
>
> The requirement is to generate report as blow table, combine all group
> into one cell.
> ===================
> | Name | Group |
> ===================
> Clark Admin, Financial, Staff
> John Staff,Engineer
>
> Could anyone help me what is the best way to do in the report design?
>
>
Re: Grouping multiple rows from result into single cell [message #908259 is a reply to message #908054] Wed, 05 September 2012 03:18 Go to previous messageGo to next message
Simon Yeh is currently offline Simon YehFriend
Messages: 4
Registered: September 2012
Junior Member
To Jason:

WOW, it really works and Thanks !
Actually , I had tried "Aggregation" function in Calculated Column and that didn't work and error !

You really did a great help for me
icon4.gif  Re: Grouping multiple rows from result into single cell [message #916743 is a reply to message #908259] Wed, 19 September 2012 11:59 Go to previous messageGo to next message
Simon Yeh is currently offline Simon YehFriend
Messages: 4
Registered: September 2012
Junior Member
OH,Becuase of limitation of system software, I have to use old BIRT v2.2 only to generation my customized report!

Today, I migrate the report to BIRT v2.2 and found there is NO "concatenate" function in Aggregation

Any one could help ?
Re: Grouping multiple rows from result into single cell [message #916963 is a reply to message #916743] Wed, 19 September 2012 17:31 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

The concatenate function was not available in 2.2. You will need to do some scripting. I am attaching an example that shows how to do it using your data. Look at the group header oncreate, and the detail row's group data item oncreate and the expression for the data item in the group footer. Also note that the detail row is hidden using visibility.

Jason
Re: Grouping multiple rows from result into single cell [message #918942 is a reply to message #916963] Fri, 21 September 2012 14:08 Go to previous messageGo to next message
Simon Yeh is currently offline Simon YehFriend
Messages: 4
Registered: September 2012
Junior Member
Hi Jason

Your sample is work. However, I encounter error at the beginning of deployment of your code. It seems that you edit XML code directly instead of by Layout and Script to define your code.

Finally, I have a table which contains Group by AccountID
For example:
Group Headers | ID | Groups |
Detail | | GrupName |
Group Footers => AccountID | AggredGroupName |

1. Define grpstr=""; in the "on Prepare" of Script of Group Headers
2. Define Groupname fx() as your code
2-1. Define "oncreate" as your code (grpstr += this.getValue() + ",";)
3. Define AggredGroupName fx() as your code (AggredGroupName = grpstr + row["GroupName"])
3. Define grpstr="" in the "on render" of Script of GroupFooters;

The above code is ok and compatible in the v2.2

Otherwise , I will get error result as below:
1. The return value of AggredGroupName is dirty which means contains last one value of grpstr;
2. The end of report will show "grpstr" is not defined when i run the preview.

I don't know the way i do is correct or not ...please advise if any!

Thanks!
Re: Grouping multiple rows from result into single cell [message #919087 is a reply to message #918942] Fri, 21 September 2012 17:12 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

I did not modify the xml directly. I used the 2.2.2 designer. I think your method is ok with the exception of this line:
1. Define grpstr=""; in the "on Prepare" of Script of Group Headers

I would do this in the oncreate of the header row as onPrepare is only called once. The oncreate will be called for each new group header row.

Jason
Previous Topic:Grid in Grid / Grid height to Cell height
Next Topic:Birt designer throws 'Connection is closed' error while previewing the report
Goto Forum:
  


Current Time: Sat Dec 20 06:41:09 GMT 2014

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

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