spreadsheet server for use with infinium - release … · spreadsheet server for use with...

Global Software, Inc.'sSpreadsheet Server

for use with Infinium®User Manual

Release V10 R2 M4

Worldwide Headquarters3200 Atlantic AvenueRaleigh, NC 27604 USA+1.919.872.7800www.glbsoft.com

EMEA Headquarters500 Chiswick High RoadLondon, W4 5RG UK+44 (0) 20 8956 2213

Spreadsheet Server for use with Infinium

Global Software, Inc's Spreadsheet Server converts familiarspreadsheet software, such as Microsoft® Excel, into tightlyintegrated analytical tools for financial systems. Financial userscan leverage the strength of spreadsheets with seamlessreal-time integration to financial information. Spreadsheet Servereliminates the re-keying or downloading of data into spreadsheetsand makes them an integral part of the financial application.

All rights reserved. No parts of this work may be reproduced in any form or by any means - graphic, electronic, ormechanical, including photocopying, recording, taping, or information storage and retrieval systems - without thewritten permission of the publisher.

Microsoft, Excel, Windows, Office, Access, Outlook, and SQL Server are all registered trademarks of MicrosoftCorporation. Infinium is a registered trademark of Infor Global Solutions. Oracle is a registered trademark ofOracle Corporation. IBM, DB2, iSeries, and AS/400 are trademarks or registered trademarks of InternationalBusiness Machines Corporation. Trade names referenced are the service marks, trademarks, or registeredtrademarks of their respective manufacturers in the United States and/or other countries. Global Software, Inc. isnot associated or affiliated in any manner with the respective owners of the foregoing trademarks, trade names orservice marks unless expressly stated otherwise. The respective owners of the foregoing trademarks, trade namesor service marks have not endorsed, certified or approved any of Global Software, Inc.'s products for use inconnection with their respective products. While every precaution has been taken in the preparation of this document, the publisher and the author assumeno responsibility for errors or omissions, or for damages resulting from the use of information contained in thisdocument or from the use of programs and source code that may accompany it. In no event shall the publisher andthe author be liable for any loss of profit or any other commercial damage caused or alleged to have been causeddirectly or indirectly by this document.

Printed: November 2009

Spreadsheet Server for use with Infinium

Table of Contents

Part I Spreadsheet Server Overview 3

................................................................................................................................... 31 Introducing Spreadsheet Server

................................................................................................................................... 42 Features & Benefits

Part II Spreadsheet Server Installation 5

................................................................................................................................... 51 Additional Components

................................................................................................................................... 62 Spreadsheet Server Installation

................................................................................................................................... 73 Uninstall Process

Part III Getting Started 8

................................................................................................................................... 81 Initiate Excel Add-In

................................................................................................................................... 92 User Settings

Part IV Load Local PC Database (optional) 11

Part V Building Spreadsheets 14

................................................................................................................................... 151 Worksheet Wizard

................................................................................................................................... 182 GXD Formula

................................................................................................................................... 193 GXA Formula for Account Values

................................................................................................................................... 214 GXA Formula for Budget Manager Data

................................................................................................................................... 225 GXU Formula for User Fields

................................................................................................................................... 236 List Accounts for a GXA or GXU Formula

................................................................................................................................... 247 Spreadsheet Server Lists

.......................................................................................................................................................... 24Create Lists

.......................................................................................................................................................... 26Enter Values for Segment Lists

.......................................................................................................................................................... 27Enter Values for User Field Lists

.......................................................................................................................................................... 28Enter Descriptions for User Fields

.......................................................................................................................................................... 29Print Lists

.......................................................................................................................................................... 30Using Lists in a GXA Formula

.......................................................................................................................................................... 31SSLDESC Formula

.......................................................................................................................................................... 32View or Modify List Values from within Excel

Part VI Refresh Calculations 33

................................................................................................................................... 331 Calculation Options

................................................................................................................................... 342 Review/Refresh PC Cache

Part VII Drill Down Functionality 35

................................................................................................................................... 351 General Grid Features

.......................................................................................................................................................... 36Copy / Paste Grid Records from Detail Account Balances

.......................................................................................................................................................... 37Copy / Paste Grid Records from Journal Drill Down Panels

.......................................................................................................................................................... 39Modify Grid Layouts

Spreadsheet Server for use with Infinium - Release V10 R2 M4I

© 2009 ... Global Software, Inc.

................................................................................................................................... 412 Drill Down to Detailed Account Balances

................................................................................................................................... 423 Drill Down to Multiple Column Account Balances

................................................................................................................................... 434 Drill Down to Journals per Account(s)

................................................................................................................................... 445 Drill Down to Journal Entry Lines

................................................................................................................................... 456 Drill Down to Subsystem Detail

Part VIII Review Account Master 46

................................................................................................................................... 461 List Accounts

................................................................................................................................... 472 Copy Accounts from Account List

................................................................................................................................... 483 View Account Balances

Part IX Miscellaneous Features 50

................................................................................................................................... 501 SS Toolbar

................................................................................................................................... 512 Generate Account Detail for Current Sheet

................................................................................................................................... 533 Expand Detail Reports - Account Details

.......................................................................................................................................................... 54Expand a Single Source Line

.......................................................................................................................................................... 57Expand Multiple Source Lines

.......................................................................................................................................................... 62Retrieve User Field Data

................................................................................................................................... 644 Expand Detail Reports - Journal Entry Details

................................................................................................................................... 675 Account Security

................................................................................................................................... 696 Hide Rows with Zero Balances

................................................................................................................................... 707 Disable / Enable Spreadsheet Server Formula Calculations

................................................................................................................................... 718 Reset Host Server Connection

Index 72

IIContents


Spreadsheet Server for use with Infinium - Release V10 R2 M43

1 Spreadsheet Server Overview

1.1 Introducing Spreadsheet Server

What is Spreadsheet Server?Global Software Spreadsheet Server converts familiar spreadsheet software (Microsoft® Excel) into a tightlyintegrated analytical tool for financial systems. Financial users can leverage the strength of spreadhseets withseamless real-time intergration to financial information. Spreadsheet Server eliminates the re-keying ordownloading of data into spreadsheets and makes those spreadsheets an intergral part of financial applications.

Ease of UseBased on knowledge of Excel, minimal training is required to use Spreadsheet Server. No programming or queryknowledge is necessary. Users utilize the standard spreadsheet capabilities supplemented with simple cell formulasto gain access to real-time financial information.

Leverage Spreadsheet SkillsWhile maintaining the full functionality of the spreadsheet application, Spreadsheet Server allows the user to mixGeneral Ledger and non General Ledger data in a single worksheet. The combination of powerful spreadsheetfunctions (charting, text formatting, and sorting) and real-time financial information provides the basis for building anincome statement, balance sheet, and other financial statements.

Spreadsheet Server Overview 4

Global Software, Inc.

1.2 Features & Benefits

Features· Retrieve real-time period, quarter-to-date, and year-to-date balances· Retrieve account descriptions· Retrieve balances using ranges, wildcards, user fields, or segment lists· Drill down to detailed account balances· Drill down to journal detail for selected accounts· Drill down to journal lines for a selected journal entry· Drill down to selected subsystem detail· Copy drill down data and paste into spreadsheets or other documents

Benefits· Leverage spreadsheet skills and write reports within minutes· Eliminate requirement for IT or super-users to create/change financial reports· No more downloading or re-keying of spreadsheet data· Reduce number of days to close financial books; save just 3 hours per month per user and the investment is paid for in

