Eclipse Community Forums: BIRT

Home » Archived » BIRT » Own calculations

Thu, 23 March 2006 09:36

Eclipse User

Originally posted by: r.budner.abg.com.pl

Hi,

I would like to calculate total sum of the whole report and place it in
the outer table's footer row. The data I want to sum are in inner table.
To achieve this, I created global variable and I want to calculate sum
myself in onCreate for each inner table's row:

total_sum = total_sum + ...

I don't know what to add.

total_sum = total_sum + row["myColumn"] - doesn't work

total_sum = total_sum +
this.getRowData().getExpressionValue("row[myColumn]") - doesn't work
neither because getExpressionValue() doesn't return direct dataset's row
value, but it returns expression value instead. In this case this
expression is 'row["myColumn"] + " hrs' so it obviously cannot be used
in addition.

I'm then seeking the method to reference the dataset's pure value for
current row but with no luck so far.

Help

Re: Own calculations [message #147622 is a reply to message #147490]

Thu, 23 March 2006 17:05

Eclipse User

Originally posted by: vladperl.semanticprogrammer.org

> I would like to calculate total sum of the whole report and place it in
> the outer table's footer row. The data I want to sum are in inner table.
> To achieve this, I created global variable and I want to calculate sum
> myself in onCreate for each inner table's row:
>
> total_sum = total_sum + ...
>
> I don't know what to add.
>
> total_sum = total_sum + row["myColumn"] - doesn't work
>
> total_sum = total_sum +
> this.getRowData().getExpressionValue("row[myColumn]") - doesn't work
> neither because getExpressionValue() doesn't return direct dataset's row
> value, but it returns expression value instead. In this case this
> expression is 'row["myColumn"] + " hrs' so it obviously cannot be used in
> addition.
>
> I'm then seeking the method to reference the dataset's pure value for
> current row but with no luck so far.

Re: Own calculations [message #147629 is a reply to message #147490]

Thu, 23 March 2006 17:08

Eclipse User

Re: Own calculations [message #147804 is a reply to message #147629]

Fri, 24 March 2006 06:32

Eclipse User

Originally posted by: r.budner.abg.com.pl

Thanks for your answer.

OK, let's analyse this issue if you don't mind.

Assuming that we have two datasets, inner and outer table I follow with
this steps:

1. Declaration of variable in intialize()
total_sum = 0;

2. There is Data element placed in inner table detail. This element has
expression: fun(row["MyColumn"])
fun is a function that converts number of minutes to number of hours or
days and adds suffix "min", "hours" or "days". So that report displays
for example "5 min", "3.5 hours", "4 days"

3. We need to calculate total sum of MyColumn. Remember that MyColumn is
in inner table. Such total sum has to be placed in outer table footer.
That's why it will appear only once at the end of report.

4. I thought I would use onCreate event of inner table DETAIL ROW. It is
pretty good place, I suppose, because you can call
getRowData().getExpressionValue("row[MyColumn]")

First I placed there
total_sum = total_sum + 1 (for testing purposes and it works - tota_sum
= total number of detail rows)

but placing:
total_sum = total_sum + row["MyColumn"] generates errors evaluating script

placing:
total_sum = total_sum + this.getRowData().getExpressionValue("row.MyColumn")
returns 0

placing:
total_sum = total_sum + this.getRowData().getExpressionValue(3)
(3 - indicates our Data element)
results with displaying long concatenated string like this:
"5 min3.5 hours4 days"

I've run out of ideas :(

Re: Own calculations [message #147890 is a reply to message #147804]

Fri, 24 March 2006 10:56

Eclipse User

Originally posted by: vladperl.semanticprogrammer.org

> OK, let's analyse this issue if you don't mind.
>
> Assuming that we have two datasets, inner and outer table I follow with
> this steps:
>
> 1. Declaration of variable in intialize()
> total_sum = 0;

This is good.

> 2. There is Data element placed in inner table detail. This element has
> expression: fun(row["MyColumn"])
> fun is a function that converts number of minutes to number of hours or
> days and adds suffix "min", "hours" or "days". So that report displays for
> example "5 min", "3.5 hours", "4 days"
I assume that row["MyColumn"] is integer type and return number of minutes.
Is it correct?

> 3. We need to calculate total sum of MyColumn. Remember that MyColumn is
> in inner table. Such total sum has to be placed in outer table footer.
> That's why it will appear only once at the end of report.

I got it.

> 4. I thought I would use onCreate event of inner table DETAIL ROW. It is
> pretty good place, I suppose, because you can call
> getRowData().getExpressionValue("row[MyColumn]")

I agree the place is good.

> First I placed there
> total_sum = total_sum + 1 (for testing purposes and it works - tota_sum =
> total number of detail rows)

This is important to know.

> but placing:
> total_sum = total_sum + row["MyColumn"] generates errors evaluating script

It seems should work. Only reason I can see why it failed that if your
column is not a number :)
Maybe this code will help:
if (Number.NaN(row["MyColumn"] )) {
alert("Not a number!")
}
else {
total_sum += row["MyColumn"]
}
In case if your column is String you can use parseInt(row["MyColumn"] ) to
conver string to integer

> placing:
> total_sum = total_sum +
> this.getRowData().getExpressionValue("row.MyColumn")
> returns 0

try this (I didn't check it):
total_sum = total_sum + this.getRowData().getExpressionValue(row.MyColumn)
or try this(also didn't check it)
total_sum = total_sum +
this.getRowData().getExpressionValue("row['MyColumn']")

Anyway I don't think that you need to use method "getExpressionValue"

> placing:
> total_sum = total_sum + this.getRowData().getExpressionValue(3)
> (3 - indicates our Data element)
> results with displaying long concatenated string like this:
> "5 min3.5 hours4 days"
The JavaScript interpreter tries to cast values between number and string
data types automatically.
When you place operator "+" between a number and a string, the string wins
and the item get concatenated together as string.
That is prove that your column is in reality string not number. Try to use
parserInt() to fix it.

Re: Own calculations [message #147928 is a reply to message #147890]

Fri, 24 March 2006 11:59

Eclipse User

Originally posted by: r.budner.abg.com.pl

I have bad news :(

I get
org.mozilla.javascript.EcmaError: ReferenceError: "row" is not defined
all the time

MyColumn is numeric - I'm sure it is.

I've even tried NamedExpressions but it didn't help much.

I have no idea what to do. There must be any way to reference current
dataset' row but how ??

If there was something like onRow event on dataset I would use it -
maybe it would be better place to increment my variable.

Robert

Re: Own calculations [message #147953 is a reply to message #147928]

Fri, 24 March 2006 12:47

Eclipse User

Robbo wrote:

> I have bad news :(

> I get
> org.mozilla.javascript.EcmaError: ReferenceError: "row" is not defined
> all the time

It's very strange. Try to use "render" method.

> MyColumn is numeric - I'm sure it is.

> I've even tried NamedExpressions but it didn't help much.

> I have no idea what to do. There must be any way to reference current
> dataset' row but how ??

I have found on the forum post regarding access to dataset. Maybe that
will help:
------------------------------------------------------------ ----------
You can use the "rows" property to refer to an outer data set from an inner
table. "rows[0]" refers to the "row" object of the outermost table's data
set, "rows[1]" the 1st level inner table, etc.
It's not possible to refer to an inner table's data set from an outer
table's elements.

--
Gary Xue
Actuate Corporation - Product Development
BIRT Committer
"David" <david.peterson@mtvnmix.com> wrote in message
news:02e8c1a9cabc06985f10aa3d9f3aa951$1@www.eclipse.org...
> How do I specify that I want to use a specific dataSet other than the one
> that I'm in? My outer report uses a different dataSet than my inner
> report.
>
> Something like this, but I can't quite get the right syntax.
> report.dataSet["outer"].row["key"]
>

-----------------------------------------------------------

> If there was something like onRow event on dataset I would use it -
> maybe it would be better place to increment my variable.

Maybe you could try something like this:
total_sum = Total.sum(row["MyColumn"],null,Total.OVERALL)

You need to find appropriate place where to put the expression above.
It should work.

Re: Own calculations [message #148082 is a reply to message #147953]

Mon, 27 March 2006 03:59

Eclipse User

Originally posted by: r.budner.abg.com.pl

>
> It's very strange. Try to use "render" method.
>
> I have found on the forum post regarding access to dataset. Maybe that
> will help:
> ------------------------------------------------------------ ----------
> You can use the "rows" property to refer to an outer data set from an inner
> table. "rows[0]" refers to the "row" object of the outermost table's data
> set, "rows[1]" the 1st level inner table, etc.
> It's not possible to refer to an inner table's data set from an outer
> table's elements.

It's strange indeed, but neither row or rows are recognized inside
onCreate, onPrepare and onRender methods.

Robbo

Re: Own calculations [message #148695 is a reply to message #148082]

Mon, 27 March 2006 16:11

Eclipse User

Can you please send me your design? It seems like you should be able to
gather up this data in the onCreate method fairly easily, I am not sure
why you are having issues. If possible, I would like to take a look at
your design to see what data you have.

If you can send me the rptdesign and then run the report in the web
viewer and send me the rptdocument file that would be helpful.

Scott Rosenbaum
BIRT PMC

Robbo wrote:
>>
>> It's very strange. Try to use "render" method.
>>
>> I have found on the forum post regarding access to dataset. Maybe that
>> will help:
>> ------------------------------------------------------------ ----------
>> You can use the "rows" property to refer to an outer data set from an
>> inner
>> table. "rows[0]" refers to the "row" object of the outermost table's data
>> set, "rows[1]" the 1st level inner table, etc.
>> It's not possible to refer to an inner table's data set from an outer
>> table's elements.
>
> It's strange indeed, but neither row or rows are recognized inside
> onCreate, onPrepare and onRender methods.
>
> Robbo

Re: Own calculations [message #149262 is a reply to message #148695]

Wed, 29 March 2006 09:19

Eclipse User

Originally posted by: r.budner.abg.com.pl

Scott Rosenbaum napisał(a):
> Can you please send me your design? It seems like you should be able to
> gather up this data in the onCreate method fairly easily, I am not sure
> why you are having issues. If possible, I would like to take a look at
> your design to see what data you have.
>
> If you can send me the rptdesign and then run the report in the web
> viewer and send me the rptdocument file that would be helpful.
>
>
> Scott Rosenbaum
> BIRT PMC

I sent you an e-mail. Did you get it?

Robert

Re: Own calculations [message #149341 is a reply to message #149262]

Wed, 29 March 2006 20:46

Eclipse User

Robbo,

I got your code, but I did not see anyplace where you had script. So
what I did is created a simple example running against the classic
models db that is distributed with BIRT. I have attached the report in
clear text at the bottom of this post.

The places you want to look at the code are:
- In the initialize method of the report object
I create a logger and two global functions
one for logging
one for adding up a total

- In the control that contains the inner value, I add each row item
value through the global function

- In the after of the entire report I display the global variable of
all the totals.

I am not quite sure why I had to use the global function, but it would
appear something was happening if I tried to access the global variable
directly (no I am not sure why). You will not want to have the logging
in there, I just put it in to prove to myself that the code was working
the way I expected it to.

Hope this helps,

Scott

Here is the report code:

<?xml version="1.0" encoding="UTF-8"?>

<report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.0.0
Build <20060123-1141></property>
<property name="units">in</property>
<property
name="comments"> ************************************************************ **************
* Copyright (c) 2004, 2005 Innovent Solutions, Inc.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* Scott Rosenbaum
* Innovent Solutions Inc. - initial implementation
*

************************************************************ ************* </property>
<method name="initialize"><![CDATA[/* INTIALIZE THE LOGGER WITH A
FILE BASED LOGGER
*/
importPackage(Packages.java.util.logging);

var fileHandler = new FileHandler("birt_log.log", false);
var rootLogger = Logger.getLogger("");
rootLogger.addHandler(fileHandler);

function log ( str )
{
Logger.getAnonymousLogger().info(str);
}

lineItemTotal = 0;
function addTotal(val)
{
log('B4: lineTotal ' + lineItemTotal + ' input val ' + val);
lineItemTotal += val;
log('After: lineTotal ' + lineItemTotal + ' input val ' + val);
}

reportContext.setPersistentGlobalVariable("addTotal", addTotal);
reportContext.setPersistentGlobalVariable("log", log);
log("Initialize");]]></method>
<method name="afterFactory"><![CDATA[log("after factory");

var hdls = Logger.getLogger("").getHandlers();
for (i = 0; i < hdls.length; i++)
{
Logger.getLogger("").removeHandler(hdls[i]);
hdls[i].close();
}]]></method>
<property name="eventHandlerClass">ReportEH</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="srcClassic"
id="43">
<property
name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="setInside" id="44">
<property name="dataSource">srcClassic</property>
<list-property name="parameters">
<structure>
<property name="name">param1</property>
<property name="dataType">integer</property>
<property name="position">1</property>
<expression name="defaultValue">103</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<property name="queryText">select customernumber, amount
from payments
where customernumber = ?</property>
</oda-data-set>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="setCustomer" id="45">
<property name="dataSource">srcClassic</property>
<list-property name="filter">
<structure>
<property name="operator">between</property>
<expression
name="expr">row["CUSTOMERNAME"].substr(0,1)</expression >
<expression name="value1">'B'</expression>
<expression name="value2">'D'</expression>
</structure>
</list-property>
<property name="queryText">select customerName,
contactFirstName, contactLastName, customerNumber
from customers
order by customerName
</property>
</oda-data-set>
</data-sets>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<property name="rightMargin">1.25in</property>
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property
name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table name="tbl" id="102">
<property name="width">100%</property>
<property name="dataSet">setCustomer</property>
<column id="115"/>
<column id="116"/>
<column id="117"/>
<header>
<row id="103">
<cell id="104">
<label id="120">
<text-property
name="text">CUSTOMERNUMBER</text-property>
</label>
</cell>
<cell id="105">
<label id="118">
<text-property
name="text">CUSTOMERNAME</text-property>
</label>
</cell>
<cell id="106">
<label id="122">
<text-property
name="text">CONTACTLASTNAME</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="107">
<cell id="108">
<data name="col1" id="121">
<expression
name="valueExpr">row["CUSTOMERNUMBER"]</expression>
</data>
</cell>
<cell id="109">
<data name="col" id="119">
<expression
name="valueExpr">row["CUSTOMERNAME"]</expression>
</data>
</cell>
<cell id="110">
<data name="col2" id="123">
<expression
name="valueExpr">row["CONTACTLASTNAME"]</expression>
</data>
</cell>
</row>
<row id="124">
<cell id="125">
<property name="colSpan">3</property>
<property name="rowSpan">1</property>
<table name="tbl1" id="128">
<property
name="backgroundColor">#FFFF80</property>
<property name="width">100%</property>
<property name="dataSet">setInside</property>
<list-property name="paramBindings">
<structure>
<property
name="paramName">param1</property>
<expression
name="expression">row["CUSTOMERNUMBER"]</expression>
</structure>
</list-property>
<column id="141"/>
<column id="142">
<property name="width">1in</property>
</column>
<detail>
<row id="133">
<cell id="134">
<data name="col3" id="145">
<expression
name="valueExpr">row["CUSTOMERNUMBER"]</expression>
</data>
</cell>
<cell id="135">
<data name="col4" id="147">
<structure name="numberFormat">
<property
name="category">Currency</property>
<property
name="pattern">#,##0</property>
</structure>
<property
name="textAlign">right</property>
<method
name="onCreate"><![CDATA[addTotal(row["AMOUNT"]);]]></method >
<expression
name="valueExpr">row["AMOUNT"]</expression>
</data>
</cell>
</row>
</detail>
<footer>
<row id="137">
<cell id="138">
<label id="149">
<property
name="fontWeight">bold</property>
<property
name="textAlign">right</property>
<text-property
name="text">Total </text-property>
</label>
</cell>
<cell id="139">
<data name="col5" id="148">
<property
name="fontWeight">bold</property>
<structure name="numberFormat">
<property
name="category">Currency</property>
<property
name="pattern">$#,##0</property>
</structure>
<property
name="textAlign">right</property>
<expression
name="valueExpr">Total.sum(row["AMOUNT"])</expression>
</data>
</cell>
</row>
</footer>
</table>
</cell>
</row>
</detail>
<footer>
<row id="111">
<cell id="112"/>
<cell id="113">
<text id="150">
<property name="fontWeight">bold</property>
<property name="textAlign">right</property>
<property name="contentType">plain</property>
<text-property name="content"><![CDATA[Line
Item Total
]]></text-property>
</text>
</cell>
<cell id="114">
<data name="col6" id="151">
<property name="fontWeight">bold</property>
<property name="textAlign">right</property>
<expression
name="valueExpr">lineItemTotal</expression>
</data>
</cell>
</row>
</footer>
</table>
</body>
</report>

Re: Own calculations [message #149348 is a reply to message #149341]

Wed, 29 March 2006 21:54

Eclipse User

Robbo,

I think I have an answer why you can't just add values to the global
variable directly from the onCreate method of the text control. I think
what is happening here is that if you have code like:

lineItemTotal += row["AMOUNT"];

Is that the lineItemTotal is getting instantiated on the control and
that you are not referencing the global variable, you are referencing a
variable that is local to the scope of this one control. What ends up
happening is that a new instance of the control is created for each new
sub-report.

So your choice would be to make sure you always get access to the global
variable each round, or the easier way which I used to create a global
method that wraps off the control.

Now you may say, why does the control that show the total only need to
use the global variable? The answer here is that we are only using the
variable through the expression editor and we are not instantiating a
variable.

Hope this helps, it feels like there may be a fix to be made here, but I
want to think about it a bit. If you have any ideas on the preferred
behavior please log a bugzilla enhancement and copy me in.
(scottr@innoventsolutions.com)

Thanks,

Scott

Re: Own calculations [message #149405 is a reply to message #149348]

Thu, 30 March 2006 09:31

Eclipse User

Originally posted by: r.budner.abg.com.pl

Hello Scott,

I owe you a beer for resolving my problem.

I've realized what I had been doing wrong.
The worst mistake I was doing was using wrong method: onCreate for Row
instead of Data item. There is no access to row variable indeed.
onCreate of Data is much better :)

The second mistake was the code like:

lineItemTotal += row["AMOUNT"];

but creating global function for addition prevents getting 0 as total sum.

I'm now more wiser. Thanks a lot.

PS: There are three little things that bother me. I have described them
in my email and some time ago to the newsgroup. If you take a look it
will be great.

Previous Topic:	date parameter
Next Topic:	Embed .NET in SWT container

Goto Forum:

-=] Back to Top [=-

Current Time: Mon May 12 19:09:16 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter