AX2231

File Processing Control Sheet

The File Processing Control Sheet defines settings for file processing. Using file processing, you can automate certain processes for a file, including file delivery, exporting data, and saving data to the database. Files can be processed "as is," or you can use multipass processing to process the file using a unique filter for each pass.

To add a File Processing Control Sheet to an Axiom file:

  • On the Axiom tab, in the File Output group, click File Processing > Enable File Processing in this workbook.

The File Processing pane opens, and a sheet named Control_FileProcessing is added to the file. In most cases, it is easiest to use the File Processing pane instead of the control sheet. The control sheet is updated for changes made in the pane, and vice versa. However, there are certain configuration settings that cannot be made in the pane:

  • Enabling or disabling file processing (conditional processing)
  • Enabling or disabling screen updating
  • Enabling the output file to open in Axiom Software after processing
  • Defining current value defaults for source columns
  • Defining batch variables

The File Processing Control Sheet is only visible to administrators or to users with the Allow File Processing permission to the file. Otherwise, it is hidden by default.

Multipass Columns and Current Value Defaults

The following settings apply when using multipass processing:

Item Description

Source Columns

The source columns for multipass processing. Each source column must be placed in a separate cell in this row.

For more details on source columns, see Configuring advanced multipass settings.
Current Value Defaults

The corresponding current value default for each source column.

Current value defaults are optional. If defined, each current value default must be placed directly underneath its corresponding source column.

If a current value default is defined for a source column, then that value will be returned by the GetCurrentValue function (and the [Current_Value] variable) when the file is processed using standard, non-multipass processing (Process File).

If no current value default is defined for a source column, then the function and the variable will return nothing (blank) when the file is processed using standard, non-multipass processing.

For more information, see Defining default values for current value.

The only way to edit current value defaults is on the control sheet. Current value defaults cannot be defined in the File Processing pane.

General Settings

The following general settings apply to all configurations of file processing:

Item Description
Processing Enabled

Determines whether or not the file processing action occurs once the file refresh is complete. If True, the action (snapshot, export, save data) occurs. If False, the action does not occur and the process either ends (if not using multipass processing) or continues to the next pass (if using multipass processing).

The purpose of this setting is to allow turning the file processing action on or off based on a condition in the file. For example, if the data in the file is all zeros for a particular pass, you may not want to send that file. You can set up a formula that returns True if the file has data, and False if the file has no data.

The Processing Enabled setting is checked after the file refresh occurs. The file refresh always occurs when you execute file processing, regardless of this setting.

Processing Type

Specifies the action to be taken on the file when it is processed. Select from one of the following:

NOTE: The file is always refreshed before the action is performed.

Screen Updating

Specifies whether the screen is updated during file processing, or whether all actions take place using background processing.

By default, this is set to Off. Updating the screen can be resource-intensive and slow the process, particularly when performing multipass processing.

If set to On, then the screen refreshes as if you were performing the process manually. If using multipass processing, the screen updates for each pass of data.

This setting is primarily for demonstration or testing purposes.

Multipass Settings

For more information on these settings, see Configuring advanced multipass settings. For more information on how multipass processing works, see How the multipass list of items is determined and How data is filtered during multipass processing.

These settings do not apply and will be ignored if the processing type is File Collect, Batch, or Save Data in Batches.

Item Description

Primary Table

The primary table for the multipass query that determines the list of items to process.

Sum data by these columns

The sum level for the multipass query that determines the list of items to process.

Sort results by these database columns

The sort for the multipass query that determines the list of items to process. The sort determines the order in which items are processed.

Limit MultiPass iterations by this filter

A filter criteria statement to filter the list of items to process.

Apply MultiPass Data Filter to these Tabletypes

Apply MultiPass Data Filter to these Tables

Specifies the tables and/or table types that you want filtered by the current pass item, when multipass processing occurs.

IMPORTANT: These settings are optional and are only used if you want to override the default multipass filter behavior. If these settings are used, then only the tables or table types listed here are filtered, and the default multipass filtering behavior no longer applies.

Snapshot Settings

These settings only apply if the file processing type is Snapshot File.

Item Description

Snapshot file type

The file type for the snapshot: XLSX (default), XLSM, XLS, or PDF.

NOTE: If you select XLS, and the spreadsheet contains features that are not supported by the XLS format, the compatibility warning is not displayed during file processing. You may want to test saving the file to XLS to ensure that the end result will be as expected.

Formula Conversion

