Damn...I wrote almost that identical function..but forget and used
commas....
Aging is a terrible thing...LOL
Ocke Janssen wrote:
Hi Paolo,
Paolo wrote:
After my last post I've also realized that values from the first row
of every group won't be added to the total.
So the results are wrong anyway.
I had not realizaed it before as many of my groupings have a 0 value
as first row.
Am I doing something wrong? or is the Report Builder that needs some
tuning?
May be you have to set the initial value to the value of the field
like [field].
As formula for the 2nd problem you may use following formula
[SumCost] + IF(ISBLANK([field]);0;[field])
This sum all fields which are not NULL. Now, may be you ask yourself
where did he find all the functions. :-)
Well in the folder uno_packages where the Sun report builder is
installed you find a jar called libformula.jar. Unzip it. These
functions are currently supported but not documented so far. We should
do this on the wiki site. Volunteers? :-)
Best regards,
Ocke
Thank
Paolo
On Monday 08 October 2007 23:23:59 Paolo wrote:
Ok, I've made a few more tests with my report and here what I've
found. I'm
not sure wether it is a "bug" or a "feature", so please don't flame
me if
the message is definitely OT.
My columns are made of "numbers", either BIGINT or FLOAT. Values can be
zero, non-zero or empty cells.
I'm adding values from different groupings using a function like:
function "SumCost" defined like [SumCost]+[Cost]
as a data source for a text field.
The report will sum correctly only the last few rows starting from
the last
empty cell. It will return an error if all of the values are empty.
Probably this explaination is not really clear, so let's try a few
examples:
First case:
Row Value (i.e. "Cost")
1 2
2 0
3 7
4 1
SumCost returns 10
So everything's OK
Second case:
Row Value (i.e. "Cost")
1 2
2 (empty cell)
3 7
4 1
SumCost returns 8
Only the cells after an empty one are calculated
Third case:
Row Value (i.e. "Cost")
1 (empty cell)
2 (empty cell)
3 (empty cell)
4 (empty cell)
SumCost returns "LibFormulaErrorValue{errorCode=522}"
At least, these are my findings so far. Sorry, my knowledge of
databases
and Of OpenOffice Base is really poor so maybe this is correct and it's
just what you deserve for leaving empty cells in a database :-)
Otherwise, I hope this is helpful for someone else.
Regards
Paolo
On Monday 08 October 2007 16:14:10 Paolo wrote:
Wooo hooo!
it worked!
At least at 66,667%... so far I've tried and create three different
calculated report fields, and only two of them work... They appear
exactly identical to me, but one of them only outputs
"LibFormulaErrorValue{errorCode=522}" Now I'm trying to guess why the
third one doesn't cooperate.
Anyway: really thanks a lot for your precious help! It saved me
lots of
(useless) hours and sleepless nights! Above all, now I can finally
charge
my clients what they owe to me! ;-)
Paolo
On Monday 08 October 2007 07:47:16 Ocke Janssen wrote:
Hi Paolo,
Paolo wrote:
Hello everybody,
I've been folowing the list for a few weeks and I must admit most of
the discussion is above my level of understanding. I have little or
no programming background and a rather basic understanding of
databases.
I'd like to ask a question about OO 2.3 and Report Builder: is it
possible to get calculated fields in a report?
Yes. :-)
Basic example: I create a report to print data from my job's
activities; all of the activities are nicely grouped by client's
name, and I can also print activities fulfilled for different
clients
on different pages. Every activity has a fee ("cost") field related
to it. The report gets the data from a query, collecting different
tables.
The report looks nice but doesn't provide the main information, that
is I cannot get it to sum the "cost" column for each client.
Can I calculate how much every single client owes to me and append
the information at the end of that client's list of activities?
I've been able to get calculated fields in queries, but that
doesn'help, as it only provides the ability to sum different values
from the same record ("line") of the query itself, while I need the
ability to sum values across a range of records.
Hopefully I've posted this to the correct list; I'll provide any
further detail if needed.
Open the report navigator. Open the Groups entry and the group where
you want to calculate the cost.
The group has an sub entry called functions. Open the context menu
(right mouse) on that entry and choose to create a new function and
select it. In the property browser on the right side you see the
function. Change the name to e.g CostCalc and the formula to
[CostCalc]
+ [Enter your cost column name]
In the initial value enter 0
Now you can insert a text field and bind it to your [CostCalc]
(appears
in the data field list box)
That should be all.
Best regards,
Ocke
http://wiki.services.openoffice.org/wiki/SUN_Report_Builder
Thanks a lot.
Paolo
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
