Using the Data Editor

This chapter covers the following topics:

• Data Editor Overview
• Configuring Data Editor Views
• Saving, Loading and Deleting Views in the Data Editor
• Changing All Values in Rows, Columns or Views
• Exporting Data from the Data Editor
• Performing Calculations on Data
• Highlighting Data
• Arithmetic Symbols and Functions

Data Editor Overview

Viewing and Exporting Data

The Data Editor displays data extracted from models by report queries and is useful for reporting Strategic Network Optimization information. As shown in the example below, the data appears in a spreadsheet format. You can view, edit, and customize the display of the data. The headings in the Data Editor are obtained from Label entries in the Report Properties windows. An example of the Data Editor can be seen here below:

The TMPDIR Environment Variable

When you apply a report query, the system creates a temporary file that links Strategic Network Optimization and the Data Editor that contains the smart information. This temporary file is not linx.smart, and is only created when the Data Editor is launched. The default location for this temporary file is as follows:

• on UNIX: /var/tmp

• on Windows: C:\

You can change the location of this temporary file by setting the TMPDIR environment variable. The TMPDIR environment variable can also be added to a batch script.

Note: The system automatically deletes the file after the Data Editor opens.

Configuring Data Editor Views

Configuring the Data Editor Layout

To open the Configure Layout window, from the Configure menu, select Layout.

By configuring views, you can change the appearance of the Data Editor in several ways to display your data in the most meaningful way. After you change the Data Editor's appearance, you can save your changes as a view.

To reorder columns in the Data Editor, in the Reordering columns area of the Configure Layout window, drag and drop the column buttons into the desired order. The columns in the main Data Editor window are reordered.

To spread a column horizontally in the Data Editor, drag a column button from the Reordering columns area, and drop it into the Spreading data horizontally area.

The column that was previously in the left side of the Data Editor is spread horizontally across the Data Editor.

The DC column, which consists of the Cleveland, Denver, and Minneapolis distribution centers, is spread horizontally across the Data Editor in each period.

To reorder data horizontally in the Data Editor, in the Spreading data horizontally area of the Configure Layout window, drag and drop the buttons into the desired order. The data in the main Data Editor window is reordered.

To make a column out of horizontal data in the Data Editor:

1. Drag a column button from the Spreading data horizontally area, and drop it into the Reordering columns area.

2. The column that was previously spread across the Data Editor horizontally is now a column.

Displaying Data Using Tabs

You can use tabs to display data in the Data Editor, where each tab shows information that shares a particular characteristic or field value. You can then toggle through the tabs, which are displayed along the bottom of the Data Editor as shown in the following example:

Instead of scrolling through many of the values of a field to display the data that you want, you can click the tab and instantly display the data.

You can specify the data that appears on each tab. For example, you could specify that each tab shows data for a different time period, product, or location. You can even specify more than one characteristic for each data tab. For example, you could specify that each tab shows data for one type of facility for a single time period.

To specify the data that appears on each tab, you must drag and drop a field name into the Creating tabs area of the Configure Layout windows.

To display data in the Data Editor using tabs, drag buttons from the Reordering columns area or the Spreading data horizontally and drop them into the Creating tabs area of the Configure Layout window.

The data in the main Data Editor window is reconfigured with tabs which are displayed along the bottom of the Data Editor.

Data Classifications and Types

To remove tabs from the Data Editor. drag the button out from the Creating tabs area of the Configure Layout window, and drop it into either the Reordering columns area or the Spreading data horizontally area.

The tabs along the bottom of the Data Editor are removed and the data which was organized in the tabs is either returned to columns or it is spread across the Data Editor horizontally, depending on your actions in the Configure Layout window.

To set data classifications and types:

1. Select one of the following data classifications:

   • Key. The field is part of the primary key. You cannot edit the data in a key field.

   • DataReadOnly. You can view data in this field, but you cannot edit it.

   • DataReadWrite. You can view and edit data in this field. Edited data does not update the model.

2. Select one of the following data types:

   • String. The field contains a string of text.

   • Number. The field contains numbers.

   • Yesno. The field contains either yes or no.

   • Date. The field contains a date.

Hiding or Displaying Column Headings

To hide a column heading in the Data Editor:

1. In the Data Editor, right-click the column heading.

2. Select Hide.

To display a hidden column heading in the Data Editor:

1. In the Data Editor, right-click the column heading.

2. Show.

Hiding or Displaying Data Fields

