Splitting data in 2 columns

I have a report that I want to split the data into 2 columns.

Assuming the data set returns 11 rows, first, I need to determine the median and then split the data in 2 columns.
Instead of showing the report like the following:

1/1/2009 $12.00
1/2/2009 $15.00
1/3/2009 $25.00
....
...
1/11/2009 $30.00

I would like the report to show as follows:
1/1/2009 $12.00 1/7/2009 $23.00
1/2/2009 $15.00 1/8/2009 $33.00
1/3/2009 $25.00 1/9/2009 $44.00
1/4/2009 $33.00 1/10/2009 $33.00
1/5/2009 $34.00 1/11/2009 $56.00
1/6/2009 $22.00

How should I go about in achieving this?
I am new to BIRT and was wondering if I was able to access each record one by one. any help will be appreciated.

Thanks in advance.

Find more posts tagged with

Comments

mwilliams

Hi thebat88,

To do this, you can use a 2 column, 1 row grid and put identical tables in each column. In your dataSet, you can create a computed column to count the rows or something that you can use to separate them with a filter. Then, set up a filter on each table to use the column you created in your dataSet to filter what data goes in each table. Let me know if you need more information.

thebat88

Thanks for you prompt reply.

However, in order to determine to do the filter, i need to know the total number of records and then found out the median to do the filter.
How do you achieve that in the computed column?

I am looking for something like Total Count / 2.
I am thinking the filter will be current row count < (Total Count /2)
and the other filter will be current row count >= (Total count /2)

In your answer above, please see if I understand you correctly.

If there is no filters applied to the Grid , the same record sets are going to show in a row. But with the help of that computed column and when the filter is applied, the unwanted result will he hidden and the data should as expected. Am I correct?

I guess my problem is what should I define in that computed column.

Thanks a lot.

thebat88

<blockquote class='ipsBlockquote' data-author="thebat88">I have a report that I want to split the data into 2 columns. 
 
Assuming the data set returns 11 rows, first, I need to determine the median and then split the data in 2 columns. 
Instead of showing the report like the following: 
 
1/1/2009 $12.00 
1/2/2009 $15.00 
1/3/2009 $25.00 
.... 
... 
1/11/2009 $30.00 
 
I would like the report to show as follows: 
1/1/2009 $12.00 1/7/2009 $23.00 
1/2/2009 $15.00 1/8/2009 $33.00 
1/3/2009 $25.00 1/9/2009 $44.00 
1/4/2009 $33.00 1/10/2009 $33.00 
1/5/2009 $34.00 1/11/2009 $56.00 
1/6/2009 $22.00 
 
How should I go about in achieving this? 
I am new to BIRT and was wondering if I was able to access each record one by one. any help will be appreciated. 
 
Thanks in advance.</blockquote>
 
Forget to add, 
 
The balance records are grouped together by say account number: 
 
Acct ID #1 
2/1/2009 $12.00 2/7/2009 $23.00 
2/2/2009 $15.00 2/8/2009 $33.00 
2/3/2009 $25.00 2/9/2009 $44.00 
2/4/2009 $33.00 
 
Acct ID #2 
2/1/2009 $13.00 2/7/2009 $23.22 
2/2/2009 $25.00 2/8/2009 $33.11 
2/3/2009 $35.00 2/9/2009 $44.11 
2/4/2009 $43.00 
 
and so on.... 
 
Thank you in advance.

mwilliams

thebat88,

Filters are applied to the tables in each grid cell, but yeah, you have the main concept down. Creating 2 computed columns that you won't display in your report could get you to be able to do what you said. One that shows the total rows, and one that shows each individual row number.....base the filters on those.

mwilliams

thebat88,

The grouping will cause it to be a little different. You'll have to end up using aggregations in the group. I'll make a sample report from the sample database and post it in here. I'll make it in 2.2.2, so if you have that or newer, you can open it with no version issues.

thebat88

<blockquote class='ipsBlockquote' data-author="mwilliams">thebat88, 
 
The grouping will cause it to be a little different. You'll have to end up using aggregations in the group. I'll make a sample report from the sample database and post it in here. I'll make it in 2.2.2, so if you have that or newer, you can open it with no version issues.</blockquote>
 
Great. Thanks a lot.

mwilliams

thebat88,

Does your detail data under the group value come from a second dataSet? Or is it all in one?

thebat88

It's all in one data set. 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">thebat88, 
 
Does your detail data under the group value come from a second dataSet? Or is it all in one?</blockquote>

mwilliams

thebat88,

I actually ended up making this in 2.3.0. Let me know if you have any questions.

thebat88

i ran the report, but somehow, there's nothing showing on the left side.
see attached image...

mwilliams

thebat88,

What version of BIRT are you using? This is what mine looks like in 2.3.0:

thebat88

i am using 3.2.15...

I manually change the xml version from 16 to 15 to make eclipse not complaining. not sure if there is a difference in doing that.

ok thanks, i will explore and see why...

But I get your idea in how you implement it.... thx for your help.

mwilliams

thebat88,

There must've been a bug on this in 2.2.2. I have not found a way to make it work correctly with this method in that version as of yet. I'll let you know if I do.

thebat88

I was trying to create the GroupTotal in the table footer, however, in my report the value shows as 1 on every data row rather than a total sum at the end of the group. I wonder if that is a bug in my version.

Thanks

thebat88

<blockquote class='ipsBlockquote' data-author="thebat88">I was trying to create the GroupTotal in the table footer, however, in my report the value shows as 1 on every data row rather than a total sum at the end of the group. I wonder if that is a bug in my version. 
 
Thanks</blockquote>
 
I figured it out... I have to put my table in the Group Header row in order to get the correct total count for each group. 
 
Also, instead of using the table filters, I defined the filter condition in the Table Detail 's Visibility and everything works fine. 
 
Once again thanks for your help.

mwilliams

thebat88,

No problem! Glad it worked out. Let us know if you have any other questions.

thebat88

I am working with the same report again.

I need to rearrange the report a bit so that I can show alternate color on each row for the 2 set of tables.

Here is the structure:

MAIN TABLE
** ROW 1 Column 1
NESTED TABLE
Data field 1, Data field 2, GroupRowCount
(Aggregated data field defined in Cell - AggCount)

** ROW 1, Column 2
NESTED TABLE
Data field 1, Data field 2, GroupRowCount
(Aggregated data field defined in Cell - AggCount)

When i want to define the visibility rule, all rows are hidden somehow.
GroupRowCount >= Math.ceil(AggCount /2)

It looks like the inside table cannot access the aggregated data and so all rows are hidden. Is there a workaround for this?

Thanks.

thebat88

Attached is the striped down version of the report.

Basically i want to define a Table-detail level visibility condition where
GroupRowNumber >= Math.ceil(row["groupCount] /2) for table 1

and define the following condition
GroupRowNumber < Math.ceil(row["groupCount] /2) for table 2

I tried to move the aggregate GroupCount to the same cell in GroupRowNumber but it gives me Invalid data bound.

Any help is appreciated. Thanks in advance.