Add interactive reports to the menu and dashboards

Quick Create Dashboard Card

The Quick Create Dashboard Card row action enables business users and application developers to create a dashboard card without having to leave the Interactive Report Builder. The dashboard card, and an associated application setting, are automatically given the same name as the interactive report. To add the dashboard card to a page, select the Edit Dashboard Options toggle on the dashboard page, then select the Add Cards button. Search the list of available cards for the card you created, then drag the card to a position on the dashboard page. 

Advanced configuration for application developers

You must create an application setting for both dashboard cards and menu entries. If you don't use the Quick Create Dashboard Card row action, you must create an application setti before you create the dashboard card. Once the application setting has been configured, you can either go to the Menu Definitions application to add the report to the Navigation Menu, or the Dashboard Card Builder application to add it to a dashboard. The application setting ties the interactive report to the menu or dashboard. 

In the Application Setting Definitions application, create a new application setting with the following requirements:

• Name your application setting. Best practice is to name it after your Interactive Report. For example, AmortizationReportSetting.
• In the Settings Type field, select Interactive Report. 
• In the Interactive Report Record field, enter the name of your interactive report. 
• In the Interactive Report Version field, select the report version that you want opened. 

To add the report to a dashboard card, in the Dashboard Card Builder application, create a new dashboard card with the following requirements:

• In the Type field, select Interactive Report.
• In the App Setting to Apply field, enter the name of your application setting. 
• Save your card, then add it to any dashboard pages where you want the report. 

To add an interactive report to the Navigation Menu, in the Menu Definitions application, create a new menu entry with the following requirements:

• In the Menu Key field, enter the name of the Interactive Report.
• In the Settings Type, select Interactive Report.
• In the Application Setting Name field, enter the name of the application setting you configured. 

Menu security

Like other platform objects, users must be granted access to see or use the menu entries. To view a menu entry, users must:

• In the Role Definitions application, have access granted to the menu page where the menu entry is located. For example, Financials or Directory. 
• In the Permission Definitions application, have access granted to the application or application's security group on the Application Security page. 

You can add additional layers of security to menu entries by requiring users to also have permission for the application setting. Learn more in Secure menu entries with application settings.

Secure menu entries with application settings

Prerequisites for system administrators

System administrators must:

• In the Security Group Definitions application, configure or select a security group for the application setting attached to your application. 
• In the Permission Definitions application, in the appropriate record, add the security group to the App Setting Security subtable. 

Applications

To secure the menu entry for the application:

• In the Application Builder application, select the Secure Menu Entry checkbox.
• In the Application Setting Definitions application, in the Security Group field, select the name of the security group. 

Security Design Considerations

Securing a menu entry by application setting is helpful when you have multiple versions of an application with different application settings. For example, there are multiple versions of the Hold Manager application, such as Customer Hold Manager and Supplier Hold Manager, that show holds for either Customers or for Suppliers. 

The Directory Supplier Specialist role grants access to see the Directory menu page, and has application security permission that grants access to the Hold Manager application. This role only requires access to the Supplier Hold Manager application, so the Customer Hold Manager application should be hidden from the menu. To achieve this, the applications are both secured by application setting, and the permission for the Supplier Hold Manager's application setting is added to the Directory Supplier Specialist role.

To summarize, for users to see the menu entry, they must:

• In the Role Definitions application, have access granted to the menu page where the menu entry is located. 
• In the Permission Definitions application, have access granted to the application or application's security group on the Application Security page. 
• In the Permission Definitions application, have access granted to the application setting's security group on the Application Security page.

Configure hierarchies for interactive reports

Hierarchies require multiple configuration objects, including:

• Entity table—The table of type Main that holds the values that you are trying to relate to each other, such as people, accounts, or items.
• Relationship table—The table of type Main that creates the connections between records that are stored in the entity table.
• Hierarchy table—The table of type Main which has table lookup fields that point to the parent and child entity, as well as additional fields that you reference in the hierarchy definition. This table is referenced in a join table to link it to your interactive report. 
• Join table—The table of type Join that connects the table the interactive report is built over with the hierarchy table you configure.
• Hierarchy definition—The definition that links the relationship table, interactive report table, and hierarchy. This enables users to select the Apply a hierarchy definition button in an interactive report to display records in the hierarchy you configure.

