5 Building Spreadsheets
5.9 Spreadsheet Server Lists
5.9.1 Create/Maintain Segment Lists
Segment Lists are used to create a hierarchy of individual segments or an account user field list which can be used in a GXA formula. A segment list may be used within a standard GXA formula by replacing any of the account segment values with the desired segment list name. A caret symbol (^) must be inserted prior to the segment list name in the formula for the system to recognize the segment list. A user's ability to
create/maintain segment lists from within Excel is determined by a parameter setting on the user's User Setting tab in the Configurator component.
To Create a New Segment List
1. In Excel from the SServer menu, select Segment Lists. The Segment Lists panel appears.
Nav Tip:This panel may also be accessed by selecting the SS ribbon equivalent (see SS Ribbon) -or-from the desktop, by selecting Start>All Programs>Global Software SSINF.Net>Maintain Segment Lists.
2. To create a new segment list, click the Add a New Segment List icon ( ) on the toolbar. The Segment List Profile panel appears.
3. Use the table to enter data on the Segment List Profile panel.
Field Description
List Type Identify whether the segment list will be based upon segments (i.e.
company, segment 2, segment 3, etc.) or an account user field list.
List Name Identify a unique name for the segment list. This name will be used in the formula.
Description Identify a description for the segment list.
Building Spreadsheets 42
Global Software, Inc.
4. Click the Save button to create the record. Within the same panel a new tab will open to a blank panel.
The panel displayed will vary based upon the list type assigned to the segment list.
· Segment List -- see Enter Values for Segment Lists
· Account User Field List -- see Enter Values for User Field Lists
To Maintain an Existing Segment List
1. In Excel from the SServer menu, select Segment Lists. The Segment Lists panel appears (see above).
Nav Tip:This panel may also be accessed by selecting the SS toolbar or ribbon equivalent (see SS Toolbar or SS Ribbon) -or- from the desktop, by selecting Start>All Programs>Global Software SSINF.Net>Maintain Segment Lists.
2. To sort the segment lists in the grid by name, type or description, click on the desired column header. To re-sort in descending order, click the desired column header a second time.
3. To filter the segment lists in the grid, enter the selection criteria into each appropriate column filter field.
Alphanumeric fields filter character by character.
4. To modify a segment list, select the segment list and click the Edit the Selected Segment List icon ( ) on the toolbar -or- double click the desired segment list. The appropriate List Values panel (i.e. Segment List or Account User Field List) appears displaying the values for the selected segment list. Segment list values may be added or removed as necessary.
5. To copy a segment list, select the segment list and click the Save As (Clone Selected Segment List) icon ( ) on the toolbar. The Clone Segment List panel appears. Enter the list name and description for the new segment list and click the Save button.
6. To delete a segment list, select the segment list and click the Delete the Selected Segment List icon ( ) on the toolbar.
7. To paste a segment list name to a cell on a spreadsheet, select the segment list and click the Copy the Segment List Name to Windows Clipboard icon ( ) on the toolbar. On the spreadsheet, select the desired cell and click the Paste button.
8. To lock a segment list, select the segment list and click the Change Locked/Unlocked Status icon ( ) on the toolbar. The Segment List is Currently Unlocked panel appears. Enter and verify the password, and click the Lock button. Locking the segment list allows other users to access the segment list but prevents changes from being made.
9. To unlock a segment list, select the segment list and click the Change Locked/Unlocked Status icon ( ) on the toolbar. The Segment List is Currently Locked panel appears. Enter the valid password, and click the Unlock button.
10. To print a list of segment list(s), select the segment list(s) and click the Print Selected Segment Lists icon ( ) on the toolbar. The Segment Lists Print Options panel appears. Select whether to print selected or all segment lists, and select whether or not to print detail information. Click OK. The system generates a list in Notepad which may be printed.
5.9.2 Enter Values for Segment Lists
When adding or maintaining values for a segment list, the Segment List Values panel appears if the list type for the list is a segment list.
1. In the Value field enter the segment values to include for the criteria, select the appropriate radio button to indicate whether to include or exclude the value, and click the Add button. Segment lists can be used for any portion of the account string (company, segment 2, segment 3, etc.). Single segment values, ranges or wildcards may be used.
2. Repeat step 1 until all segment values for the segment list have been added.
3. To remove a value from the segment list, select the value in the grid and click the Remove button. Or to remove all values from the segment list, click the Clear All button.
4. To purge the balances in the PC Cache file for the segment list, click the Purge from SSCache button.
This action may be taken if segment list values are altered.
5. When the segment list is complete, click the Close button.
Building Spreadsheets 44
Global Software, Inc.
5.9.3 Enter Values for User Field Lists
When adding or maintaining values for a segment list, the User Field Values panel appears if the list type for the list is an account user field list.
1. In the OR/AND radio buttons, specify the transition logic between the user field values, thus indicating whether or not the account must meet both criteria (AND) or only meet one criteria (OR) to be included in the segment list.
Note: The OR/AND selection criteria is ignored for the first value.
2. In the User Field, specify the user field to be used. Use the drop-down list to select the appropriate value.
3. Specify the operator to be applied to the user field value. Use the drop-down list to select the appropriate operator.
4. In the Value field, key the desired value to be included in the list and click the Add button. Single values, ranges, or wildcards may be used.
5. Repeat steps 1 thru 4 until all values for the segment list have been added.
6. To remove a value from the segment list, select the value in the grid and click the Remove button. Or to remove all values from the segment list, click the Clear All button.
7. To purge the balances in the PC Cache file for the segment list, click the Purge from SSCache button.
This action may be taken if segment list values are altered.
8. When the segment list is complete, click the Close button.
5.9.4 Enter Descriptions for User Fields
Each of the user field descriptions may be changed in Spreadsheet Server to properly reflect its use.
1. In Excel from the SServer menu, select Segment Lists. The Segment Lists panel appears.
Nav Tip:This panel may also be accessed by selecting the SS ribbon equivalent (see SS Ribbon) -or-from the desktop, by selecting Start>All Programs>Global Software SSINF.Net>Maintain Segment Lists.
2. Click the User Field Descriptions icon ( ) on the toolbar. The User Field Descriptions tab appears showing the default descriptions.
3. Change the description by double-clicking the corresponding description and entering the new description.
4. When all changes have been made, click the Close button.
Building Spreadsheets 46
Global Software, Inc.
5.9.5 Using Segment Lists in a GXA Formula
A segment list may be used within the standard GXA formula, by replacing any of the account segments with the desired segment list name. A caret symbol (^) must be inserted prior to the segment list name in the formula for the system to recognize the segment list. An at symbol (@) must be inserted prior to the segment list name in the formula for the system to recognize the account user field type list.
Note: It is most common to use cell references within GXA formulas to identify parameters.
Formula Example - Using Segment List for Account Segment:
=GXA("001-*-^MiscExpr-*","2000","PER","6","USD","M","Actuals")
Retrieves the actual monetary activity for period 6 for accounts included in the MISCEXPR segment list.
Formula Example - Using Segment List for User Field:
=GXA("001-310-6150-*"&"AND"&"@Tax","2001","PER","6","USD","M","Actuals")
Retrieves the actual monetary activity for period 6 for accounts in company 001, segment 2 equal 310, segment 3 equal 6150 which also meet the user field criteria in the TAX user field type segment list.
Note: An account user field type segment list is used in conjunction with account segment parameters. To accomplish this, the optional user field type segment list is added after the account segment
parameters. The two parameters must be joined by the literal "AND" or "OR".
5.9.6 SSLDESC Formula
Use the SSLDESC Formula to retrieve the description for a segment list.
Syntax:
=SSLDESC("Segment List")
Note: It is most common to use cell references within SSLDESC formulas to identify parameters.
Formula Example:
=SSLDESC("^MiscExpr")
Retrieves the segment list description for the MISCEXPR segment list.
Building Spreadsheets 48
Global Software, Inc.
5.9.7 View or Modify List Values from within Excel
Spreadsheet Server allows a user to view from a worksheet, the values contained in a specific segment list.
The user may also modify the values "on the fly". Segment lists can be shared among all Spreadsheet Server users, so caution should be exercised when making modifications. A user's ability to maintain segment lists from within Excel is determined by a parameter setting on the user's User Setting tab in the Configurator component.
1. From the spreadsheet, right click on the cell that contains the desired segment list. A popup menu appears.
2. On the popup menu, select SS Display Accounts. The appropriate List Values panel (i.e. Segment List or User Field List) appears displaying the values for the selected segment list. Refer to
Enter Values for Segment Lists or Enter Values for User Field Lists for more information about segment lists.
3. Segment list values may be added or removed as necessary.
4. When the segment list is complete, click the Close button.
5.9.8 Synchronize Segment Lists
The Synchronize Segment Lists function allows for the transfer of segment list definitions from one location to another. This process would be applicable if a user's segment list database resides on their local drive but they required updates from a centrally maintained segment list database.
1. In Excel from the SServer menu, select Segment Lists. The Segment Lists panel appears.
Nav Tip:This panel may also be accessed by selecting the SS ribbon equivalent (see SS Ribbon) -or- from the desktop, by selecting Start>All Programs>Global Software SSINF.Net>Maintain Segment Lists.
2. Click the Synchronize Segment Lists icon ( ) on the toolbar. The Synchronize Segment Lists tab appears displaying the segment lists from the current location as defined in the user settings.
Building Spreadsheets 50
Global Software, Inc.
3. Click the Browse button to navigate to and select the secondary segment list database. The system displays the segment lists from the secondary location in the bottom grid.
4. Select the desire segment list(s) to be synced and click the appropriate Arrow button.
· To sync a single segment list -- select the desired list.
· To sync multiple segment lists -- press and hold the Ctrl key as records are selected on the grid rows.
· To sync multiple segment lists in a range -- select the first list in the range, press and hold the Shift key, and select the last list in the range
Note: To push changes from the current location to the secondary location, select the desired segment lists to be synced in the current list grid and click the Down Arrow button.
To pull changes from the secondary location into the current location, select the desired segment lists to be synced in the secondary list grid and click the Up Arrow button.