To hide a data field:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, select a data field in the Data Fields list.

   The field name is no longer highlighted in this list and no longer appears in the Show (Ordered) list.

4. Click OK.

To display a hidden data field:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, select a data field in the Data Fields list.

   The field name reappears in the Show (Ordered) list.

4. Click OK.

Changing the Order of Fields

To change the order of fields:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, select a data field in the Data Fields list.

   The field name is no longer highlighted in the list and no longer appears in the Show (Ordered) list.

4. In the Show (Ordered) list, select the name of the data field that you want to appear below the data field that you are moving.

5. In the Data Fields list, select the data field that you unselected in step 3.

   The field appears in the Show (Ordered) list above the data field that you selected in step 4. If, in step 4, you select the field at the top of the Show (Ordered) list, the field that you select in the Data Fields list appears at the top of the Show (Ordered) list.

6. Click OK.

Showing Complete Field Headings

Each field in the Data Editor has a complete heading that consists of all the row labels. You can specify whether you want to display the complete heading for each field or to display the heading only when it changes.

To show complete headings for each field:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, select Show Duplicate Labels.

To show complete headings only once, select Show Duplicate Labels.

Displaying Titles

You can display two titles in views: system titles and user titles. System titles contain the names of each field in a view. The user creates user titles. To display the system title: 1. From the Configure menu, select Display. 2. In the Configure Data window, click Show System Title. 1. From the Configure menu, select Display. 2. In the Configure Data window, enter your title in the User Title field.

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, click Show System Title.

To create a user title:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, enter your title in the User Title field.

Changing the Date Format in the Data Editor

You can change the date format in the Data Editor. The table below lists available date formats. You can combine the formats in any order and include characters such as dashes, spaces, or slashes for clarity. Null dates are displayed as blanks.

Date Format	Meaning
%d	Day as a number-for example, 19
%b	Month as a three-letter abbreviation-for example, Nov
%m	Month as a number-for example, 11 for November
%y	Year as a two-number abbreviation-for example, 05
%B	Month by its full name, for example-November
%D	Entire date in the mm/dd/yy format-for example, 11/19/05
%Y	Year in full-for example, 2005
%d-%b-%y	19-Nov-05 (default date format)
%Y-&m-%d	2005-11-19
%B%d, %Y	November 19, 2005

To change the date format:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, enter the date format in the Date Display Format field.

   Be sure to enter the date formats correctly. If the Data Editor does not understand the format that you enter, it displays the current date.

Specifying the Number of Decimal Places

To specify the number of decimal places:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, enter the number of decimal places you want to view in the Precision field.

The default is 2.

Specifying the Period Durations Reference File

You can specify the name of the file used for the predefined Depletion function. To specify the name of this file:

1. From the Configure menu, select Layout.

2. Select Display from the Configure menu in the Data Editor.

3. In the Configure Data window, enter the file name in the Period Durations File field.

Saving, Loading and Deleting Views in the Data Editor

After you change the appearance of the Data Editor, you can save the layout or view, and use the same view later with the same report or with different reports. If you add, delete, or rename the labels in a report query, the view is no longer valid. When you save a view, you save information about:

• How the data appears, including row and column layout, precision, and any changes that you make to the appearance of the Data Editor.

• Printing specifications

• Expressions for calculating data, including highlighting

You can also delete views when they are no longer needed.

To save a view:

1. From the File menu in the Data Editor, select Save View or Save View As

2. In the Save window, do one of the following:

   • Click the Only Show Views That Apply option.

   • From the Files field, select the view that you want to see.

3. In the Selection field, enter the file name.

4. Click OK.

To load a view:

1. From the File menu in the Data Editor, select Open View.

2. In the Open window, do one of the following:

   • Click the Only Show Views That Apply option.

   • From the Files field, select the view that you want to see.

3. In the Selection field, enter the file name.

4. Click OK.

To delete a view:

1. From the File menu in the Data Editor, select Delete.

2. In the Delete window, do one of the following:

   • From the Files field, select the view that you want to see.

     The file name appears in the Selection field.

   • In the Selection field, enter the view file name.

3. Click OK.

4. In the confirmation window, click OK to delete the selected view name.

Changing All Values in Rows, Columns or Views

You can change all of the values in a specific row or column or throughout the entire Data Editor. For example, if you want to look at the effects of increased demand on your inventory levels, you can uniformly increase the demand. You then can apply an expression to recalculate the inventory required to meet the increased demand.

To change all of the values in a rows, columns, or views:

1. Ensure that the data you want to change is classified as dataReadWrite.

2. From the Configure menu in the Data Editor, select Fields.

3. In the Configure Fields window, select a cell in the row or column with data that you want to change.

4. From the Edit menu, select Fill Cells.

   In the Fill Cells window, the row and column numbers of the cell you selected in appear in the Row and Column fields. If you did not select a specific cell in, the default row and column number is 1.

5. In the Value to Fill Cells field, enter the value that you want in the selected row or column. 4.

6. Click one of the following options:

   • Row. Applies the change to every cell in the indicated row. You can edit this number.

   • Column. Applies the changes to every cell in the indicated column. You can edit this number. This option is available only when your Data Editor displays one data value. For example, if your Data Editor displays only the global demand data, you can use this option. However, if it displays both global demand and safety stock, you cannot use this option.

   • Entire Table. Applies the change to every cell in the Data Editor. This option is available only when your Data Editor displays one data value. For example, if your Data Editor displays only the global demand data, you can use this option. However, if it displays both global demand and safety stock, you cannot use this option.

7. Click OK.

Exporting Data from the Data Editor

You can export data from the Data Editor to a file. The file can be in basic text format, Lotus import format (each data element in quotation marks), or comma-separated variable-length format (comma-separated data), XML, or HTML.

CSV Format

A comma-separated variable-length text file is the most useful format for importing the data into Windows applications, which use this text-based representation for database tables.

The first row of the exported file has the field names. The rows that follow contain the data. Every data element is enclosed by double quotation marks, and fields are separated by commas. Internally, each row terminates with a Windows linefeed character. The following example is of an exported comma-separated variable-length text file:

Location Name,Storage Level,Safety Level Toronto,
34.4,25 Chicago,25.5,20 Boston,30,20

XML Format

Data can be exported from the Data Editor in XML format, which can be particularly useful when integrating with other applications.

Exporting Data to Files

To export data to files:

1. In the Save in: field, browse to the location where you want to export the data.

2. In the File name: field, enter the file name.

3. In the Save as type: drop-down list, select one of the following:

   • XML

   • HTML

   • CSV

4. Click Save.

Performing Calculations on Data

Performing Calculations with Breaks

A break is a new row that is automatically added to your view. You can leave this row blank or automatically calculate one of the following values:

• Sum

• Minimum

• Maximum

• Average

All configured break information appears in blue.

To perform calculations with breaks:

1. From the Configure menu in the Data Editor, select Breaks.

2. In the Configure Breaks window, select the field for which you want to perform the calculation.

3. Enter the name for the break in the Name field.

4. Click the Break Frequency button and select the break frequency from the menu.

   For example, select Every Change to create a break every time that the plant changes.

5. Click the First Break field and select the position for the first break.

6. To create a visual break following your calculation, click the Put Blank After Break option.

7. In the At Break, compute: area, select any calculations that you want to perform for fields that are listed. You have the following options:

   • Nothing. Nothing is calculated. Useful when you want to display a blank row.

   • Sum. Adds the values of this data field.

   • Minimum. Displays the field's minimum value.

   • Maximum. Displays the field's maximum value.

   • Average. Calculates the average of the displayed values.

   • First. Displays the first value displayed after the most recent break.

   • Last. Displays the last value displayed before the most recent break.

   • Nothing. Nothing is calculated. Useful when you want to display a blank row.

8. Click OK.

To hide a break:

1. In the Configure Breaks window, select Actions.

2. Select Suspend All Breaks.

To show a break

1. In the Configure Breaks window, select Actions.

2. Select Activate All Breaks.

Creating a Field and Expression

To create a field and expression:

1. From the Configure menu in the Data Editor, select the expression that you want to edit.

   If the Expression list is empty, select New from the Actions menu. A field called _undefined_ appears in the Field Name and Formula lists.

2. In the Expressions window, select Edit from the Actions menu.

3. In the Expression Editor window, enter the name of the data field you want to create in the Field Name field.

4. In the Formula field, enter the expression that you want to use.

   To display functions, you can drag and drop using the left mouse button, select List Functions from the Configure menu.

5. In the Inverse Formula field, enter an inverse formula, if necessary.

6. Click OK.

Editing and Deleting Expressions

To edit an expression:

1. From the Configure menu in the Data Editor, select Expressions.

2. In the Configure Expressions window, do one of the following:

   • Select the expression that you want to edit and select Edit from the Actions menu.

   • Double-click the expression that you want to edit.