less than 12 months· Publish executive-quality reports from current spreadsheet software· Increase the efficiency and timeliness of the budgeting process· Build a complete Executive Information System· Create ad-hoc reports or perform account analysis within minutes· Save financial user's time by combining reporting, account inquiry, and journal inquiry into one application; free up time

for true business analysis· Perform corporate consolidations with instant access to real-time data· Reduce external audit time by allowing easier, instant access to financial data with full drill down capabilities· Reconcile accounts· Print or email spreadsheets quickly and easily from a single, central application


2 Spreadsheet Server Installation

2.1 Additional Components

REQUIRED COMPONENTS

Spreadsheet Server Infinium ConfiguratorThe configurator for Spreadsheet Server must be installed and configured for each user. Contact the securityadministrator for the network location of the configurator file.

OPTIONAL COMPONENTSAdditional components may be required based upon the type of database being accessed. Contact your ITdepartment to determine what is required to be installed.

iSeries™ DB2® Database1. Either the ODBC component or the OLE DB Provider component of the IBM® iSeries Access™ for Windows®

must be loaded onto the PC which will have Spreadsheet Server installed.

2. Run the setup program within IBM iSeries Access for Windows to determine that one of the Data Accesscomponents is installed. See the screen below to verify the component is installed.

Microsoft® SQL Server™ DatabaseThe Microsoft SQL Server OLE DB Provider must be loaded on the PC which will have Spreadsheet Serverinstalled.

Oracle® DatabaseThe Oracle client software must be loaded and a host connection must be configured on the PC which will haveSpreadsheet Server installed.

Spreadsheet Server Installation 6


2.2 Spreadsheet Server Installation

1. Run the Setup program from either an installation CD or the file downloaded from Global Software's web site.

2. Follow and respond to the installation prompts. Global recommends taking the defaults.

After installation you may be directed to reboot depending upon the PC's MDAC level. This is generally typicalfor Windows® 9X / Office® 97 configurations.


2.3 Uninstall Process

It is not necessary to uninstall Spreadsheet Server prior to installing new versions. An uninstall should only be performed inorder to completely remove the product from the PC.

1. Start Excel.

2. Signing on to Spreadsheet Server is optional. Cancellation of sign on is available.

3. For Excel 2003 and Prior:· From the Excel menu, select Tools>Add-Ins. The Add-Ins panel appears.· De-select the add-in named Global's SSINFINIUM, and click OK.

For Excel 2007:· Click the Office button. The Excel Options panel appears.· Select Add-Ins, and click Go. The Add-Ins panel appears.· De-select the add-in named Global's SSINFINIUM, and click OK.

4. Ensure the SServer menu no longer appears.

5. Exit Excel.

6. Run the Unwise program installed with the product. The program is located in the Program Files\Global SoftwareSSINFINIUM folder if installation defaults were taken.

Getting Started 8


3 Getting Started

3.1 Initiate Excel Add-In

1. Start Excel.

2. For Excel 2003 and Prior:· From the Excel menu, select Tools>Add-Ins. The Add-Ins panel appears.· Select the add-in named Global's SSINFINIUM, and click OK.

For Excel 2007:· Click the Office button. The Excel Options panel appears.· Select Add-Ins, and click Go. The Add-Ins panel appears.· Select the add-in named Global's SSINFINIUM, and click OK.

3. From the Excel menu, select SServer-Not Started>Start Spreadsheet Server. The Signon to SpreadsheetServer dialog box appears.

4. On the Sign On dialog box identify the following information:· User ID (Spreadsheet Server signon)· Password (Spreadsheet Server password)· Default Library (override database library name specified in the Configurator)· Retrieve Account Balances (local database)· Work in Local Mode without a Connection

Note: When working without a connection, the user only has access to account balances in the localdatabase file, and as a result drill-downs to journals or list accounts will not be functional. In addition,the system continues to verify the user is a valid Spreadsheet Server user, thus the user must havethe Configurator on their hard drive.

5. Click OK.

Note: Each time Excel is initiated, repeat steps 3, 4, and 5 to properly start Spreadsheet Server.


3.2 User Settings

User Settings allow the user to define various criteria to control processing.

1. From the desktop, click Start>Programs>Global Software SSINFINIUM>Spreadsheet Server Settings. TheSpreadsheet Server Settings panel appears.

Note: This panel may also be accessed by selecting the SS Toolbar equivalent (see SS Toolbar).

2. Use the table to enter data on the General tab of the Spreadsheet Server Settings panel.

Field Description

No Record Found Identifies the message to display in cell if no data is found.

Include Zero Balance Accounts Indicates whether to include zero balance accounts in the accountbalances drill down window.

Quarter Periods Indicates the starting and ending period numbers for each of the fourquarters.

Drill Down Controls The first setting defines the number of drill down records that are initiallydisplayed prior to receiving a warning message. The second settingcontrols the number of records that are subsequently displayed.

Getting Started 10


3. Use the table to enter data on the Database Locations tab of the Spreadsheet Server Settings panel.

Field Description

Spreadsheet Server ListsLocation

Defines the location of the Spreadsheet Server Lists database. Thismay be a local or network drive.

PC Local Database Location Identifies the location for the Local.MDB database. This location mayalso be updated from the Load Local Database panel.

PC Budget Database Location Identifies the location for budget data that has been loaded from GlobalSoftware's Budget Manager product.

Ad Hocs Location Identifies the location of the SQL queries used for Ad Hoc drill-downs.This generally resides on a network drive. For more information on adhocs, contact Global Software.

Configurator Location Identifies the location for the Configurator database. This is generally anetwork drive. In order to work disconnected from a host, this must belocated on the user's local drive.

4. Click Save to write the record.


4 Load Local PC Database (optional)

Within Spreadsheet Server, the option exists to download account balance information into local databases, allowingfor optimized calculation speeds. This is an excellent option for producing Spreadsheet Server reports once the bookshave been closed and the analytical tasks have been performed in real time. The time spent loading the localdatabases is greatly offset by the dramatically reduced spreadsheet calculation times.

A user's ability to process the Load Local PC Databases function or to access local databases created withinSpreadsheet Server is determined by parameter settings on the User - Load PC Balances tab in the SpreadsheetServer Configurator component.

1. From the desktop, select Start>Program Files>Global Software SSINFINIUM>Load Local PC database. The SignOn dialog box appears.

2. On the Sign On Dialog box, specify the user ID, password and default library, and click OK. The Load PC LocalDatabases panel appears. Each step in the load process is represented by individual tabs.

3. Local Files Location: A separate MDB file of balances is created for each distinct dataset (year and data typecombination). The top portion identifies the location for the files. Click the Browse button and select a location forthe local database files. If a previous download has been performed to this location, the bottom portion of thepanel will display a list of the existing files.

4. Ledger Selections: Balances for year and data type combinations that are to be loaded are selected from thegrids in the middle of the panel. The grids must first be filled with selections from the host. This is accomplishedby specifying host selection criteria and clicking the Fill Selection Grids button. Click the desired years to bedownloaded. Hold down the Ctrl key and click on the lines of the grid to make multiple selections.

Load Local PC Database (optional) 12


Execution of the load process can be performed immediately (Load Now tab) or may be deferred to executeunattended at a later date and time (Schedule Load tab).

5. Load Now: Ensure all preceding steps have been completed and click the Load button. The program will querythe host based upon the selections and load the balances to the appropriate databases in the Local location.Statuses are displayed during the load process.

When completed, final statistics are displayed along with the total records loaded and the start and finish times.


