Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Show summary so far on each detail line(Is there any easy way to show the sum of column for the current row and the rows that precede it in the current sort order?)
Show summary so far on each detail line [message #723228] Thu, 08 September 2011 05:53 Go to next message
Richard  is currently offline Richard
Messages: 7
Registered: September 2011
Junior Member
We use dynamic sorting. That is, we build the query string, including the ORDER BY clause, in a Java event handler in the beforeOpen method.

Each detail row has a column that contains a weight value. There is another column (called the "scale weight") that shows that accumulated total so far (the total of weight in the current row and all rows preceding it in the current sort order).

We are able to calculate the scale weight in SQL by building a rather complex subquery that retrieves the appropriate SUM for each row. We build the SQL string of the subquery dynanically to handle an unlimited number of sort fields and to handle ascending and descending sort directions, which can vary from field to field. It works and is fast but the code is complex and difficult to maintain.

It would be more straightforward to do calculate this field in BIRT. If define a report variable ("weightSoFar") ) with an initial value of 0.0., we could add the weight to weighSoFar as each row is calculated . We could then have the report column show this report variable.

The question then is: Where do we add the row weight to weightSoFar? We tried onCreate for the row , but that's too late. We tried onPrepare, but the row data is not available. So where does it go? Or even better, is there another way to get the sum so far that we haven't come across?
Re: Show summary so far on each detail line [message #723578 is a reply to message #723228] Thu, 08 September 2011 20:41 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Can you not use a computed column on the data set and then use a running
sum aggregate?

Jason

On 9/8/2011 1:53 AM, Richard wrote:
> We use dynamic sorting. That is, we build the query string, including
> the ORDER BY clause, in a Java event handler in the beforeOpen method.
> Each detail row has a column that contains a weight value. There is
> another column (called the "scale weight") that shows that accumulated
> total so far (the total of weight in the current row and all rows
> preceding it in the current sort order).
> We are able to calculate the scale weight in SQL by building a rather
> complex subquery that retrieves the appropriate SUM for each row. We
> build the SQL string of the subquery dynanically to handle an unlimited
> number of sort fields and to handle ascending and descending sort
> directions, which can vary from field to field. It works and is fast but
> the code is complex and difficult to maintain.
> It would be more straightforward to do calculate this field in BIRT. If
> define a report variable ("weightSoFar") ) with an initial value of
> 0.0., we could add the weight to weighSoFar as each row is calculated .
> We could then have the report column show this report variable.
> The question then is: Where do we add the row weight to weightSoFar? We
> tried onCreate for the row , but that's too late. We tried onPrepare,
> but the row data is not available. So where does it go? Or even better,
> is there another way to get the sum so far that we haven't come across?
Re: Show summary so far on each detail line [message #723592 is a reply to message #723228] Thu, 08 September 2011 22:21 Go to previous messageGo to next message
Richard  is currently offline Richard
Messages: 7
Registered: September 2011
Junior Member
How do you implement a running sum aggregate?
Re: Show summary so far on each detail line [message #723634 is a reply to message #723592] Fri, 09 September 2011 03:08 Go to previous message
Richard  is currently offline Richard
Messages: 7
Registered: September 2011
Junior Member
Oh, I found it. RUNNING SUM works no matter how we sort the data. Thanks. That makes it simple.
I have more general question, but I'll start a new topic for it.

Previous Topic:Tomcat + BIrt
Next Topic:Help!!! Issue with flat data file
Goto Forum:
  


Current Time: Sun Sep 21 02:16:50 GMT 2014

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

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