AX1747

Including process columns in a report

You can include process columns in an Axiom query in order to display plan file process information in a report. When creating an Axiom form, you can also include process columns in a Data Grid component.

Using the process columns, you can display information such as the current step status for each plan file, previous step statuses, and time spent in each step. This can be done using a single query and without needing a separate function to return the information. This option can improve the performance of process reports by eliminating or reducing the number of GetProcessInfo functions necessary to return the desired information.

Requirements to create a process Axiom query

In order to use process columns in an Axiom query, the query must be set up as follows:

• Set the Process Definition ID of the query to the ID of the plan file process. This setting is not available in the Sheet Assistant, so you must enter the ID directly into the Control Sheet. The setting is located in the Axiom query settings, in the Query Details section.

  

  The process definition ID is the document ID of the process definition. To find the ID of a process, you can use the GetProcessInfo function. The ID is also listed in the tooltip when you hover your cursor over the process definition in Axiom Explorer or the Explorer task pane.

  NOTE: If the process has a process filter, then the query is automatically limited using the same filter. Plan codes that are not part of the process will not be returned by the query.

• Set the Primary Table of the query to one of the following:

  • The plan code table of the file group where the specified process definition ID is active.
  • A data table with a lookup to the plan code table.

  For example, if the plan file process is for the Budget 2019 file group, and the plan code table for that file group is the Dept table, then the primary table can be Dept or a data table that looks up to Dept (such as BGT2019). If you use a data table, then the data returned will only be for plan codes that are referenced within that table.

Once these requirements are met, you can use process columns from the system tables Axiom.ProcessInstance and Axiom.ProcessStatus in the Axiom query. The columns can be used in the following Axiom query settings:

• Field definition
• Sum by
• Data filter
• Data sort

NOTE: When using a process column to sort an Axiom query, the Sheet Assistant may indicate that the column is invalid. However, as long as the column name is correct, you can ignore this message and the query will be sorted as expected.

The following screenshot shows a simple example to report on the current status of all plan files, by plan code (Dept). The desired columns from Axiom.ProcessInstance are simply added to the field definition, using the special column syntax.

The next screenshot shows an example of using the Axiom.ProcessStatus columns to return step information for a specific plan file. The sum level for the query has been set to the Dept key and the step ID, in order to show the department and step level of detail.

The last screenshot shows an example of setting the sum level of the query to just the step ID, to return information grouped by step (such as the average time in step).

Requirements to create a process Data Grid

In order to use process columns in a Data Grid component (in an Axiom form), the component properties must be set up as follows:

• Set the Selected File Group to the file group of the plan file process that you want to report on. You can specify a file group directly or use a file group alias. The component automatically uses the process designated as the Plan File Process for that file group (in the file group properties).

  NOTE: If the process has a process filter, then the query is automatically limited using the same filter. Plan codes that are not part of the process will not be returned by the query.

• Set the Primary Table of the component to one of the following:

  • The plan code table of the selected file group.
  • A data table with a lookup to the plan code table.

  For example, if the plan file process is for the Budget 2019 file group, and the plan code table for that file group is the Dept table, then the primary table can be Dept or a data table that looks up to Dept (such as BGT2019). If you use a data table, then the data returned will only be for plan codes that are referenced within that table.

Once these requirements are met, you can use process columns from the system tables Axiom.ProcessInstance and Axiom.ProcessStatus in the grid. The columns can be used as follows:

• Any of the process columns can be used as [Column] rows in the data source, so that the columns display in the grid. The columns can also be designated as sum by columns for the grid, and used to sort the grid.

• Process columns can be used in the data filter for the grid. You can also enable [IsFilterable] for any of the columns.

• Process columns that return numeric data can be used in [CalculatedColumn] rows. Practically speaking, this is only useful with the Axiom.ProcessStatus.TimeinStep column—for example, to use a calculation to convert the seconds to hours or days.

All other grid features, including grouping, drilling, and row selection, can all be used when the grid contains process columns.

