AX1675

Setting up web report processing

You can perform production reporting for web reports using multipass processing. The report can be processed multiple times over a dimension, generating a filtered PDF or Excel copy of the report for each value of the dimension. The report copies can be saved to a designated location and/or emailed to designated recipients.

To perform multipass processing on a web report, use the Scheduler task Web Report Processing. When you set up this task, you configure the following:

• The web report to process. You can process any web report created from a product-delivered template.

• The output format of the processing. Each pass will generate a filtered PDF or Excel output file. The name of the file can be set dynamically using processing variables and job variables.

• The delivery option for the processing. Each output file can be saved to a folder location, emailed to a recipient, or both.

  • If the output is saved, you specify the location of the target folder (local or Axiom repository) and the folder path. The folder path can be set dynamically using processing variables and job variables.

  • If the output is emailed, you specify the recipients of the email, and the email subject and body text. The recipients can be manually entered into the task settings (and can optionally use job variables), or you can specify a table column to dynamically look up the recipients. Recipients can be email addresses, or you can list user and role names to look up email addresses from Axiom security. The email subject and body text can be set dynamically using processing variables and job variables.

• The dimension to process. You can specify any dimension that will be compatible against the data queried in the target web report. The web report will be processed once for each value in the dimension. If desired, you can define a filter to limit the dimension values to process.

Configuring a web report processing task

In order to create a Scheduler job with a Web Report Processing task, you must be an administrator or a user with the Scheduled Jobs User permission. You must also have read/write access to at least one folder in the Scheduler Jobs Library to save the job. Scheduler jobs can only be created in the Desktop Client General term for using either the Excel Client or the Windows Client, both of which are installed to the user's desktop..

To create a Scheduler job with a web report processing task:

1. On the Axiom tab, in the Administration group, click Manage > Scheduler.

   NOTE: In systems with installed products, this feature may be located on the Admin tab. In the System Management group, click Scheduler.

2. In the Scheduler dialog, on the Job tab, click New.

   

3. Select the Tasks section of the job, then on the Job tab, click Add > Web Report Processing.

   

4. Select the Web Report to Process. This is the report that will be processed by the task.

   • Click the Browse button to open the Axiom Explorer dialog.
   • Navigate to the web report that you want to process, then select the report and then click Open.

   The selected report is listed in the Web Report to Process box.

   IMPORTANT: Remember, only web reports that are created from a product-delivered template can be processed. The Axiom Explorer dialog is filtered to only show reports that were created from template.

   

   Example task with report selected for processing

5. Complete the general processing properties that determine the processing type and the output:

   Item	Description
   Processing Type	Select one of the following to determine the output format of each pass: Export to Excel (default): The contents of the report are exported to a spreadsheet (XLSX) file. The output uses the same behavior as when you export to spreadsheet while viewing the web report. See Exporting grid data in a web report to Excel. Export to PDF: The report is saved as a PDF file. The output uses the same behavior as when you save to PDF while viewing the web report. See Exporting a PDF copy of a web report.
   Save or Email Files	Select one of the following to determine the delivery method for the output: Save Files (default): 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.
   File Generation	Select one of the following to determine whether the output is saved as a single file or multiple files: Multiple Output files (default): 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). Single Output File: The results of each pass are collected into a single output file. For example, if the multipass settings result in 10 passes, then the results of all 10 passes are placed in a single output file. If the output type is Excel, then each pass is a separate sheet in the Excel file. If the output type is PDF, then the PDF for each pass is combined into one large PDF file.
   File Name	Specify how the output file (or files) should be named. You can do the following: You can use processing variables and/or Scheduler job variables to generate dynamic file names. You can type a "hard-coded" file name. If the task will generate multiple output files, then the file name (or the output folder path) must use a processing variable so that the output of each pass is unique. If the task will generate a single output file, then variables are not required. To use a processing variable, you can type the variable or you can click the pencil icon to open a text editor. From the Insert Variable list, select the variable that you want to use. For example, you could set the file name to Income Statement [Current_Value]. If the report is being processed by region to multiple output files, this will generate file names such as Income Statement West, Income Statement East, and so on (where "East" and "West" are region names). NOTE: Processing variables and Scheduler variables use different syntax. Processing variables are enclosed in square brackets. Scheduler job variables are enclosed in curly brackets.
   Sheet Name	Specify how the sheet for each pass should be named. This property only applies when the processing type is Export to Excel. You can do the following: You can use processing variables and/or Scheduler job variables to generate dynamic sheet names. You can type a "hard-coded" sheet name. If the task will collect all of the output into a single spreadsheet file, then the sheet name must use a processing variable so that the output of each pass is unique. If the task will generate multiple output files, then variables are not required. To use a processing variable, you can type the variable or you can click the pencil icon to open a text editor. From the Insert Variable list, select the variable that you want to use. For example, you could set the sheet name to [Current_Value]. If the report is being processed by region, this will generate sheet names such as West, East, and so on (where "East" and "West" are region names). NOTE: Processing variables and Scheduler variables use different syntax. Processing variables are enclosed in square brackets. Scheduler job variables are enclosed in curly brackets.

   

   Example task with general processing properties configured