Prerequisites

You must have a a relationship table built over the entity table which you want the hierarchy configured on. Learn more in Relationship application structure.

Table Definitions - Hierarchy

In the Table Definitions application, create your hierarchy table. You must:

• Build a table of type Main.
• Reference the same Child Field and Parent Field as the relationship table.
• Include the following data items: 
  • ParentHierarchyDepth
  • ChildHierarchyDepth
  • HasChildren
  • A table lookup data item which points to the parent entity. 
  • A table lookup data item which points to the child entity. 

Table Definitions - Join 

In the Table Definitions application, create your join table. You must:

• Build a table of type Join.
• In the Join Type field, select Left Outer Join. 
• In the Join Type subtable, configure your join with the following fields:
  • In the JoinFields.TableSchemaForJoins field, select the hierarchy table you configured.
  • In the JoinFields.Table1Fields field, select the table lookup field from your hierarchy table which uniquely identifies the child records (nwId).
  • In the JoinFields.TableSchemaForJoins2 field, select the table your interactive report is built over.
  • In the JoinFields.Table2Fields field, select the table lookup field from your interactive report table which you are applying the hierarchy to. 

Hierarchy Definitions

In the Hierarchy Definitions application, you must: 

• Create a new record. 
• On the General page, in the Relationship Table field, enter the name of your relationship table. 
• In the Relationship Mappings subtable, map the table and fields from the parent and child records you want included in the hierarchy. 
• On the Flat Table Configuration page, in the Hierarchy Table field, enter the name of your hierarchy table. 
• Optionally, in the Entity Related Fields subtable, map related fields from the entity table to your hierarchy table. Entities in the hierarchy table are nwIds that point to the entity table. Related fields are other fields from the entity table that must also be included in the hierarchy table. 
• On the Join Table Mappings page, in the Interactive Report Mappings subtable, enter the name of your interactive report data source and the name of the join table you created. For each table you want to apply this hierarchy definition to, you must include an entry in the subtable. 

Hierarchy Definitions application

For example, you may want to include a hierarchy in your report to view aggregate data from different levels within a company structure. Learn more in How to use hierarchies in interactive reports.

Before you configure a hierarchy definition, you must configure a hierarchy table and join table. Learn more in Configure hierarchies for interactive reports.

General page

Select the relationship table which the hierarchy is built over, and map the parent and child relationships from the table. 

Flat Table Configuration page

Select the hierarchy table which links the relationship table to this definition. Map the fields from the hierarchy table which identify the parent and child records, as well as any related fields from the interactive report table that you want included. 

Join Table Mappings page

Select the interactive report tables which you want this hierarchy available in.

Configure links in interactive reports

To configure, navigate to the Links section and:

• Select your source region. This can either be the Report Data table or the Row Details table. 
• Enter the Source Field where you want to attach the links. If no field is entered, the link is attached as a row action by default. 
  • If you do select a field, enter a Source Field Aggregate.
• Select your Destination Type. This can be another interactive report, an application, or a dashboard. 
• Enter the Destination Name. This is the internal name of the report, application, or dashboard where you want the link to navigate to. 
  • For destinations of type Application, enter the default form which you want the application to open in. 
• If the destination is a report or application and has an application setting, enter it in the Destination Setting field. 

Mappings 

You can optionally create mappings which pull values from the selected report into the destination. 

For links of destination type Application, you can create mappings in the Link section. Then, navigate to the appropriate Report Version and select the name of your link in the Link field. 

For example, you may have an interactive report which pivots and summarizes transactional data from a financial application. If you wanted to filter the destination application for records which have revenue less than the summarized revenue value from your report, you would configure mappings with the following values: 

When someone selects the application link, it would navigate them to the application and only show them the records which have a Revenue less than or equal to the Sum of the Revenue from the field or row in the report where you selected the link. 

