As Of summary table records summarize data in a date or date ranges. When a new record is created in the summary table, it creates a date range from the date specified up until the day before the next transaction. For example, if a transaction occurs on January 5, 2021 and the next transaction occurs on February 3, 2021, the date range for the January transaction summary table record would be 01/05/2021-02/02/2021.
Learn more in the As Of summary table configuration topic.
General page
After filling out the fields on the General page, save the definition. Saving the definition creates the Data Table and associates it with the summary table. The Data Table is the table you’ll query in a logic block when fetching data from the cube summary table.
Optionally, select the Enable Record Log checkbox to track the summary table records' processing status. When selected, the Record Log Table which stores the record log data is automatically created at the time of save. Learn more in Track processed summary table records.
Data page
Select your data sources for the summary table, and create unique names for different types of events, such as a transaction for posting an invoice and a transaction for voiding an invoice. This allows you to apply different metrics, dimensions, and logic blocks to different types of transactions.
Optionally, create filters to limit the records which are tracked by the summary table. Records that match the filter criteria initiate the event. If a record matches multiple filters, it runs both events. If a record matched filter criteria, but then changes to not match the filter, it is removed from the summary table.
Dimensions page
Define the dimensions which form the summary table. Dimensions are used to categorize how records are summarized. Every record tracked by this summary table will be converted into a dimension set, and applied to the summary record matching all of its dimension values.
For As Of summary tables, you must define an As Of Date Dimension which holds the value of the effective As Of date for the summary data records.
Metrics and Display Fields page
Select the metrics you want included in the summary table. Metrics are numeric summaries of dimension values, and are continuously updated by new transactions that impact the summary table.
Optionally, use the Display Fields subtable to configure display fields you want included on the summary table. Display fields are non-categorical values that are attached to summary table records that don’t fit into the dimension or metric categories. For example, Unit of Measure or Computation Method.
Transactional Event Logic Blocks page
Save the definition to create the Transactional Event Logic Block Table. Any changes saved to the definition are also reflected in the table. This means the transaction event table always contains the fields defined in the summary table.
Select the Create Transactional Event Logic Block button to create the logic block you want used for any logic block calculated metrics, as well as any display fields.
You can also run a logic block each time a transaction is processed in the table. Select the name of the logic block in the Transactional Event Logic Blocks subtable.
Optionally, make fields from transaction data sources available to transactional event logic blocks in the Transactional Event Work Fields subtable.
As Of Date Roll Forward Configuration
Configure how the data in the table should roll forward each period. Select the Modify Roll Forward Logic Block button to open the Logic Block Builder. The logic block is automatically populated with information on how to configure it.
In the Roll Forward Fields subtable, select the field you want to rollover, and how you want the roll forward field to appear when it is applied to future date records.
As Of summary table configuration
Configure an As Of summary table with the As Of Summary Table Definitions application and the Logic Block Builder.
Configure your as of summary table with the following applications:
- As Of Summary Table Definitions application—Build your summary table definition. Use application links and form actions to navigate to the Logic Block Builder and Summary Table Activations application as needed.
- Logic Block Builder—Configure a transactional event logic block which calculates and assigns logic block calculated metric and display fields. Once you have added it to the definition, you will then create the roll forward logic block.
- Summary Table Activations application—Activate your summary table so it starts to run and receive data. Mark as Primary when you want your summary table to begin being queried by the system.
As Of Summary Table Definitions
In the As Of Summary Table Definitions application, you must:
- On the General page, optionally select the Enable Record Log checkbox to track the summary table records' processing status. When selected, the Record Log Table which stores the record log data is automatically created at the time of save. Learn more in Track processed summary table records.
- On the Data page, in the Data Sources subtable, select your data sources for the summary table.
- Optionally, use the filter to refine which transactions from the data sources are added to the summary table.
- On the Dimensions page, define the dimensions you want included in the summary table.
- Define an As Of Date Dimension which holds the value of the effective As Of date for the summary data records.
- In the Dimensions subtable, add your dimension. You must also add the As Of Date dimension.
- In the Dimension Mappings subtable, add an entry for each dimension.
- Save your summary definition. This automatically creates the transaction event logic block table, roll forward event logic block table, and roll forward event logic block.
- On the Metrics and Display Fields page, define your metrics and, optionally, any display fields you want included in the summary table.
- In the Metrics subtable, add your metrics and select they should be calculated. Calculations can be system aggregated or logic block calculated for more complex calculations and conditionals.
- In the System Aggregated Metric Data Mappings subtable, add an entry for each metric that is system calculated, and map the field from the data source that is needed for the calculation. For example, a metric for period total must have a transaction amount mapped for the system to calculate the value.
- Optionally, use the Display Fields subtable to configure display fields that you want included on the summary table.
- On the Transactional Event Logic Blocks page, save your definition to create the Transactional Event Logic Block Table table. Then, select the Create Transactional Logic Block button to open the Logic Block Builder to configure your transactional event logic block.
Logic Block Builder
In the Logic Block Builder, create your transactional event logic block of type Cube over the Transactional Event Logic Block Table. The logic block is run during a transaction event to calculate and assign logic block calculated metrics and display fields.
As Of Summary Table Definitions
- On the Transactional Event Logic Blocks page, in the Transactional Event Logic Blocks subtable, select the logic block that you want used for any logic block calculated metrics, as well as any display fields.
- Optionally, in the Transactional Event Work Fields subtable, map fields from your transaction tables that you want available in your logic block.
- On the As Of Date Roll Forward Configuration page, configure how the data in the table should roll forward.
- Optionally, in the Roll Forward Fields subtable, map any fields you want available for your logic block.
- Select the Modify Roll Forward Logic Block application link to navigate to the Logic Block Builder.
Logic Block Builder
In the Logic Block Builder, you must create and configure your roll forward event logic block. Instructions on how to build your logic block are available in the builder when you select the Modify Roll Forward Logic Block button.
As Of Summary Table Definitions
Once you have configured your summary table, select the Activate form action. Activating a summary table makes it receive data from the transactional tables it references.
Summary Table Activations
In the Summary Table Activations application, view current versions, also called activations, of summary tables. There can be multiple activations built over the same summary table. Select the Make Primary Activation row action on your summary table to make it the Primary table which the system references when the summary table is queried by outside reports.