6. Depending on the selected processing type (PDF or Excel), complete the properties specific to that processing type:

   Export to PDF Settings

   Item	Description
   PDF Orientation	Select the orientation for the PDF, either Portrait or Landscape. Portrait is the default orientation.
   Page Size	Select the page size for the PDF. You can choose from the following standard page sizes: A3, A4, A5, Legal, Letter, or Tabloid. Letter is the default size.

   Export to Excel Settings

   Item	Description
   Include Column Headers	Specifies whether column headers are included in the file output. By default this is set to On, which means column header text is included in the first row of the spreadsheet. Column grouping headers and multi-row headers are not included. If this option is set to Off, then column headers are omitted from the file output and the data starts in the first row of the spreadsheet.
   Include total row	Specifies whether the total row is included in the file output. By default this is set to On, which means that the total row is included in the spreadsheet. If this option is set to Off, then the total row is omitted from the file output. NOTE: This option only applies when the web report being processed is a dynamic row report with the total row enabled. If the web report being processed uses a fixed row structure, then the total and subtotal rows defined in the fixed row structure are always included in the spreadsheet.

   

   Example task with PDF-specific settings

7. If the processing is set to Save Files or Save and Email Files, complete the Output File Settings:

   Item	Description
   Output To	Select one of the following: Local File System (default): The output location is outside of Axiom, to a location on your local network share. The specific path is detailed in the Output Folder setting. Access to output files is not controlled by Axiom. 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.
   Output Folder	Specify the folder location for the file output. You can type a folder path, or you can click the folder icon to browse to the folder location. The browse dialog will display either your local file system or the Axiom file system, depending on what you selected for Output To. The output folder can be made dynamic as follows: If File Generation is set to Multiple Output Files, then processing variables can be used in the output folder path. For example, you can include [Current_Value] in the output folder path, and this will be replaced with the current multipass value. Processing variables are not valid in the output folder path if the task is configured to generate a single output file. Scheduler job variables can be used in the output folder path. NOTE: Processing variables and Scheduler variables use different syntax. Processing variables are enclosed in square brackets. Scheduler job variables are enclosed in curly brackets. Local file system The output folder location must be entered as a UNC path, and must be accessible by the Scheduler service user account (for on-premise systems) or the Axiom Cloud Integration Service (for cloud systems). For more information, see Troubleshooting file access. The ability to save files to the specified location and access them after saving is controlled by local network security. Axiom repository The specified location in the Axiom file system must be within the Reports Library, and the location must use the full path (meaning: \Axiom\Reports Library\...). The ability to save files to the specified location and to create new folders (if necessary) depends on the Axiom security permissions for the user processing the file. Users can only create new folders if they have read/write permissions to the parent folder, and they can only create new files if they have read/write permissions to the target folder. Once the files are created within the Axiom file system, access to those files is dependent on the user's permissions to the output folder. Typically you should create the output folder in advance (or if you want to create output folders on-the-fly, create a parent folder to hold the output folders), and then set permissions for that folder as appropriate in Axiom security, so that the appropriate users will be able to access the files after they are created.
   Remote Data Connection	This option only applies when the file output is being saved to your local file system, and only for Axiom Cloud systems that are using remote data connections. Select the name of the remote data connection to use for the file processing operation. The designated remote data connection will be used to access the local file system and save output file(s) to the designated location. A remote data connection is required to save files locally from an Axiom Cloud system. For more information, see Managing remote data connections.
   Purge Setting	This option only applies when the file output is being saved to the Axiom Repository. If you want the file output to be automatically deleted after a specified period of time, then click the pencil icon to open the Choose Date dialog. No purge date (default): File output is not automatically deleted. Static purge date: Select a specific date, after which the output will be deleted. Relative purge date: Specify a number of days to keep the output after it has been generated. The output will be deleted after the specified number of days have passed. For more information, see Automatically deleting file output generated by file processing.

   

   Example task saving output to the Axiom Repository