For links of destination type Interactive Report, data mappings need to be created in the Common Context section. Any mappings created in the Link section are not applied. To configure, you must:

• Navigate to the Common Context section of your report and configure your Publisher mappings. Then, navigate to the interactive report record which is the destination to configure the Subscriber mappings. Learn more in Configure common context in interactive reports.
• Navigate to the appropriate Report Version in both your Publisher and Subscriber reports and select the name of your common context configuration in the Common Context field. 

Configure common context in interactive reports

Common context Subscribers and Publishers are configured in the Configuration panel, in the Field Dependencies section in the Application Builder, or in the Common Context section of the Interactive Report Builder. Publishers are the source from which Subscribers receive data and are usually configured within separate applications or reports.

Scope

Publishers and Subscribers are grouped using a scope. The scope defines in what context a Subscriber listens to a Publisher. This is necessary because Publishers and Subscribers can have one-to-many, many-to-one, and many-to-many relationships. The scope is defined using the data mapper tool when configuring the Publisher or Subscriber. Common context can be used in place of, as well as in addition to, logic blocks and application settings to control field values.

Scope boundary

You can create a boundary for your common context scope so that if the application or interactive report is reused elsewhere, it is not impacted by common context tied to the dashboard or composite application you configure. 

On a dashboard page record, select the Common Context Scope Boundary checkbox.

In a composite application record, navigate to the Composite Configuration page and select the Common Context Scope Boundary checkbox.

Publishers

 The table below describes the Publisher options available for common context as well as any specific configuration requirements:

For example, the ReceivablesCommonContext application uses the Contact field as a publisher to other cards on the Receivables Dashboard page, and uses the following configuration:

Subscribers

The table below describes the Subscriber options available for common context as well as any specific configuration requirements: 

For example, the ContactNotesMini application uses the Contact field as a subscriber to the Contact field on the Dashboard Filter dashboard card, and uses the following configuration:

View an example of a common context dashboard configuration in Example: common context on a dashboard.

Common Context Monitor

You can use the Common Context monitor to show which publishers and subscribers are recognized and whether they are populated. If a publisher or subscriber doesn't appear, it is not configured correctly. If it appears but is not populated, you may have selected the wrong publisher field or typed the subscriber incorrectly. 

Access the Common Context Monitor by opening the Sidebar Menu, expanding the Monitors section, and toggling on Common Context. 

Configure report bucket definitions

Report Bucket Definitions

Configure your bucket definitions in the Report Bucket Definitions application. The Bucket Name is the unique identifier which allows you to select which bucket to reference when running a bucketed report. 

Each report bucket definition is configured with columns that sort the records into categories based on the bucket type. There are several bucket types to select from, such as: 

• Relative Date—The records in the report are organized based on date ranges which are relative to a specific date. 
• Relative Period—The records in the report are organized based on date ranges which are relative to a specific period within a fiscal year. 

The bucket type selected determines which fields populate in the form for configuration. 

The Bucket Columns subtable dictates the date ranges for each column in the report. The bucket columns subtable is different depending on which bucket type is selected. For example, with a bucket type of Relative Date, the subtable is the BucketByRelativeDateSubtable. You can find the name of the subtable by selecting the help text icon. The data item name is found along the bottom of the help text box. See the Report bucket example topic for more information. 

Merge Text Template Setup

Create the labels for your columns in the Merge Text Template Setup application. There are several considerations during configuration, such as:

• The Template Type should be either Text or Text Area.
• The Template should be formatted with a structure of ${FieldName} and whatever additional criteria you want. For example, ${BucketRelativeDateLow}+ Days or ${BucketRelativeDateLow} - ${BucketRelativeDateHigh} Days
• In the Input Type Hints subtable, define each variable you included in the template. This allows the report bucket definition to identify the column header templates. 
  • The MergeTextTemplateTypeHints.MergeTextTemplateTypeHintInput can be any field from the Bucket Column subtable in the Report Bucket Definition application. To view all of the available field options, open the BucketByRelativeDateSubtable and BucketByRelativePeriodSubtable subtables in the Table Definitions application.

