Formatting for the data import file

This section describes the import formatting for a Microsoft^® Excel spreadsheet, Open Document spreadsheet, or XML file.

The following formatting guidelines are necessary for importing a scorecard, objective, or initiative. If the following guidelines are not followed, the import might be unsuccessful. Actual values can only be imported from a file if the objective or initiative data source is set to log values manually or import data from a file.

Microsoft Excel and Open Document spreadsheet format

The spreadsheet formatting consists of six sections. The import file must contain a title and execution name, but the following sections are optional:

Targets (required if Zones specified)
Zones (required if Targets specified)
Values
Associations

Note: To only import values for an objective or initiative, specify the Execution Name and Values sections.

Title

The title is required and must:

Reside on the second row
Span cells A2 to I2
Occur once per file.

Execution Name

The Execution Name is required. The following table describes the Execution Name properties:

Table 1. Execution Name
Field	Required	Description
Execution Name	Yes	Specifies the name of the objective or initiative. Can appear more than once if importing a scorecard. The length of the objective or initiative name cannot exceed 256 characters.
Perspective Name	No	Specifies the perspective for the objective or initiative. If not specified, the default value is the first perspective listed in the system. The length of the perspective name cannot exceed 256 characters.
Value Type	No	Specifies the value type for the objective or initiative. 1 = Number, 2 = Currency, 3 = Percentage, 4 = Boolean, 5 = Status. If not specified, the default values is currency. If the objective or initiative already exists, the value type cannot be changed and will be ignored.
Rollup Type	No	Specifies the aggregate from alignment type for the objective or initiative. 1 = Additive, 2 = Average, 3 = Minimum, 4 = Maximum, 5 = Weighted, 6 = Manual, 7 = External. If not specified, the default value is manual. If the objective or initiative already exists, the rollup type cannot be changed and will be ignored.
Summary	No	Specifies the description for the objective or initiative. If not specified, the default value is blank. The summary cannot exceed 1024 characters.
Milestone	No	Specifies the milestone frequency for the objective or initiative. 1 = Hourly, 2 = Daily, 3 = Weekly, 4 = Bi-Weekly, 5 = Monthly, 6 = Bi-Monthly, 7 = Quarterly, 8 = Semi-Yearly, 9 = Yearly, 10 = End of Objective. If not specified, the default value is monthly.
Start Time	No	Specifies the start time for the objective or initiative. (Format: mm/dd/yyyy hh:mm:ss AM or PM) If not specified, the default value is the current system time during import. If adding a new objective or initiative and the start time is not specified, the first target's time is used as the start time.
Completion Time	No	Used for export informational purposes only.
Type	No	Specifies objective or initiative. 1 = Objective, 2 = Initiative. If not specified, the default value is objective. If the objective or initiative already exists, the type cannot be changed and will be ignored.

Targets

The Targets section is optional. If importing targets, Time and Value x are required. If not importing targets, remove the entire section from the file. The following table describes the properties for Targets:

Table 2. Targets
Field	Description
Targets	This section specifies targets for the objective or initiative by specifying a time and value.
Time	Specifies the time for a target value. (Format: mm/dd/yyyy hh:mm:ss AM or PM)
Value 0	Specifies the value for a target value as an ordinal. 0 = specifies the top zone separator line, 1 = specifies the next zone separator line below the top line. Increment the Value `x` by 1 to specify additional values for zone separator lines below the top zone separator line.

Zones

The Zones section is optional. If importing zones, Zone and Color are required. If not importing zones, remove the entire section from the file. The following table describes the properties for Zones:

Table 3. Zones
Field	Description
Zones	This section specifies status zones for the objective or initiative by specifying a status zone and status.
Zone	Specifies the number of zones. 0 = Top zone, 1 = Second from top zone, and so on.
Color	Specifies the status (color) for a zone. 2 = Positive (Green), 1 = Moderate (Yellow), 0 = Negative (Red)

Values

The Values section is optional. If importing values, Time and Value are required. If not importing values, remove the entire section from the file. Create a row for each actual value to import. The following table describes the properties for Values:

Table 4. Values
Field	Description
Values	This section specifies actual values for the objective or initiative by specifying a time and value.
Time	Specifies the time for an actual value. (Format: mm/dd/yyyy hh:mm:ss AM or PM)
Value	Specifies the value for an actual value.

Associations

The Associations section is optional and applies to initiatives only when doing an import of a scorecard. If importing associations, Type and To Objective are required. If not importing initiatives, remove the entire section from the file. Create a row for each association to import. The following table describes the properties for Associations:

Table 5. Associations
Field	Description
Associations	This section specifies the associations an initiative has with other objectives or the scorecard.
Type	Specifies the type of association for the initiative. 1 = Objective, 2 = Scorecard
To Objective	Specifies the objective name to associate with the initiative. The objective must already exist, or be specified in the import file. To associate the initiative with the scorecard, do not type an objective name in the To Objective field.

The following table describes the formatting for creating a spreadsheet file for importing an objective.

Table 6. Objective Formatting
	A	B	C	D	E	F	G	H	I
