How to Use Analyze with Excel

PointForce iTopia enables you to export the results of a search operation into a Microsoft Excel spreadsheet, and subsequently use many of the Excel analytical features to look at the data in different ways.

Once in Excel, you can use the Refresh Data option to pull in the latest data from the view that was used to create the spreadsheet. The original criteria is also maintained.

While viewing the results in Excel format, you can perform a number of operations, such as:

• Sort or filter columns;
• Refresh data;
• Set a refresh interval; and,
• View the last data retrieval date and time and number of rows.

The following list identifies the parameters in which Edit with Excel works in PointForce iTopia:

• Dates and numbers in Microsoft Excel are displayed and entered using the running PC's configured regional settings. Analyze with Excel supports this functionality.

• There is a limit to the number of cells that can be safely selected for an Analyze with Excel operation and/or saved to the database at any one time. (The number of cells represents the number of rows multiplied by the number of columns.) The value of the general.results.max_num_cells property, configured within the Tecsys.properties file, determines the limit, which is set to 50000 by default. This limit identifies the maximum number of cells that can be safely processed without huge performance degradation or reaching system limits at any level: application server, operating system, network and client device (PC). Only a user with sufficient permission may modify this value. Changing the default might require more memory to be allocated for the affected environment.

• For initial data loading purposes, you can use the Load Data feature which is available via the Views resource.

• Although iTopia supports a total numerical precision of 17 digits, Excel has a limitation of 15 digits. Any attempt to import or export numbers which are greater than 15 digits will result in rounding.

• View names containing non-alphanumeric characters may cause this feature to fail unexpectedly, as they may be incompatible with worksheet naming conventions in Microsoft Excel.

Prerequisites

To use the Analyze with Excel feature, a user must meet the following technical requirements:

• Microsoft Excel (32- or 64-bit) version 2010 and above must be installed on the user's system.
• A Web services license must be assigned to the user by a system administrator.
• The TECSYS Excel Add-In must be installed.

Note: Only views set up via the Role Permissions resource to allow the Maintain List operation, as well as the Read operation, can support the Analyze with Excel feature.

Configuring Interactivity with MS Excel

To configure MS Excel to work with the Edit with Excel feature in PointForce iTopia, see the Configuring Interactivity with MS Excel topic.

Using the Analyze with Excel Feature

In this example, let's assume that you have launched the P/O Purchasing Information resource to execute a search that will list the currently saved supplier/product records:

Once the Analyze with Excel action is applied, a separate window opens and transfers all the records in the search to a spreadsheet in Microsoft Excel. You receive control when the transfer is complete. If the system is unable to transfer the records to Microsoft Excel, it will display an error message.

At this point, you can add your own columns with formulas, create pivot tables and graphs.

About the Excel Worksheet

The Excel worksheet containing the search results you wish to analyze will have the following characteristics:

• Comments, required columns or columns with domain values will contain notes in the column headings. These notes contain information relating to the kind of data allowed within that column, such as accepted values with their descriptions, or the maximum number of characters allowed.

• The spreadsheet data is configured for automatic filtering.

• The Microsoft Excel ribbon will include a TECSYS iTopia tab that will have been provided by the TECSYS Excel Add-In when it was installed.

  

The Analyze with Excel feature makes use of the TECSYS iTopia tab's Refresh section. The Refresh section provides a mechanism to refresh the data in the worksheet either manually or automatically at predefined intervals. Once data is retrieved from the view's search results, the date and time of the last retrieval is displayed along with the number of rows processed.

The following list defines the 3 main components of the worksheet:

1. Refresh Data Action

   The next time you want to retrieve the latest data from the application database, simply click the Refresh Data action available in the TECSYS iTopia tab.

   When the Refresh Data action is applied, the following occurs:

   • All of the columns from the original view are cleared out and replaced with the latest data.
   • If a column is no longer included on the original view, the column remains in the worksheet, but a message appears on the first data row in the form of a comment.
   • If a column has been added to the view, the new column is not transferred into the Excel spreadsheet.
   • Columns added directly to the worksheet in Excel are not cleared when a refresh is executed and are left as is. However, if a column contains a formula, the formula will be copied down to all the lines of the refreshed data.
   • When a view contains subtotals and grand totals, these are not transferred into the Excel spreadsheet.
   • The Retrieved On date and time displayed in the iTopia tab are reset to the date and time at which the refresh occurred.
   • The number of rows displayed in the iTopia tab is reset to the latest number of rows returned by the last refresh.

   Note: If the session token has expired, the following login window appears:

   

   • The current user's user name is displayed by default and may be overridden. The new user name used is then saved to the spreadsheet and used for all subsequent refresh attempts.
   • When the password is typed in, the system attempts a login. If the login is successful, the data is automatically refreshed. If the login is not successful, an error message is displayed.
   • The password is never saved on the spreadsheet.
   • The new token replaces the original expired token.

2. Interval Action

   The Interval drop-down action, available in the TECSYS iTopia tab, allows you to define whether or not data is to be refreshed automatically without the user having to click the Refresh Data action.

   The following time intervals are available for selection: None, 5 seconds, 10 seconds, 20 seconds, 30 seconds, 1 minute, 5 minutes, 10 minutes, 20 minutes, and 30 minutes.

   • If the session token has expired, the login window will appear.
   • If an error occurs when the user attempts to log in, or if there is any other type of error from the Web service call, the interval is switched back to None automatically.
   • Edit with Excel may also be set to use the data refresh interval option; however, if you are editing data when the refresh operation begins, this will clear your data out. For this reason, this approach is not recommended.

3. TECSYS Data Range and Pivot Tables

   When an Analyze with Excel operation is performed and the spreadsheet is opened, a data range called TecsysDataRange is generated automatically.

   The range starts with the title row and second column (excludes the Status column) and extends to the last row containing data in the second column and the last column with a title.

   When the Refresh Data action is applied, the TecsysDataRange is updated automatically.

   If you create pivot table worksheets using the TecsysDataRange data range, they are refreshed automatically as well.

Excel Formula Renderer

PointForce iTopia supports column and domain attribute that allow the user to define a formula to be used in Excel. The attribute is specified with a key of render.excel.formula.

The render.excel.formula attribute key includes the following characteristics:

• Supports Edit with Excel and Analyze with Excel but not Export to Excel.
• Only columns of the same table can be referenced.
• Columns are referenced using the notation {cell.column_name_alt_id}, where column_name_alt_id is the alternate identifier of the column to which you are referring.

A sample column has been defined in the iBIS database's P/O Purchasing Information resource to showcase this feature. Here's what you need to know:

• The column is named xls_purpri and two column attributes were created to provide two distinct renderings: (1) the column's rendering in the search results and (2) the column's rendering in the Excel spreadsheet.

• The column attribute for the search results displays a blank.

• The column attribute for the formula determines whether the tolerance level for the purchase price is exceeded, i.e. is greater than $50.00. If the purchase price exceeds tolerance of $50.00, then the field displays 1 (yes), otherwise the field displays 0 (no).

• The column attribute for the formula is defined as =if({cell.PurchasePrice}>50,1,0).

• The column alternate identifier PurchasePrice refers to another column in the popi (P/O Purchasing Information) table.

• The formula is not validated when defined in the attributes table. However, if it is incorrectly formulated, an error is reported when the data is transferred to Excel.

• The Excel worksheet cell shows the value as a formula and not just the value of the result of that formula.