Calc Scenario Protect

OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	John Marmion	20 November 2003	Final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	spreadsheet
Task ID	i20889; i22473
Category	Feature

Abstract

Scenarios allow users to create a set of grouped data which may change depending on choosing a different item or scenario from a drop-down list. To increase compatibility with Microsoft Excel, Calc needs the ability to protect scenarios. This feature is similar to the protection of cells or drawing objects. The protected scenario properties or values cannot be edited or deleted.

i-Team Members

	Name	E-mail Address
Specification Owner	John Marmion (jm)	jmarmion@openoofice.org
User Experience	Falko Tesch (ft)	ft@openoffice.org
Development	John Marmion (jm)	jmarmion@openoffice.org
Quality Assurance	Oliver Cramer (oc)	oc@openoffice.org
Documentation	Frank Peters	fpe@openoffice.org

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	2003-11-06
Development	John Marmion	2003-11-11
Quality Assurance	Oliver Cramer	2003-11-06
Documentation	Uwe Fischer	2003-11-11
String Review	Elizabeth Matthis	2003-11-18

Document Change History

Rev. Level	Change	Initials	Date
1.0	initial revision	jm	2003-10-8
1.1	String review changes	jm	2003-11-18

Contents

Glossary	1

1	Motivation	1

2	Goals	1

3	Requirements and Dependencies	1

3.1	Requirements	1

3.2	Technical Dependencies	1

4	User Scenarios	1

5	Competitive Analyses	1

6	Detailed Specification	1

6.1	Error Conditions	2

7	Future Tasks	2

8	Legal Issues	2

9	Notes	2

10	References	2

Glossary

Term	Description
Scenario	Set group of cells whose content may change on selection of a different item from a drop-down list.

Motivation

To increase interoperability with the MS Excel file format.

Goals

To improve interoperability with the MS Excel file format.

Ease-of-Use with competitive products. It is mandatory that the users finds a comparable interface within the same interface structure as in competitive products.

This protection enhancement should be consistent with how Calc handles similar objects.

Requirements and Dependencies

Requirements

This enhancement will allow users to migrate Excel file format documents employing protected scenarios to Calc.

Technical Dependencies

None.

User Scenarios

An imported Excel file format document may contain protected scenarios. This protection is currently not acted upon and is not exported back to MS Excel format.

Once implemented, scenario protection will ensure that existing scenarios cannot be modified or deleted.

Competitive Analyses

This exists in competitive products. Users have the ability to protect modification and deletion of existing scenarios and offer only the ability to add new scenarios.

Detailed Specification

This feature needs to be implemented in the Calc core. Once it is available, then Excel import filter will update this core functionality. On export, the appropriate scenario protection will be maintained.

The issue of scenario protection cannot be divorced from Sheet protection. Scenario protection is only acted upon if Sheet protection is on. If Sheet protection is off then any scenario protection is ignored. This follows the behaviour of cell protection in a protected sheet.

In all possible cases, we will examine the effect of Sheet protection in conjunction with scenario protection on and off and Sheet protection off. This gives us three possibilities for each situation examined.

We need to consider two situations.

Creating Scenarios and

Modifying Scenarios which includes both Edit and Delete modes.

Creating Scenarios

The current scenario dialogue looks like Figure 1:

Figure 1

We will add a new protected checkbox to the Settings. The default will be TRUE on creation.

Figure 2

The protected scenario is only acted on if the Sheet itself is protected. Once the sheet is protected and the scenario has been protected, then users can only create new scenarios.

Modifying Scenarios

Modifying Scenarios can take two forms. First, we can modify the cell values that make up the scenario and secondly we can modify the properties of the scenario e.g. Name, comments or border colour. Modifying Scenario properties uses the 'Edit Scenario Dialogue'. This is activated by using the Display Navigator [see Figure 3]. The resulting Edit Dialog is activated from the Properties menu task and is identical to the 'Create Scenario' dialogue.

Figure 3

Changing Scenario Cell Values

Changing Scenario cell values can be achieved by editing the cell values directly. If the 'copy back' is set then the scenario cell value is updated. Alternatively, the values in the scenario sheet itself can be edited.