6. Schedule Load: Click the Date and Time buttons to specify when the load is to be executed. Click theSchedule Now button. The program will minimize and wait for the load date and time. When the scheduled dateand time is reached, the program will reconnect to the host (in case an IPL or reboot of the server hastranspired) and initiate the load process. See the Load Now section for an explanation of the load events.

Building Spreadsheets 14


5 Building Spreadsheets

Spreadsheet Server retrieves financial data from the General Ledger into Excel using the following formulas:

· GXD - Returns an account description -- Refer to GXD Formula· GXA - Returns an account balance -- Refer to GXA Formula for Account Values· GXU - Returns an account balance using user field references -- Refer to GXU Formula for User Fields· GXE - Explodes summary data line into detail data lines -- Refer to Expand Detail Reports - Account Details and Expand Detail Reports - Journal Entry Details· SSLDESC - Returns a segment list description -- Refer to SSLDESC Formula

These formulas can be used in a spreadsheet cell in the same manner as other spreadsheet functions.

Spreadsheet Server provides the Worksheet Wizard as a tool for starting a spreadsheet.


5.1 Worksheet Wizard

The Worksheet Wizard provides users with an easy method of getting started with Spreadsheet Server. TheWizard can be used to create a new worksheet or to insert the required rows and columns used for GXA formulasinto an existing worksheet.

1. Select either a new Excel worksheet or an existing worksheet. If using an existing worksheet, select the firstcell that will be populated with a GXA formula.

2. From the Excel menu, select SServer>Worksheet Wizard. The Getting Started Worksheet Wizard panelappears.

In the example below, cell B7 is the first cell that will be populated with a GXA formula. The worksheet selectedis an existing one, not an empty one.



3. Select the Empty Sheet or Non-Empty Sheet radio button based upon whether using a new or existingworksheet.

4. Use the scroll bars to assign the Target Cell location for the GXA formula.

5. The wizard assumes the account number contains at least 3 segments. Indicate additional account segmentsare used by selecting the appropriate check boxes for the segment numbers. This example indicates that theInfinium ledger is set up to use a maximum of 5 segments in the account number.

6. Enter either asterisks or valid data for the account segments being used. It is not necessary to be exact, as thewizard is used to simply start the worksheet.

7. Indicate whether the account segments 1, 2, or 3 will be part of the column or row parameters in the GXAformula. In this example, segment 1 (Company) will be a column parameter.

8. Enter valid data for the Year, Format, Period, Currency Code, and Balance Type parameters. For variousfields, click the down arrow and select the appropriate field value.

9. Click the Modify Current Worksheet button. Columns and rows used for the GXA parameters are inserted intothe worksheet and a GXA formula is generated in cell H15 (adjusted from the original target cell address of B7).The wizard generates a formula that targets ACTUALS.



5.2 GXD Formula

Use the GXD formula to retrieve an account description for a single account string.

Syntax:=GXD("Account String")

Note: It is most common to use cell references within GXD formulas to identify parameters.

Formula Examples:=GXD("001-100-1070-000")Retrieves the account description for the account string 001-100-1070-000 (CO-DEPT-PRIME-SUB).


5.3 GXA Formula for Account Values

Use the GXA formula to retrieve an account balance for a particular balance type and currency, in a specific format,for an individual period.

Syntax:=GXA("Account String","Year","Format","Period","Currency","Balance Type","Budget or Actuals")

Note: It is most common to use cell references within GXA formulas to identify parameters.

Parameters:Account StringThe account string represents the account segments delimited by a dash. Each segment of the account string is required.

Single Account String 001-100-1070-000Retrieves a single account 001-100-1070-000.

Account Mask (End of String) 001-100-107*-*Retrieves any account with 001 in company, 100 in department and any prime beginning with 107.

Account Mask (Multiple) 001-1*-1*Retrieves any account with 001 in company, any department that begins with 1, and any prime that begins with 1.

Account Segment Ranges 001-123-1000.2000-*Retrieves any account with 001 in company, 123 in department, and a range of primes from 1000 to 2000.

YearYear identifier.

FormatFormat options are:

PER Period activity for the selected month

QTR Activity for the periods included in the selected quarter number

YTD Activity for periods 1 through the designated period number excluding the opening balance

PeriodCorresponds to period, quarter number, or range of periods (i.e. 1.5 for periods 1 thru 5) for the specified format.

CurrencyIdentifies the currency code for the account balance.

Balance TypeBalance types are:

M Monetary balances

S Statistical balances (currency is blank)

D Debit balances

C Credit balances

1 Numeric User Field 1

2 Numeric User Field 2



Formula Examples - Using Single Account Value:=GXA("001-123-1000-000","2005","PER","12","USD","M","Actuals")Retrieves the monetary balance for period 12 for a single account string.

=GXA ("001-123-1000-000","2005","PER","12","USD","M","CYBUD")Retrieves the monetary balance for period 12 for a single account string for budget named CYBUD.

Note: To retrieve actual data, the literal "Actuals" must be identified. To retrieve budget data the budget name mustbe identified.

Formula Example - Using Alternate Library Name:=GXA("Account String","Year","Format","Period","Currency","Balance Type", "Budget or Actuals","Library Name")

Note: An alternate library name can be specified in a GXA formula to allow data retrieval from multiple libraries. Toaccomplish this, an optional library parameter should be included at the end of the formula. The parametersyntax is the database library name.


5.4 GXA Formula for Budget Manager Data

Use the GXA formula to retrieve budget amounts that have been transferred from Global's Budget Manager intoSpreadsheet Server.

Syntax:=GXA("Account String","Year","Format","Period","Currency","Balance Type","Budget:Name:Revision")


Parameters:Budget ParameterEnter the literal "Budget" and then identify the budget name and budget revision separated by a colon.

Formula Example:=GXA("001-100-3100-100","2005","PER","6","USD","M","Budget:2005FISCAL:003")Retrieves the budget data from Budget Manager budget 2005FISCAL, revision 003, for period 6 for a singleaccount.



5.5 GXU Formula for User Fields

Use the GXU formula to retrieve an account balance using the User Field references within the account master.

Syntax:=GXU("Company","User Field 1","User Field 2","User Field 3","User Field 4","Year","Format","Period","Currency","Balance Type","Budget or Actuals")

Note: It is most common to use cell references within GXU formulas to identify parameters.

Parameters:User Field 1The User Field 1 represents the first user field reference in the account master. A single value,range or wildcardcan be used.

User Field 2The User Field 2 represents the second user field reference in the account master. A single value, range orwildcard can be used.

User Field 3The User Field 3 represents the third user field reference in the account master. A single value, range or wildcardcan be used.

User Field 4The User Field 4 represents the fourth user field reference in the account master. A single value, range orwildcard can be used.

Formula Example:=GXU("01","*","2000","*","PROD","2005","PER","12","USD","M","Actuals")Retrieves the monetary balance for period 12 using User Field 2 and User Field 4 criteria.


5.6 List Accounts for a GXA or GXU Formula

This feature allows review of account numbers which will be used in the calculation of a GXA or GXU formula.

1. Select the desired cell that contains the GXA or GXU formula and right-click. A popup menu appears.

2. From the popup menu, select SS List Accounts. The Display Accounts window appears.

3. Review the accounts listed in the grid to ensure the formula is retrieving the appropriate accounts.

4. To modify the list of accounts beings displayed on the Display Accounts window, alter the values in the GXA orGXU account segment filter fields and click the Display button. The account segment values may includeranges, segment lists, and/or wildcards.

Note: Modifying the filter does not change the account segment values in the spreadsheet cell being referenced by the GXAformula.

