Spreadsheet Tools - EcoSys - 4.01 - Help - Hexagon PPM

EcoSys Projects Help (4.01)

Language
English
Product
EcoSys
Search by Category
Help
EcoSys Standard Version
4.01

Tool Menu

The tool menu is in the top right corner of a spreadsheet and consists of the following options:

  1. Change Density: Spreadsheet can be zoomed up to three levels.

  2. Show/Hide Grid Lines option enables or disables the column separator lines in the spreadsheet.

  3. Clear Display Filters and Show Display Filters options allow you to filter the values found in any of the columns displayed in the spreadsheet.

  4. Select Delete Row(s) to remove an entire row from the spreadsheet. Click OK to confirm the deletion in the Pop-up. Once saved, data cannot be retrieved.

  5. Select Insert Row to insert a blank row.

  6. Select Refresh Spreadsheet Data to reload the spreadsheet after using any filters or making changes to the data.

  7. Select SAVE to save all updates to the spreadsheet cells.

Customize Columns

The customize columns feature allows the user to choose the columns that appear on the spreadsheet. This option is also found on the View Settings drop down under the Columns tab.

  1. Right-click the column header bar.

  2. Check/ Un-check the box next to the column caption to be hidden or displayed (if previously hidden). The user selections are saved automatically and applied.

Spreadsheet Splitter

The spreadsheet splitter separates a spreadsheet into two vertical panes. The splitter can be moved by clicking and dragging directly on the splitter, though the overall width of the left pane cannot exceed the width of all columns within the pane.

The spreadsheet splitter only appears on spreadsheets that have been configured with the setting "Freeze column to left" set on at least one column. Only columns with this setting selected will appear in the left pane; this cannot be adjusted by the end user of the spreadsheet.

List Menus

Sheet List

The Sheet list consists of the following options:

  • Save - Records all updates to the spreadsheet cells.

  • Refresh - Reloads the spreadsheet.

  • Report - launches the EcoSys reporting engine and generate a report from the data displayed in the spreadsheet. (This option often has a larger volume limit than the Sheet > Export to Excel option).

  • Import - Allows the user to import an Excel spreadsheet or tab-delimited text file. This option is only displayed for those EcoSys spreadsheets for which import capability is enabled.

  • Export to Excel - Exports the EcoSys Spreadsheet to an Excel Spreadsheet.

  • Export to Text - Exports the EcoSys Spreadsheet to a .txt file.

    SHARED Tip To export a spreadsheet as a text format such as RTF, CSV, and so on, select Report and choose the desired export format.

Edit List

The Edit list consists of the following options:

  • Cut or Copy a cell value and Paste into a different cell within EcoSys or to/from desktop spreadsheet applications.

    Paste Special option pastes the copied cell value with original formatting.

  • Fill Down, Fill Down All, Fill Up, Fill Right, or Fill Left to auto-populate the selected adjoining cells with the same value.

  • Clear Values will remove data from a particular cell.

    Tool Menu - Edit List_4.01

    The Fill Down All option is available only in the Edit menu and will fill down across all rows loaded in the spreadsheet, not just the rows currently being displayed via paging controls.

Display List

The Display list consists of the following options:

  • Show/Hide Columns option.

  • Auto Size Left Columns allows the application to resize the columns on the left side of the screen (only available if Splitter is visible on the screen).

  • Reset Column Widths will reset the columns widths to values assigned by the spreadsheet designer.

  • Spreadsheet Metrics displays data related to time and volume of data loaded by the spreadsheet. Refer to the Spreadsheet Browser Metrics section below for additional information.

  • Show Filter enables the Display Filter. Refer to the Display Filter section below for additional information.

  • Add to Display Filter Within a data cell, Ctrl+Shift+A will copy the data up to the Display Filter.

  • Clear Filter will clear all filter values

  • Show Paging Bar displays the paging bar at the bottom of the spreadsheet. De-select this option to hide the bar.

  • Display row capabilities are: Show All Rows, First Page, Previous Page, Next Page, Last Page.

  • Zoom capabilities are: Zoom Out, Zoom In, and Zoom Reset.

    Tool Menu - Display List_4.01

    Many of the options available in the Display menu are also present in the Tool Bar.

Rows List

The Row list consists of the following options:

  • Edit Row Details launches a separate Pop-up that provides access to all potential row values.
    (This option is not recommended and may be deactivated within the spreadsheet configuration.)

  • Duplicate will create an exact copy of the selected row with all fields completed with the same information.

  • Insert to insert a blank row.

  • Select Insert Multiple to insert multiple blank rows. Enter the desired number of new rows in the pop-up then click the OK button.

  • Select Insert Under to add an additional line item under the selected parent or an item that will be assigned the value of the group band, depending on the spreadsheet configuration. This is available only to grouped data and hierarchical mode spreadsheets.

  • Insert Multiple Under will insert multiple rows under the selected parent or multiple items that will be assigned the value of the group band row, depending on the spreadsheet configuration. Enter the desired number of new rows in the popup then click the OK button. This is available only to grouped data and hierarchical mode spreadsheets.

  • Select Delete to remove an entire row from the spreadsheet. Click OK to confirm the deletion, otherwise click Cancel in the pop-up that appears. Once saved, data cannot be retrieved.

    Tool Menu - Row List_4.01

Right-Click Menu

Right-click in the spreadsheet area and a drop-down menu will be displayed. The available options consist of select commands discussed above.

Paging Controls