Changing Scenario Cell Values from the Sheet

If the Sheet is protected and cell values are protected, then regardless of the scenario protection value, attempts to change the cell values directly will not be allowed. This is the current behaviour.

If the Sheet is protected and cell values are unprotected and the scenario is protected and the value of the 'copy back' is true, then direct input to the cell will be disabled, as if the cells were protected.

If the Sheet is protected and cell values are unprotected and the scenario is protected and the value of the 'copy back' is false, then direct input to the cell will be successful. This is the current behaviour.

If the Sheet is protected and cell values are unprotected and the scenario is not protected, then direct input to the cell will update scenario cell values according to the value of the setting 'copy back' . This is the current behaviour.

If the Sheet is not protected, then modifying cell values will update scenario cell values according to the value of the flag 'copy back'.

Changing Scenario Cell Values from the Scenario Sheet

In the case of the Scenario Sheet, we will check if the preceding non-scenario sheet is protected and the scenario itself is protected before allowing the values to be changed.

Changing Scenario Properties

If the Sheet is protected and the scenario is protected, then the Properties menu item in Figure 3 will be inactive.

If the Sheet is protected and the scenario is not protected, then the protected checkbox is set to FALSE and inactive. The remaining existing property values of the scenario will follow their current behaviour. In order to change the value of the protected checkbox, the user will first need to remove the Sheet protection. This is similar to the current behaviour of Excel.

If the Sheet is not protected, then the protected checkbox will display its current value and will be active for input. The remaining existing property values of the scenario will follow their current behaviour.

Deleting Scenarios

If the Sheet is protected and the scenario is protected, then the Delete menu item in Figure 3 will be inactive.

If the Sheet is protected and the scenario is not protected, then the Delete menu item is active.

If the Sheet is not protected, then the Delete menu item is activated regardless of the protected state of the scenario.

String list

Item	English	German	Comments
Checkbox	~Prevent changes	~Änderungen verhindern	

Tab Order

New field added to the end of current dialogue so tab order will continue to new field.

Key Board Shortcuts

None.

Error Conditions

None.

Future Tasks

Competitive products offers the possibility of additional protection by hiding the scenarios. This is a future task to consider.

Legal Issues

None.

Notes

None.

References

http://www.openoffice.org/project/www/issues/show_bug.cgi?id=20889

http://www.openoffice.org/project/www/issues/show_bug.cgi?id=22473

<Required – This paragraphs gives the audience idea of what to aspect from this document. It should be described shortly what problem will be solved.>

The source of software product implementation and change is the I-Team (Implementation Team). I-Teams are typically comprised of people who have skills in the areas of Product Marketing, Development Engineering, Technical Publications, and Test Engineering. I-Teams define the features or set of changes (based on input from SCs, customers, Engineering, or Marketing), plan the implementation project, perform the implementation and testing...

<Required. It makes sense to describe or visualize the tabbing order. The description of the tab order has to contain which control has the initial focus.>

<Required - Key Board shortcuts used in dialogs have to be listed in a table with a small description.>

<Required - List all error conditions, and the action the system takes about them. If an alert box comes up, which kind is it? Include exact text of messages, or say what points need to be covered. Error Messages need a string list>

<Recommended - If there are features which couldn't be implemented but have to be implemented in the future. State these in here with a description why the feature couldn't be implemented right now.>

<Recommended - If there are any legal issues to deal with the should be described here. Legal issues could be for example we have to redistribute third party technology. This section can also contain links the Legal Review Tool.If the Specification will be published on OpenOffice.org this section has to be removed.>

<Recommended - Questions will arise during the writing of the specification. State these in here.>

Required - Give references to related documentation, such as engineering designs, technical specs, and other sources for related units. Also include links to documents that other sections in this spec need to refer to, such as user opinions usability test reports.

Calc Scenario Protect

Page 5

http://specs.openoffice.org/calc/compatibility/ScenarioProtection.sxw

Matthias Müller-Prove

2003-10-08T10:36:52