For example, the BucketActualDateHigh field isn't visible in the Report Bucket Definitions application, but it is in the Bucket Column subtable. Select this field in the merge text template to create a column label that uses the highest date found within the column date range. If your column dates are 01/03/2022-01/09/2022, the column label for a merge text template configured as Week Ending ${BucketActualDateHigh} would be Week Ending 01/09/2022.

Report bucket example

The bucket type determines which input and date fields are available for configuration. The Bucket Column subtable fields determine the date ranges for each column. There are several fields to consider in the subtable, such as:

• Lower range bound—Dictates the lowest date range in that column, such as the relative date (low) or relative period (low). 
• Higher range bound—Dictates the highest date for the date range in that column, such as the relative date (high) or relative period (high). 
• Label—Dictates what the column is labelled. 

Relative Date

For report bucket definitions with a bucket type of Relative Date selected, there are several fields to consider, such as:

• Time Unit—Dictates whether the values in BucketByRelativeDateSubtable.BucketRelativeDateLow and BucketByRelativeDateSubtable.BucketRelativeDateHigh are counting in day or week increments. 
• Start Date Input—Dictates the date which the BucketByRelativeDateSubtable.BucketRelativeDateLow and BucketByRelativeDateSubtable.BucketRelativeDateHigh are dependent on. The start input date specifies a report parameter name from the report version application. 
• Date Field—Dictates which field from the record is checked by the bucket definition to determine if the record falls within the selected date range. 

The table below shows how different values would impact the date ranges for each column with a bucket type of Relative Date. 

Relative Period

For report bucket definitions with a bucket type of Relative Period selected, there are several fields to consider, such as:

• Starting Period Input—Dictates the period within the fiscal year which the Relative Period (Low) and Relative Period (High) are dependent on. 
• Starting Fiscal Year Input—Dictates the fiscal year for the starting period input. 
• Period Data Field—Dictates which field from the record is checked by the bucket definition to determine if the record falls within the selected period.
• Fiscal Year Data Field—Dictates which field from the record is checked by the bucket definition to determine if the record falls within the selected fiscal year. 
• Aggregate Adjustment Period Input—Dictates if there is an adjustment period for the company's fiscal year which should be included in the report. 

The table below shows how different values would impact the date ranges for each column with a bucket type of Relative Period in a standard 12 period year fiscal calendar with period 13 as an adjustment period. 

Enable bucket definitions for report version applications

Report Builder

To allow users to select bucket definitions for use in their report version applications, you must first configure them in Report Builder. There are several sections in Report Builder which bucket definitions can be used in, such as the Header, Group Header, Footer, Group Footer, and Report Summary. Only one column needs to be set up for a bucket definition in Report Builder. Each content item in that column must have the bucket definition selected in the Bucket Definition field. Once that column is configured, the system duplicates the configuration information at runtime, such as the width and height, for each column found in that report bucket definition's subtable.

When a bucket definition is selected in Report Builder, additional options for Display Fields are available, such as: 

• Bucket Field Value—Indicates that the individual field values show beneath the bucket definition columns that their records fall into. If a record does not fit into a bucket definition column, it prints on the report as a value of zero.
• Bucket Subtotal—Indicates that each column should have a subtotal of all the records listed in the bucket definition. 
• Bucket Total—Indicates there should be a total for all of the records included in the bucket definition. 
• Bucket Running Total—Indicates there should be a running total for each consecutive group in the report. The running total looks at the upper bound in the range defined in the bucket subtable, so any record that falls within that range in the bucket definition is included. The value for each date range column is added to the previous date range columns found in the table.

After you configure a bucket definition in Report Builder, you can enable the Bucket Definition field in report version applications. The field is a text table lookup report input where users can specify the actual bucket definition at runtime in their report version applications. 

Learn more about Report Builder in the Report Builder topic. 

Nextworld delivers already configured report bucket definitions, but you can also create your own in the Report Bucket Definition application. 