Each spreadsheet is limited to the number of rows that have been configured as the default by the system administrator. This setting could be a very high number reaching hundreds or thousands or records, therefore it may not be practical to view these many items on one page. The user may control how many rows or records they wish to view on each page by modifying the settings at the bottom of each spreadsheet.

  • Show All checkbox will display all available rows in the spreadsheet on one page.

  • Page Size allows user entry of a specific number of rows to be displayed per page.

  • First, Prev, Next, or Last can be used to navigate between the pages.

    Spreadsheet Tools - Paging Controls_4.01

    SHARED Tip The paging controls, if hidden, can be displayed by selecting Show Paging Bar from the display menu drop-down.

Spreadsheet Browser Metrics

Each spreadsheet is enabled to provide additional data transfer information upon data retrieval completion. Once a spreadsheet has finished retrieval of data rows it is possible to review the Spreadsheet Browser Metrics page by running the mouse pointer over the lower left corner of the spreadsheet or by selecting Display Spreadsheet Metrics.

Spreadsheet Browser Metrics_4.01

The following information is displayed:

  • Total load time is amount of elapsed time (in seconds) from the time you click "Refresh" or "Save" to the time the browser displays the result in the browser window.

  • Definition load time - The amount of time (in seconds) used to load the spreadsheet definition.

  • Format request time - The amount of time (in seconds) spent formatting the request.

  • Server processing time - The amount of time the server spent computing the result data. This includes any database queries and calculations on the server, and is independent of the network or browser. This value is detailed in the "Process Metrics" table view.

  • Network time - The time spent sending and receiving data on the network, processing by firewalls and proxy servers, and parsing the response in the browser.

  • Browser blocked time - The time the browser was blocked by other processes on the workstation.

  • Grid render time -The amount of time the browser spent drawing the spreadsheet grid on the screen.

  • Total number of rows and cells as well as First page row and First page cell counts.

This information can be copied to the clipboard by clicking the Copy link. The Spreadsheet Process Metrics link provides additional timing metrics for the database or application server administrator if needed.

Display Filter

Enter a value or part of a value for one or more columns in the fields provided to narrow down the data to the desired output. The spreadsheet rows will automatically update according to the criteria entered in the cells. To hide the filter, simply select it again. Use the Clear Filter option to remove all filter values in a single click. Display Filter options are retained when you refresh or leave the spreadsheet.

Data Selection and Entry

Users will update data in the spreadsheets per the rules that have been set up for different rows and columns. Regardless of these intended restrictions, the users still have many capabilities and options for using filters, adding or inserting rows, using fill downs, copying and pasting to make the necessary updates to their data. One of the most powerful tools is the ability to generate a report directly from the data displayed in the screen, which can be used in meetings, presentations, or saved as a record of financial information to be analyzed later. All of the above spreadsheet capabilities make updating financial data more efficient, productive and simple for not only the user, but the entire department or company that benefits from the output.

If more than one transaction makes up the row of data displayed in a summary transaction spreadsheet row (indicated by a red triangle in the corner of the cell), editing this data will impact all the transactions found in that group. The transactions will be erased, and one new transaction will be created. The new transaction will inherit all the summarized attributes and the changed values upon saving. If only one transaction makes up the row of data, a red triangle will not be displayed in the spreadsheet cells. The transaction will be replaced with a new transaction upon saving.

If the range of data available for entry must be regulated in order to drive proper system functionality, the user must select data from a drop-down box. To make the drop-down box appear in EcoSys, move the cursor to the right of the field. An ellipsis () will appear. To display the list of values, click on the ellipsis.

Navigation - Project Period_4.01

Type-ahead data-entry is available for columns that have a look up with a list of items. Editable type-ahead cells will display a limited list of matching values and as you type more characters, the list will be filtered appropriately. The down arrow keys can be used to select a list item as well.

Type-ahead cells will accept any data input however, if the value is not one that was selected from the drop-down list, the value may not be saved. Hierarchy path columns will match each segment of the path as you type.

  • Shift + arrow keys select a range of cells. Hold down the ‘Shift’ key and then use the 4 arrow keys to expand the selection.

  • Shift + mouse click - Ranges of cells can be selected by holding the ‘Shift’ key and then clicking another cell. The range of cells between the current selection and the click point will be selected.

    Path ID should always be selected by clicking on an ellipsis. It should never be manually entered.

  • Dates may be chosen using a pop-up calendar. In any field requiring date data entry, simply double-click on the date field and the calendar pop-up box will appear. Click on the month displayed in the header to display a list of months. Click on the year displayed in the header to display a list of years.

    Calendar_4.01

Spreadsheet Imports

Users have the option to import data directly into EcoSys using Spreadsheets. Data can be imported into existing spreadsheets either from an Excel file or from tab delimited files. The data once imported into the spreadsheet can then be saved in EcoSys.

Import file prerequisites are as follows:

  • The first row of the Excel file or the tab delimited file should contain the column header information.

  • The sequence of the columns does not matter providing the column headers match.

  • File to Import - This is the external file that the user wants to import into EcoSys.

  • Format- The user can specify whether the input file format is Excel or tab delimited. By default, Excel is selected.

  • Import Rule for Incoming Records - One of the following options must be selected:

    • Create New / Update Existing - This option will update any existing rows with the same unique identifier; e.g., Hierarchy Path ID. If the identifiers do not match, then a new row is created.

    • Create New / Fail Existing – If a row does not match an existing identifier, a new row is created. If the row matches an existing identifier, a new row is not created, the data is not updated, and an error is written to the EcoSys log.

    • Create New / Ignore Existing - If a row does not match an existing identifier, a new row is created. If the row matches an existing identifier, a new row is not created, the data is not updated, and an error is not written to the EcoSys log.

  • Preview data before saving: Choose to view the data imported prior to saving the data. If this is not checked, the data will be automatically saved.

    Import Spreadsheet_4.01