8. If the processing is set to Email Files or Save and Email Files, complete the Email Settings:

   Item	Description
   Recipient column	Optional. Specify a table column that holds the desired email recipients for each pass. This option only applies if File Generation is set to Multiple Output Files, so that each pass will be sent a separate email. You can type the name of a table column, or click the column button to select a column from the multipass table or a lookup table. (You must select a multipass column first before you can use the column button to select a column.) For example, if the multipass column is Dept.VP, the recipient column might be Dept.VP.Email. The specified column can contain any of the following: email addresses, user login names, and/or role names. The column can contain multiple values separated by a semicolon. The recipients listed in the column will be used as the To address for the email (in addition to any recipients listed directly in the To field). If the column contains a user login name, that user's email address as defined in security will be used. If the column contains a role name, the email will be sent to all users in the role. To verify that the recipient column will resolve as you expect for each pass, you can click the Preview Multipass List button in the Multipass Data Settings section. The specified recipient column displays in this preview so that you can see the recipient column values associated with the multipass column values. NOTE: The recipient column must have a one-to-one relationship with the values in the specified multipass column.
   To	Specify the To recipient(s) for the email. This is required if a recipient column is not specified. If a recipient column is specified, the recipients listed here will be added to the recipients listed in the column for each pass. You can type one or more email addresses, user login names, and/or role names. Separate multiple recipients with semicolons. If a user login name is listed, that user's email address as defined in security will be used. If a role name is listed, the email will be sent to all users in the role. NOTE: If File Generation is set to Multiple Output Files, the recipients in the To field will receive a separate email for each pass. The only way to dynamically send the emails to different recipients per pass is to use the Recipient Column option.
   CC	Optional. Specify the CC recipient(s) for the email. This field follows the same rules as the To field.
   BCC	Optional. Specify the BCC recipient(s) for the email. This field follows the same rules as the To field.
   From	Select one of the following to specify the From address for the email: Current User: The email will be sent from the user who executes the Scheduler job. System User: The email will be sent from the designated From user for Scheduler. This is the same value returned by the {Scheduler.FromEmailAddress} job variable.
   Subject Line	Enter the subject line for the email. Processing variables can be used in the subject line when File Generation is set to Multiple Output Files. To use a processing variable, you can type the variable or you can click the pencil icon to open a text editor. From the Insert Variable list, select the variable that you want to use. For example, you could set the subject line to Monthly report for [Current_Value] in order to include the current pass value in the subject line.
   Body Text	Enter the body text for the email. Processing variables can be used in the body text when File Generation is set to Multiple Output Files. To use a processing variable, you can type the variable or you can click the pencil icon to open a text editor. From the Insert Variable list, select the variable that you want to use.

   Scheduler job variables can be used in any of the email settings except the From setting.

   

   Example task looking up email addresses from a recipient column