3. In the Expression Editor window, make the changes that you want in the Field Name, Formula, or Inverse Formula fields.

4. Click OK.

To delete an expression:

1. From the Configure menu in the Data Editor, select Expressions.

2. In the Expression List window, select the expression that you want to delete.

3. From the Actions menu, select Delete.

Recalculating Expressions

To recalculate all formulas except inverse formulas:

1. From the Edit menu in the Data Editor, select Recalculate.

2. Select All Formulas.

To recalculate only inverse formulas:

1. From the Edit menu in the Data Editor, select Recalculate.

2. Select All Inverse Formulas.

Use this option only with inverse formulas that assign a value to another field.

Inverse Formulas

Inverse formulas are used to assign a value to another field and automatically update the data in another field. Inverse formulas use the Assign function to assign a value to a field. For example, suppose you have a field called BlueInfo.

The data in this field is the maximum value of the fields BlueInv and BlueSafety. This information is specified in the Formula field of the Expression Editor as follows:

Field Name: BlueInfo Formula:MAX(BlueInv, BlueSafety)

You can also use an inverse formula to assign the value from the Forecast field multiplied by two to the field named BlueDemand. Following is the inverse formula you enter in the Expression Editor for the field BlueInfo:

Assign(#BlueDemand,Forecast*2)

Notice that the expressions in the Formula and Inverse Formula fields do not seem to be related to each other. When you use the Inverse Formula to assign a value to a field, the formula does not have to correspond with the Formula field. To update values in an assigned field, you must select the menu option. These inverse formulas are not updated automatically.

Updating Values in an Assigned Field

To update values in an assigned field:

1. From the Edit menu in the Data Editor, select Recalculate.

2. Select Inverse Formulas.

Updating Data in Another Field Automatically

An inverse formula automatically updates the values in fields that are based on data in another location, such as another field or an expression. If you create a field that is based on the data from another location and change the value of the field, the inverse formula automatically updates the values in the other location to correspond with the initial change.

For example, suppose you create a field called RedPercent. This field displays the percentage of the maximum of the product Red that was produced. In the Formula field of the Expression Editor, you enter:

Field Name: RedPercent Formula:PERCENT(Used,Max)

Suppose the Used field displays a value of 70 and the Max field displays a value of 140. In this case, the RedPercent field displays a value of 50 percent. If you want the percentage to be 80 percent instead of 50 percent, you need to use an inverse formula to change the values in the Used and Max fields. The inverse formula that you enter is:

Assign(#Used,RedPercent*Max/100)

This expression tells the Data Editor to multiply the values in the RedPercent field and the Max field, divide this number by 100, and assign it to the Used field.

If this expression is entered in the Inverse Formula field for the RedPercent field, and you change the value in that field to 80 percent, the Used field is updated automatically to a value of 112, which is 80 percent of the Max field value of 140.

To load expressions from different views:

1. From the File menu i n the Data Editor, select Import Expression.

2. To see a list of views that apply only to the currently displayed Data Editor, select Only Show Views That Apply.

   By default, only those views with whose expressions that are applicable to the current data are available.

3. Do one of the following:

   • From the Files field, select the view that you want to see.

   • In the Selection field, enter the file name.

4. Click OK.

The view is loaded from the file specified in the Selection field, and expressions are copied into the current view.

Highlighting Data

You might want the Data Editor to display different colors for any value that violates a boundary that is important to you. For example, to display a field's value in red each time that it goes over 25 and in black if it is less than or equal to 25, you enter:

IF(Maximum>25,RED(),BLACK()) 

You can also embed highlighting expressions within each other, just as you can with regular expressions. For example, to use red highlighting if the maximum value is greater than 25 or when the minimum value is less than 10 and to use blue highlighting if the minimum value is greater than 10, you enter:

IF(Maximum>25,RED() , IF(Minimum<10,RED(),BLUE())) 

This table shows the results, which depend on the values in the minimum and maximum fields:

Max	Min	Highlighting	Meaning
30	10	Red	The maximum is greater than 25. This result matches the condition of the IF statement, so the highlighting is red. The minimum is not considered because the first condition of the IF statement was met.
9	5	Red	The maximum is less than 25. This result does not match the first condition of the IF statement, so the second condition is considered. The second condition is matched, so the highlighting is red.
12	14	Blue	Neither the first nor the second conditions is met, so the third condition is considered. It states that when the minimum is not less than 10, blue highlighting is used.

You can highlight the results of calculations or to specific fields. The following colors can be used for highlighting:

• Black

• Blue

• Green

• Orange

• Pink

• Red

• Violet

• Yellow

Highlighting the Results of Calculations

To highlight the results of calculations:

1. From the Configure menu, select Expressions.

2. From the Configure menu, select Highlighting.

3. In the Highlighting list, select the expression that you want to highlight.

4. From the Actions menu, select Edit.

5. Double-click the expression that you want to edit.

6. In the Highlight Editor, do one of the following in the Highlight Formula field:

7. Enter the color expression for the circumstances under which you want the field to be highlighted.

8. From the Configure menu, select List Functions. From the Functions window, drag and drop the expression that states the circumstances under which you want the field to be highlighted.

9. In the Highlight Editor, click OK.

10. In the Highlight Expression List window, click OK.

Editing or Deleting Highlighting Expressions

You might need to edit or delete a highlighting expression. Although no delete function exists, you can remove the highlighting information for an expression by deleting it.

To edit or delete highlighting expressions:

1. From the Configure menu, select Highlighting.

2. In the Highlighting list window, select the expression that you want to edit.

3. In the HighlightExpression List window, select Edit from the Actions menu.

4. Double-click the field that you want to color.

5. In the Highlight Editor, do one of the following in the Highlight Formula field:

6. Enter the expression that states the circumstances under which you want the field to be highlighted.

7. From the Configure menu, select List Functions.

8. From the List Functions window, copy the expression that states the circumstances under which you want the field to be highlighted.

9. To delete the highlighting and return the field to black, replace the expression with the default BLACK().

10. Click OK.

11. In the HighlightExpression List window, click OK.

Highlighting Individual Fields

You can use the Expression Editor to highlight your regular data, even if it is not part of an expression or the result of a calculation. For example, you could display all of the data in violet.

To highlight individual fields:

1. From the Configure menu, select Highlighting.

   The Highlighting list displays your fields and expressions. The Formula field displays the color for each field. The default color is BLACK().

2. Select a row from the Actions menu and select Edit.

3. In the Highlight Editor window, enter the color name in the Highlight Formula field.

4. Click OK.

5. In the Highlighting list, click OK.

Highlighting from Other Views

Just as you can use expressions from another view, you can use the highlighting that you defined in another view.

To use highlighting from other views:

1. From the Configure menu, select Highlighting.

2. In the Configure Highlighting window, select Only Show Views That Apply.

   A list of views that apply to the currently displayed Data Editor appears. By default, only those views that are applicable to the current data are available.

3. Do one of the following:

   • From the Files field, select the view that you want to use.

   • In the Selection field, enter the view file name.

4. Click OK.

Arithmetic Symbols and Functions

Expressions and Functions Syntax

Each function has its own syntax, but the following general rules apply to all functions and expressions:

• When you enter a data field name in an expression, the value from that field is used.

• Use single quotation marks around data field names that contain spaces. For example, enter:

  'Storage Min'

• Each function must be followed by a left and right parenthesis. For some functions, the parenthesis remain empty. For example:

  RED()

• For other functions, the parenthesis must contain arguments. For example:

  SUM( a , b , c )

• You can use multiple functions and nested functions in an expression.

• Variables in functions are indicated by x, y, or some other variable name. In place of the variable name, you can enter:

  • A number

  • A field name if the field contains a number

  • An expression that results in a number

• Variable lists in functions are indicated by a,b.... In place of this expression, you can enter:

  • A list of numbers

  • A list of fields that contain numbers

  • A list of expressions that result in a number

For example, you could enter the Sum function, which calculates the sum of its arguments in three different ways:

SUM(3,5,7)

or

SUM(Min,Safety,Max)

or

SUM(PRODUCT(Amount,Cost),Storage)

Viewing Available Functions

You can use many different functions in the Data Editor to create a variety of different expressions. Each function has its own components that need to be written in a specific order.

To view available functions, in the Data Editor, select List Functions from the Configure menu.

Copying Expressions

You can copy portions of text from one location and place them in another location.

To copy and paste an expression:

1. In a cell, highlight the expression that you want to copy.

2. Move the pointer to the selected expression and do one of the following:

   • Right-click and select Copy.

   • Press and hold the Ctrl key, and then click the C key.

3. Move the pointer to the cell where you want to paste the text and do one of the following:

   • Right-click and select Paste.

   • Press and hold the Ctrl key, and then click the V key.