Spreadsheet Server for use with Global Software User Manual

(1)

Spreadsheet Server

for use with Global Software

User Manual

Corporate Headquarters

3200 Atlantic Avenue

Raleigh, NC 27604 USA

+1.919.872.7800

www.glbsoft.com

EMEA Headquarters

500 Chiswick High Road

London, W4 5RG UK

+44 (0) 20 8956 2213

(2)

Spreadsheet Server for use with Global Software

Global Software Spreadsheet Server converts familiar spreadsheet software, such as Microsoft® Excel, into tightly integrated analytical tools for financial systems. Financial users can leverage the strength of spreadsheets with seamless real-time integration to financial information. Spreadsheet Server eliminates the re-keying or downloading of data into spreadsheets and makes them an integral part of the financial application.

(3)

Spreadsheet Server for use with Global Software

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 retrieval systems - without the written permission of the publisher.

Microsoft, Excel, Windows, Office, Access and SQL Server are all registered trademarks of Microsoft Corporation. IBM, DB2, iSeries and AS/400 are all trademarks or registered trademarks of International Business Machines Corporation in the United States and/or other countries. Trade names referenced are the service marks, trademarks, or registered trademarks of their respective manufacturers in the United States and/or other countries. Global Software, Inc. is not associated or affiliated in any manner with the respective owners of the foregoing trademarks, trade names or service marks unless expressly stated otherwise. The respective owners of the foregoing trademarks, trade names or service marks have not endorsed, certified or approved 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 author assume no responsibility for errors or omissions, or for damages resulting from the use of information contained in this document or from the use of programs and source code that may accompany it. In no event shall the publisher and the author be liable for any loss of profit or any other commercial damage caused or alleged to have been caused directly or indirectly by this document.

(4)

Part I Spreadsheet Server Overview

3

... 3 1 Introducing Spreadsheet Server

... 4 2 Features & Benefits

Part II Spreadsheet Server Installation

5

... 5 1 Additional Components

... 6 2 Spreadsheet Server Installation

... 7 3 Uninstall Process

Part III Getting Started

8

... 8 1 Initiate Excel Add-In

... 9 2 User Settings

Part IV Load Local PC Database (optional)

14 Part V Building Spreadsheets

17

... 18 1 Worksheet Wizard

... 20 2 GXD Formula

... 21 3 GXA Formula for Account Values

... 23 4 GXA Formula for Value Lists

... 24 5 GXA Formula for Budget Manager Data

... 25 6 List Accounts for a GXA Formula

... 26 7 Spreadsheet Server Segment Lists

... 26 Create Segment Lists

... 27 Enter Values for Segment Lists

... 28 Using Segment Lists in a GXA Formula

... 29 SSLDESC Formula

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

Part VI Refresh Calculations

31

... 31 1 Calculation Options

... 32 2 Review/Refresh PC Cache

Part VII Drill Down Functionality

33

... 33 1 General Grid Features

... 34 Grid Layouts

... 37 Copy / Paste

... 38 2 Drill Down to Detailed Account Balances

... 39 3 Drill Down to Multiple Column Account Balances

... 40 4 Drill Down to Journals per Account(s)

Spreadsheet Server for use with Global Software I

(5)

... 41 5 Drill Down to Journal Entry Lines

... 42 6 Drill Down to Subsystem Detail

Part VIII Review Account Master

43

... 43 1 List Accounts

... 44 2 Copy Accounts from Account List

Part IX Miscellaneous Features

45

... 45 1 Generate Account Detail for Current Sheet

... 47 2 Expand Detail Reports - Account Details

... 49 3 Expand Detail Reports - Journal Details

... 51 4 Account Security

... 53 5 Hide Rows with Zero Balances

... 54 6 Disable / Enable Spreadsheet Server Formula Calculations

Index

55

II Contents

II

(6)

Spreadsheet Server for use with Global Software 3

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 tightly integrated analytical tool for financial systems. Financial users can leverage the strength of spreadsheets with seamless real-time integration to financial information. Spreadsheet Server eliminates the re-keying or downloading of data into spreadsheets and makes those spreadsheets an integral part of financial

applications. Ease of Use

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

Leverage Spreadsheet Skills

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

(7)

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

(8)

2 Spreadsheet Server Installation

2.1 Additional Components

OPTIONAL COMPONENTS

Additional components may be required based upon the type of database being accessed. Contact your IT department to determine what is required to be installed.

iSeries™ DB2® Database

1. 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, thus Spreadsheet 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 Access components is installed. See the screen below to verify the component is installed.

Microsoft® SQL Server™ Database

The Microsoft SQL Server OLE DB Provider must be loaded on the PC which will have Spreadsheet Server installed.

Oracle® Database

The Oracle client software must be loaded and a host connection must be configured on the PC which will have Spreadsheet Server installed.

(9)

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 typical for Windows® 9X / Office® 97 configurations.

(10)

2.3 Uninstall Process

It is not necessary to uninstall Spreadsheet Server prior to installing new versions. An uninstall should only be performed 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\Global Software Spreadsheet Server folder if installation defaults were taken.

(11)

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 to Spreadsheet Server dialog box appears.

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

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

(12)

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

(13)

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 drill down window.

Processing More Than 14 Period Balances

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

Use Pipe Character | as Account Segment Delimiter

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

Using Accounts Segments Logical

Indicates that the logical GLLBALSS has been created over

GLPADATA file. The logical breaks the account number stored in the data file into account structure segments.

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

iSeries Host Connection Provider

Select the appropriate iSeries connection provider. The selected IBM Data Access component must be installed on the PC prior to using Spreadsheet Server.

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

(14)

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

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

(15)

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

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

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

(16)

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

Global Accounts Payable Chase

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

Global Accounts Receivable Chase

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

Global Accounts Payable Chase Web Link URL

Identifies the name of the custom hyperlink to be used for displaying the corresponding image associated with an entry on the Accounts Payable drilldown panel. Contact Global for more information. Friedman JV Chase Identifies the name of the database library to be used for drilling down

to entries for the Friedman subsystem.

(17)

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 Server reports once the books have been closed and the analytical tasks have been performed in real time. The time spent loading the local databases is greatly offset by the dramatically reduced spreadsheet calculation times.

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 Local Databases 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 top portion identifies the location for the files. Click the Browse button and select a location for the local database files. If a previous download has been performed to this location, the bottom portion of the panel will display a list of the existing files.

(18)

4. Ledger Selections: Balances for dataset types that are to be loaded are selected from the grid in the middle of the panel. The grid must first be filled with selections from the host. This is accomplished by clicking the Fill Selection Grids button. Click the desired datasets to be downloaded. Hold down the Ctrl key 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 to execute 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 program will query the 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.

(19)

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

(20)

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)

(21)

Building Spreadsheets 18

5.1 Worksheet Wizard

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

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

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

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 existing worksheet. 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 column or 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 single

values, 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 inserted into the worksheet and a GXA formula is generated in cell G11.

(22)

(23)

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

(24)

5.3 GXA Formula for Account Values

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

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.

Single Account String 001-310-7260-005 Retrieves 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 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.

Dataset

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

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

Period

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

Identifies 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

(25)

(26)

5.4 GXA Formula for Value Lists

In a GXA formula when retrieving account balances, lists of values may be used for any of the account segments. The list of values for an account segment is identified by brackets [ ] and each 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 the

(27)

5.5 GXA Formula for Budget Manager Data

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

Syntax:

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

Budget Parameter

Enter the literal "B". The formula will look for budget amounts in PC Cache for the account(s) and dataset specified.

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.

(28)

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 the

Account 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

(29)

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 a single GXA formula.

1. From the desktop, click Start>Program Files>Global Software SSGLOBAL>Maintain Segment Lists. The Maintain 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.

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

Description Identify a description for the list.

(30)

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 for the segment list.

1. In the Value field enter the segment values to include for the criteria, select the appropriate radiobutton to indicate 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 all values from the list, click the Clear All button.

(31)

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 the desired segment list name. A caret symbol (^) must be inserted prior to the segment list name in the formula for the system to recognize the segment list.

Formula Examples:

=GXA("001-310-^OH-*","CYACT","PER","12","USD")

(32)

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

(33)

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 Server users, 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 the

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

(34)

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 strongly recommends setting 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 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 have changed since the last calculation.

(35)

Refresh Calculations 32

6.2 Review/Refresh PC Cache

When spreadsheets are calculated, Spreadsheet Server stores the account balances in the PC's cache database. Spreadsheet Server will first look at the PC cache for account balances prior to retrieving the information from the host. As a result, it may be necessary to clear and recalculate the PC cache file when any 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 the

information from the host.

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

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

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

(36)

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 column

heading. To re-sort a column in descending order, hold the Shift key and click the column heading.

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

Print Records On selected panels, there is an option to print the grid list in a standard 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 future drill downs for this particular grid, but only for the specific Spreadsheet Server user. Other users are not affected.

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

(37)

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 Excel worksheet, columns in the drill down grids may be hidden, resized and moved to different positions. Once the grid layout has been customized, it may be saved as the default for that specific grid for the specific user. Other users are 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 but the action can be ignored. In any case, the column will change colors and will be considered selected. Only selected columns can be moved.

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

(38)

Specify the desired location of the selected column by dragging the position marker, which changes position as the mouse pointer crosses the right edge of a column. In the example below, the Amount column is to be moved between 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 the position 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 must be saved as a new default. Right-mouse click on any line in the grid. A pop-up menu appears. Select the menu item: Save Grid Layout as New Default. When the next drill down is performed for a specific grid, the new default layout will be displayed.

Restore a grid layout:

(39)

may need to be redone starting with the installed default layout. In addition, the Spreadsheet Server product may be enhanced with new or changed grids, in which case the customized grid layouts would need to be recreated to include 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 Layout from 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. Hidden columns will be ignored. In addition, any repositioning of columns due to customization will be honored. In other words, WYSIWYG is in effect.

(40)

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 records or 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 standard windows clipboard -or- to export data directly to the target worksheet in the spreadsheet. Also specify whether or not to include column headings in the copy. When exporting directly to a worksheet, the following 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.