Note: Double click on the S1 header (not in the field) to mask each segment, and to clear all GXU segment fieldsand From/To Date fields.



5.7 Spreadsheet Server Lists

5.7.1 Create Lists

Spreadsheet Server Lists are used to create a hierarchy of individual segments, business unit category codes, oraccount category codes that can be used in a single GXA formula.

1. From the desktop, click Start>Program Files>Global Software SSINFINIUM>Maintain Spreadsheet Server Lists.The Maintain Spreadsheet Server Lists panel appears.

2. To create a new segment list, click the New button. The List Profile popup panel appears.

3. Use the table to enter data on the List Profile popup panel.


Field Description

List Type Identify whether the segment list will be based upon segments(company, department, prime, sub), or an Account User Field List.

List Name Identify a unique name for the list. This name will be used in the GXAformula.

Description Identify a description for the list.

4. Click Save to create the record.

5. On the Maintain Spreadsheet Server Lists panel, click the Values button. The panel displayed will vary basedupon the list type assigned to the segment list.



5.7.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 thelist is a segment list.

1. In the Value field enter the segment values to include for the criteria, select the appropriate radio button toindicate whether to include or exclude the value, and click the Add button. Single segment values, ranges orwildcards may be used. Segment lists can be used for any portion of the account string (company, department,etc.).

2. Repeat step 1 until all segment values for the list have been added.

3. To remove a value from the list, select the value in the grid and click the Remove button. Or to remove allvalues from the list, click the Clear All button.

4. To purge the balances in the PC Cache file for a segment list, click the Purge SSCache button. This actioncan be used if segment list values are altered.

5. When the list is complete, click the Close button.


5.7.3 Enter Values for User Field Lists

When adding or maintaining values for a segment list, the List Values panel appears if the list type for the list is anaccount user field list.

1. In the OR/AND radio buttons, specify the transition logic between the user field values, thus indicating whetheror not the account must meet both criteria (AND) or only meet one criteria (OR) to be included in the segmentlist.

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 ot select the appropriateoperator.

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 through 4 until all values for the list have been added.

6. To remove a value from the list, select the value in the grid and click the Remove button. Or to remove allvalues from the list, click the Clear All button.

7. To purge the balances in the PC Cache file for a segment list, click the Purge SSCache button. This action canbe used if segment list values are altered.

8. Once the list is complete, click the Close button.



5.7.4 Enter Descriptions for User Fields

Each of the user field descriptions can be changed in Spreadsheet Server to properly reflect its use.


2. Click the User Field Descriptions button. The User Field Descriptions panel appears showing the defaultdescriptions.

3. Change the description by double-clicking the corresponding description and entering the new description.


5.7.5 Print Lists

The system allows users to print selected segment lists and their list detail, or to generate a list of all segment listsexcluding the list detail.


To Print Selected Segment Lists (including segment list detail)2. On the Maintain Spreadsheet Server Lists panel, select the desired segment lists to be printed and click the

Print button. The system generates a list in Notepad which may be printed.

To Print a List of All Segment Lists (excluding segment list detail)3. On the Maintain Spreadsheet Server Lists panel, right click on anywhere in the segment list grid. A popup menu

appears.

4. On the popup menu, select Print Catalog of Lists. The system generates a list in Notepad which may be printed.



5.7.6 Using Lists in a GXA Formula

A segment list may be used within the standard GXA formula, by replacing any of the segment strings with thedesired segment list name. A caret symbol (^) must be inserted prior to the segment list name in the formula for thesystem to recognize the segment list type list. An at symbol (@) must be inserted prior to the segment list name inthe formula for the system to recognize the account user field type list.


Formula Example - Using Segment List for Account Segment:=GXA("001-310-^MiscExpr-*","2005","PER","12","USD","M","Actuals")Retrieves the monetary balance for period 12 for accounts included in the MiscExpr segment list.

Formula Example - Using Segment List for User Field:=GXA("001-310-*-*"&"And"&"@UF04","2005","PER","12","USD","M","Actuals")Retrieves the monetary balance for period 12 for accounts in company 001, department 310, whichmeet the user field criteria in the UF04 user field type segment list.

Note: An account user field type segment list is used in conjunction with account segment parameters. To accomplishthis, the optional user field type segment list is added after the account segment parameters. The twoparameters must be joined by the literal "And".


5.7.7 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.



5.7.8 View or Modify List Values from within Excel

Spreadsheet Server allows a user to view from a worksheet, the values contained in a specific list. The user mayalso modify the values "on the fly". Lists may be shared among all Spreadsheet Server users, so caution should beexercised when making modifications.

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 List Accounts. The Segment List Values panel appears displaying the valuesfor the selected segment list.

3. Segment list values may be added or removed as necessary.

4. When the segment list is complete, click the Close button.


6 Refresh Calculations

6.1 Calculation Options

Multiple options are available for retrieving and calculating data within Spreadsheet Server. These are listed below andexplained further in this chapter.

1. By default, the calculation function within Excel is set to calculate automatically. Global strongly recommendssetting this value to manual so that all required spreadsheet changes or additions can be completed prior to re-calculating. This setting is found on the Calculation tab within Excel's Tools>Options menu.

2. The following options are available for calculating spreadsheets:

F2 + Enter Calculates the active cell.

F9 Calculates the active worksheet.

Shift+F9 Calculates all formula cells within any active workbooks.

Ctrl+Alt+F9 Calculates all formula cells within any active workbooks. Normally, Excelonly re-calculates cells in which parameters of the cell formula have changed.This function will re-calculate the cell regardless of status.

Refresh Calculations 34


6.2 Review/Refresh PC Cache

When spreadsheets are calculated, Spreadsheet Server stores the account balances within the PC's cachedatabase. The contents of PC cache may be viewed and refreshed as necessary.

1. From the Excel menu, select SServer>PC Cache. The PC Cache panel appears displaying the cached records.

Note: Spreadsheet Server will first look at the PC cache for account balances prior to retrieving the informationfrom the iSeries host.

2. To refresh the account balances during an Excel session, the cache records should be cleared. Click the ClearAll button to clear all records in the cache. To clear selective records, select certain records and click the ClearSelected button.

3. After the cache records have been cleared, click Trigger Excel to automatically recalculate after close checkboxto recalculate the spreadsheet once this cache window is closed.

4. To automatically trigger the PC Cache refresh, from the Excel menu select SServer>Clear PC Cache andRecalculate.

Note: This refresh may also be launched by using the shortcut key Shift+Ctrl+R -or- by selecting the SS Toolbarequivalent (see SS Toolbar).


7 Drill Down Functionality

7.1 General Grid Features

Within most of the drill down grids of Spreadsheet Server, the following features exist:

Ad Hoc Drill Down To drill down from a predefined grid to information retrieved by an ad hoc query,select the desired records on the grid. Right click and select Ad Hoc Drill Down.On the Ad Hoc Queries panel, select the query to be used for drill down and clickExecute. Contact Global Software for more information.

Copy / Paste Select the desired records within the grid. Right click and copy the records tothe windows clipboard or paste directly to a worksheet.

Re-sort Columns To re-sort a column in ascending order, simply click the column heading. To re-sort a column in descending order, hold the Shift key and click the columnheading.

Resize / Hide Columns To resize or hide a column, position the mouse at the right edge of a column,and then drag right or left to resize or hide the column

Move Columns To move a column, click on the desired column header to select the column.Click on the column header again and while pressing down the mouse, drag thecolumn right or left to the desired position.