Specifies how formulas are handled in the snapshot:

  • Convert All Formulas (default): All formulas are converted to values.

  • Convert Axiom Formulas Only: Axiom formulas are converted to values, but Excel formulas are left as is. Note that if an Excel formula references a sheet that is not included in the snapshot, that formula will be converted to a value. This option is shown as Retain Excel Native Formulas in the File Processing task pane.

    NOTE: If the file contains a pivot table, this option must be selected in order for the pivot table to work in the snapshot copy.

This option does not apply if PDF is the selected file type.

Export to Text File Settings

These settings only apply if the file processing type is Export.

Item Description

Delimiter and File Type

 The file type and delimiter for the output file. You can select CSV (comma-delimited), or a variety of different TXT formats using different delimiters.

Include Headers

Specifies whether to include the header row in the export file. By default, this is On. The header row uses the tags in the export-to-file control row.

If you do not want a header row in the export file, change this setting to Off.

File Settings

These settings only apply if the file processing type is Snapshot or Export.

Item Description

Sheets to Process

The sheets to include in the output file. Separate multiple sheet names with semicolons.

Output To Local File System or Axiom Repository

The location for the output file, either:

  • Local File System (default): The output location is outside of Axiom, to either your local computer or a network share. The specific path is detailed in the Output Folder setting. Access to output files is not controlled by Axiom Software.
  • Axiom Repository: The output location is the Axiom file system, within the Reports Library. The specific path is detailed in the Output Folder setting. Access to output files is controlled by security access to the designated folder within Axiom.

Remote Data Connection Name

Specifies the name of the remote data connection to use for the file processing operation. This option only applies when snapshot or export file output is being saved to your local file system, and only for Cloud Service systems that are using remote data connections.

When the output file is created, the designated remote data connection will be used to access the local file system and save the file to the designated location.

A remote data connection is required to save files locally from a cloud system. For more information, see Managing remote data connections.

Purge Files Setting

Specifies whether and when the file output will be automatically purged by Axiom Software. This only applies if the file output is saved to the Axiom Repository.

If blank, then file output is not purged. Otherwise, specify either of the following:

  • A date, to delete output after that date. For example, specify 12/10/2014 (or the appropriate date format for your locale) to delete the output after that date is passed.
  • A number representing the number of days to keep the output after it is generated. For example, specify 20 to keep the output for 20 days and then delete it.

The deletion is performed by the System Data Purge job in Scheduler.

Output Folder

The folder location for the output files. For more information, see Defining the output folder for file processing.

Microsoft Sharepoint support

You can specify a Sharepoint URL for the folder location, to save the output files to a Sharepoint portal. This feature is only available when running file processing locally via the Excel Client, and when the processing type is snapshot. The user executing the processing must have the appropriate permissions to the target folder in Sharepoint. Note that if the specified folder does not already exist in Sharepoint, this will not be detected by Axiom Software during the file processing, but a Microsoft error will report the location as not found.

Output Filename

The file name for the output file. For more information, see Defining the file name for file processing.

Output Sheet Name

Specifies how the sheets to process will be named in the output file. The sheet name setting takes a single entry that applies to all sheets being processed.

For more information, see Defining sheet names for file processing.

Save or Email Generated Files

Specifies a delivery option for the output file (or files):

  • Save Files: The output files are saved to the specified output folder.
  • Email Files: The output files are emailed to the specified recipients. The output files are not saved anywhere on the file system.
  • Save and Email Files: The output files are both saved and emailed.

If you select an option that includes emailing, then the Email Settings section displays in the File Processing pane.

Multipass File Generation Mode

Select one of the following:

  • Create a Single Output File (default): The results of each pass are collected into a single output file.
  • Create an Output File for Each Pass: The results of each pass are saved as individual output files. For example, if the multipass settings result in 10 passes, then 10 output files are created (one file for each pass).

Note that if you are exporting data to a file, and you selected multiple sheets to process, then you will get one file per sheet (and also per pass, if you selected to create an output file for each pass).
Open Output File after Processing

Optional setting to open the output file in Axiom Software after processing. By default, this is set to Off.

If On, and if the processing results in a single file, then the output file is opened after processing. If an output folder is specified, then the file is saved as normal and then opened. If no output folder is specified, then the file is opened as a temporary file and must be manually saved if you want to retain it.

Save Data Settings

These settings only apply if the processing type is Save Data or Save Data in Batches.

Item Description
Save Data Mode