9. Complete the multipass settings for processing:

   Item	Description
   Multipass Column	Specify the column to use for multipass processing. You can type a Table.Column name, or click the column icon to select the column from a dialog. You can select any column on a data or reference table, though typically processing is performed by a dimension such as Dept.Dept, or a grouping such as Dept.Region. The report will be processed once for each unique value in the specified column (except for any values excluded by the Source Filter). A filter is applied to the data query in the report so that the data is limited to the current pass value. For example, if you are processing by Dept.Dept, then the report will be processed once for each department, and the report data will be limited to only the data for that department. Keep in mind the difference between processing by a data table column such as GL2022.Dept, versus a dimension table column such as Dept.Dept. When processing by GL2022.Dept, the report will be processed by each department with data in the GL2022 table. When processing by Dept.Dept, the report will be processed by each department in the Dept table. To verify the list of values for processing, click the Preview Multipass List button to view the list of items. The first 100 values are shown, in the order they will be processed. If the task configuration includes a Recipient Column (in the email settings) or a Sort By column, these columns are also shown in the preview.
   Current Pass Header	Optional. Define a header to display in the report output file. This option only applies if the processing type is Export to PDF. The current pass header should use processing variables to display information about the current pass. To use a processing variable, you can type the variable or you can click the pencil icon to open a text editor. From the Insert Variable list, select the variable that you want to use. For example, you can define a header such as: Processed by [MULTIPASS_COLUMN] [CURRENT_VALUE] When processing by Dept.Dept, this would resolve such as Processed by Dept 22000 By default, if the current pass header is left blank, then the PDF output will not include a header to indicate the current pass information. However, it is possible that the template used to create the report may have been designed with a dynamic header that will display this information.
   Sort By	Optional. Specify one or more sort columns for the list of multipass values. You can type a Table.Column name, or click the column icon to select the column from a dialog. You can also optionally specify Asc or Desc after the column name (ascending order is used if not specified). For example: Dept.Dept Desc. Separate multiple values with semicolons. By default, the values are sorted by the multipass column in ascending order. The Sort By field only needs to be used if you want the values to be sorted in descending order instead, or if you want the values sorted by a different column in the same table. The processing order is only relevant when File Generation is set to Single Output File, since it determines the order of each individual pass within the single file. When outputting to Multiple Output Files, the order is still used during processing but it has no useful impact on the outcome.
   Source Filter	Optional. Specify a filter to limit the multipass list of items. You can type a filter, or you can click the filter icon to use the Filter Wizard. When the multipass list of values is generated, any value that does not meet the source filter will be excluded from processing. By default, all values in the specified multipass column are processed if the source filter is left blank.

   Scheduler job variables can be used in any of the multipass settings.

   

10. Complete the remaining task and job settings as desired. For more information, see the Scheduler documentation. Note the following:

    • Generally speaking, the Advanced Options displayed at the top of the Web Report Processing task should only be modified as advised by Axiom Support. For more information on how these settings work, see Scheduler task: Web Report Processing.

    • If you want to schedule the job for execution at a later date and/or time, including setting up recurring execution, use the Scheduling Rules section of the job.

    • If you want to use Scheduler job variables in any task settings, these variables should be defined in the Variables section of the job.

    • It is recommended to review the Notification settings for the job and adjust them as needed. By default, Scheduler jobs are configured to send an email to the user who executed the job when the job completes, regardless of the job status.

11. On the Job tab, click Save to save the job.
12. In the Axiom Explorer dialog, select a folder location in the Scheduler Jobs Library and define a name for the job, then click Save.

If the job settings included an active scheduling rule, this rule is evaluated when the job is saved and the next scheduled execution is added to the Scheduler job queue.

Executing web report processing

Once you have set up a Scheduler job with a Web Report Processing task, you can execute the web report processing by executing the Scheduler job. Scheduler jobs can be executed on demand by using the Run Once feature within Scheduler, or you can schedule the job for future execution by defining and saving a scheduling rule in the job. Scheduler jobs can also be executed on demand using RunEvent, such as to kick off the Scheduler job from a custom task pane, Axiom form, or spreadsheet Axiom report.

When web report processing is executed, the following occurs:

• The list of multipass values to process is obtained using the Multipass Column limited by the Source Filter, sorted in the default or specified sort order.
• The specified report is processed once for each value in the multipass list.
  • The report data query is filtered by the current pass value and the report data is refreshed.
  • A PDF or Excel copy of the report is generated, depending on the specified Processing Type.
