spreadsheet server for use with global software · spreadsheet server for use with global ......

Spreadsheet Server for use with Global Software

User Manual

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

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


Global Software Spreadsheet Server converts familiar spreadsheetsoftware, such as Microsoft® Excel, into tightly integrated analyticaltools for financial systems. Financial users can leverage thestrength of spreadsheets with seamless real-time integration tofinancial information. Spreadsheet Server eliminates the re-keyingor downloading of data into spreadsheets and makes them anintegral 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, or mechanical, including photocopying, recording, taping, or information storage and retrievalsystems - without the written permission of the publisher.

Microsoft, Excel, Windows, Office, Access and SQL Server are all registered trademarks of MicrosoftCorporation. IBM, DB2, iSeries and AS/400 are all trademarks or registered trademarks of InternationalBusiness Machines Corporation in the United States and/or other countries. Trade names referenced are theservice marks, trademarks, or registered trademarks of their respective manufacturers in the United Statesand/or other countries. Global Software, Inc. is not associated or affiliated in any manner with the respectiveowners of the foregoing trademarks, trade names or service marks unless expressly stated otherwise. Therespective owners of the foregoing trademarks, trade names or service marks have not endorsed, certified orapproved any of Global Software's products for use in connection with their respective products.

While every precaution has been taken in the preparation of this document, the publisher and the authorassume no responsibility for errors or omissions, or for damages resulting from the use of informationcontained in this document or from the use of programs and source code that may accompany it. In no eventshall the publisher and the author be liable for any loss of profit or any other commercial damage caused oralleged to have been caused directly or indirectly by this document.

Printed: June 2008

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) 14

Part V Building Spreadsheets 17

................................................................................................................................... 181 Worksheet Wizard

................................................................................................................................... 202 GXD Formula

................................................................................................................................... 213 GXA Formula for Account Values

................................................................................................................................... 234 GXA Formula for Value Lists

................................................................................................................................... 245 GXA Formula for Budget Manager Data

................................................................................................................................... 256 List Accounts for a GXA Formula

................................................................................................................................... 267 Spreadsheet Server Segment Lists

.......................................................................................................................................................... 26Create Segment Lists

.......................................................................................................................................................... 27Enter Values for Segment Lists

.......................................................................................................................................................... 28Using Segment Lists in a GXA Formula

.......................................................................................................................................................... 29SSLDESC Formula

.......................................................................................................................................................... 30View or Modify Segment List Values from within Excel

Part VI Refresh Calculations 31

................................................................................................................................... 311 Calculation Options

................................................................................................................................... 322 Review/Refresh PC Cache

Part VII Drill Down Functionality 33

................................................................................................................................... 331 General Grid Features

.......................................................................................................................................................... 34Grid Layouts

.......................................................................................................................................................... 37Copy / Paste

................................................................................................................................... 382 Drill Down to Detailed Account Balances

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

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

Spreadsheet Server for use with Global SoftwareI

© 2008 ... Global Software, Inc.

................................................................................................................................... 415 Drill Down to Journal Entry Lines

................................................................................................................................... 426 Drill Down to Subsystem Detail

Part VIII Review Account Master 43

................................................................................................................................... 431 List Accounts

................................................................................................................................... 442 Copy Accounts from Account List

Part IX Miscellaneous Features 45

................................................................................................................................... 451 Generate Account Detail for Current Sheet

................................................................................................................................... 472 Expand Detail Reports - Account Details

................................................................................................................................... 493 Expand Detail Reports - Journal Details

................................................................................................................................... 514 Account Security

................................................................................................................................... 535 Hide Rows with Zero Balances

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

Index 55

IIContents

II© 2008 ... Global Software, Inc.

Spreadsheet Server for use with Global Software3

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 spreadsheetswith seamless real-time integration to financial information. Spreadsheet Server eliminates the re-keying ordownloading of data into spreadsheets and makes those spreadsheets an integral part of financialapplications.

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

Leverage Spreadsheet SkillsWhile maintaining the full functionality of the spreadsheet application, Spreadsheet Server allows the user tomix General Ledger and non General Ledger data in a single worksheet. The combination of powerfulspreadsheet functions (charting, text formatting, and sorting) and real-time financial information provides thebasis for building an income statement, balance sheet, and other financial statements.

Spreadsheet Server Overview 4

Global Software, Inc.

1.2 Features & Benefits

Features· Retrieve real-time balances based on ledger specific formats and time periods (Period, Quarter, Year-

to-Date, etc.)· Retrieve balances using ranges, wildcards, or segment lists· Retrieve account descriptions· 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

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. By default,Spreadsheet Server expects to communicate to the iSeries host via the OLE DB Provider, thusSpreadsheet Server settings must be modified to connect to the host via ODBC.

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 willhave Spreadsheet 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 website.

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 generallytypical for 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 beperformed in order 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 Spreadsheet Server, 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 Spreadsheet Server, 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\GlobalSoftware Spreadsheet Server 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 Spreadsheet Server, 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 Spreadsheet Server, and click OK.

3. From the Excel menu, select SServer-Not Started>Start Spreadsheet Server. The Sign On toSpreadsheet Server dialog box appears.

Note: If user settings are set to automatically start Spreadsheet Server during Excel startup, then step 3not applicable.

4. On the Sign On dialog box identify the following information:· Host Name or IP Address (iSeries system name or IP address)· Database Library Name (name of Global data library)· User ID (standard iSeries sign on)· Password (standard iSeries password)· Retrieve Account Balances (local database)

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 SSGLOBAL>Spreadsheet Server Settings. TheSpreadsheet Server Settings panel appears.

Getting Started 10


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

Field Description

Base Currency Identifies the base currency for the ledger database.

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

Include Zero Balance Accounts Indicates to include zero balance accounts in the account balances drilldown window.

Processing More Than 14Period Balances

Indicates that ledger balances are stored in more than 14 periods for ayear.

Use Pipe Character | asAccount Segment Delimiter

Indicates to use the pipe character ( | ) as the account segmentdelimiter in GX formulas. If not selected, a dash ( - ) is used as theaccount segment delimiter in GX formulas.

Using Accounts SegmentsLogical

Indicates that the logical GLLBALSS has been created overGLPADATA file. The logical breaks the account number stored in thedata file into account structure segments.

Spreadsheet Server Startup Select the appropriate option to indicate to sign on to SpreadsheetServer automatically when Excel is started or to manually startSpreadsheet Server each time Excel is started.

iSeries Host ConnectionProvider

Select the appropriate iSeries connection provider. The selected IBMData Access component must be installed on the PC prior to usingSpreadsheet Server.

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


3. Use the table to enter data on the Quarter Periods tab of the Spreadsheet Server Settings panel.

Field Description

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

Getting Started 12


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

Field Description

PC Segment Lists Location Defines the location of the Spreadsheet Server Segment Listsdatabase. This may be a local or network drive.

PC Local Database Location Identifies the location for the Local.MDB database. This location is alsoupdated from the Load Local Database panel.


5. Use the table to enter data on the Subsystem Libs tab of the Spreadsheet Server Settings panel.

Field Description

Global Accounts PayableChase

Identifies the name of the database library to be used for drilling downto entries for the Accounts Payable subsystem.

Global Accounts ReceivableChase

Identifies the name of the database library to be used for drilling downto entries for the Accounts Receivable subsystem.

Global Accounts PayableChase Web Link URL

Identifies the name of the custom hyperlink to be used for displayingthe corresponding image associated with an entry on the AccountsPayable drilldown panel. Contact Global for more information.

Friedman JV Chase Identifies the name of the database library to be used for drilling downto entries for the Friedman subsystem.

6. Click Save to write the record.

Load Local PC Database (optional) 14


4 Load Local PC Database (optional)

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

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

2. On the Sign On Dialog box, specify the user ID and password, 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 The topportion identifies the location for the files. Click the Browse button and select a location for the localdatabase files. If a previous download has been performed to this location, the bottom portion of the panelwill display a list of the existing files.