Save Grid Layout Columns may be hidden and re-arranged per the user's preferences. If thisfeature is selected, the custom grid layout will apply to all future drill downs forthis particular grid, but only for the specific Spreadsheet Server user. Otherusers are not affected.

Restore Grid Layout Restores the grid to the installation, predefined layout. The result is notimmediate. Close the grid window and perform the drill down function again.

Ctrl + Q Quick exit from grid to return to the spreadsheet.

Drill Down Functionality 36


7.1.1 Copy / Paste Grid Records from Detail Account Balances

1. From the Detail Account Balances drill down grid, select the desired record(s):· To copy a single record -- select the desired record and right click. A popup menu appears.

Note: May use short cut Ctrl+S for copying the selected record.· To select multiple records -- press and hold the Ctrl key as records are selected on the grid rows. Right click

on the grid. A popup menu appears.Note: May use short cut Ctrl+S for copying selected records.

· To copy all records in the grid -- right click on the grid. A popup menu appears.Note: May use short cut Ctrl+G for copying all records in the grid.

Note: Selected records are denoted by a yellow background.

2. On the popup menu, select the appropriate copy / paste function to indicate whether to copy / paste onlyselected records or to copy / paste all records. The Copy of Paste Options panel appears.

3. On the Copy or Paste Options panel, select the desired option to either copy records to the standard windowsclipboard -or- to paste data directly to the target worksheet in the spreadsheet. Also specify whether or not toinclude column headings in the copy. When pasting directly to a worksheet, the following information must bespecified:· Target worksheet in which to place the data. This may enter name of an existing worksheet or if a new name

is entered, the system will create it.· Starting cell into which to place the data.

4. Click the Copy or Paste button to copy the selected data.


7.1.2 Copy / Paste Grid Records from Journal Drill Down Panels

1. From the Journals List for Accounts or Journal Entry Lines drill down grids, select the desired record(s):· To copy a single record -- select the desired record and right click. A popup menu appears.

Note: May use short cut Ctrl+S for copying the selected record.· To select multiple records -- press and hold the Ctrl key as records are selected on the grid rows. Right click

on the grid. A popup menu appears.Note: May use short cut Ctrl+S for copying selected records.

· To copy all records in the grid -- right click on the grid. A popup menu appears.Note: May use short cut Ctrl+G for copying all records in the grid.

Note: Selected records are denoted by a yellow background

2. On the popup menu, select the appropriate copy / paste function to indicate whether to copy / paste onlyselected records or to copy / paste all records. The Copy or Paste Options panel appears.

3. On the Copy or Paste Options panel, select the desired option to either copy records to the standard windowsclipboard -or- to paste data directly to the target worksheet in the spreadsheet. Also specify whether or not to



include column headings in the copy. When pasting directly to a worksheet, the following must be specified:· Target worksheet in which to place the data. This may be the name of an existing worksheet or if a new

name is entered, the system will create it.· Starting cell into which to place the data.

4. When pasting directly to a worksheet, if subtotals are desired the following information must be specified:· Select the Generate Excel Subtotals option.· Change In fields to be used for subtotal breaking points.· Subtotal function to be applied to the subtotal fields.· Subtotal Fields for which to calculate totals.

5. To clear subtotal selections, uncheck the Generate Excel Subtotals check box.

6. Click the Copy or Paste button to copy the selected data and subtotals (when selected).

Note: If more than 65,000 rows are being copied, an additional worksheet will be created in Excel.


7.1.3 Modify Grid Layouts

Most drill down grid layouts may be customized to a specific user's preference. Just like columns in an Excelworksheet, columns in the drill down grids may be hidden, resized and moved to different positions. Once the gridlayout has been customized, it can be saved as the default for that specific grid for the specific user. Other users arenot affected.

Note: Resize/Hide and Copy/Paste operations work for all drill down grids.Move, Save, and Restore operations only work on Journals List and Journal Entry drill down grids.

Resize or Hide a Grid Column:1. Position the mouse at the right edge of the column's header. The pointer changes to a horizontal double arrow.

2. Drag the pointer right or left to resize the column.

3. If the pointer is dragged all the way to the left, the column is still there but it becomes hidden.

Move a Grid Column:1. Select the column to be moved by clicking once on the column's header. This may trigger a sort on the column

but the action can be ignored. The column changes colors and is considered selected.

2. Left-click and press the mouse pointer within the header of the selected column. The pointer changes to anarrow with a column header box on its tip, and a small box at its lower right corner. A position marker consistingof two red triangles appears at the left edge of the selected column.

3. Specify the desired location of the selected column by dragging the position marker, which changes position asthe mouse pointer crosses the right edge of a column. In the example below, the Amount column is to be movedbetween the Line # and Batch # columns.

4. Complete the operation by releasing the mouse button. The selected column is immediately moved to the left ofthe position marker.



Save a Grid Layout:A grid layout may be modified and then saved as the default for subsequent drill down operations.

1. After columns have been hidden or re-arranged, right click anywhere on the grid. A pop-up menu appears.

2. On the pop-up menu, select Save Grid Layout as New Default. The customized grid layout will apply to futureddrill downs for the grid, but only for the specific user.

Restore a Grid Layout:A customized grid layout may be reset to its predefined layout. This may become necessary when the user desiresto re-customized the grid -or- in the event that the grid layout has been enhanced to include new fields or achanged display, and the user wants to re-customized the grid to include any new or changed columns.

1. Right click anywhere on the grid. A pop-up menu appears.

2. On the pop-up menu, select Restore Installation Grid Layout. The next time the drill down is performed for thespecific grid, the original grid layout will be used, exposing all columns, sizes and displays.

Copy / Paste Operations:Copy / Paste operations performed on most drill down grids process only visable columns and ignore hiddencolumns. In addition, any repositioning of columns due to customization is honored. In other words, WYSIWYG isin effect.


7.2 Drill Down to Detailed Account Balances

Spreadsheet Server allows drilling down from an amount on the spreadsheet to see what account balances make upthe value. Via Spreadsheet Server Settings various processing criteria related to drill downs may be defined; suchas whether or not zero balance accounts are displayed on the screen, and the number of drill down recordsdisplayed prior to the first warning.

1. Select a cell containing a GXA formula and right click. A popup menu appears.

2. From the popup menu, select SS Drill Down. The Detail Account Balances window appears showing thebalance for each individual account.

Note: Steps 1 and 2 may be replaced by selecting a cell and from the Excel menu selecting SServer>Drill Down.

Note: Refer to General Grid Features for features available within most drill down grids.



7.3 Drill Down to Multiple Column Account Balances

Spreadsheet Server allows drilling down from multiple cells on the same row on the spreadsheet to see whataccount balances make up the values. A maximum of 12 columns may be selected. Via Spreadsheet ServerSettings various processing criteria related to drill downs may be defined; such as whether or not zero balanceaccounts are displayed on the screen, and the number of drill down records displayed prior to the first warning.

1. Select multiple cells on the same row containing GXA formulas and right click. A popup menu appears.

2. From the popup menu, select SS Drill Down. The Detail Accounts Balances window appears showing detailaccount balances for each column. If only two columns were selected, a variance column will automatically bedisplayed.

Note: Steps 1 and 2 may be replaced by selecting multiple cells and from the Excel menu selectingSServer>Drill Down.



7.4 Drill Down to Journals per Account(s)

Spreadsheet Server allows drilling down from detail account balances to the journals which make up the accountbalances. Via Spreadsheet Server Settings various processing criteria related to drill downs may be defined, suchas the number of drill down records displayed prior to the first warning.

