Spreadsheet Server Settings

Starting in version 25.2, the Settings button includes a new dropdown menu, making it easier to access other applications bundled with Spreadsheet Server.

From Spreadsheet Server version 25.2 onwards:

The main Gear icon continues to launch Settings.
Application Configurator appears only if you have it installed locally.
Control Panel is visible to all users.
The Cloud Connector button has been moved under Settings.
- To access Cloud Connector from the main ribbon, you must enable the corresponding checkbox in the Security settings, as shown in the following screenshot.
- If a user has access to multiple Cloud Connector environments, all available environments will appear in a flyout menu.

TheSpreadsheet Server Settings panel allows users to define various criteria to control processing. The majority of the options define processing for all users on the machine, but it defines various options like auto-start, display tool tips, enhanced logging, and expand rows for individual users. You may set and/or override settings for GL type, segment lists, ad hoc queries, Designer GL definitions, and EDQ file locations based on the parameters defined in Application Configurator. Various settings panels are conditional and based on the user's licensed features.

From the Spreadsheet Server Ribbon, select Settings. The General panel displays.

Tip: You can also access this panel from 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

Select the general ledger type from the drop-down menu. If you do not have the license to use the General Ledger feature, the value None displays in the field.

Note: The system navigates to the associated ledger-specific panel as you select the GL type.

Label Language

Select the language to use when displaying labels in Spreadsheet Server panels like the ribbon, right-click menu, Formula Builder, Formula Assistant, and so on.

Connection Options:

iSeries/DSN/SQL Server/Oracle

Specify the connection protocol to use to connect to the host system.

Network Security & Connections Files

Click Browse 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 files folder in order to switch to standard mode.

Available Environments

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

Standard Options:

Start Automatically When Excel Starts

Select this checkbox to autoload the Spreadsheet Server ribbon each time you launch Excel, as opposed to manually enabling it.

Enhanced Logging

Select this checkbox to record additional logs.

For optimal processing, it is recommended to clear the log file and unselect this option as you resolve errors.

Override List

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

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 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/to values in a range

Specify the delimiter for separating from and to values in a range. This defaults to Period ( . ), and you should only change it to Percent ( % ) 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 default selection for segments and columns in the formula panels should be a row or column.

Display Tooltips

Select this checkbox to display tooltips in various Spreadsheet Server panels.

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 whether to create a new worksheet each time when pushing drill down results to a worksheet 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

Select Local Cache or Global Cache with Redis Technology from the drop-down menu. Additional settings are available based on the selected cache type.

Note: You must install additional components to implement the Global Cache with Redis Technology option. Contact your account manage 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 Shared Cache

Select this checkbox to use shared user data in the cache for processing. If you do not select this option, the system uses user generated cache records for processing and the user security remains intact.

Auto Refresh Pending Items

Select this checkbox to refresh any pending items in the cache and continue to process. If you do not select this option, any pending formulas will continue to display Pending in the workbook until manually refreshed.

Button	Function
Demo	Allows users to switch from the standard to demo mode. This button only appears when the user is in standard mode.
View Log	Opens the Log Viewer panel where the user can analyze the logging information.
Maintain Segment Lists	Opens the Segment List Maintenance panel allowing users to create and/or maintain segment lists.

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	Select this checkbox to include zero balance accounts with activity in the drill down, expand GL row, and GXE results.
Summary Drill Down: Prompt for processing criteria	Select this checkbox to display a prompt when you select the Summary Drill Down function allowing users to define the processing criteria, as opposed to using the default selection criteria.
GXL: Insert trailing dash	Select this checkbox to append a dash at the end of the account string.
Template: Default currency	Specify the default currency value when using Build a Template in Formula Builder.
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.

With ledger-specific panels, you can maintain criteria specific to the ledger. Use the following links to access ledger-specific settings information:
- Analyst
- BPCS 4.05
- BPCS 6.02
- BPCS 6.04
- Data Warehouse
- Designer GL
- Infinium
- Jack Henry 20/20
- Jack Henry Silverlake
- JD Edwards
- Lawson
- Movex
- Oracle
- PeopleSoft
- SAP

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. The application uses these quarters when using the `QTR` format in formulas.
Daily Period Quarters	Specify the starting and ending days for each quarter, for up to twelve quarters. The application uses these quarters when using the `DQTR` format in formulas.

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