See the Report bucket example and Configure report bucket definitions topics.

Merge Text Template Setup application

Report buckets dynamically categorize records in their reports based on date ranges organized into column. The column headers must be configured with a merge text template record to show the correct value at report runtime. 

For example, the BucketActualDateHigh field isn't visible in the Report Bucket Definitions application, but it is in the Bucket Column subtable. Configure this field in the merge text template to create a column label that uses the highest date found within the column date range. If your column dates are 01/03/2022-01/09/2022, the column label for a merge text template configured as Week Ending ${BucketActualDateHigh} would be Week Ending 01/09/2022.

To configure:

• The Template Type should be either Text or Text Area.
• The Template should be formatted with a structure of ${FieldName} and whatever additional criteria you want. For example, ${BucketRelativeDateLow}+ Days or ${BucketRelativeDateLow} - ${BucketRelativeDateHigh} Days
• In the Input Type Hints subtable, define each variable you included in the template. This allows the report bucket definition to identify the column header templates. 
  • The MergeTextTemplateTypeHints.MergeTextTemplateTypeHintInput can be any field from the Bucket Column subtable in the Report Bucket Definition application. To view all of the available field options, open the BucketByRelativeDateSubtable and BucketByRelativePeriodSubtable subtables in the Table Definitions application.

Learn more in Configure report bucket definitions.

Report Bucket Definition application

Report bucket definitions allows users to dynamically categorize records in their reports based on date ranges in individual columns. Date ranges can be configured in several formats, such as days, weeks, or fiscal periods. 

Learn more in the Report bucket example and Configure report bucket definitions.

Summary tables

Interactive reports can be built directly over a transactional table, but can have slow performance with large datasets. Summary tables filter the transactional tables for the required data, perform logic block calculations over that data, then categorize and summarize it in meaningful ways. Interactive reports built over the summary table have much better performance since the underlying table has already done most of the data manipulation. For example, an interactive report built over an AccountReceivable table would potentially have to load 100,000 records each time the report was opened. Users would then have to use the interactive report features to filter and refine the data into meaningful patterns. Instead, a summary table built over the same tables would have up-to-date summarized information from that table already ready for the interactive report to query. 

Additionally, summary tables can perform complex calculations that interactive reports are unable to do since they can use logic blocks during processing.

Records from transaction tables are tracked in real-time, then categorized in the summary tables with a dimensional modeling approach. Dimensional modeling uses metrics, dimensions, and display fields to sort records into related groupings. Each unique dimension set has a single record within the summary table that is continuously updated by records from the transaction tables that have the same dimension values. 

The table below reviews important terminology for dimensional modeling and summary tables, and provides examples of how each aspect would be configured for a Receivables summary table:

Summary values for metrics are continuously updated by new transactions, so when records are entered into the transaction tables, the system reviews the EffectiveStartDate and OrganizationalUnit values then updates the appropriate summary table record with those values. 

Summary table types

There are several types of summary tables which provide different functionality, such as: 

• Cube—The most basic summary table type. Use cube summary tables when you want logic block calculations applied to records as they are processed. Results of these calculations can then be used by business users in interactive reports. Learn more in Cube Summary Table Definitions application and Cube summary table configuration.
• Periodic—Has all of the functionality of a Cube summary table but also allows you to summarize business data based on fiscal year and periods. Periodic tables allow complex logic to be configured when values are rolled forward from one period to the next, so accurate cumulative balances, such as year-to-date, values can be summarized, and previous year data can be omitted from roll forward behavior if desired. Learn more in Periodic Summary Table Definitions application and Periodic summary table configuration.
• As Of—Has all of the functionality of a Cube summary table but also allows you to summarize business data based on an As Of date or date range. As Of tables allow logic block calculations to be applied to historical values balances from a certain point in time. For example, you may want to view the open balance for a customer on January 1, 2023, and compare it to their open balance at the end of the month. 

Activations

