Formula in calc

Formula in calc

Postby bb39700 » Mon Dec 19, 2011 9:22 pm

Use ods to make calc report
No problem with the creation of the report, but the sum in the result is static.
Now i try to use the sum() calc fonction to make the sum of lines created with python loop, and so to have dynamic sum.

1) i try to put the sum() in a cell, but the loop insert line without changing the range in the fonction.
2) don't find the syntax in python code to put fonction in cell and not text.

Is someone know the syntax to use in python code to put the sum function in the report, or the way in ods file to change the range of sum function when inseerting line ?
bb39700
 
Posts: 20
Joined: Sun Mar 14, 2010 6:04 pm

Re: Formula in calc

Postby emearg » Tue Dec 20, 2011 11:18 pm

Hi, You need to use python code, not calc formulas. In documentation sum function is described, or for more complex totals, generally I create a custom parser which holds the totals and then refer to them. Usually a dictionary or a list and have a helper function which returns a string for example

total(0) or total('assets')

def total(self, index)
return '%.2f' % self.totals[index]
emearg
 
Posts: 39
Joined: Mon Dec 20, 2010 8:03 am

Re: Formula in calc

Postby bb39700 » Wed Dec 21, 2011 12:14 pm

I already do it. But the sheet is static.

The goal is to make sheet with value extract from openerp, and when i modify values in the sheet, i want that all sum and calculated field are recalculate (the normal use of sheet).
bb39700
 
Posts: 20
Joined: Sun Mar 14, 2010 6:04 pm

Re: Formula in calc

Postby sraps » Thu Dec 22, 2011 11:21 am

Hi!
bb39700 wrote:I already do it. But the sheet is static.

The goal is to make sheet with value extract from openerp, and when i modify values in the sheet, i want that all sum and calculated field are recalculate (the normal use of sheet).


OOo has some differences from MS Excel, some of them are great, some... I would not say so. One of, no so great differences, is that OOo not always update it's cell references in itself (the same with OOo Writer, yes Writer can do some calculations and charts as well, in opposite to Word). As Aeroo is manipulating the document directly, and it is not purposely developed spreadsheet processor, it does not know of the cell references.

Yes someday it would probably be possible to implement this feature, still we have to be aware that all of these tiny tricks consume 90% of the performance. Aeroo in itself is as fast as ReportLab RML reports are, in creating ODF template -> ODF document. The drawback in performance is ODF -> PDF conversion. So it wouldn't be wise to sacrifice this reasonably good performance to some features.

One tip, if you really desire, some spreadsheet features, like state of the art formulas or charts, you have to reserve the number of rows and columns to fit all your records and keep inside the reference boundaries.

P.S. At the moment there is no feature to write cell functions from python.

BR
Kaspar
sraps
 
Posts: 403
Joined: Mon May 04, 2009 10:04 pm

Re: Formula in calc

Postby jluitz » Wed Jul 01, 2020 6:02 pm

I know this is a very old thread, but I found a "tricky solution" to this problem that I want to share:

Instead of referencing the cell to be used for a formula directly, you can construct the relative cell address as follows:

=CONCAT(CHAR(COLUMN()+64-1);ROW())

this gives the address left-neighbor cell,

to get the value of that cell use:

=INDIRECT(CONCAT(CHAR(COLUMN()+64-1);ROW())))

Indeed for bigger formulas this is not easy to read, but at least it works.

All the best
Joachim
jluitz
 
Posts: 2
Joined: Sun Feb 10, 2013 11:10 pm


Return to Aeroo Reports & OpenOffice.org Reporting Engine

Copyright © 2001 Alistek Ltd., ALL RIGHTS RESERVED