RE: [office-formula] Calculation Settings

[Eric Patterson <ericpa@exchange.microsoft.com>]

I have a proposal for how to clarify the ambiguity related to Calculation settings.  I propose that we amend the spec to indicate which calculation settings produce Portable Documents.  Adding the following text to sectk 2.1.1 would resolve the ambiguity and also help interoperability by defining what is portable in this area:


Applications creating portable documents shall support the following calculation settings.  Applications may support other values for these settings.  Each setting shall have the effect as defined in this specification.
        table:case-sensitive = FALSE
        table:precision-as-shown = FALSE
        table:search-criteria-must-apply-to-whole-cell = FALSE
        table:automatic-find-labels = FALSE
        table:use-regular-expressions = FALSE
        table:use-wildcards = FALSE
        table:null-year = 1930
        table:null-date ="1899-12-30"



Further, I think it would be helpful to add a bit more clarity around Portable Documents.


The current draft (in Section 2 - Conformance) says:

...this specification discusses what is required for a document to assert that it is a portable document. A portable document shall only depend on the capabilities defined in this specification, and shall not depend on undefined or implementation-defined behavior.  A portable document shall only claim to conform to a given group if the document only depends on the capabilities of the given group.


I proposed that we update it as follows:

...this specification discusses what is required for a document to assert that it is a portable document.
"A spreadsheet document (as opposed to a spreadsheet application) is defined as "portable" when it only depends on the capabilities defined in this specification, and does not depend on undefined or implementation-defined behavior.   A portable document shall adhere to all constraints specifically recommended for portable documents elsewhere in this specification.   A portable document shall only claim to conform to a given group if the document only depends on the capabilities of the given group.   Applications may provide users with assistance (in the form of warning messages or other features) for the creation and editing of portable documents.


-Eric

-----Original Message-----
From: Eric Patterson [mailto:ericpa@exchange.microsoft.com]
Sent: Tuesday, May 12, 2009 2:05 PM
To: office-formula@lists.oasis-open.org
Subject: [office-formula] Calculation Settings

I've been reviewing Calculation Settings and finding that conformance is a bit ambiguous.

Looking at sections 2.1.1 and 3.3, they indicate that conforming applications shall implement the calculation settings, and that applications may use non-default values for new documents.

What is unclear is whether support of specific values are required for conformance.

For example, table:null-date lists some commonly used values in a note, but list no values as required for conformance.  Are conforming applications any that read and write a single value that conforms to the definition of table:null-date?  Is application conformance reading all of the settings and only loading files with the combinations of settings that are supported by the application?

-Eric



From 2.1.1:
-----------------------
If the formula is embedded in an OpenDocument document, applications conforming to the small group shall implement the following calculation settings (that is, each setting shall have the effect as defined in this specification):
1.      table:case-sensitive
2.      table:precision-as-shown
3.      table:search-criteria-must-apply-to-whole-cell
4.      table:automatic-find-labels
5.      table:use-regular-expressions
6.      table:use-wildcards
7.      table:null-year
8.      table:null-date


3.3     OpenDocument Calculation Settings
-----------------------------------------
If it is in an OpenDocument file, formula recalculation shall be influenced by “calculation settings”, and where they apply they are part of the recalculation context. Several are defined in the OpenDocument specification. Below are relevant calculation settings, their default value (the value if there is no relevant setting at all), and a description of their meaning:
1.      table:case-sensitive (true): if true, text comparisons are case-sensitive. This influences the operators =, <>, <, <=, >, and >=, as well as database query functions that use them. Note that the EXACT function is always case-sensitive, regardless of this calculation setting.
2.      table:precision-as-shown (false): If true, calculations are performed using rounded values of those displayed; otherwise, calculations are performed using the precision of the underlying numeric representation.  Note: This does not impose a particular numeric model.  Since implementations may use binary representations, this rounding may be inexact for decimal value.
3.      table:search-criteria-must-apply-to-whole-cell (true): If true, the specified search criteria shall apply to the entire cell contents if it is a text match using = or <>; if not, only the initial text needs to match.
4.      table:automatic-find-labels (true): if true, row and column labels are automatically found.
5.      table:use-regular-expressions (true): If true, regular expressions are used for character string comparisons and when searching
6.      table:use-wildcards (false): If true, wildcards question mark '?' and asterisk '*' are used for character string comparisons and when searching. Wildcards may be escaped with a tilde '~' character.
7.      table:null-year (1930): This defines how to convert a two-digit year into a four-digit year. All two-digit year values are interpreted as a year that equals or follows this year.
8.      table:null-date (1899-12-30): Defines the beginning of the epoch; a numeric date of 0 equals this date.

TODO: Check that the use-regular-expressions is sufficiently defined in Volume 1.

Applications may choose to set a calculation setting to a non-default value when creating a new document (giving the user the effect of different defaults), but when writing to the OpenDocument format they shall include any overriding value.

Note: Excel 2003 semantics are case-sensitive=false and search-criteria-must-apply-to-whole-cell=false.