1. From the Detail Account Balances panel, select the desired account(s):· To select a single account -- double click the account. The Journal List for Accounts window appears

displaying journals for the selected account.· To select multiple accounts -- press and hold the Ctrl key as accounts are selected on the grid rows. Right

click on the grid. A popup menu appears. From the popup menu select Journals List for Selected Accounts.The Journal List of Accounts window appears displaying journals for all the selected accounts (grouped byaccount in period/journal date order).

· To select all accounts in the grid -- right click on the grid. A popup menu appears. From the popup menuselect Journals List for ALL Accounts. The Journal List for Accounts window appears displaying journals forall accounts (grouped by account in period/journal date order).Note: May use short cut Ctrl+A for drilling down to journals for all accounts in the grid.




7.5 Drill Down to Journal Entry Lines

Spreadsheet Server allows drilling down from journals to the journal entry lines which make up the journal. ViaSpreadsheet Server Settings various processing criteria related to drill downs may be defined, such as the number ofdrill down records displayed prior to the first warning.

1. From the Journal List for Accounts panel, select the desired journal and right click. A popup menu appears.

2. On the popup menu, select Display All Lines. The Journal Entry Lines panel appears displaying all the entry linesfor the journals.

Note: Steps 1 and 2 may be replaced by double clicking the selected journal.



7.6 Drill Down to Subsystem Detail

Spreadsheet Server allows drilling down from a journal entry line to the subsystem detail from which the entry lineoriginated. The valid database library for each subsystem file is defined in the Spreadsheet Server Configuration.Currently a subsystem drill down is available for the Accounts Payable application.

1. From the Journal List for Accounts panel, select the desired journal entry line and right click. A popup menuappears.

2. On the popup menu, select View Subsystem Detail. The appropriate subsystem panel appears.


Review Account Master 46


8 Review Account Master

8.1 List Accounts

Spreadsheet Server enables the user to generate a list of existing accounts. Filtering is available to control whichaccounts are listed.

1. From the Excel menu, select SServer>List Accounts. The Display Accounts panel appears.


2. On the Display Accounts panel, specify the name of the database library and desired filter for which to displayaccounts and click the Display button. The appropriate accounts are displayed on the Display Account panel. Ifthe database library and filter are left blank, the system will retrieve all account masters. When entering accountsegment filter values, ranges, segment lists and/or wildcards may be used.

3. To display accounts created or revised for a given date range, click the elipse button for both the From Date andTo Date fields to select the desired From and To dates, then click the Display button. When filtering by daterange, the system still utilizes filter criteria entered in the account segment fields.

Note: Double click on the S1 header (not in the field) to mask each segment, and to clear all GXU segment fieldsand From/To Date fields.

4. To sort the data in the grid by a specific column, click the column heading (ascending) or hold down the ShiftKey and click the column heading (descending).


8.2 Copy Accounts from Account List

Spreadsheet Server allows copying accounts from the List Accounts Screen into the spreadsheet.

1. From the List Accounts panel, select the desired account(s):· To copy a single account -- select the desired account and right click. A popup panel appears.

Note: May use short cut Ctrl+S for copying the selected record.· To select multiple accounts -- press and hold the Ctrl key as accounts are selected on the grid rows. Right

click on the grid. A popup menu appears.Note: May use short cut Ctrl+S for copying selected records.

· To copy all accounts in the grid -- right click on the grid. A popup menu appears.Note: May use short cut Ctrl+G for copying all accounts in the grid.

2. On the popup panel, select the appropriate copy function to indicate whether to copy only select accounts or tocopy all accounts. The List Accounts Copy or Paste Options panel appears.

3. On the List Accounts Copy or Paste Options panel, specify the following processing criteria:· Segment Control - Separate Account Segments - Indicate whether or not to parse the account strings into the

appropriate segments and columns.· Description Control - Include Account Descriptions - Indicate whether or not to include the account description

in the paste process.· Copy or Paste Control - Indicate whether to copy the selected records to the standard windows clipboard or to

paste data directly to a target worksheet in the spreadsheet. When pasting directly to a worksheet, a targetworksheet (new or existing) and starting cell in which to place the data must be specified.

4. Click the Copy or Paste button.

Review Account Master 48


8.3 View Account Balances

Spreadsheet Server enables the user to view account balances and subsequently drill down to the journals andjournal entry lines for any or all of the listed accounts. Selection criteria is available to control which accounts arelisted.

1. From the Excel menu, select SServer>View Account Balances. The View Account Balances panel appears.


2. On the View Account Balances panel, specify the various account balance criteria (i.e. account segments oruser fields, year, balance type, budget, currency, database) for which to display accounts. When enteringaccount or user field selection criteria, ranges, segment lists, and/or wildcards may be used. Click the ViewBalances button. The appropriate accounts and balances are displayed on the View Account Balances panel.

Note: Double click on the S1 header (not in the field) to mask each segment.

3. To drill down to the journals for a specific account/period, on the View Account Balances window double click inthe grid cell for the desired account/balance. The Journal List for Accounts panel appears displaying all journalsfor the selected account/period.

4. To drill down to the journal entry lines for a specific journal, on the Journal List for Accounts panel select thedesired journal and double click. The Journal Entry Lines panel appears displaying all the entry lines for the


journal.

Miscellaneous Features 50


9 Miscellaneous Features

9.1 SS Toolbar

After Spreadsheet Server is initiated as an add-in to Excel, a toolbar is added to Excel. The toolbar may be used asa convenient alternative to various SServer menu functions.


9.2 Generate Account Detail for Current Sheet

The Generate Account Detail option allows the user to create an audit trail which lists the individual accounts andbalances included in the GXA formulas of the worksheet.

1. From the Excel menu, select SServer>Generate Account Detail from Current Worksheet. The GenerateAccount Detail Options panel appears.

2. On the Generate Account Detail Options panel, specify the following processing criteria:· Source Worksheet Input - Column - Identify the column within the worksheet that contains the report line

description. This description is used as the first column in the extracted audit trail.· Source Worksheet Input - Row - Identify the first row containing a GXA formula to be used in generating the

detail. This will set the starting point for the extracted audit trail.· Target Options - Indicate whether to copy the selected records to the standard windows clipboard or to paste

data directly to a target worksheet in the spreadsheet. When pasting directly to a worksheet, a targetworksheet (new or existing) and starting cell into which to place the data must be specified.

3. Click the OK button. An audit trail showing the line description and all included accounts and their balances willbe generated.


9.3 Expand Detail Reports - Account Details

The Expand Detail option allows the user to select a single or multiple financial statement lines using Spreadsheet Serverformulas and explode the line(s) into the individual account details. The process is similar to the Generate Account Detailoption but uses a single line to explode into a designated workbook location.

This process is primarily used to launch detailed financial statements into the same template. This option can be used formultiple departments to ensure consistency for each report. Instead of creating each possible account number as differentrows in the spreadsheet, a single line can be created that contains ranges, segment lists, or wildcards. This line will thenbe exploded into the applicable individual accounts and placed into a formatted worksheet.



9.3.1 Expand a Single Source Line

1. Create a standard Spreadsheet Server report using the GXA formula. The single report line becomes the basis forthe exploded report. When the process is executed the single line will be exploded into individual account lines andplaced into the final formatted worksheet (see below).

The example below shows a report based on an account mask.· GXA formulas are created in a single line. In the example, the account string parameters are shown in cells

A1:A4. The balance parameters of the GXA (year, format, period, currency, balance type, data type) are enteredin each column heading (A7:G12). The actual GXA formulas are contained in cells A16, B16, F16, and G16.

