| Show summary so far on each detail line [message #723228] |
Thu, 08 September 2011 01:53  |
Eclipse User |
|
|
|
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 16:41  |
| Eclipse User |
|
|
|
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?
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04337 seconds
Home