Field	Description
Ad Hoc Queries:
Filter by GL	Select this checkbox to only list ad hoc queries associated with the selected ledger in the panel and to include them 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)	Select this checkbox to disable creating, editing, copying, or deleting ad hoc queries. This function is password protected. Contact your account manager 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
Query Options:
Limit GEXD to 64K Rows/Sheet	Select this option to limit the output of a GEXD formula to 64,000 rows per sheet. The query terminates if the output exceeds the limit. Note: Do 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.

Field

Description

Query Options:

Limit GEXD to 64K Rows/Sheet

Select this option to limit the output of a GEXD formula to 64,000 rows per sheet. The query terminates if the output exceeds the limit.

Note: Do 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	Select this checkbox to record additional logs. For optimal processing, it is recommended to clear the log file and unselect this option as you resolve errors.
Filter Writeback Selections on Writeback Panels by Type	Select this checkbox to only display sheets containing writebacks for the selected writeback type (Oracle API or generic tables) being processed in the Select Worksheet drop-down menu in Writeback panels. To optimize performance and reduce unnecessary filtering, insightsoftware recommends only selecting this option if the workbook is processing multiple writeback types.
Oracle API Options:
Use Cached API Lists	Select this checkbox to retrieve Oracle APIs 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 to insert the `Date` and `Status` columns to the far left or far right of the writeback data grid when generating the API Loader sheet.
Generic Options:
When Generating Writeback Definition...	Specify whether to insert the `Date` and `Status` columns to the far left or far right of the writeback data grid when generating the writeback definition.

Button	Function
View Log	Opens the Log Viewer panel where the user can analyze the logging information.

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

Note: Based on the Application Configurator settings, a user can create or maintain writeback templates.

Field	Description
Writeback Template:
Template Name	Specify the template name.
Builder Sheet Name	Specify the prefix name to assign to builder sheets when using the template. You cannot use special characters. If left blank, the value defaults to `Builder`. When processing the template, 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 assign to loader sheets when using the template. You cannot use special characters. If left blank, the value defaults to `Loader`. When processing the template, the system adds a sequence number to the name.
Consolidate Loader Sheets	Select this checkbox to create a single loader sheet containing all API loaders for the template, as opposed to creating one loader sheet is per API loader.
Configuration	Select the Oracle host configuration the template should use to connect to the database from the drop-down menu.
API List	Displays a list of the APIs for the template. To add an API to the list, click Add. The Select Oracle Public API panel allows users to select the API to add 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 are unavailable. To display parameters for an API, select the API in the window. The parameters display in the Parameter List panel. 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 from the list, select the API in the window, and click the delete button.
Omit API-Level Validation	Select this checkbox to indicate 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 processing 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 checkbox. 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 display for a parameter in the Formula Builder Sheet and Loader Sheet, type the value in the Display Name field. To pre-set the Set As Default flag for a parameter in the Builder Sheet, select the Set As Default checkbox. Select or unselect the Set As Default check box in the header to select or unselect the option for all parameters. To pre-set 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 Lookup, 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 the fields and resets the 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 in Spreadsheet Server. The Application Configurator pre-loads the following file locations.

Field	Description
File Locations:
Shared Documents	For administrators using Citrix environments only, click Browse to select the location for the basic folders. This function is password protected. Contact your account manager for more information.
Segment Lists	Type the path or click Browse 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 Browse to specify the location of the local cache database.
Ad Hoc Queries	Type the path or click Browse to specify the location of the ad hoc queries.
PC Local Database	For BPCS and Data Warehouse ledgers only, type the path or click Browse to specify the location of the local MBD database. You can also update ths location 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 Browse to specify the location of the budget balances when using insightsoftware's Budget Manager application.
Designer GL Definitions	Type the path or click Browse to specify the location of the Designer GL definitions.
Drill Down Layouts	Type the path or click Browse to specify the location of the user-defined drill down grid layouts.
EDQ Files	Type the path or click Browse to specify the location of the EDQ query files.
Writeback APIs	Type the path or click Browse to specify the location of the Writeback APIs.
Writeback Templates	Type the path or click Browse 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 Email. 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 Send.
The About panel provides general information about your system and the application.
Click OK. The system processes the changes made in the current panel and closes it.

Spreadsheet Server Settings

Was this article helpful?