· Standard Excel formulas are used to calculate the budget variances in cells C16 and H16.· The AcctNum designation has been inserted into cell D16. This designation will generate the account number

detail into that particular column.· The AcctDesc designation has been inserted into cell E16. This designation will generate the account description

into that particular column.

2. Create/label a cell called Target Sheet Name. In the example, this is cell B21 and it has been identified asworksheet Final Report. This cell identifies the name of the worksheet that contains the final formattedworksheet and will receive the exploded detail.

3. Create/label a cell called Source Sheet Name. In the example, this is cell B22 and it has been identified asworksheet Expand Detail Report. This cell identifies the worksheet that contains the GXA formulas that will beexploded. In this example, the same worksheet contains both the source and the GXE parameters.

4. Create/label a cell called Target Detail Starting Row. In the example, this is cell B23 and it has been identifiedas row 8. This cell identifies the first row within the target worksheet to begin inserting the exploded accountdetail.

5. Create/label a cell called Source Detail Row. In the example, this is cell B24 and it has been indentified as row


16. This identifies the row number in the source worksheet that contains the GXA formulas.

6. Create a GXE formula with the following parameters:· Target Sheet (B21)· Source Sheet (B22)· Target Detail Row (B23)· Source Detail Row (B24)

Formula Example:=GXE(B21,B22,B23,B24)

7. Format the target worksheet with headings in rows 1-6, blank rows in rows 7-9, and a totals row in row 10.Each total was set to a range of rows 7-9 (i.e. =SUM(A7:A9)). When the Expand Detail Reports function isinitiated, the total line is shifted down or up, based upon the number of data rows populated each time theexpansion is processed.

8. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports -or- select the SSToolbar equivalent (see SS Toolbar). The system will look for any GXE formulas and expand the results intothe formatted sheet (see below).


9.3.2 Expand Multiple Source Lines

1. Create a standard Spreadsheet Server report using the GXA formula. The multiple report lines become the basisfor the exploded report. When the process is executed the lines will be exploded into individual account lines andplaced into the final formatted worksheet (see below).

The example below shows a report based on an account mask.· GXA formulas are created in multiple lines. In the example, the account string parameters are shown in cells

A1:A4. The balance parameters of the GXA (year, format, period, currency, balance type, data type) are enteredin each column heading (A7:B12). The actual GXA formulas are contained in cells A17, B17, A18, B18, A19 andB19.

· Standard Excel formulas are used to calculate the budget variances in cells C17, C18 and C19.· The AcctNum designation has been inserted into cell D17, D18 and D19. This designation will generate the

account number detail into that particular column.· The AcctDesc designation has been inserted into cell E17, E18 and E19. This designation will generate the

account description into that particular column.

2. Create/label a cell called Target Sheet Name. In the example, this is cell B23 and it has been identified asworksheet Final Report. This cell identifies the name of the worksheet that contains the final formattedworksheet and will receive the exploded detail.

3. Create/label a cell called Source Sheet Name. In the example, this is cell B24 and it has been identified asworksheet Expand Detail Reports. This cell identifies the worksheet that contains the GXA formulas that will beexploded. In this example, the same worksheet contains both the source and the GXE parameters.



4. Create/label a cell called Target Detail Starting Row # or ID. In the example, this is cell B25 and it has beenidentified as GXE01. This cell identifies the unique ID value associated with the GXE. This user-defined valuewill be assigned on the target worksheet to indicate where to begin inserting the exploded account detail.

Note: The ID value must begin with alpha characters (i.e. GXE01, DEPT310, RPT310, etc.).

5. Create/label a cell called Source Detail Row. In the example, this is cell B26 and it has been indentified as row 17.This identifies the row number in the source worksheet that contains the GXA formulas.

6. Create a GXE formula with the following parameters:· Target Sheet Name (B23)· Source Sheet Name (B24)· Target Detail Starting Row # or ID (B25)· Source Detail Row (B26)

Note: In the example above, cell references are NOT used for Target Detail Starting Row # or IDs and SourceDetail Rows parameters, but instead the values are defined in the GXE formula.

Formula Example:=GXE(B23,B24,B25,B26)=GXE(B23,B24,"GXE02","18")=GXE(B23,B24,"GXE03","19")

7. Format the target worksheet with headings in rows 1-6, 13-15, and 22-24; blank rows in rows 7-9, 16-18, and25-27; and total rows in row 10, 19 and 28. Each total was set to a range of rows 7-9, 16-18 or 25-27 (i.e.=SUM(A7:A9)). When the Expand Detail Reports function is initiated, the total lines will be shifted down or up,based upon the number of data rows populated each time the expansion is processed.


8. For each GXE to be expanded on the Target Worksheet, select the inital target row (middle row of the blank rows)column AZ cell, and right click. On the popup menu, select Insert Comment. In the comment box, replace anydefault data with a less than sign, the unique Target Detail Starting ID value for the GXE, and a greater than sign.



9. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports -or- select the SS Toolbarequivalent (see SS Toolbar). The system will look for any GXE formulas and expand the results into the formattedsheet (see below).



9.3.3 Retrieve User Field Data

When using the GXE formula to expand single or multiple financial statement lines into individual account details, it ispossible to include retrieving associated user field data.

1. Create a standard Spreadsheet Server report using the GXA formula. Follow the instructions as defined inExpand a Single Source Line and/or Expand Multiple Source Lines.

2. On the source worksheet, create columns in which to place user fields, and in each column on the source lineindicate which user field to retrieve (i.e UF01, UF02, UF03 or UF04).

3. On the target worksheet, format a column for each desired user field.


4. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports -or- select the SS Toolbarequivalent (see SS Toolbar). The system will look for any GXE formulas and expand the results into the formattedsheet (see below).



9.4 Expand Detail Reports - Journal Entry Details

The Expand Detail option allows the user to create single financial statement line using Spreadsheet Serverformulas which can then be exploded into journal entry detail. The process works similarly to the Expand Detailoption for account detail using additional parameters.

1. Create a standard Spreadsheet Server report using the GXA formula. The single report line becomes the basisfor the exploed report. When the process is executed the single line will be exploded into individual journal entrylines and placed into the final formatted worksheet (see below).

The example below shows a report based on an account mask.· GXA formulas are created in a single line. In the example, the account string parameters are shown in cells

A1:A4. The balance parameters of the GXA (year, format, period, currency, balance type, data type) areentered in each column heading (A7:G12). The actual GXA formulas are contained in cells A16, B16, F16,and G16.

· Standard Excel formulas are used to calculate the budget variances in cells C16 and H16.· The AcctNum designation has been inserted into cell D16. This designation will generate the account number

detail into that particular column.· The AcctDesc designation has been inserted into cell E16. This designation will generate the account

description into that particular column.

2. Create/label cells for Target Sheet Name, Source Sheet Name, Target Detail Starting Row, and Source DetailRow (see Expand Detail Reports - Account Details).


3. Create/label a cell called JE Detail GXA. In the example, this is cell E21. This cell identifies the column thatreflects the time span for which to generate entries.

4. Create/label a cell called JE Detail Target. In the example, this is cell E22 and it has been identified asworksheet JE Detail. This cell identifies the name of the worksheet that will received the journal entry detail.

5. Create/label a cell called JE Detail Start Cell. In the example, this is cell E23 and it has been identified as cellA1. This cell identifies the starting cell location within the target worksheet to begin inserting the explodedjournal entry detail.

6. Create/label a cell called Headings. In the example, this is cell E24 and it has been set to Y. This informs thesystem to include headings in the explosion of journal entry detail.

