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] Mon, 03 September 2012 23:25 Go to next message
Simon Yeh is currently offline Simon Yeh
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?

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #908054 is a reply to message #907455] Tue, 04 September 2012 11:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
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?
>
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #908056 is a reply to message #907455] Tue, 04 September 2012 11:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
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?
>
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #908058 is a reply to message #907455] Tue, 04 September 2012 11:56 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
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?
>
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #908259 is a reply to message #908054] Tue, 04 September 2012 23:18 Go to previous messageGo to next message
Simon Yeh is currently offline Simon Yeh
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

Report message to a moderator

icon4.gif  Re: Grouping multiple rows from result into single cell [message #916743 is a reply to message #908259] Wed, 19 September 2012 07:59 Go to previous messageGo to next message
Simon Yeh is currently offline Simon Yeh
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 ?

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #916963 is a reply to message #916743] Wed, 19 September 2012 13:31 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
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

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #918942 is a reply to message #916963] Fri, 21 September 2012 10:08 Go to previous messageGo to next message
Simon Yeh is currently offline Simon Yeh
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!

Report message to a moderator

Re: Grouping multiple rows from result into single cell [message #919087 is a reply to message #918942] Fri, 21 September 2012 13:12 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
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

Jason Weathersby

BIRT Exchange

Report message to a moderator

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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 23 23:58:22 EDT 2013

Powered by FUDForum. Page generated in 0.02079 seconds