Once you've built your summary table, you must activate it with the Activate form action in the summary table definition. Activated summary tables have a record in the Summary Table Activations application. Summary tables only begin receiving data from transactional tables once they have been activated. Activated tables cannot be changed. If you do need to make changes to the underlying summary table definition, you would need to activate the table again once you have completed your changes. The updated table would then have its own record in the Summary Table Activations application. 

You can have multiple activated summary tables from the same definition, but only one of those activations can be queried by the system. Mark an activation as the Primary activation in the summary table activation record to make it available for use in interactive reports. 

Table lookups in summary tables

If you want a table lookup's related fields analyzed in reports built over the summary table, there are several configuration options outlined in the table below:

Cube Summary Table Definitions application

Learn more in the Cube 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 cube 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.

All dimensions added on this page must also have data mappings assigned from one of the data sources specified. 

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

Create the logic block you want used for any logic block calculated metrics, as well as any display fields.

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. 

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.

Cube summary table configuration

Configure your cube summary table with the following applications:

• Cube 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. 
• 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. 

Cube Summary Table Definitions

In the Periodic 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. 
  • In the Dimensions subtable, add your dimension. The order in which you enter the dimensions controls the index ordering, so enter them in the order of importance.
  • In the Dimension Data Mappings subtable, add an entry for each dimension.
• Save your summary definition. This automatically creates the transaction event logic block table. 
• 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 transaction amount must have the transaction amount field mapped from the data source in order for the system to calculate the transaction amount summary 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. 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.

Optionally, include additional complex calculations which should be applied to records as they are processed in the summary table. 

Cube 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. 

Once you have configured your cube 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. 

Periodic Summary Table Definitions application

Learn more in the Periodic 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 which should be referenced when using the summary table data. 

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 periodic summary table, and create unique names for different types of events. Different types of transactions within the same table can receive different dimension and metric behavior. This allows you to apply different metrics, dimensions, and logic blocks. 

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 periodic summary tables, the required dimensions are fiscal year, fiscal period, and company, but you can also define additional dimensions on this page. The ledger field can also be defined, but if left empty it will derive the primary ledger of the company. The ledger field is used specifically for understanding the fiscal calendar. All dimensions added on this page must also have data mappings assigned from one of the data sources specified. 

Metrics and Display Fields page

Select the metrics you want included in the periodic 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.

Periodic Continuity Configuration page

Select the type of continuity you want your table to have. In the Specify Continuity Dimension Set field, you can select:

• Continuity Through Last Transaction Period—Ensures no periods are skipped in the summary table, even if no transactions occur during that time frame. 
• Create Entire Fiscal Year—Creates the periods for an entire fiscal year when a transaction matching the dimension set for that year is entered.

Optionally, select the Specify Continuity Dimension Set checkbox to select which dimensions must be matched in records in order for the continuity behavior to be applied. If cleared, records must match all of the dimensions defined in the definition. 

Periodic Roll Forward Configuration page

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 the field which holds the previous value you want to add to the rollover field.

Periodic summary table configuration

Configure your periodic summary table with the following applications:

• Periodic 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 periodic 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. 

Periodic Summary Table Definitions

In the Periodic 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. 
  • Dimensions for fiscal year, fiscal period, and company are required. 
  • In the Dimensions subtable, add your dimension. You must also add the fiscal year, fiscal period, and company you already selected. The order in which you enter the dimensions controls the index ordering, so enter them in the order of importance.
  • In the Dimension Mappings subtable, add an entry for each dimension.
• Save your summary definition. This automatically creates the transaction event logic block table, periodic 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 table. The logic block is run during a transaction event to calculate and assign logic block calculated metrics and display fields.

Periodic 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 Period Continuity Configuration page, select the type of continuity you want for your summary table. 
  • Optionally, select the Specify Continuity Dimension Set to refine which dimensions records must match in order for the roll forward behavior to be applied to them. 
• On the Periodic Roll Forward Configuration page, configure how the data in the table should roll forward each period. For example, a logic block that adds the previous period field to the current period field in order to calculate the running total for period amounts.
  • Select the Modify Roll Forward Logic Block application link to configure your logic block.
  • Optionally, in the Roll Forward Fields subtable, map any fields you want available for your logic block. 

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. 