7. Create/label a cell called Autofit. In the example, this is cell E25 and it has been set to Y. This informs thesystem to initiate the auto-fit columns function within Excel.

8. Create/label a cell called Totals. In the example, this is cell E26 and it has been set to Y. This informs thesystem to generate account subtotals as well as a grand total.

9. Create a GXE formula with the following parameters:· Target Sheet (B21)· Source Sheet (B22)· Target Detail Row (B23)· Source Detail Row (B24)· JE GXA Column (E21)· JE Target Sheet (E22)· JE Starting Cell (E23)· Headings (E24)· Autofit (E25)· Totals (E26)

Formula Example:=GXE(B21,B22,B23,B24,E21,E22,E23,E24,E25,E26)

10. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports -or- select the SSToolbar equivalent (see SS Toolbar). The system will look for any GXE formulas and explode the results intothe detail journal entry worksheet (see below).


9.5 Account Security

The Account Security Profile allows an administrator to control the account strings that a particular user mayaccess.

1. From the desktop, click Start>Program Files>Global Software SSINFINIUM>Accounts Profile. The AccountsProfile panel appears.

2. In the Account Mask field on the Accounts Profile panel, specify the account string to which the user will haveaccess and click the Add button. Single account strings, ranges, or wildcards may be entered. A maximum of20 different account strings is allowed per profile.

3. Repeat step 2 until all account masks for the account profile have been added.

4. To remove an account mask from the list, select the value in the grid and click the Remove button. To removeall account masks from the list, click the Clear All button.

5. To lock the account profile for security purposes (i.e. prevent others from making changes to the accountprofile), click the Lock button. The This Profile is Currently Unlocked dialog box appears.



6. On the This Profile is Currently Unlocked dialog box, specify a password and click the Lock button. Thispassword is required to change the account profile. It is not mandatory to lock an account profile with apassword.

7. When the account profile is complete, click the Close button.


9.6 Hide Rows with Zero Balances

The Hide Rows with Zero Balances function will analyze the active workbook and perform a row hide function for anyspreadsheet row that contains formulas in which the net result is zero for all cells. This function is very effective forstandardized reporting templates such as departmental reports. Multiple rows can be created as a template but thefunction can then hide non-used or zero rows.

To Hide Rows with Zero Balances:1. From the Excel menu, select SServer>Hide Rows with Zero Balances.

To Restore Hidden Rows:1. From the Excel menu, select SServer>Restore Hidden Rows.



9.7 Disable / Enable Spreadsheet Server Formula Calculations

Disabling calculations will effectively halt GXA and GXD formula calculations when Excel calculates spreadsheets.As a result of disabling calculations, GXA formulas will return zero values and GXD formulas will return <SS NotConnected> values. When disabled, Spreadsheet Server does not communicate to the Host.

Disabling Spreadsheet Server formula calculations can be extremely useful when:· Discontinuing calculation of a complex or long-running spreadsheet. Press Shift+Ctrl+Z to quickly disable formula

calculations.· A workbook is inadvertently opened whose calculation option is set to automatic. Press Shift+Ctrl+Z to quickly

disable formula calculations.· Creating a new worksheet or modifying an existing worksheet by adding columns and/or rows with Spreadsheet

Server formulas. From the Excel menu, select SServer>Disable Spreadsheet Server Formula Calculation.

Enabling Formula Calculations:1. From the Excel menu, select SServer>Enable Spreadsheet Server Formula Calculations. The Enable

Spreadsheet Server Formula Calculations confirmation popup panel appears.

2. Click the Yes button to clear the PC Cache and recalculate the spreadsheet.


9.8 Reset Host Server Connection

Spreadsheet Server allows the user to switch from one host server to another without having to close Excel, run theSettings program, and restart Excel.

1. From the Excel menu, select SServer>Reset Host Server Connection. The Sign On to Spreadsheet Serverdialog box appears.

2. On the Sign On dialog box identify the following information:· User ID (Spreadsheet Server signon)· Password (Spreadsheet Server password)· Default Library (override database library name specified in the Configurator)· Retrieve Account Balances (local database)· Work in Local Mode without a Connection

Note: When working without a connection, the user only has access to account balances in the localdatabase file, and as a result drill-downs to journals or list accounts will not be functional. In addition,the system continues to verify the user is a valid Spreadsheet Server user, thus the user must havethe Configurator on their hard drive.

3. Click OK.

Index- A -account 19, 48

balance 19, 48

balance drill down 41

Budget Manager balances 21

description 18

generate audit trail 51

journal drill down 43, 48

journal entry line drill down 44, 48

master balances 48

multiple columns drill down 42

security 67

segment lists 26

subsystem detail drill down 45

account master 46

copy accounts 47

drill down 48

list 46

ad hoc drill down 35

add-in 8

audit trail 51

- B -benefits and features 4

Budget Manager balances 21

building spreadsheets 14

- C -calculation options 33

disable 70

enable 70

refresh PC cache 34

components 5

configurator 5

copy accounts 47

copy grid records 35

from account balances 36

from journal panels 37

customize grid layout 35, 39

- D -disable formula calculations 70

drill down 48

account balances 48

account balances copy/paste 36

detailed account balances 41

general grid features 35

journal entry lines 44

journals 43

journals copy/paste 37

multiple column account balances 42

subsystem detail 45

- E -enable formula calculations 70

Excel add-in 8

expand detail reports 53

account detail 53

account detail (multiple source lines) 57

account detail (single source line) 54

account detail (user fields) 62

journal entry detail 64

- F -features and benefits 4

formula 70

disable calculations 70

enable calculations 70

GXA for account values 19

GXA for Budget Manager 21

GXD 18

GXE 53, 54, 57, 62, 64

GXU 22

SSLDESC 31

GXA using segment lists 30

- G -generate account detail 51

grid features 35

ad hoc drill down 35

copy records 35

copy records from account balances 36

Index 72


grid features 35

copy records from journal panels 37

customize layout 35, 39

move/resize columns 35, 39

re-sort columns 35

GXA 19

for account values 19

for Budget Manager 21

list accounts 23

using segment lists 30

GXD 18

GXE 53, 54, 57, 62, 64

GXU 22

list accounts 23

- H -hide zero balance rows 69

host server 71

reset connection 71

- I -installation 6

Introduction 3

- J -journal drill down 43

copy/paste 37

journal entry line drill down 44

- L -list accounts 23

account master 46

copy accounts 47

in GXA formula 23

in GXU formula 23

load local PC database 11

local PC database 11

- M -move/resize grid columns 35, 39

- O -optional components 5

- P -PC cache 34

print segment lists 29

- R -refresh PC cache 34

required components 5

reset host server connection 71

re-sort grid columns 35

restore hidden rows 69

rows 69

hide 69

restore 69

- S -security account profile 67

segment lists 24

create 24

description 31

enter descriptions for user fields 28

enter values for segment lists 26

enter values for user field lists 27

print 29

using in GXA 30

view values 32

settings 9

SS toolbar 50

SSLDESC 31

start Spreadsheet Server 8

reset host server connection 71

subsystem detail drill down 45

- T -template wizard 15

toolbar 50



- U -uninstall 7

user fields 28

descriptions 28

formula 22

GXE 62

segment lists 27

user settings (see settings) 9

- V -view account balances 48

view accounts (see list accounts) 23, 46

view segment list values 32

- W -wizard template 15

- Z -zero balance rows 69

hide 69

restore 69

Index 74


spreadsheet server for use with infinium - release … · spreadsheet server for use with...

Documents