1
2					Title
3
4	Execution Name	Perspective Name	Value Type	Rollup Type	Summary	Milestone	Start Time	Completion Time	Type
5	(Required) In this cell, type the objective name.	In this cell, type the perspective name for the objective this objective is associated with.	In this cell, type the objective value type.	In this cell, type the objective roll-up type.	In this cell, type the objective description.	In this cell, type the objective milestone.	In this cell, type the objective start time.		In this cell, type the objective type.
6		Targets	Time	Value 0	Value 1	Continue adding columns to specify additional values for zone separator lines below the top zone separator line. Increment the value number by 1.
7			In this cell, type the first target time.	In this cell, type the first target value for the top zone separator line.	In this cell, type the first target value for the next zone separator line below the top line.
8			If necessary, type the second target time in this cell.	If necessary, type the second target value for the top zone separator line in this cell.	If necessary, type the second target value for the next zone separator line below the top line in this cell.
9			Continue adding rows to specify additional targets.
10		Zones	Zone	Color
11			0	In this cell, type the status for the first zone from the top.
12			1	In this cell, type the status for the second zone from the top.
13			Continue adding rows to specify additional zones. Increment the zone number by 1.
14		Values	Time	Value
15			In this cell, type the time for the first value.	In this cell, type the value for the first value.
16			In this cell, type the time for the second value.	In this cell, type the value for the second value.
17			Continue adding rows to specify additional values.

The following table describes the formatting to use when creating a spreadsheet file for importing an initiative.

Table 7. Initiative Formatting
	A	B	C	D	E	F	G	H	I
1
2					Title
3
4	Execution Name	Perspective Name	Value Type	Rollup Type	Summary	Milestone	Start Time	Completion Time	Type
5	(Required) In this cell, type the initiative name.	In this cell, type the perspective name for the objective this initiative is associated with.	In this cell, type the initiative value type.	In this cell, type the initiative roll-up type.	In this cell, type the initiative description.	In this cell, type the initiative milestone.	In this cell, type the initiative start time.		In this cell, type the initiative type.
6		Targets	Time	Value 0	Value 1	Continue adding columns to specify additional values for zone separator lines below the top zone separator line. Increment the value number by 1.
7			In this cell, type the first target time.	In this cell, type the first target value for the top zone separator line.	In this cell, type the first target value for the next zone separator line below the top line.
8			If necessary, type the second target time in this cell.	If necessary, type the second target value for the top zone separator line in this cell.	If necessary, type the second target value for the next zone separator line below the top line in this cell.
9			Continue adding rows to specify additional targets.
10		Zones	Zone	Color
11			0	In this cell, type the status for the first zone from the top.
12			1	In this cell, type the status for the second zone from the top.
13			Continue adding rows to specify additional zones. Increment the zone number by 1.
14		Values	Time	Value
15			In this cell, type the time for the first value.	In this cell, type the value for the first value.
16			In this cell, type the time for the second value.	In this cell, type the value for the second value.
17			Continue adding rows to specify additional values.
18		Associations	Type	To Objective
19			1	In this cell, type the objective name that this initiative is associated with.
20			Continue adding rows to specify additional associations if necessary. If Type is 2, leave To Objective cell empty.

To import a scorecard, start with the objective template, and then add additional Execution Name sections followed by Targets, Zones, Values, and Associations. Repeat as necessary.

XML file format

The XML formatting consists of six sections. The import file must contain a title and execution name, but the following sections are optional:

targetSet
associations

Note: To only import values for an objective or initiative, specify the Execution name and targetSet sections.

Execution name

The Execution name is required. The following table describes the Execution name properties:

Table 8. Execution Name
Field	Required	Description
Execution name	Yes	Specifies the name of the objective or initiative. Can appear more than once if importing a scorecard. The length of the objective or initiative name cannot exceed 256 characters.
perspective	No	Specifies the perspective for the objective or initiative. If not specified, the default value is the first perspective listed in the system. The length of the perspective name cannot exceed 256 characters.
valueType	No	Specifies the value type for the objective or initiative. 1 = Number, 2 = Currency, 3 = Percentage, 4 = Boolean, 5 = Status. If not specified, the default values is currency. If the objective or initiative already exists, the value type cannot be changed and will be ignored.
rollupType	No	Specifies the aggregate from alignment type for the objective or initiative. 1 = Additive, 2 = Average, 3 = Minimum, 4 = Maximum, 5 = Weighted, 6 = Manual, 7 = External. If not specified, the default value is manual. If the objective or initiative already exists, the rollup type cannot be changed and will be ignored.
milestone	No	Specifies the milestone frequency for the objective or initiative. 1 = Hourly, 2 = Daily, 3 = Weekly, 4 = Bi-Weekly, 5 = Monthly, 6 = Bi-Monthly, 7 = Quarterly, 8 = Semi-Yearly, 9 = Yearly, 10 = End of Objective. If not specified, the default value is monthly.
startTime	No	Specifies the start time for the objective or initiative. (Format: mm/dd/yyyy hh:mm:ss AM or PM) If not specified, the default value is the current system time during import. If adding a new objective or initiative and the start time is not specified, the first target's time is used as the start time.
completionTime	No	Used for export informational purposes only.
type	No	Specifies objective or initiative. 1 = Objective, 2 = Initiative. If not specified, the default value is objective. If the objective or initiative already exists, the type cannot be changed and will be ignored.

