Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Running Count Distinct
Running Count Distinct [message #557563] Wed, 08 September 2010 06:20 Go to next message
Tobias is currently offline TobiasFriend
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.
Re: Running Count Distinct [message #557622 is a reply to message #557563] Wed, 08 September 2010 11:10 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
Messages: 199
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.
Re: Running Count Distinct [message #557634 is a reply to message #557622] Wed, 08 September 2010 11:52 Go to previous messageGo to next message
Tobias is currently offline TobiasFriend
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?
Re: Running Count Distinct [message #557663 is a reply to message #557563] Wed, 08 September 2010 13:08 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
Messages: 199
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.
Re: Running Count Distinct [message #557883 is a reply to message #557663] Thu, 09 September 2010 10:25 Go to previous messageGo to next message
Tobias is currently offline TobiasFriend
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.
Re: Running Count Distinct [message #648483 is a reply to message #557563] Thu, 13 January 2011 07:25 Go to previous messageGo to next message
KarthikFriend
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
Re: Running Count Distinct [message #824063 is a reply to message #648483] Mon, 19 March 2012 08:00 Go to previous messageGo to next message
Tobias Feldker is currently offline Tobias FeldkerFriend
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.
Re: Running Count Distinct [message #824414 is a reply to message #824063] Mon, 19 March 2012 16:33 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
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.
Previous Topic:Nested Table Name field value from Parent
Next Topic:Migration
Goto Forum:
  


Current Time: Sun Nov 23 07:47:57 GMT 2014

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

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