Periodic Summary Table Definitions

Once you have configured your periodic 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. 

As Of Summary Table Definitions application

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 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. 

Track processed summary table records

This configuration can be applied to the Cube Summary Table Definitions, Periodic Summary Table Definitions, and As Of Summary Table Definitions applications. 

Summary table definition

On the General page of your summary table definitions application, you must:

• Select the Enable Record Log checkbox. 
• Save the definition record to prompt the system to create the Record Log Table. This is the table you query if you want record log information. 

Table definition

In the Table Definitions application, you must:

• Create a new table of type Join. 
• In the Join Type field, select Left Outer Join.
• In the Primary Join Table, select the name of the transaction table from your summary table definition which you want to track.
• In the Secondary Tables subtable, select the name of the table populated in the Record Log Table field of the summary table definition. 
• In the Join Fields subtable, you must:
  • In the JoinFields.Table1Fields, specify nwId.
  • In the JoinFields.Table2Fields, specify STFDataSourceRecordId.

View data

Once you definition is configured, you can build either an interactive report or application over the join table. This allows you to view the record log information in the report data table of the report or the list form of the application. 

Summary Table Activations application

Multiple versions of a summary tables can be built over the same data table. These activations can run and receive data simultaneously from the underlying transactional tables while you build, test, and prove the functionality of the new table. This allows you to build the new table without having to stop using the old one. Only the primary activation is referenced by interactive reports and logic blocks when they reference the data table. Select the Make Primary Activation row action to activate a table. This automatically deactivates a summary table if it was previously marked as the Primary activation. 

Open a summary table record to view information about the activation, such as the:

• Status—Indicates the current status of the activation. Only Loading, Active and Out of Sync activations receive data from the transaction tables which feed the summary table. 
• Error Tolerance—Indicates the number of failed events which may occur on the table before it becomes corrupted and can no longer receive data.
• Primary—Indicates which activation table is referenced by the system when the table is queried by outside applications or reports. Only one activation can be selected as the primary. 

Summary Table Events application

Summary table best practices

Create a summary table at the most general level of dimensions and metrics you can. Then, leverage interactive reports to further aggregate, pivot, and filter the data. 

Data Sources

There are several considerations for your summary table data sources, including:

• If your data source is a Detail table, any applied filters should be on values from the Detail table. Only changes made in the Detail tables are tracked by the system. 
• If your data source is a Detail table, you only need to define the Header Detail table if Header fields are used. 

Dimensions and Metrics

There are several considerations for dimensions and metrics, including:

• If you are creating a dimension related to a table lookup, use the table lookup data item. If you use the related fields, it can become out of sync if the table lookup changes. 
• If a field is both on the Header and the Detail table, it is more performant to use the Detail table field. 
• Not all metrics require mappings. You may be reading multiple tables and only have one table that holds the metric, such as Debit and Credit amounts. Only map what you need to map. 

Display Fields

There are several considerations for display fields, including:

• Display fields should typically be fields that describe an aspect of a metric, but are not functional categorizations. For example, if your summary table is modeling inventory items, you could use Unit of Measure as a display field since it is connected to an item dimension.
• Functionally, display fields and logic block calculated metrics are the same. Use logic block calculated metrics for numeric calculations and display fields as descriptors. For example, Percent of Total would be an appropriate Metric, while Unit of Measure is an appropriate Display Field.
• Use the format of <FieldName><Work> when naming any transaction event work fields. For example, UnitOfMeasureWork. 

Logic blocks

• Generally, it is not recommended to use Interrupt with Errors within transactional event logic blocks. This can break the summary table, depending on the allowable error tolerance. 
• You should define both an insert and removal event type for your transactional event logic blocks. Removal covers cases where a transaction changes and no longer fits the summary record criteria.
• Assign your logic blocks to an event data source. If you don’t have a specified data source and then the summary table is extended in the future, any additional data sources would also be impacted by the logic block.