The following code shows a sample execution name entry for importing an objective or initiative:

<com.ibm.lwp.bpm.emf.ei:Execution name="Multimedia Collaboration Initiative Plan (Establish the multimedia group in company)" perspective="Internal Business Process" valueType="3" rollupType="6" milestone="5" startTime="2/1/2006 8:59:59 AM" completionTime="01/01/9999 12:00:00 AM" type="1">

The following code shows a sample execution name entry for importing a scorecard:

<execution name="Attend Conference of Media Content" perspective="Learning and Growth" valueType="4" rollupType="6" milestone="5" startTime="2/1/2006 8:59:59 AM" completionTime="01/01/9999 12:00:00 AM" type="1">

targetSet

The targetSet section is optional. If importing targets, time, ordinal, and value are required. If not importing targets, remove the entire section from the file. If importing zones, zone and color are required. If not importing zones, remove the entire section from the file. The following table describes the properties for targetSet:

Table 9. Targets and Zones
Field	Description
targetSet	This section specifies targets and zones for the objective or initiative.
time	Specifies the time for a target value. (Format: mm/dd/yyyy hh:mm:ss AM or PM)
ordinal	Specifies the value for a target value as an ordinal. 0 = specifies the top zone separator line, 1 = specifies the next zone separator line below the top line. Increment the ordinal by 1 to specify additional values for zone separator lines below the top zone separator line.
value	Specifies the value for a target value. 0 = specifies the top zone separator line
zone	Specifies the number of zones. 0 = Top zone, 1 = Second from top zone, and so on.
color	Specifies the status (color) for a zone. 2 = Positive (Green), 1 = Moderate (Yellow), 0 = Negative (Red)

The following code shows sample entries for targets and zones:

<targetSet>
	<target time="2/1/2006 8:59:59 AM">
		<value ordinal="0" value="0.0"/>
		<value ordinal="1" value="0.0"/>
	</target>
	<zones>
		<zone zone="0" color="2"/>
		<zone zone="1" color="1"/>
		<zone zone="2" color="0"/>
	</zones>
</targetSet>

values

The values section is optional. If importing values, time and actual are required. If not importing values, remove the entire section from the file. Create a row for each actual value to import. The following table describes the properties for values:

Table 10. Values
Field	Description
values	This section specifies actual values for the objective or initiative by specifying a time and value.
time	Specifies the time for an actual value. (Format: mm/dd/yyyy hh:mm:ss AM or PM)
actual	Specifies the value for an actual value.

The following code shows sample actual value entries:

<values>
	<value time="2/1/2006 8:59:59 AM" actual="0.0"/>
	<value time="3/4/2006 8:59:59 AM" actual="10.0"/>
	<value time="4/4/2006 8:59:59 AM" actual="17.5"/>
	<value time="5/4/2006 8:59:59 AM" actual="25.0"/>
	<value time="6/4/2006 8:59:59 AM" actual="30.0"/>
	<value time="7/4/2006 8:59:59 AM" actual="34.0"/>
	<value time="8/4/2006 8:59:59 AM" actual="38.0"/>
</values>

associations

The associations section is optional and applies to initiatives only. If importing associations, type and toLink name are required. If not importing initiatives, remove the entire section from the file. Create a row for each association to import. The following table describes the properties for associations:

Table 11. Associations
Field	Description
associations	This section specifies the associations an initiative has with other objectives or the scorecard.
association type	Specifies the type of association for the initiative. 1 = Objective, 2 = Scorecard
toLink name	Specifies the objective name to associate with the initiative. To associate the initiative with the scorecard only, remove the associations section.

<associations>
	<association type="1">
		<toLink name="Multimedia Collaboration Initiative Plan (Establish the multimedia group in company)" type="12" owner="uid=benjamin_tanner,o=default organization"/>
	</association>
</associations>

Sample import files

The following table contains sample files for importing a scorecard, objective, or initiative:

Table 12. Sample Import Files
Import	File Type
Scorecard	XLS	ODS	XML
Objective	XLS	ODS	XML
Initiative	XLS	ODS	XML

Note: These sample import files were exported from the sample data user, Benjamin Tanner. To assist in the creation of an import file, create an objective in the user interface, and then export the objective as a file. Modify the exported file as necessary, and then import the same file.

Import scenarios

The following describes some common import scenarios:

If targets are not tracked for an imported objective or initiative, the targets and zones sections are ignored.
If targets or values exist for an imported objective or initiative, the existing targets and values are replaced with the imported targets and values. Objectives or initiatives that are completed or removed will not be affected by imported targets and values.
For boolean value type, only one targets value column is allowed.
For targets, at least one set of values (Value 0 column) are required. If a Value 1 column is present, the values must be less than or equal to the Value 0 column values for the same time period.
Any actual value in the future will not be imported.
Actual values must be of the same type of the existing objective or initiative to be updated.