The following screenshot shows an example DataGridColumns data source that is set up to show time-in-step information for a process. The grid uses several process columns and also includes a calculated column to convert the time-in-step from seconds to days.

The file group for the process is specified in the component properties, and the primary table is set to the plan code table.

When the form is rendered, the grid looks as follows:

Axiom.ProcessInstance columns

The Axiom.ProcessInstance table can be used to return information on the overall process instance, such as the current status and step for each plan file. To include a column, use the syntax Axiom.ProcessInstance.ColumnName.

Column Name	Description
CurrentStepID	Returns the database ID of the current step. Note that this is not the step number; this is a unique numeric value that identifies each step.
CurrentStepName	Returns the name of the current step of the plan file.
ProcessInitiatorName	Returns the process initiator for the plan file. By default, this is the user who started the plan file in the process.
ProcessInitiatorID	Returns the user ID for the process initiator.
ProcessInstanceID	Returns the ID of the process instance for the plan file.
ProcessStatus	Returns the current process status for each plan code. For example: Active, Stalled, Completed, or Aborted.
ProcessStatusCD	Returns the numeric ID that corresponds to the current status of the plan file. The IDs map as follows: 1=Aborted, 2=Active, 3=Completed, 5=Stalled.

Axiom.ProcessStatus columns

The Axiom.ProcessStatus table can be used to return process information for each step that a plan file has been active in. To include a column, use the syntax Axiom.ProcessStatus.ColumnName.

When using columns from Axiom.ProcessStatus, you should set the "sum by" for the query as follows, depending on the goal of the query:

• If the goal of the query is to see step detail by plan code, then the sum level for the query should be set to both the key of the plan code table and the Axiom.ProcessStatus.StepID column. This ensures that the query data will show each combination of plan code and step. For example, you might use these columns and filter by a specific plan code to return the step history for that plan file.

• If the goal of the query is to see grouped information about the step, then the sum level for the query should be set to just Axiom.ProcessStatus.StepID. This is useful to see information such as average time in step, and the count of workbooks that have been active in the step.

Column Name	Description
OrdinalStepNumber	Returns an ordinal step number showing the overall step order, including sub-steps. This column can be used to sort steps in the correct order. For example, step 4.1 has an ordinal step number of 5. The StepNumber column returns the number as a string, so sorting on StepNumber will not always sort the steps in the current order (for example, step 10 would come directly after step 1).
StepID	Returns the database ID of the step. Note that this is not the step number; this is a unique numeric value that identifies each step.
StepName	Returns the name of the step.
StepNumber	Returns the number of the step.
StepStatus	Returns the current status of the step. Note that this is not the same as the plan file status. For example, a plan file may be at a status of Stalled, whereas the step where the plan file stalled is at a status of Error.
StepStatusCD	Returns the numeric ID that corresponds to the current status of the step. The IDs map as follows: 2=Active, 3=Completed, 4=Skipped, 5=Rejected, 6=Aborted, 7=Error.
TimeInStep	Returns the time spent in each step, in seconds. You can divide this value as desired to show the time at the desired level—for example, divide by 86400 to show time in days. If the sum level for the query is just Axiom.ProcessStatus.StepID, you can see the average time in step by using the AVG alternate aggregation on this column. For example, use the following syntax in an Axiom query: AxAggregate(AVG)Axiom.ProcessStatus.TimeInStep
WorkbookCount	Returns the count of workbooks that have been active in the step. This column is only useful when the sum level for the query is just Axiom.ProcessStatus.StepID (or similar), so that you can see the count for all plan codes that have been active in the step. If the sum level includes the plan code key, then the WorkbookCount will always return 1 for each plan code / step combination. This column always uses Count aggregation. You do not need to use alternate aggregation to return the count. NOTE: This column cannot be used in the Data Filter of either Axiom queries or Data Grid components. In an Axiom query, it can be used in the advanced Post Query Filter setting instead (which is only available on the Control Sheet). For Data Grid components, the only way to filter by the workbook count is to enable [IsFilterable] for the column.