Skip to main content

Designer GL Ledger Specifics

Use the following table to enter data in the Spreadsheet Server Settings > Ledger Specifics >Designer GL panel, or to access other functions:

Spreadsheet Server now enables Keys within CustomGL profiles to leverage SQL statements for dynamic lookup lists, replacing the previous limitation to static validation lists. This enhancement allows for interdependent key relationships where selecting one key dynamically updates the available options in related keys based on SQL query results.

Prerequisites for Dynamic SQL Profile Keys

  • User must be on v25.3 or later.

  • The profile must be modified or delivered with SQL written into the Keys.

Configuration Fields

Field Description

Designer General Ledger Options:

 

File Name

Specify the Designer GL file name. May use alphanumeric or underscore characters.

Connection Name

Click the drop-down list, and select the connection name. Right-click and select Refresh to refresh the data.

Key 1 Caption

Specify the caption for Key 1.

Key 1 Data Validation

Specify the values to be included in the Key 1 drop-down list.

With Dynamic SQL Profile Keys: Keys can now use SQL queries instead of static validation lists for dynamic lookup functionality.

Key 2 Caption

Specify the caption for Key 2.

Key 2 Data Validation

Specify the values to be included in the Key 2 drop-down list.

With Dynamic SQL Profile Keys: Keys can now use SQL queries instead of static validation lists for dynamic lookup functionality.

QuickText

Specify the button label to appear in the Spreadsheet Server Ribbon > Build a Template > General Ledger menu, to identify the ledger for building a GXL formula.

Tool Tip

Specify the tool tip to display for the associated QuickText item.

SAP: Secondary Database Connection

For SAP Designer GLs only, if desired, specify the name of the secondary database connection to be used for processing.

SQL TRIM Supported

If selected, the TRIM function used in SQL to remove a specified prefix or suffix from a string is supported.

Preload HSegments

If selected, HSegment values are preloaded into the cache when Spreadsheet Server is started. If not selected, the hierarchy option in the Locate Segment function is disabled, as there are no values to display.

Reset Connections on Clear Cache & Recalculate

If selected, the system closes and reopens connections when the Clear Cache & Recalculate function is processed, in an effort to improve performance. This option should only be selected in specific scenarios. Please contact insightsoftware prior to selecting this option.

Dynamic SQL Profile Keys Configuration

Profile Setup

  1. Load a profile in Spreadsheet Server Settings and notice the new SQL Override buttons that appear for all Keys.

  2. Save a new copy of the Profile for testing purposes to preserve original settings.

  3. Check the SQL Override button to enable SQL functionality.

    Note: When the box is checked, it clears all previous contents and does not save previous values

    Tip: Use the New Settings UI when possible to avoid this limitation.

  4. SQL Key Configuration

    1. Change the Keys to use SQL queries in the cleared content area.

    2. Supported variables include: APPUSER and CLOUDAPPUSER.

  5. Testing and Validation

    1. Open the Formula Builder and test the new dynamic SQL Keys.

    2. Verify that dynamic lists appear in Excel cells.

    3. Test key dependencies where one key selection affects available options in related keys.

Updating Dynamic Lists

There are three methods to update values shown in the lists:

Method 1: Formula Builder Refresh

  1. Select the cell with the Key or where the GXL is located.

  2. Open the Formula Builder.

  3. Dropdown lists in the Formula Builder are always dynamic and provide updated results automatically.

Method 2: Refresh Cached Segment Values

  1. Use "Refresh Cached Segment Values" under Locate a Segment to update validation lists in Excel.

  2. This updates all lists at once.

  3. When key dependencies exist, dependent keys will be cleared when parent key values change, alerting personnel to fill out new values.

Method 3: Data Refresh: Use Data Refresh (also known as Clear Cache and Recalculate) to update validation lists

Available Functions

Button Description

Set Default Designer GL

Opens Windows Explorer, allowing the user to browse to, and, select or set the default Designer GL without having to enter a password.

Load Default GL Definition

Loads the default Designer GL data in the panel. This function is password protected.

Browse for GL Definition

Opens Windows Explorer, allowing the user to browse to and select a Designer GL definition. This function is password protected.

Refresh Acct Key Sec Cache

Refreshes the account segment security cache, to process any changes made to ad hoc files.

Save

Saves all changes to the Designer GL definition.

User Interface Options

This design works in both the Old and New Settings UI. The New Settings UI provides additional features:

  • Colors for SQL code

  • Larger input boxes

  • Test buttons on each Key

  • Expand button to view larger SQL statements

  • Retention of values from SQL and manual validation lists (limitation exists only in Old UI)

Important Considerations

  • This feature requires Professional Services assistance for profile modification

  • This is an internal benefit for enhanced profile delivery, not intended for direct customer implementation

  • Customers should not modify profiles independently as this could break Spreadsheet Server functionality

  • Changes are backwards compatible across both user interfaces

Note: These options are used to maintain Designer GL definitions. These panels are password protected. Contact insightsoftware for more information.

See Spreadsheet Server Settings for more information.

Was this article helpful?

We're sorry to hear that.