Ok I gave it a try and it seems I'm still stuck with my problem (well not
really at the starting point, say somewhere in the middle).
Changing the starting value from "0" to [field] did the trick, and now all of
the rows are calculated! Good!
On the other hand I need to pack all of the empty rows with zeroes, or I get a
wrong calculation:
Example 1
Row Value (i.e. "Cost")
1 2
2 0
3 7
4 1
SumCost returns 10
So everything's OK
But: example 2
Row Value (i.e. "Cost")
1 2
2 (empty)
3 7
4 1
SumCost returns 8
So it starts to sum from the last empty cell.
Furthermore, if the row containing the total falls in a new page, and only
empty cells are contained above it in the same page, it will return the usual
nasty error: "LibFormulaErrorValue{errorCode=522}"
So: example 3
Row Value (i.e. "Cost")
1 2
2 5
3 (empty)
---------page break---------
4 (empty)
SumCost returns Error
Now I promise to be good and study my functions in the .jar archive before
bedtime. I don't know if it will help, as my programming skills are...
well... zero? ;-)
Just one more question: is this mailing list the right place for this kind of
discussion? I don't want to be considered an "intruder"
Thanks to all the nice persons around for your help so far.
Regards
Paolo
On Tuesday 09 October 2007 08:01:08 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]
--
Paolo
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