• If the File Generation is Multiple Output Files, then the output file for each pass is saved and/or emailed according to the task configuration.
• If the File Generation is Single Output File, then the result of each pass is saved in temporary storage and then merged into a single file once all passes are complete. This single file is then saved and/or emailed according to the task configuration.

Each pass of multipass processing can succeed or fail independently without affecting the other passes. For example, imagine the multipass list has 10 items. Pass 1 fails because the specified recipient column does not contain a valid email address, user name, or role for the pass 1 value. This pass-level failure does not stop passes 2-10 from being processed. The job status will report partial success in this case.

A Scheduler job can contain multiple Web Report Processing tasks, followed by a File Processing task to collect the various output files into "report books", and then save and/or deliver the collected books. For more information, see Setting up file collect with web report processing.

Using processing variables

The following processing variables can be used in various settings within the Web Report Processing task, in order to dynamically change the setting using information for the current pass.

Item	Description
[CURRENT_VALUE]	This variable returns the current multipass processing value. For example, if you are processing by Dept.Dept, and the current pass is for department 20000, the variable will be replaced by the value "20000" for this pass. This variable is typically used in settings such the file name, sheet name (when generating Excel output), and folder path.
[CURRENT_PASSNUMBER]	This variable returns the current pass number. For example, if the current pass is number 20 of 35 passes, the variable will be replaced by the value "20" for this pass.
[MULTIPASS_COLUMN]	This variable returns the name of the multipass column. For example, if you are processing by Dept.Dept, the variable will be replaced by the value "Dept" for all passes. This variable could be used whenever you want to reference the name of the dimension processed. For example, instead of just referencing the current value in the file name, you might want to reference the column name and the value. A variable construction like [MULTIPASS_COLUMN] [CURRENT_VALUE] would resolve to "Dept 20000" when processing by Dept.Dept and the current pass is for department 20000.

Processing variables can only be used in certain settings, and sometimes only when the output is multiple files (versus a single file). See the documentation for each individual setting to see if processing variables are supported in that setting.

NOTE: Processing variables and Scheduler variables use different syntax. Processing variables are enclosed in square brackets. Scheduler job variables are enclosed in curly brackets.

Using Scheduler job variables in task settings

Scheduler job variables can be used in any Web Report Processing task setting that you can directly type into, such as the file name, sheet name, folder path, and various email settings. To use a Scheduler job variable, you first define the variable on the Variables tab of the job, then you enter the variable in the desired setting using curly brackets. For example, if the variable name as defined on the Variables tab is columnname, then enter {columnname} in the task setting. When the job is executed, the variable in curly brackets will be replaced by the current value of the variable.

Scheduler job variables are useful when you want a task setting to change dynamically based on a variable value that gets passed to the Scheduler job. Various processes in Axiom can trigger a Scheduler job for execution and pass variable values to the job. Additionally, previous tasks in the job can set a variable value that is then passed to subsequent tasks in the job.

NOTE: Processing variables and Scheduler variables use different syntax. Processing variables are enclosed in square brackets. Scheduler job variables are enclosed in curly brackets.

Using Scheduler job variables to pass refresh variable values

If the web report uses refresh variables, Scheduler job variables can be used to pass variables to these variables. For example, in some cases the report may require certain refresh variables to be set before data can be queried. In this case, the Scheduler job must pass values for these required refresh variables. The refresh variables will be used to refresh data for each pass, in addition to the multipass filter for the current pass.

In order to pass a Scheduler job variable value to the report as a refresh variable value, special syntax is used for the job variable:

For example, the report may contain a refresh variable that specifies the grouping level (row dimension) of the report. This refresh variable takes values such as Dept, WorldRegion, Country, and so on.

Example refresh variable

In this example, the ID of this refresh variable is groupingColumnVar. Therefore to pass a value to this refresh variable, a Scheduler job variable named ReportVariable.groupingColumnVar can be used. This variable must be assigned a value that exactly corresponds to a value that can be selected for the refresh variable within the Refresh Variables panel.

When this report is processed, the value for this refresh variable will be set to Region.