Specifies how data is saved:

  • Save After Each Pass: A save-to-database occurs after each pass.

  • Save Once at the End: The data from each pass is saved in memory until all passes are complete, and then the save-to-database occurs.

    You should save at the end if the process could result in multiple rows of data with the same key codes, so that the rows are summed before saving to the database, rather than having the data from one pass overwrite the other.

  • Save to Output Sheet: The data from each pass is collected and saved to an "output sheet" within the file being processed. No data is saved to the database. The output sheet is named SaveData_SheetName, where sheetname is the name of the sheet set up to save to the database.

    This option is primarily intended for testing the file setup, so that you can review the data that would be saved without actually saving it.

Use Cached Settings

Specifies whether save data tags and other settings are read once at the start of the process, or whether they are refreshed and evaluated for each pass. By default this option is not enabled, which means that the tags and settings are refreshed each pass. (This is the same setting as Save data tags are static for all passes on the File Processing task pane.)

This option should only be enabled if you are bringing in data using functions or update-only Axiom queries, so that the number of rows and the placement of the save-to-database tags remain static for each pass. Enabling this option allows Axiom Software to read the save-to-database tags once at the start of the process. The File Processing Control Sheet settings and the default Control Sheet settings are also cached at the start of the process and will not be refreshed for each pass. Eliminating the need to refresh these settings speeds up processing for each individual pass.

If instead you are using rebuild Axiom queries to bring in your data, or if you are dynamically enabling or disabling save-to-database tags or other settings for each pass, then you should not enable this option.

Batch Mode Batch Size

The number of records to process in each batch. Only applies when using Save Data in Batches.

By default, this is set to 7,000. In most cases, you can leave the default batch size. However, you might consider making the batch size smaller if your in-sheet calc method uses many rows.

For example, if your calc method is 1 row and the batch size is 7,000, that means the Axiom query will return and process 7,000 rows of data per pass. However, if the calc method is 20 rows and the batch size is 7,000, that means the Axiom query will return and process 140,000 rows of data per pass. In the latter case, the processing performance may be improved if you lower the batch size to lower the overall rows of data to per processed per pass.

Batch Mode Source Sheet

The sheet that contains the source Axiom query for the process.

Batch Mode Source AQ

The source Axiom query for the process. Only applies when using Save Data in Batches.

Print Settings

These settings only apply if the file processing type is Print.

Item Description

Print views

Specifies the print views (and sheets) to print. Print views are specified using the following syntax: SheetName:ViewName. Separate multiple view names with semicolons.

You can specify multiple print views for a single sheet if applicable. If a sheet does not have any defined Axiom print views, specify the Default view, which uses the native spreadsheet print settings. You can print any sheet except control sheets and hidden sheets.

Printer

The name of the printer to use. Leave this setting blank to use the default printer.

Email Settings

These settings only apply if an email option is selected for Save or Email Generated Files. For more information, see How email is delivered for file processing.

Item Description
Distribution List

The email addresses to receive the output file via email. (This corresponds to the "To:" field on the File Processing task pane.) Separate multiple addresses with a semicolon.

If the file will be processed using multipass processing, to multiple output files, then you should use formulas to dynamically generate the appropriate email recipients for each pass (otherwise each pass will be sent to the same recipients). See Using dynamic email addresses with file processing.

Bcc

The email addresses to be blind copied on the email. Separate multiple addresses with a semicolon.

If the file will be processed using multipass processing, to multiple output files, then you should use formulas to dynamically generate the appropriate email recipients for each pass (otherwise each pass will be sent to the same recipients). See Using dynamic email addresses with file processing.
From

Select one of the following to specify the From address:

  • System User: The From address is the default From address specified for Axiom Software in the system configuration settings.
  • Current User: The From address is the email address for the user who performs the file processing, as defined in Security.
Subject Line The subject line of the email.
Body Text The body text of the email.

Batch Variables

Batch variables can be used when processing the file using batch processing, or when processing via Scheduler. You can specify variable values in the Batch Control Sheet or in the Scheduler File Processing task, and those values will be placed in the corresponding value cells on the File Processing Control Sheet. If the file is set up to reference those cells and use the value in some way, then the file can be processed differently for each batch or File Processing task.

For more information, Using batch variables with file processing.

Item Description
Variable Names

The names of the batch variables. Each variable must be in a separate cell.

When the file is processed via batch processing or via Scheduler, the value for the variable will be placed in the cell directly underneath the variable name.
Variable Values

The cells where the variable values will be placed by Scheduler. Each cell corresponds to the variable name directly above it.

You can reference these cells in the report, to change the report in some way based on the variable value.