Eclipse Community Forums: BIRT » Running Count Distinct

Help

Home

Home » Eclipse Projects » BIRT » Running Count Distinct

Show: Today's Messages :: Show Polls :: Message Navigator

Running Count Distinct [message #557563]

Wed, 08 September 2010 02:20

Tobias
Messages: 9
Registered: November 2009

Junior Member

Hi,

is there any possibility to do something like a running count but
distinct for a certain dataset column as computed column in the dataset?

Any hint would be appreciated.

Report message to a moderator

Re: Running Count Distinct [message #557622 is a reply to message #557563]

Wed, 08 September 2010 07:10

Richard A. Polunsky is currently offline

Richard A. Polunsky
Messages: 197
Registered: July 2009
Location: Houston TX

Senior Member

I don't see any straightforward way to do it unless you guarantee that your dataset is ordered on the field that you want the running count of. Otherwise on each row you have to run through the preceding rows to see whether the current value has occurred before.

If you stipulate order on the field of interest, you should be able to check the value in the row event and increment a counter when the value changes.

Report message to a moderator

Re: Running Count Distinct [message #557634 is a reply to message #557622]

Wed, 08 September 2010 07:52

Tobias
Messages: 9
Registered: November 2009

Junior Member

On 08.09.2010 13:10, Richard A. Polunsky wrote:
> If you stipulate order on the field of interest, you should be able to
> check the value in the row event and increment a counter when the value
> changes.
Actually my DataSet is ordered. So how would I perform that check? Would
I have to write an onFetch-Script in the DataSet or where do I have to
put it?

Report message to a moderator

Re: Running Count Distinct [message #557663 is a reply to message #557563]

Wed, 08 September 2010 09:08

Richard A. Polunsky
Messages: 197
Registered: July 2009
Location: Houston TX

Senior Member

Yes, the onFetch event would be the place. You need two tracking variables: varValue and runCount. I suspect you have to define and initialize those in the beforeOpen event, but someone more familiar with dataset event scripting would have better information. It's not something I've had to do for my purposes.

Report message to a moderator

Re: Running Count Distinct [message #557883 is a reply to message #557663]

Thu, 09 September 2010 06:25

Tobias
Messages: 9
Registered: November 2009

Junior Member

On 08.09.2010 15:08, Richard A. Polunsky wrote:
> Yes, the onFetch event would be the place. You need two tracking
> variables: varValue and runCount. I suspect you have to define and
> initialize those in the beforeOpen event, but someone more familiar with
> dataset event scripting would have better information. It's not
> something I've had to do for my purposes.
I got the script working on a new DataSet... The same Script won't work
on my old one... I don't understand why that happend but now I have my
running count distict.
Thanks a lot.

Report message to a moderator

Re: Running Count Distinct [message #648483 is a reply to message #557563]

Thu, 13 January 2011 02:25

Karthik
Messages: 1
Registered: January 2011
Location: Pune

Junior Member

Hi Tobias,

My name is Karthik. Working as Software Engineer in Pune.
I am new to BIRT Reporting Tool. Me too looking for same problem. If u got any idea regarding ur problem pls inform me.
My email id is Karthik.Sathiyaseelan@cognizant.com.

Thanks in Advance

Regards,
Karthik

Report message to a moderator

Re: Running Count Distinct [message #824063 is a reply to message #648483]

Mon, 19 March 2012 04:00

Tobias Feldker
Messages: 1
Registered: March 2012

Junior Member

Great I just found my own post again Very Happy

I will post my solution since Karthik asked for it.

A running count distinct can be acquired using the script methods on the DataSet.

For my solution, it is necessary that the DataSet is sorted by the column that should be counted distinct running. The sort order would determine whether the smallest or the biggest entry gets the count 1.

First create a computed column that gets the default 1. In my expample the column is called "computedCoulmun".

Second you have to initialize the variables you want to use in the onFetch()-script. I use java.lang.Integer just for initialization issues, I didn't find another way to securely initialize my variables.
befroreOpen() would look like this:

importClass( Packages.java.lang.Integer );
oldCol = new Integer(0);
oldCount = new Integer(0);
count = new Integer(0);

Third step would be that little onFetch()-Script. You would have to replace "columnToBeCountedDistinct" with the name of the column that should get a running count distinct. onFetch()-Script:

col = row.columnToBeCountedDistinct;
count = row.computedCoulmun;
if (oldCol != col) {
	oldCol = col;
	oldCount = BirtMath.add(oldCount, 1);
}
row.columnToBeCountedDistinct = oldCount;

I hope this post helps all people that want to create a manual count distinct.

I would appreciate a BIRT aggregation that would do the count for me and that works without sorting the DataSet first.

Report message to a moderator

Re: Running Count Distinct [message #824414 is a reply to message #824063]

Mon, 19 March 2012 12:33

Jason Weathersby
Messages: 9167
Registered: July 2009

Senior Member

Thanks for posting. Just an FYI, BIRT does have an aggregation
extension point. This sounds like an excellent option for this.
Take a look at this post:
http://birtworld.blogspot.com/2010/10/birt-duplicate-rows.html

Jason

On 3/19/2012 4:00 AM, Tobias Feldker wrote:
> Great I just found my own post again :d I will post my solution since
> Karthik asked for it.
>
> A running count distinct can be acquired using the script methods on the
> DataSet.
>
> For my solution, it is necessary that the DataSet is sorted by the
> column that should be counted distinct running. The sort order would
> determine whether the smallest or the biggest entry gets the count 1.
>
> First create a computed column that gets the default 1. In my expample
> the column is called "computedCoulmun".
>
> Second you have to initialize the variables you want to use in the
> onFetch()-script. I use java.lang.Integer just for initialization
> issues, I didn't find another way to securely initialize my variables.
> befroreOpen() would look like this:
>
> importClass( Packages.java.lang.Integer );
> oldCol = new Integer(0);
> oldCount = new Integer(0);
> count = new Integer(0);
>
> Third step would be that little onFetch()-Script. You would have to
> replace "columnToBeCountedDistinct" with the name of the column that
> should get a running count distinct. onFetch()-Script:
>
> col = row.columnToBeCountedDistinct;
> count = row.computedCoulmun;
> if (oldCol != col) {
> oldCol = col;
> oldCount = BirtMath.add(oldCount, 1);
> }
> row.columnToBeCountedDistinct = oldCount;
>
>
> I hope this post helps all people that want to create a manual count
> distinct.
>
> I would appreciate a BIRT aggregation that would do the count for me and
> that works without sorting the DataSet first.

Jason Weathersby

BIRT Exchange

Report message to a moderator

Previous Topic:	Nested Table Name field value from Parent
Next Topic:	Migration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 10:39:35 EDT 2013