4. Ledger Selections: Balances for dataset types that are to be loaded are selected from the grid in themiddle of the panel. The grid must first be filled with selections from the host. This is accomplished byclicking the Fill Selection Grids button. Click the desired datasets to be downloaded. Hold down the Ctrlkey and click on the lines of the grid to make multiple selections.

Execution of the load process can be performed immediately (Load Now tab) or may be deferred toexecute unattended 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 programwill query the host based upon the selections and load the balances to the appropriate databases in theLocal location. Statuses are displayed during the load process.

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

Load Local PC Database (optional) 16


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 thescheduled date and time is reached, the program will reconnect to the host (in case an IPL or reboot ofthe server has transpired) and initiate the load process. See the Load Now section for an explanation ofthe load events.


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 and

GXA Formula for Budget Manager Data)· SSLDESC - Returns a segment list description (Refer to SSLDESC Formula)

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

Building Spreadsheets 18


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

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.

3. Use the wizard panel to specify the default row and column data to be used by the GXA formula.

4. The Empty Sheet or Non-Empty Sheet radio button is selected based upon whether using a new or existingworksheet. Verify the proper option is selected.

5. Use the scroll bars to adjust the Target Cell location.

6. Indicate whether the Division, Department, Prime, Sub, and Remainder segments will be part of the columnor row parameters in the GXA formula. In this example, the Division segment will be column parameters.

7. Enter valid data for Division, Department, Prime, Sub, and Remainder. Valid data can include singlevalues, masks, or ranges.

8. Enter valid data for the Dataset, Format, Period, and Base Currency parameters.

9. Click the Modify Current Worksheet button. Columns and rows used for the GXA parameters are insertedinto the worksheet and a GXA formula is generated in cell G11.



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-310-7260-005")Retrieves the account description for the account string 001-310-7260-005 (CO-DEPT-PRIME-SUB).


5.3 GXA Formula for Account Values

Use the GXA formula to retrieve an account balance for a particular dataset and currency, in a specificformat, for an individual period.

Syntax:=GXA("Account String","Dataset","Format","Period","Currency")

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

Single Account String 001-310-7260-005Retrieves a single account 001-310-7260-005.

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 thatbegins 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 to2000.

DatasetIdentifies the Global ledger dataset name, such as CYACT, CYBUD, etc.

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

OBAL Opening balance at the beginning of the year and all activity up to period specified.

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.

Formula Examples:=GXA("001-310-7260-005","CYACT","PER","12","USD")Retrieves the monetary balance for period 12 of CYACT for a single account string.

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

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


5.4 GXA Formula for Value Lists

In a GXA formula when retrieving account balances, lists of values may be used for any of theaccount segments. The list of values for an account segment is identified by brackets [ ] andeach value in the list is delimited by a comma. Single values, ranges, and masks can be used.A slash in front of a value is the equivalent of an "exclude".

Syntax:=GXA("Account String","Dataset","Format","Period","Currency")

Parameters:Account String

The account string represents the account segments delimited by a dash. Each segment of\the account string is required.

Note: It is most common to use cell references within GXA formulas to identify the individual formula parameter.