(41)

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 up the value. Via Spreadsheet Server Settings various processing criteria related to drill downs may be defined; such as whether or not zero balance accounts are displayed on the screen, and the number of drill 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 selecting

SServer>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).

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

(42)

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 what account balances make up the values. A maximum of 12 columns may be selected. Via

Spreadsheet Server Settings various processing criteria related to drill downs may be defined; such as whether or not zero balance accounts 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 detail account balances for each column. If only two columns were selected, a variance column will automatically be displayed.

Note: Steps 1 and 2 may be replaced by selecting multiple cells and from the Excel menu selecting

SServer>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).

(43)

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 account balances. Via Spreadsheet Server Settings various processing criteria related to drill downs may be 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 for Selected Accounts. The Journal List of Accounts window appears displaying journals for all the selected 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 popup menu select Journals List for ALL Accounts. The Journal List for Accounts window appears

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

(44)

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 as the 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.

(45)

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 line originated. 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 menu appears.

(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 which 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 List Accounts button. The appropriate accounts are displayed on the List Accounts panel. If the filter is left blank, the system will retrieve all account masters. When using a filter, a valid account string combination should 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 the Shift Key and click the column heading (descending).

(47)

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 select accounts 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.

(48)

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 accounts and balances included in the GXA formulas of the worksheet.

1. From the Excel menu, select SServer>Generate Account Detail from Current Worksheet. The Generate Account 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, a target 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 balances

(49)

Miscellaneous Features 46

(50)

9.2 Expand Detail Reports - Account Details

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

1. Create a standard Spreadsheet Server report using the GXA formula. The single report line becomes the basis for the exploded report. When the process is executed the single line will be exploded into individual account 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 each column 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 as worksheet GXE Results. This cell identifies the name of the worksheet that contains the final formatted worksheet 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 as worksheet GXE Formula. This cell identifies the worksheet that contains the GXA formulas that will be exploded. 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

(51)

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

5. Create/label a cell called Source Detail Row. In the example, this is cell B19 and it has been identified 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 (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 is initiated, the total line is shifted down or up, based upon the number of data rows populated each time the expansion is processed.

8. To launch the explosion, from the Excel menu select SServer>Expand All Detail Reports. The system will look for GXE formulas and expand the results into the formatted sheet (see below).

(52)

9.3 Expand Detail Reports - Journal Details

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

1. Create a standard Spreadsheet Server report using the GXA formula. The single report line becomes the basis for th exploded report. When the process is executed the single line will be exploded into individual journal entry 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 each column 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 cells for Target Sheet Name, Source Sheet Name, Target Detail Starting Row, 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 cell identifies 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 has

been identified as worksheet JE Detail. This cell identifies the name of the worksheet that will received the journal entry detail.

(53)

and it has been identified as cell A1. This cell identifies the starting cell location within the 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 set to 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 to

Y. 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 to

Y. 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 journal entry worksheet (see below).

(54)

9.4 Account Security

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

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

2. In the Account Mask field on the Accounts Profile panel, specify the account string to which the user will have access and click the Add button. Single account strings, ranges, or wildcards may be entered. A maximum 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. To remove 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 account profile), click the Lock button. The This profile is currently unlocked dialog box appears.

(55)

6. On the This profile is currently unlocked dialog box, specify a password and click the Lock button. This password is required to change the account profile. It is not mandatory to lock an account profile with a password.

(56)

9.5 Hide Rows with Zero Balances

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

(57)

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 not connected> 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.

(58)

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

(59)

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 User Manual

Spreadsheet Server

for use with Global Software

User Manual

Corporate Headquarters

3200 Atlantic Avenue

Raleigh, NC 27604 USA

+1.919.872.7800

www.glbsoft.com

EMEA Headquarters

500 Chiswick High Road

London, W4 5RG UK

+44 (0) 20 8956 2213

Spreadsheet Server for use with Global Software

Spreadsheet Server for use with Global Software

Table of Contents

Part I Spreadsheet Server Overview

3

Part II Spreadsheet Server Installation

5

Part III Getting Started

8

Part IV Load Local PC Database (optional)

14

Part V Building Spreadsheets

17

Part VI Refresh Calculations

31

Part VII Drill Down Functionality

33

Part VIII Review Account Master

43

Part IX Miscellaneous Features

45

Index

55

1

Spreadsheet Server Overview

1.1

Introducing Spreadsheet Server

1.2

Features & Benefits

2

Spreadsheet Server Installation

2.1

Additional Components

2.2

Spreadsheet Server Installation

2.3

Uninstall Process

3

Getting Started

3.1

Initiate Excel Add-In

3.2

User Settings

4

Load Local PC Database (optional)

5

Building Spreadsheets

5.1

Worksheet Wizard

5.2

GXD Formula

5.3

GXA Formula for Account Values

5.4

GXA Formula for Value Lists

5.5

GXA Formula for Budget Manager Data

5.6

List Accounts for a GXA Formula

5.7

Spreadsheet Server Segment Lists

5.7.1

Create Segment Lists

5.7.2

Enter Values for Segment Lists

5.7.3

Using Segment Lists in a GXA Formula