Report Data Model
The Report Data Model allows you to efficiently retrieve data from tables for real-time calculations and multi-source editing in Presenter Adaptive reports. You can use Report Data Models to identify and limit the data and calculations included in your report. To read more about the Report Data Model, see: Adding a Report Data Model. To learn more about the Report Data Model, take the Creating, Using, and Troubleshooting the Report Data Model in Incentives course.
Getting Started
Introducing Report Data Models
The Report Data Model allows you to efficiently retrieve data from tables for real-time calculations and multi-source editing in Presenter Adaptive reports. You can use Report Data Models to identify and limit the data and calculations included in your report. To read more about the Report Data Model, see: Adding a Report Data Model. To learn more about the Report Data Model, take the Creating, Using, and Troubleshooting the Report Data Model in Incentives course.
Adding a Report Data Model
The Report Data Model (RDM) is a structure where you can retrieve data from tables for real-time calculations and multi-source editing in Presenter Adaptive reports. Through RDMs, multiple reports can use the same data manipulations and calculations.
To add an RDM:
In the Reports module, click Create.
Click Report Data Model.
Create a Name.
Click Save.
To begin building out your RDM, add a Report data store, aggregation, or parameter.
Report data stores
Report data stores
Report data stores are built from underlying Varicent Incentives tables to combine data as needed for reports. Charts and stored values can use Report data stores as sources. With Report data stores you can create a data set of multiple Incentives tables that are joined together and can support the following:
Edits to data in the table
Feeding data into a series of calculations with results that can be written back to the Report data store
Real-time dynamic updates within the reports displaying the Report data store
Writing back data to the underlying Incentives tables when the report is saved
In the Reports module, click Create.
Click Report Data Model.
Create a Name.
Click Save.
Click Add Report data store.
Select the tables and calculations to use as data sources.
Once selected, drag and drop the tables and calculations under editable or noneditable data sources.
Note
Editable sources:
Can be populated with calculations
Can have their field set to editable in a Presenter Adaptive report
Must match with records in other editable sources
Note
Noneditable sources:
Used for bringing in additional information for the records needed for calculations or to display on the report
Cannot have fields set to editable in a Presenter Adaptive report
Cannot be populated by calculations
You have the option to set the editable sources as an anchor. When an editable source is set as an anchor, records in the editable sources can be included in the Report data store even if there is no matching record found in a noneditable source.
Click Next.
Add restrictions to define which records to include and exclude from your data sources.
Click Next.
Add a calculation, lookup, source column, or report-only column to populate your Report data store.
Click Create.
Data sources
Data sources are all the tables you want to bring together into the Report data store. These are records that you will use throughout the entirety of the report and in the calculations that will run. Data sources are not dynamic - once set up, they will not change as the calculations are running.
In the Reports module, click the Report Data Model tab.
Create a new Report Data Model or select an exisiting one, and click + Add Report data store.
Select the tables and calculations to use as data sources.
Once selected, drag and drop the tables and calculations under editable or noneditable data sources.
Note
Editable sources:
Can be populated with calculations
Can have their field set to editable in a Presenter Adaptive report
Must match with records in other editable sources
Note
Noneditable sources:
Used for bringing in additional information for the records needed for calculations or to display on the report
Cannot have fields set to editable in a Presenter Adaptive report
Cannot be populated by calculations
Turn on the Anchor toggle to set an editable source as an anchor.
When you set an editable source as an anchor, records from these sources can be included in the Report data store even if there is no corresponding record found in a non-editable source.
For example, imagine you are preparing a financial report where the primary dataset composed of transaction records is maintained as an editable source. If you set this dataset as an anchor, you can ensure that all financial transactions are accounted for in the report, even if some transactions don't have corresponding entries in auxiliary datasets, such as a customer information database.
Click Next.
Add restrictions to define which records to include and exclude from your data sources.
Click Next.
Add a calculation, lookup, source column, or report-only column to populate your Report data store.
Click Create.
Joining multiple tables
You can join two or more tables together to define the data sources for your calculation by adding columns. This is referred to as a join.
Use restrictions to join each new source to an existing source based on a point of commonality. Adding restrictions is an important part of adding a data source. If you do not create restrictions, each row in the new source is added to each row in the existing source. This action creates many more rows than either source would have individually.
Normally, you join data sources together based on a point of commonality by defining restrictions for the join. By default, columns with the same name and of the same type are joined automatically.
To learn more, read: Restrictions.
Example:
Imagine you work in the marketing department of a company and you need to create a detailed report that includes information about potential leads and the median income in their zip code area. To do this, you have access to two different datasets.
Lead table: This table contains information about potential leads, specifically their names and the zip codes in which they reside. For example:
Lead name
Zip code
John Doe
Q1W 2E3
Jane Doe
R4T 5Y6
Zip code table: This table holds data about various zip codes, along with demographic information like population and median income within those areas. For example:
Zip code
Population
Median Income
Q1W 2E3
50,000
$60,000
R4T 5Y6
75,000
$75,000
To create a comprehensive report, you need to combine these two tables so each lead includes their corresponding zip code demographic data. The key to joining these tables is identifying a common piece of information – here, it's the "Zip code" field. Both tables include this field, making it the logical point of commonality.
To join the tables:
Identify commonality: Here, the commonality is the "Zip code" column. This is the foundation upon which you will join the two tables.
Set up restrictions: The restriction ensures that the data is joined correctly. In the simplest terms, you will link each lead's zip code in the Lead Table to the same zip code in the Zip code Table. This is written as a restriction:
Lead.Zipcode = Zipcode.Zipcode
Execute the join: By applying this restriction, the data management system will check each row in the Lead Table against each row in the Zip code Table and will combine rows where the zip codes match.
The result of this join operation would be an enhanced table like this:
Lead name
Zip code
Population
Median income
John Doe
Q1W 2E3
50,000
$60,000
Jane Doe
R4T 5Y6
75,000
$75,000
This table now provides a complete view – you can see both the potential leads and the demographic context of their location, making it easier to tailor marketing strategies based on localized economic data.
By using restrictions to perform this join, you avoid accidentally combining every row with every other row (which would happen in a Cartesian product), thus ensuring that the data remains accurate and meaningful.
Reversibility of join: Once the tables are joined, there isn't a reversal or "unjoin" option. If you need to separate joined tables, you will need to delete the join and recreate individual tables or data sources as needed.
Visual indicators of joined data: There are no immediate visual indicators in the system to show which tables or data sources have been joined. However, you can get an idea by using the preview functionality. By clicking the preview button, you can view the content of the joined tables to understand how they have been integrated. To make comparisons or to examine the joined data in depth, you can keep previews of the joined tables open in different tabs and compare the results.
Take the Filtering and joining data using restrictions course to learn more.
Restrictions
Restrictions can be used to exclude certain records from your Report data store. For example, you might not want your calculation results to include every record in the source data table or all of the results from a prior calculation. By defining a restriction, you can limit the records included in the calculation by specifying conditions for their selection. To define the records that are included in your calculated results, you might impose restrictions on sales that include product 2 or transactions that exceed $50,000 and are sold to a customer in New York.
For each restriction, you must define a Report data source, an operator, and a value to determine whether the restriction must be applied. The following operators are available:
= (equals)
<> (does not equal)
< (is less than)
> (is greater than)
<= (is less than or equal to)
>= (is greater than or equal to)
is empty
is not empty
Defining restrictions for joined sources
When working with multiple data sources, it’s essential to establish restrictions by identifying a common attribute that they share. This ensures the data from each source is connected correctly. Restrictions between sources are automatically generated based on predetermined criteria; however, you can customize these to better meet your specific data analysis needs.
For example, consider you have two separate data sources: one contains sales data, and the other contains customer feedback data. To gain insights into how customer feedback correlates with sales performance, you can define restrictions to join these sources based on a common element, such as the Product ID or Transaction date.
From the Reports module, click the Report Data Model tab.
Create a new Report Data Model or select an existing one, and click + Add Report data store.
On the Restrictions tab, expand the source for which you want to define restrictions.
Hover your mouse over the area next to AND and click Restriction.
Tip
If you need to create many restrictions at once, it is faster to click Restriction multiple times and then drag the columns into the empty fields. For example, if you know you need to create five restrictions, click Restriction five times in a row.
Drag the column from the source that you want to restrict and place it in the first field.
Select an operator for the restriction from the drop-down list.
Drag a column from the joined source to the second restriction field to define the restriction.
Defining global restrictions
A Global Restrictions dynamic panel is displayed at the bottom of the Restrictions tab. You can define the restrictions in this pane that are global and would be applied after all other restrictions in the normal pane or for filters related to anchoring the base data source.
From the Reports module, click the Report Data Model tab.
Create a new Report Data Model or select an existing one, and click + Add Report data store.
On the Restrictions tab, expand the Global Restriction pane.
Hover your mouse over the area next to AND and click Restriction.
Drag a column on which you want to restrict from the data source to the first field on the Global Restriction pane. For example, drag the ProductID column to the Product table source.
Select an operator from the drop-down list. For example, select the equal operator.
In the second field of the restriction, perform one of the following actions:
To select a value from the selected column to define the restriction, click in the second restriction field and select the value.
Drag a column on which you want to restrict from the data source to the first field on the Global Restriction pane.
Multiple restrictions that use AND or OR groups
If you need to get two sets of results in one object, you can use OR within AND restrictions.
For example, you can show all data that is either Product ID 001 and Payee ID Huddle or all data that is Payee ID 001 and Payee ID Young.
AND Groups
When multiple restrictions are defined, they are separated with an AND operator by default. For example, if you define the following restrictions, you get results for payee 1234 in January:
Payee ID = 1234
Month = January
OR Groups
You can use OR groups to define the following type of restriction: Payee ID = 1234 or Payee ID = 3456.
Adding AND or OR groups in restrictions
You can define multiple restrictions for your results by adding AND or OR groups.
On the Restrictions tab, expand the data source that you want to create an AND or OR group for.
Hover your mouse over the area next to AND, and select one of the following options:
Option
Description
AND group
Both or all defined restrictions in the group are applied to the data.
OR group
Data where any restrictions in the group are true is retrieved.
Define the restrictions.
Columns
Columns in the Report Data Model (RDM) is where you can add a calculation, lookup, source column, or report-only column to populate your Report data store. Here, you can also rename source columns that have not been used in reports.
Calculations
Calculations are defined in the RDM for any real-time calculations that need to be displayed on the report that the user is viewing. The calculations are saved back to the Incentives repository.
From the Reports module, select a Report Data Model.
Click the More options
button on the selected Report data store.Select Edit
.On the Columns and calculations tab, click Add.
Select Calculation.
Create a Name.
Create a formula using functions to add a calculation to your Report data store. For example, you can set a formula where a column from the data store is divided by a numeric parameter that's selected from a pick list. By changing the values selected in the pick list, the parameter is updated. And since the parameter is used in the calculation, the calculation will run and the table will be updated in the report.
[Optional] Add the calculated value to another column. When this option is enabled, you can select a target field where the calculated results will populate back to.
You can also enable running the calculation when a user updates the values. It's best to only run a calculation when a field is manually updated.
Click Save.
Lookup
A Lookup retrieves results from a column to add to your Report data store. It is similar to a calculation where it happens dynamically on the report as you change values. A Lookup enables you to set up conditional rules for how to match the records in a table against the records in another table.
From the Reports module, select a Report Data Model.
Click the More options
button on the selected Report data store.Select Edit
.On the Columns and calculations tab, click Add.
Select Lookup.
Create a name for the Lookup.
Add a source. A source is another table that you want to Lookup a value against. For example, you might be scoring employees on an objective. The score could be one through ten. The score will translate into a payout and the score corresponds to a payment percentage. A score of ten could equal to 100%, a score of 9 could equal to 90%, etc. You want to avoid merging this as a data source into the data store because data sources are merged together in the beginning and they don't dynamically change. You need a Lookup instead to make the score column editable. The Lookup will change dynamically the same way a formula calculation would.
Select the column where the data will be retrieved from.
Set the rules to define the conditions for displaying the source values in your Lookup column. Parameters are available to select as a source column when defining your rules. For example, you can set a rule where a text parameter would equal to a specific Lookup column of the selected data store.
[Optional] Add the calculated value to another column.
Click Save.
Source column
Source columns are the columns that will be added to the Report Data Store and be displayed on your report.
Columns from your selected data sources have been automatically populated into the Columns and calculations panel
From the Reports module, select a Report Data Model.
Click the More options
button on the selected Report data store.To add another data source, drag and drop a data source from the left panel on the Data sources tab or select the Columns and calculations tab and click Add and select Source column.
Create a column name.
Add the Source value for your Report data store.
You can rename a column if it has not been used in the reports. To rename, click on the column on the Columns and calculations panel.
Edit the column name.
Click Save.
Report-only column
A Report-only column adds a column to the Report data store. It acts the same as a source column when you use that data store in a report. A Report-only column will only be shown on the report.
From the Reports module, select a Report Data Model.
Click the More options
button on the selected Report data store.Click Add and select Report-only column.
Create a column name.
Select the data type. Data types can be text, numeric, or date.
Click Add Report-only column.
Aggregations
Aggregating information is a crucial step that helps organize and summarize large datasets for easier analysis. Aggregation involves combining and processing data to provide a summary or total that can offer insights into patterns or trends.
Imagine looking at a massive spreadsheet filled with data about various departments in a company. Each department has multiple entries, such as employee salaries or department budgets. Aggregating data means organizing this information in a way that makes sense for your analysis, such as finding the total salary expenses across all departments or comparing department budgets through summaries.
In the Reports module, click the Report Data Model tab.
Create a new Report Data Model or select an existing one, and click + Add Report data store.
Note
Before performing aggregations, make sure that Report data stores are set up in the Report Data Model. You cannot add aggregations without them. Report data stores serve as the sources for the aggregations. If a filter is applied to the corresponding Report data store, the data in Presenter Adaptive (which is sourced from the aggregation) will display the filtered results, even if no filter is applied directly to the aggregation.
From the Aggregations section, click + Add aggregation.
Enter a name for the aggregation.
Click Select Report data store, then select an existing Report data store containing the records you wish to aggregate.
Click Next.
From the Column Groups section, click + Add column group.
Note
Sometimes, you might want to calculate a single total for all your records. You may need to break down your data into more detailed totals in other situations. For example, you could group payees by their department or manager to compare these figures against a budget. To achieve this finer level of aggregation, you can create column groups.
Select a column from the selected data source that you want to group.
The columns displayed in the Group by list refer to the Report data store you have previously selected and configured within this Report Data Model. If you want to group by more columns, click + Add column group again and select the additional column.
From the Calculated column section, click + Add calculated column.
Enter a Column name.
Create a formula for calculating your aggregated column using the functions, operators, and sources.
Note
The aggregate is calculated by applying the formula defined in the Calculated column.
Click Add calculated column.
Click Add aggregation.
The aggregation creates a structured table that combines your column groups and calculated columns. This structured output can be treated as a standalone object within your report.
Click Done.
The saved aggregation can now be directly used in your report. You can incorporate it as a data source for tables, charts, KPI widgets, and other visualizations.
Read Report Data Model in Presenter Adaptive to learn how to use aggregations in a Presenter Adaptive report.
Parameters
Parameters
Parameters can be used to pass additional information from the report to the Report Data Model. Parameters can be text, numeric or date and can have a default value. A parameter can be linked to a pick list on a report and used in calculations in the Report Data Model. The calculations will update when the pick list value is changed. The created parameter is available to use in calculations and lookups.
From the Reports module, click the Report Data Model tab.
Create a new Report Data Model or select an existing one, and click + Add parameter.
Create a name.
Select the data type as numeric, text, or date.
Optional: add a default value.
Note
A default value is used when a value has not been mapped or there is no value from a report. For example, when a default value is set as USD, any report that doesn't have a mapped parameter where a value is passed will use USD. If a default value is not specified, the default will be 0.
Click + Add parameter.
To delete a parameter, click the More options menu (...), and select Delete.
Important
A parameter cannot be deleted if it is in use in a calculation and a lookup.
Connecting parameters to pick lists on a report
You have the option to connect a Report Data Model's parameters to a pick list on a Presenter Adaptive report. The value selected for the pick list will be passed through the parameter and used in the calculations running in the Report Data Model.
Note
The report should be a Presenter Adaptive report connected to a Report Data Model.
From the Reports module, create a new or select an existing Presenter Adaptive report.
Create a new Pick list object and configure it, or choose an existing pick list you want to map the parameter to.
Note
To connect a pick list value to the Report Data Model, make sure to select the ID column.
In the Data tab, under Report Data Model, click Connect value to Report Data Model.
Select the parameter from the list. The list will show all available parameters in the selected Report Data Model for the report. All available parameters have not been mapped anywhere else on the report.
Disconnecting parameters to pick lists on a report
On the parameter, click the
icon.Click Remove.
Report Data Model in Presenter Adaptive
Report Data Model in Presenter Adaptive
You can add a Report Data Model into your Presenter Adaptive report.
In the Reports module, click Create.
Select Presenter Adaptive.
Select a template for your report.
Click Next.
Select Create a Presenter Adaptive report that connects to a Report Data Model.
Click Next.
Select a Report Data Model from the list and click Next.
Note
If you have not created a Report Data Model, read: Adding a Report Data Model
Select the Report data store and aggregations that you want to use in this report.
Note
The Report data stores and aggregations selected are the ones to be displayed in the report and used to run the calculations. Calculations will not run if they involve Report data stores that have not been selected.
Click Create report.
Filter the Report data stores to only include records that you would need to run calculations on by clicking on the Report data store.
Warning
The calculations being run on all records will impact both the performance and results of aggregated calculations. There is a limit of 5,000 records that will be loaded.
Click the Filters tab.
Click
Add filter.To learn more about adding filters, read: Adding filters to Presenter Adaptive reports.
Filters can be defined both at the report level or the object level. To define a filter on the object level, add a chart or a table.
Select a Report Data Model to use as a source.
Under the Filters tab, click
Add filter.
Connecting an existing Presenter Adaptive report to a Report Data Model
An existing Presenter Adaptive report can be connected a Report Data Model even after the report has initially been set up.
From a Presenter Adaptive report, click Report Options
.Click Connect Report Data Model.
Select the Report Data Model that will connect to your report.
Click Next.
Select the Report data stores and aggregations to use for this Report Data Model.
Click Connect Report Data Model.
Disconnecting an existing Presenter Adaptive report to a Report Data Model
From a Presenter Adaptive report, click Report Options
.On the Report Data Model that you want to disconnect, click more options
.Click Remove.
Using Report data stores in objects in a Presenter Adaptive report
Charts and stored values can use Report data stores as sources. With Report data stores you can create a data set of multiple Incentives tables that are joined together.
In a Presenter Adaptive report, click + Add object.
Select the object that you want to add. For example, you might want to add a table.
Under Source you'll see a list of all sources from the Report Data Model that you've selected to bring into the report. Select the source you want to use for your object.
Filter the Report data stores to optimize the report's performance.
Select a Report data store.
Click on the Filters tab.
To learn more about adding filters, read: Adding filters to Presenter Adaptive reports.
Important
If a value is coming from Incentives when the report is loaded, it will always initially load what is stored in Incentives. On the other hand, Report-only columns will run a calculation immediately on display and populate the value.
Changing the Report data stores and Aggregations selected for the Presenter Adaptive report
You can change the Report data stores and aggregations selected for the Presenter Adaptive report after the report is initially added.
From a Presenter Adaptive report, click Report Options
.On the Report Data Model that you want to change the Report data stores and aggregations, click more options
.Click Edit.
Select or deselect the Report data stores and aggregations that will be used for the Report Data Model.
Click Save.
Important
For an existing report, removing a Report data store will impact all of the objects on the report that display that Report data store.
Viewing a Presenter Adaptive report with a Report Data Model in Sales Portal
You can view your Presenter Adaptive report connected to a Report Data Model in the Sales Portal. In the Sales Portal, users can dynamically adjust input criteria, such as sales periods or team members, and see updated calculations and visualizations in real time.
When you first create a Presenter Adaptive report in the Incentives Admin client, it will not automatically be visible in the Sales Portal to your users. To enable your payee users to view a Presenter Adaptive report in the Sales Portal, you must first activate it in the Portal Access module:
From the Portal Access module:
Configure web tab:
Click the Web Tabs tab, and expand the web tab group for the section where the report will be displayed.
Confirm that the desired web tab is present, or create a new web tab.
Assign a name to the web tab and specify the Presenter Adaptive report to which it should be pointing.
For more information on creating a web tab group, read: Adding web tab groups.
Manage report visibility:
In the Web tab configuration, review and configure visibility toggles:
Visibility option
Description
Display in main navigation
Shows the web tab in the top navigation bar of the Sales Portal.
Display in footer navigation
Shows the web tab in the footer navigation.
Note
Make sure the Enable footer toggle is turned on in the More options tab of Model Settings.
Display in mobile app
Allows the web tab to appear in the Varicent mobile app.
For additional Sales Portal configurations, read: Customizing Sales Portal Menu position, and Display navigation icons in Sales Portal.
Configure report assignment:
Click the Assignment tab, expand the Web Tabs folder, and locate the web tab you created in the previous step.
From the Access column, assign the appropriate access tree to the web tab.
The access tree determines which groups of users can view the web tab and its associated report.
Expand the Presenter Adaptive folder and locate your report.
From the Access column, assign the report to the same access tree as step 3b.
For more information on Portal Access assignments, read: Assignments.
Verify tree access:
Click the Trees tab, and select the same access tree assigned in the previous step.
Verify that the access tree contains the correct groups (for example, Account Executives, Sales Managers) who should have permission to view the report.
For more information on Portal Access trees, read: Creating Portal Access trees.
Verify group membership:
Click the Groups tab, and locate the relevant group(s) associated with the previously assigned access tree.
Verify that all intended Sales Portal users are members of the group.
For more information on Portal Access groups, read: Portal Access groups.
Test the configuration:
Log in to the Sales Portal as a user who belongs to the set access tree.
Navigate to the section where the web tab was configured (for example, main navigation or footer).
Confirm that the report is displayed and accessible.
The above procedure is further illustrated with an example demonstrating how the Portal Access module is configured to display a Presenter Adaptive report on the Sales Portal. For a detailed video demonstration, take the Explain how reports are made accessible on the Sales Portal course.
Configuring a button to run Report Data Model calculations
Warning
This feature is Feature Flagged and available only for Toronto Dominion HR clients
You can enable a button that lets you efficiently rerun Report Data Model calculations from directly within the report. This is useful in scenarios where report data is not changed through the report, but through external sources. For example, if you have an external source for your target payments and this data changes, it needs to be re-imported into Varicent Incentives. If you have the run Report Data Model calculations button enabled, you can rerun your Report Data Model calculations, it will pick up this newly imported data, and your report will update with the latest results.
In the Reports module, click Create and select Presenter Adaptive.
Select Blank Report and click Next.
Select the option to Create a Presenter Adaptive report that connects to a Report Data Model.
Click Next.
Select the Report Data Model that will connect to your report.
Click Next.
Select the data stores to use for the Report Data Model.
Click Create report.
Add a table object and use the Report Data Model as the source type.
Select the Report data store as a source.
Select columns.
Under Web user options, enable Allow column edit.
Click Column edit settings.
Enable the editable columns to submit the values on Sales Portal.
In the Report Data Model calculations tab, turn on the toggle to Run Report Data Model calculations.
Click + Add columns to select columns that will be used to run the Report Data Model calculations.
Important
The added columns will run according to how they are ordered.
Click Done.
Click Save as then Published.
Click Publish report.
Configuring the report on Sales Portal
In the Admin Client Go to the Portal Access module.
Navigate to Sales Portal and select the report.
In the report on Sales Portal, click Run calculations
.Click Submit to submit the values.