9 Miscellaneous Features - Spreadsheet Server for use with Global Software User Manual

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 will be generated.

Miscellaneous Features 46

Global Software, Inc.

Spreadsheet Server for use with Global Software 47

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

Miscellaneous Features 48

Global Software, Inc.

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

Spreadsheet Server for use with Global Software 49

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.

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

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

Miscellaneous Features 50

Global Software, Inc.

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

Spreadsheet Server for use with Global Software 51

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.

Miscellaneous Features 52

Global Software, Inc.

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.

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

Spreadsheet Server for use with Global Software 53

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:

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

Miscellaneous Features 54

Global Software, Inc.

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.

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

Index

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

Spreadsheet Server for use with Global Software 55

H

-security account profile 51 segment lists 26

create 26

enter values for segment lists 27

retrieve description 29 using in GXA 28

In document Spreadsheet Server for use with Global Software User Manual (Page 48-59)