Formula Example (using value lists):=GXA("001-000-[1000.2999,/1040]"CYACT","YTD","12","USD")

Retrieves the balance(s) from the CYACT dataset for USD currency for period 6 with 001 in thecompany, 000 in the department, any prime that falls within a range of 1000 to 2999, but excluding prime 1040.



5.5 GXA Formula for Budget Manager Data

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

Syntax:=GXA("Account String","Dataset","Format","Period","Currency","B")

Parameters:Budget ParameterEnter the literal "B". The formula will look for budget amounts in PC Cache for the account(s) and datasetspecified.

Formula Example:=GXA("001-310-7260-005","NYBUD","PER","1","USD","B") Retrieves the budget data from PC Cache for period 1 of NYBUD for a single account.

Note: It is most common to use cell references within GXA formulas to identify the individual formula parameters. Refer to the example under the GXA Formula for Account Values.


5.6 List Accounts for a GXA Formula

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

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

2. From the popup menu, select SS List Accounts. The List 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 being displayed on the List Accounts window, alter the value in theAccount Filter field and click on the List Accounts button.

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



5.7 Spreadsheet Server Segment Lists

5.7.1 Create Segment Lists

Spreadsheet Server Segment Lists are used to create a hierarchy of individual segments that can be used in asingle GXA formula.

1. From the desktop, click Start>Program Files>Global Software SSGLOBAL>Maintain Segment Lists. TheMaintain Segment Lists panel appears.

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

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

Field Description

SL Name Identify a unique name for the list. This name will be usedin the GXA formula.

Description Identify a description for the list.

4. Click Save to create the record. The Segment List Values panel appears.


5.7.2 Enter Values for Segment Lists

When adding or maintaining values for a segment list, the Segment List Values panel is used to enter values forthe segment list.

1. In the Value field enter the segment values to include for the criteria, select the appropriate radiobutton toindicate whether to include or exclude the value, and click the Add button. Single segment values, ranges,or wildcards 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. When the list is complete, click the Close button.



5.7.3 Using Segment 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 formulafor the system to recognize the segment list.

Formula Examples:=GXA("001-310-^OH-*","CYACT","PER","12","USD")Retrieves the monetary balance for period 12 of CYACT for accounts included in the OH segment list.


5.7.4 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("^OH")Retrieves the segment list description for the OH segment list.



5.7.5 View or Modify Segment List Values from within Excel

Spreadsheet Server allows a user to view from a worksheet, the values contained in a specific segment list.The user may also modify the values "on the fly". Segment lists can be shared among all Spreadsheet Serverusers, so caution should be exercised 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 thevalues for 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.

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

2. The following options are available for calculating spreadsheets:

F2+Enter Calculates the active cell.

F9 Calculates all worksheets in all open workbooks.

Shift+F9 Calculates the active worksheet.

Ctrl+Alt+F9 Calculates all worksheets in all open workbooks, regardless of whether they havechanged since the last calculation.

Refresh Calculations 32


6.2 Review/Refresh PC Cache

When spreadsheets are calculated, Spreadsheet Server stores the account balances in the PC's cachedatabase. Spreadsheet Server will first look at the PC cache for account balances prior to retrieving theinformation from the host. As a result, it may be necessary to clear and recalculate the PC cache file whenany of the following occur:· Data has changed on the host system since the prior calculation.· A segment list has changed since the prior calculation.· The accounts profile has changed since the prior calculation.

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

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

2. To refresh the account balances during an Excel session, the cache records should be cleared. Click theClear All button to clear all records in the cache. To clear selective records, select certain records andclick the Clear Selected button.

3. After the cache records have been cleared, click Trigger Excel to automatically recalculate after closecheckbox to 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.


7 Drill Down Functionality

7.1 General Grid Features

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

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

Copy/Paste Select the desired records within the grid. Right click and copy therecords with or without the column headings to the windows clipboardor paste directly to a worksheet.

Print Records On selected panels, there is an option to print the grid list in astandard notepad/wordpad print.

Save Grid Layout Columns may be hidden and re-arranged per the user's preferences.If this feature is selected, the custom grid layout will apply to all futuredrill downs for this particular grid, but only for the specific SpreadsheetServer user. Other users are not affected.

Restore Grid Layout Restores the grid to the installation, predefined layout. The result isnot immediate. Close the grid window and perform the drill downfunction again.

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

Drill Down Functionality 34


7.1.1 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 may be saved as the default for that specific grid for the specific user. Other usersare not affected.

Resize or hide a grid column:Position the mouse at the right edge of the column's header. The pointer changes to a horizontal double arrow,which allows the user to drag right or left to resize the column.

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

Move a grid column:Select the column to be moved by clicking once on the column's header. This may trigger a sort on the column butthe action can be ignored. In any case, the column will change colors and will be considered selected. Onlyselected columns can be moved.

Left-click and press the mouse pointer within the header of the selected column. The pointer will change to an arrowwith a column header box on its tip, a small box at its lower right corner. A position marker consisting of two redtriangles will appear at the left edge of the column being pointed to and highlighted.


Specify the desired location of the selected column by dragging the position marker, which changes position as themouse pointer crosses the right edge of a column. In the example below, the Amount column is to be movedbetween the Trans Date and JV Number columns.

Complete the operation by releasing the mouse button. The selected column is immediately moved to the left of theposition marker.

Save a grid layout:If a user wishes to have a customized grid as the new default for subsequent drill down operations, the layout mustbe saved as a new default. Right-mouse click on any line in the grid. A pop-up menu appears. Select the menuitem: Save Grid Layout as New Default. When the next drill down is performed for a specific grid, the new defaultlayout will be displayed.

Restore a grid layout:There may be occasions when the original grid layout needs to be restored. For example, the customization process



may need to be redone starting with the installed default layout. In addition, the Spreadsheet Server product may beenhanced with new or changed grids, in which case the customized grid layouts would need to be recreated toinclude or excluded any new columns.

To restore the layout for the grid, right-mouse click on any line in the grid and select Restore Installation Grid Layoutfrom the pop-up menu. When the next drill down is performed for the specific grid, the original layout will be used,exposing all columns, sizes and displays.

Copy / Paste operations:Copy / Paste operations performed on most drill down grids will only process those columns that are visible. Hiddencolumns will be ignored. In addition, any repositioning of columns due to customization will be honored. In otherwords, WYSIWYG is in effect.


7.1.2 Copy / Paste

1. From the drill down grid, select the desired record(s):· To copy a single record -- 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 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.

2. On the popup panel, select the appropriate copy function to indicate whether to copy only selected recordsor to copy all records. The Copy or Export Options panel appears.

3. On the Copy or Export Options panel, select the desired option to either copy records to the standardwindows clipboard -or- to export data directly to the target worksheet in the spreadsheet. Also specifywhether or not to include column headings in the copy. When exporting directly to a worksheet, thefollowing information must be specified:· Target worksheet (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 Export button to copy the selected data.



7.2 Drill Down to Detailed Account Balances

Spreadsheet Server allows drilling down from an amount on the spreadsheet to see what account balancesmake up the value. Via Spreadsheet Server Settings various processing criteria related to drill downs maybe defined; such as whether or not zero balance accounts are displayed on the screen, and the number ofdrill down records displayed 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 the balance for each individual account.

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

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

4. To display a subtotal of various accounts on the drill down panel, select the desired accounts and right click. Apop-up panel appears. On the pop-up panel, select Total Amount of Selected Accounts. A window appearsdisplaying the subtotal of the selected accounts.


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 seewhat account balances make up the values. A maximum of 12 columns may be selected. ViaSpreadsheet Server Settings various processing criteria related to drill downs may be defined; such aswhether or not zero balance accounts are displayed on the screen, and the number of drill down recordsdisplayed prior to the first warning.

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

2. From the popup menu, select SS Drill Down. The Detail Accounts Balances window appears showingdetail account balances for each column. If only two columns were selected, a variance column willautomatically be displayed.

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

3. To sort the data in the grid by a specific column, click the column heading (ascending) -or- hold down the Shift key and click the column heading (descending).



7.4 Drill Down to Journals per Account(s)

Spreadsheet Server allows drilling down from detail account balances to the journals which make up theaccount balances. Via Spreadsheet Server Settings various processing criteria related to drill downs maybe defined, such as 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 forSelected Accounts. The Journal List of Accounts window appears displaying journals for all theselected accounts (grouped by account in period/journal date order).

· To select all accounts in the grid -- right click on the grid. A popup menu appears. From the popupmenu select Journals List for ALL Accounts. The Journal List for Accounts window appearsdisplaying journals for all 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.

2. To sort the data in the grid by a specific column, click the column heading (ascending) -or- hold downthe Shift key and click the column heading (descending).


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.Via Spreadsheet Server Settings various processing criteria related to drill downs may be defined, such asthe number of drill 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 of Selected Journal. The Journal Entry - All Lines panel appears displaying all the entry lines for the journals.

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

3. To sort the data in the grid by a specific column, click the column heading (ascending) -or- hold downthe Shift key and click the column heading (descending).



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. Use the Spreadsheet Server Settings function to define the valid database library for each subsystem.Currently subsystem drill down is available for Accounts Payable.

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

2. On the popup menu, select Display Subsystem for Select Journal. The appropriate subsystem panel appears.


8 Review Account Master

8.1 List Accounts

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

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

2. On the List Accounts panel, specify the desired filter for which to display accounts and click the ListAccounts button. The appropriate accounts are displayed on the List Accounts panel. If the filter is leftblank, the system will retrieve all account masters. When using a filter, a valid account string combinationshould be used. In addition, the filter can include ranges, segment lists, and/or wildcards.

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

Review Account Master 44


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+A for copying all accounts in the grid.

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

3. On the List Accounts Copy 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.· Indicate whether to include the first account segment.· Indicated whether to include account descriptions.

4. Click the OK button.


9 Miscellaneous Features

9.1 Generate Account Detail for Current Sheet

The Generate Account Detail option allows the user to create an audit trail which lists the individual accountsand balances 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 the 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, atarget worksheet (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 balanceswill be generated.

Miscellaneous Features 46



9.2 Expand Detail Reports - Account Details

The Expand Detail option allows the user to select a single financial statement line using Spreadsheet Serverformulas and explode the line into the individual account details. The process is similar to the Generate AccountDetail option 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 canbe used for multiple departments to ensure consistency for each report. Instead of creating each possibleaccount number as different rows in the spreadsheet, a single line can be created that contains ranges, segmentlists, or wildcards. This line will then be exploded into the applicable individual accounts and placed into aformatted worksheet.

1. Create a standard Spreadsheet Server report using the GXA formula. The single report line becomes thebasis for the exploded report. When the process is executed the single line will be exploded into individualaccount lines 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 (dataset, format, period, currency) are entered in eachcolumn heading (A6:G9). The actual GXA formulas are contained in cells A13, B13, F13, and G13.

· Standard Excel formulas are used to calculate the budget variances in cells C13 and H13.· The AcctNum designation has been inserted into cell D13. This designation will generate the account

number detail into that particular column.· The AcctDesc designation has been inserted into cell E13. 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 B16 and it has been identified asworksheet GXE Results. 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 B17 and it has been identified asworksheet GXE Formula. 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 B18 and it has been



identified as row 8. This cell identifies the first row within the target worksheet to begin inserting theexploded account detail.

5. Create/label a cell called Source Detail Row. In the example, this is cell B19 and it has been identified asrow 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 (B16)· Source Sheet (B17)· Target Detail Row (B18)· Source Detail Row (B19)

Formula Example:=GXE(B16,B17,B18,B19)

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. The system willlook for GXE formulas and expand the results into the formatted sheet (see below).


9.3 Expand Detail Reports - Journal Details

The Expand Detail option allows the user to create single financial statement line usingSpreadsheet Server formulas which can then be exploded into journal entry detail. Theprocess works similarly to the Expand Detail option for account detail using additionalparameters.

1. Create a standard Spreadsheet Server report using the GXA formula. The single reportline becomes the basis for th exploded report. When the process is executed the singleline will be exploded into individual journal entry lines and placed into the final formattedworksheet (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 (dataset,format, period, currency) are entered in each column heading (A6:G9). The actualGXA formulas are contained in cells A13, B13, F13, and G13.

· Standard Excel formulas are used to calculate the budget variances in cells C13 andH13.

· The AcctNum designation has been inserted into cell D13. This designation willgenerate the account number detail into that particular column.

· The AcctDesc designation has been inserted into cell E13. This designation willgenerate the account description into that particular column.

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

3. Create/label a cell called JE detail GXA. In the example, this is cell E16. This cellidentifies the column that reflects the time span for which to generate entries.

4. Create/label a cell called JE Detail Target. In the example, this is cell E17 and it hasbeen identified as worksheet JE Detail. This cell identifies the name of the worksheetthat will received the journal entry detail.

5. Create/label a cell called JE Detail Target Starting Cell. In the example, this is cell E18



and it has been identified as cell A1. This cell identifies the starting cell location withinthe target worksheet to begin inserting the exploded journal entry detail.

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

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

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

9. Create a GXE formula with the following parameters:· Target Sheet (B16)· Source Sheet (B17)· Target Detail Row (B18)· Source Detail Row (B19)· JE GXA Column (E16)· JE Target Sheet (E17)· JE Starting Cell (E18)· Headings (E19)· Autofit (E20)· Totals (E21)

Formula Example:=GXE(B16,B17,B18,B19,E16,E17,E18,E19,E20,E21)

10. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports.The system will look for any GXE formulas and explode the results into the detail journalentry worksheet (see below).


9.4 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 SSGLOBAL>Accounts Profile. TheAccounts Profile panel appears.

2. In the Account Mask field on the Accounts Profile panel, specify the account string to which the user willhave access and click the Add button. Single account strings, ranges, or wildcards may be entered. Amaximum of 20 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. Toremove all 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.5 Hide Rows with Zero Balances

The Hide Rows with Zero Balances function will analyze the active workbook and perform a row hide functionfor any spreadsheet row that contains formulas in which the net result is zero for all cells. This function isvery effective for standardized reporting templates such as departmental reports. Multiple rows can becreated as a template but the function 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.6 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.

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.

Index- A -account 20

balance 21

balance drill down 38

Budget Manager balances 24

description 20

generate audit trail 45

journal drill down 40

journal entry line drill down 41

multiple columns drill down 39

security 51

segment lists 27

subsystem detail drill down 42

account master 43

copy accounts 44

list 43

add-in 8

audit trail 45

- B -benefits and features 4

Budget Manager balances 24

- C -calculation options 31

disable 54

enable 54

GXA for account values 21

GXA for Budget Manager 24

GXA using segment lists 28

GXD 20

refresh PC cache 32

components 5

copy accounts 44

copy grid records 33, 37

- D -database 14

local PC 14

disable formula calculations 54

drill down 38

detailed account balances 38

general grid features 33

journal entry lines 41

journals 40

multiple column account balances 39

subsystem detail 42

- E -enable formula calculations 54

Excel add-in 8

- F -features and benefits 4

formula 54

disable calculations 54

enable calculations 54

GXA for account values 21

GXA for Budget Manager 24

GXD 20

SSLDESC 29

GXA using segment lists 28

- G -generate account detail 45

grid features 33

copy records 33, 37

print records 33

re-sort columns 33

restore grid layout 33, 34

save grid layout 33, 34

grid layouts 34

GXA 21

for account values 21

for Budget Manager 24

list accounts 25

using segment lists 28

GXD 20



- H -hide zero balance rows 53

- I -installation 6

introduction 3

- J -journal drill down 40

journal entry line drill down 41

- L -list accounts 25

in GXA formula 25

account master 43

copy accounts 44

load local PC database 14

local PC database 14

- P -PC cache 32

print grid records 33

- R -refresh PC cache 32

required components 5

re-sort grid columns 33

restore hidden rows 53

rows 53

hide 53

restore 53

- S -security account profile 51

segment lists 26

create 26

enter values for segment lists 27

retrieve description 29

using in GXA 28

view values 30

settings 9

SSLDESC 29

start Spreadsheet Server 8

subsystem detail drill down 42

- U -uninstall 7

user settings (see settings) 9

- V -view accounts (see list accounts) 25, 43

view segment list values 30

- W -worksheet wizard 18

- Z -zero balance rows 53

hide 53

restore 53

Index 56


spreadsheet server for use with global software · spreadsheet server for use with global ......

Documents