Spreadsheet Server Settings

Spreadsheet Server Settings allow users to define various criteria to control processing. The majority of the options define processing for all users on the machine; various options (auto-start, display tool tips, enhanced logging, and expand rows) are defined for each user, however. Settings for GL type, segment lists, ad hoc queries, Designer GL definitions, and EDQ file locations may be set and/or overridden based on the parameters defined in Application Configurator. Various settings panels are conditional and based on the user's licensed features.

In Excel, from the Spreadsheet Server Ribbon, click Settings. The General panel appears.

Tip: This panel can also be accessed by clicking Settings in the Control Panel.

Use the following table to enter data in the General panel, or to access other functions:

Field Description

General Options:

General Ledger Type

Click the drop-down list, and select the General Ledger type being used. If you are not licensed to use the General Ledger feature, the value is set to None. Highlighted values have been preloaded based on settings in Application Configurator.

Note: After a selection is made, the system automatically navigates to the associated ledger-specific panel.

Label Language

Click the drop-down list, and select the language to be used while displaying labels in the following panels:

Build a Template
Formula Assistant
Spreadsheet Server Ribbon
Right-click Menu

Connection Options:

iSeries/DSN/SQLServer/Oracle

Specify the connection protocol to be used to connect to the host system.

Network Security & Connections Files

Click the Browse button and select the location of the security and connections files as specified in Application Configurator. This function is password protected.

Note: If in demo mode, select the desired security and connections file in order to switch to standard mode.

Available Environments

Specify the environment to be used for processing. This option only appears when the network security and connection files location is on the network and an available training environment exists.

Standard Options:

Start Automatically When Excel Starts

If selected, Spreadsheet Server automatically starts each time Excel is launched. Otherwise, it must be manually started.

Note: This option must be unselected in order to allow users to edit and/or double-click Excel objects that are embedded in a Word document.

Enhanced Logging

If selected, additional logging is recorded. For optimal processing, once an error has been resolved, it is recommended to clear the log file, and unselect this option (see View Log for more information).

Override List Separator With

Specify the character to use to as the list separator in Excel formulas and data validation lists. Generally, this value should be a comma ( , ), unless using different regional settings. Contact insightsoftware for more information.

Override Exclusion Character With

Specify the character to use to indicate when to exclude a value. The character cannot be one the following, nor, be equal to the Override List Separator With value:

~	*	]
@	-	\|
%	=	;
^	[	,
.

SQL Timeout (in seconds)

Specify the amount of time, in seconds, to wait before terminating an attempt to execute a GXL query and generating an error. A value of 0 indicates unlimited wait time.

Delimiter for separating from and to values in a range

Specify the delimiter for separating from and to values in a range. This default is a period ( . ), and should only be changed if other periods exist in the account segment values or query data. This setting applies to ranges used in Spreadsheet Server, Query, and Query Designer components.

Build a Template Row/Col button default value

Specify whether the Row/Col button for account segments and parameters in the Build a TemplateGXL, GEXQ, GEXD, GEXS, and GEXI panels defaults to Row or Col.

Display Tooltips

If selected, tooltips display in the following panels:

Spreadsheet Server Settings
Build a Template
Formula Assistant
SAP T-Codes

Drill Down Options:

Drill Down Timeout (in seconds)

Specify the amount of time, in seconds, to wait before terminating an attempt to drill down and generating an error. A value of 0 indicates unlimited wait time.

Drill Down

Specify whether to use a single dialog panel per drill down, or whether subsequent drill downs appear in a different tab within the same panel, when displaying drill down results.

SSRibbon Drill Down Button Default Action

Specify whether the Drill Down button in the Spreadsheet Server ribbon displays results in a panel, or pushes results to a worksheet.

Drill Down to Worksheet Goes To

Specify when pushing drill down results to a worksheet whether to create a new worksheet each time or to reuse the same drill down worksheet.

Drill Down to Worksheet data goes to

Specify whether to place the drill down in a table, range, or cell when pushing drill down results to a worksheet.

Cache:

Cache Type

Click the drop-down list, and select whether to use local or global cache with Redis Technology. Additional options are available based on the cache type selected (see View or Refresh Local Cache or View or Refresh Global Cache for more information).

Note: Additional components must be installed to implement the Global Cache with Redis Technology option. Contact insightsoftware for more information.

Global Cache URL

Specify the URL of the global cache.

Redis Server/Database ID/Password

Specify the server, database ID, and password associated with the Redis database.

Use SmartCache Buffer

If selected, balances from different periods with the same account key will be returned from an internal buffer to speed up processing. When selected, the option, Clear Buffer, appears in the Cache drop-down list in the Spreadsheet Server ribbon, allowing users to clear the internal SmartCache buffer.

Use Shared Cache

If selected, shared user data in the cache is used for processing. If not selected, cache records generated by the user are used for processing and user security remains intact.

Auto Refresh Pending Items

If selected, any pending items in the cache will refresh and continue to process. If not selected, any pending formulas will continue to display Pending in the workbook until manually refreshed.

Button	Function
Demo	Allows users to switch from standard to demo mode. The button only appears when the user is in standard mode.
View Log	Opens the Log Viewer panel, where the user may view, filter, or copy log entries, and email or clear the log file (see View Log for more information).
Maintain Segment Lists	Opens the Segment List Maintenance panel, allowing users to create and/or maintain segment lists (see Create or Maintain Segment Lists for more information).

Use the following table to enter data in the GL Reporting panel, or to access other functions:

Field	Description
GL Reporting Options:
Drill Down, Expand GL Row & GXE: Include zero balance accounts with activity	If selected, accounts with activity but zero balances are included in the account balances drill down, expand `GL` row and `GXE` results.
Summary Drill Down: Prompt for processing criteria	If selected, a prompt appears when the Summary Drill Down function is selected, allowing users to define processing criteria. Otherwise, default selection criteria is used.
GXL: Insert trailing dash	If selected, a dash is appended at the end of the account string.
Template: Default currency	Specify the default currency value when using Build a Template.
Delimiter for separating account segments	Specify the delimiter for separating account segments.
Expand GL Row function expands detail rows	Specify whether the Expand GL Row function expands detail rows up or down.
Expand GL Row: Convert null segment values to	Specify whether the Expand GL Row function converts null account segment values to an asterisk ( * ), or an empty cell.

Button	Function
Maintain Accounts Profile	Opens the Accounts Profile panel, allowing users to view and/or maintain the list of account strings to which they have access (see Account Security for more information).
Refresh Cached Segment Values	Refreshes cached `GL` account segment values.

Ledger-specific panels are used to maintain criteria specific to the ledger. Use the following links to access ledger specific settings information:

Use the following table to enter data in the Quarters panel:

Field	Description
Accounting Quarter Periods:
Monthly Period Quarters	Specify the starting and ending months for each quarter. These quarters are referenced in the `GXL` formula when using the `QTR` format.
Daily Period Quarters	Specify the starting and ending days for each quarter, for up to twelve quarters. These quarters are referenced in the `GXL` formula when using the `DQTR` format.

Use the following table to enter data in the Ad Hoc Queries panel:

Field	Description
Ad Hoc Queries:
Filter by GL	If selected, only ad hoc queries associated with the selected ledger are listed in the panel and are included in the list of ad hoc queries in the drill down feature. unselect this option to list all available ad hoc queries.
SQL is Read Only (uncheck to edit)	If selected, ad hoc queries are not to be created, edited, copied or deleted. This function is password protected. Contact insightsoftware for more information.

Button	Function
Test	Runs the selected ad hoc query and displays the results in a Drill Down panel.

Use the following table to enter data in the Query panel:

Field Description

Field	Description
Query Options:
Limit GEXD to 64K Rows/Sheet	If selected, the output of a `GEXD` formula is limited to 64,000 rows per sheet, and the query terminates if the output exceeds the limit. Note: You should not select this option in order to take advantage of the more than one million rows available.
Query Timeout	Specify the amount of time, in seconds, to wait before terminating an attempt to execute an `EDQ` formula query, and generating an error.

Query Options:

Limit GEXD to 64K Rows/Sheet

If selected, the output of a GEXD formula is limited to 64,000 rows per sheet, and the query terminates if the output exceeds the limit.

Note: You should not select this option in order to take advantage of the more than one million rows available.

Query Timeout

Specify the amount of time, in seconds, to wait before terminating an attempt to execute an EDQ formula query, and generating an error.

Use the following table to enter data in the Writeback panel:

Field	Description
Writeback Options:
Enhanced Logging	If selected, additional logging is recorded. For optimal processing, once an error has been resolved, it is recommended to clear the log file and unselect the option.
Filter Writeback Selections on Writeback Panels by Type	If selected, the Select Worksheet drop-down list in Writeback panels only displays sheets containing writebacks for the selected writeback type (Oracle API or generic tables) being processed. To optimize performance and reduce unnecessary filtering, insightsoftware recommends only selecting this option if multiple writeback types are processed in a workbook.
Oracle API Options:
Use Cached API Lists	If selected, Oracle APIs are retrieved from a cached list to speed up the processing time of APIs in the SWB Build a Template > Oracle Public API panel.
When Generating API Loader...	Specify whether the `Date` and `Status` columns are inserted to the far left or far right of the writeback data grid when the API Loader sheet is generated.
Generic Options:
When Generating Writeback Definition...	Specify whether the `Date` and `Status` columns are inserted to the far left or far right of the writeback data grid when the writeback definition is generated.

Button	Function
View Log	Opens the Log Viewer panel, where users may see information and error log entries, copy selected log entries, or clear the log file.

Use the following table to enter data in the Writeback Template panel:

Note: A user's ability to create or maintain writeback templates is determined by the user's Application Configurator settings.

Field	Description
Writeback Template:
Template Name	Specify the template name.
Builder Sheet Name	Specify the prefix name to be assigned to Builder sheets when the template is used. Special characters are not permitted. If left blank, the value defaults to `Builder`. When the template is processed, the system adds a suffix of `_API-n` (where `n` is the sequence number) to the name.
Loader Sheet Name	Specify the prefix name to be assigned to Loader sheets when the template is used. Special characters are not permitted. If left blank, the value defaults to `Loader`. When the template is processed, the system adds a sequence number to the name.
Consolidate Loader Sheets	If selected, a single loader sheet is created containing all API Loaders for the template. Otherwise, one loader sheet is created per API Loader.
Configuration	Click the drop-down list, and select the Oracle host configuration to be used by the template in order to connect to the database.
API List	Displays a list of the APIs for the template. To add an API to the list, click the Add button. The Select Oracle Public API panel appears, allowing users to select the API to be added to the list. Note: The Select Oracle Public API panel is similar to the SWB Build a Template > Oracle Public API panel, with the exception that unrelated fields have been removed or disabled. To display parameters for an API, select the API in the window. The parameters are displayed in the Parameter List window. To reorder APIs in the list, select the API in the window, and click the appropriate arrow button to move the selected API up or down in the list. To remove an API in the list, select the API in the window, and click the delete () button.
Omit API-Level Validation	If selected, indicates whether to ignore the `P_VALIDATE` parameter for the selected API during writeback validation processing. This addresses scenarios in which field dependencies exist between related `P_VALIDATE` APIs being processed together. This option only appears when the selected API contains the `P_VALIDATE` parameter.
Parameter List	Displays a list of parameters for the selected API. To include a parameter in the API query, select the Included check box. Select or unselect the Included check box in the header to select or unselect the option for all parameters. To assign a user-defined name to be displayed for a parameter in the Formula Builder Sheet and Loader Sheet, type the value in the Display Name field. To preset the Set As Default flag for a parameter in the Builder Sheet, select the Set As Default check box. Select or unselect the Set As Default check box in the header to select or unselect the option for all parameters. To preset the API field default value of a parameter in the Builder Sheet, type the value in the Data Value field. To populate a cell in the grid with a value from a query, click the Lookup button, navigate to and select the desired query, select the desired value, then click Insert Selected Value(s). The system returns the value from the first column to the selected cell.

Button	Function
New Template	Clears all fields and resets default field values in the panel, allowing users to start creating a new template.
Browse for Template	Opens Windows Explorer, allowing users to browse to and select a writeback template.
Save	Saves the changes to the writeback template.

The File Locations panel displays the location of various files used in Spreadsheet Server. Highlighted file locations have been preloaded based on settings in Application Configurator. Use the following table to enter data in the File Locations panel:

Field	Description
File Locations:
Shared Documents	For administrators using Citrix environments only, click the Browse button to select the location to which to move the basic folders. This function is password protected. Contact insightsoftware for more information.
Segment Lists	Type the path or click the Browse button to specify the location of the Segment Lists database and Segment Mapping objects. This may be a local or network drive.
Local Cache	Type the path or click the Browse button to specify the location of the local cache database.
Ad Hoc Queries	Type the path or click the Browse button to specify the location of the ad hoc queries.
PC Local Database	For BPCS and Data Warehouse ledgers only, type the path or click the Browse button to specify the location of the local `MBD` database. This location may also be updated from the Load Local PC Database or the Load Data Warehouse from GL panels.
Budget Manager Balances	For BPCS, Infinium and JD Edwards ledgers only, type the path or click the Browse button to specify the location of the budget balances when using insightsoftware's Budget Manager application.
Designer GL Definitions	Type the path or click the Browse button to specify the location of the Designer GL definitions.
Drill Down Layouts	Type the path or click the Browse button to specify the location of the user-defined drill down grid layouts.
EDQ Files	Type the path or click the Browse button to specify the location of the `EDQ` query files.
Writeback APIs	Type the path or click the Browse button to specify the location of the Writeback APIs.
Writeback Templates	Type the path or click the Browse button to specify the location of the Writeback Templates.

The System Information panel displays various data related to the user's system and Spreadsheet Server. To email this information to the helpdesk, click the Email button. The system opens a new Outlook message window, with the contents of this panel in the body of the email. Modify the email address and/or text of the email body as necessary and click the Send button.

The About panel displays insightsoftware's contact information. Click the Online Manuals link to access the online user manual.

Click OK. The system processes the changes made in the current panel, then closes it.

Spreadsheet Server Settings

Related articles

Was this article helpful?