AX3652

Table task pane

Once you have opened a table using Open Table in Spreadsheet, the Table task pane is available in the Axiom Assistant area.

By default, this pane displays the settings for the currently active table opened in a spreadsheet, including any filter settings or other special options.

You can use this pane to change any of the Open Table in Spreadsheet settings, for either the current table or to open a new table. When you refresh the spreadsheet, the new settings are applied and current data is brought into the spreadsheet.

NOTES:

Refreshing the spreadsheet essentially deletes the existing spreadsheet and replaces it with a new spreadsheet. If any changes have been made to the spreadsheet (and the spreadsheet is open read/write), then you will be prompted to save your changes before refreshing.
If you use the file tab to save the table as a favorite, the current Open Table in Spreadsheet settings will be saved in the favorite. When you subsequently open the table from Favorites, the Open Table in Spreadsheet dialog will be pre-populated with the favorite settings, which you can then edit if desired.

The following options are available in the task pane:

Option	Description
Table	The table to open in a spreadsheet. The number of rows in the selected table is displayed underneath the table name. To specify a table, you can either type the table name, or use the Choose Table button to the right of the box. Keep in mind that changing the table will replace the current table in the spreadsheet; it does not open an additional spreadsheet. If you want to open multiple tables at the same time, you must use the Table menu or Axiom Explorer to open the table, not the Table task pane.
Columns to Return	Specify the columns to return in the spreadsheet: All columns (default): The spreadsheet displays all columns in the table. Selected columns: Select the columns that you want to display by clicking the column icon to the right. Key columns are always included in the spreadsheet. You can select any other user-defined columns to display by checking or clearing the column check boxes. Additional options: Include audit columns: The spreadsheet displays audit columns in addition to the user-defined columns. This includes ActivityID, RecordModifiedBy, and RecordModifiedDTM. Audit columns are read-only. Include calculated columns: The spreadsheet displays calculated columns in addition to the user-defined columns. Calculated columns are read-only.
Rows to Return	Optional. Specify the rows to return in the spreadsheet: All rows: The spreadsheet displays all rows of the table, up to the maximum row limit. By default, the limit is 50,000 rows. This limit can be changed in the system configuration settings. Limit to: Type the desired number of rows that you want to display in the spreadsheet. The rows returned are a random set within the eligible data. If you specify 0 rows, then the column headers will be brought into the spreadsheet with no data.
Data Filter	Optional. Specify a filter criteria statement to limit the data to return in the spreadsheet. You can type the filter or you can use the Filter Wizard . The filter must use the current table or a lookup table. If a row limit and a data filter are both defined, then the data filter is first applied to determine the total eligible rows, and then the row limit is applied. NOTE: If you have filtered write access to a table and you want to open the table for editing, the records that you bring into the spreadsheet must match your write filter. If your read filter is the same as your write filter, then this will happen by default. However, if your read filter is greater than your write filter, then you must apply a filter to Open Table in Spreadsheet that is equal to or less than your write filter, or else you will not be able to save changed data.
Apply Autofilter	Applies Microsoft Excel's AutoFilter feature to the spreadsheet. You can use this to sort and filter the data in the spreadsheet.
Open Read-Only	Opens the table read-only in the spreadsheet. You should select this option if all you need to do is view the data, to prevent the table from being locked for editing. If you only have read-only access to the table, then this option is selected by default and cannot be cleared.
Sort Data	Specifies whether data is sorted as part of the database query to the table. This option is selected by default, which means that data is sorted by the first key column in the table. If desired, you can uncheck this option to bring unsorted data into the spreadsheet. This will improve performance when querying large numbers of records. Data can then be sorted in the spreadsheet using the AutoFilter feature. NOTE: If the table has 100,000 records or more, this option is unchecked by default to speed the data query.
Limit to Unused Items	Filters the table so that only items that are not used in any associated data tables are displayed. This option is only available for reference tables. For more information, see Identifying unused items.

After making any changes to the Table options, click the Refresh Spreadsheet button at the bottom of the task pane.