A constraint-based configurator was shipped with MicrosoftDynamics AX 2012. In this post, we will provide an overview of the data modelthat is used to persist the values that you can select when you configure aproduct using the constraint-based configurator.
At first glance, you will notice that there are severaltables involved. If you are not familiar with previous versions of Dynamics AXand the Product Builder configurator, this might not surprise you. However, ifyou are familiar with the Product Builder, you will know that the oldconfigurator persisted all user selections in one table, namely thePBATableInstance Table. This implies that retrieving the user selected valuesis not as simple as writing a select statement against a single table. In thefollowing, I will use a report as an example and show you how you can retrieve userselections in tables.Building a report
The report** that we use as an example displays all the userselected values that are related to the configurations for a given product configurationmodel.
**Disclaimer:The report is not performance optimized. The purpose of the report is solely toillustrate traversal of the data structure.
In the report, each element is indented to show theparent-child relationship between, for example, the HomeTheaterSystem rootcomponent and the “Color” attribute. The “Color” attribute can be assignedother values, such as “Red”, but in the data on which the report was executedno such configuration existed.
Note: I have used the Contoso dataset.
The data required to build the report can be broken downinto three parts:
Let’s take a look at how to retrieve the data requirements tobuild the report. The following statements will meet the data requirements and youcan also see the table involved:
protected void provideDataForModel(PCProductConfigurationModel _productConfigurationModel)
// get all configurations made for the given product configuration model
while select componentInstance
// only get instances which belong to a variant configuration, not a configuration template
join TableId from variantConfiguration
where variantConfiguration.RecId == componentInstance.ProductConfiguration
// find the root component instances of the selected model
join TableId from rootComponentInstance
where rootComponentInstance.ComponentInstance == componentInstance.RecId
where rootComponent.RecId == rootComponentInstance.RootComponentClass
&& rootComponent.RecId == _productConfigurationModel.RootComponentClass
// handle data for component
// get all attribute value assignments related to the component instance
while select value
join TableId from attributeValue
where value.RecId == attributeValue.Value
join TableId from componentInstanceValue
where componentInstanceValue.RecId == attributeValue.InstanceValue
&& componentInstanceValue.ComponentInstance == _componentInstance.RecId
where attribute.RecId == attributeValue.Attribute
// handle data for attribute and attribute value
The values as such are sub-typed to model their respective data type.
// get all the subcomponents that have been associated with values
while select component
join TableId from subcomponent
where subcomponent.ParentComponentClass == _parentComponent.RecId
&& subcomponent.ChildComponentClass == childComponent.RecId
join TableId from subComponentInstance
where subComponentInstance.SubComponent == subcomponent.RecId
&& subcomponentInstance.ParentComponentInstance == _componentInstance.RecId
where componentInstance.RecId == subComponentInstance.ChildComponentInstance
// handle data for component
The data in the report is hierarchical in nature because ofthe relationship between components and subcomponents.
The report that we build includes the parent-childrelationship between a component and a subcomponent and it also includes the parent-childrelationships between, for example, anattribute and an attribute value. This data cannot be retrieved from DynamicsAX using a single queryso we will use a data provider to populate a temporary table. The table has thefollowing structure:
This is not completely true because in Dynamics AX 2012, each component isbrought about as a reference to the configured variant. Thus, you couldretrieve all the data and then resolve the parent/child relations once the datais retrieved.
A quick explanation of why the fields are required:
For more information about how to build a simplehierarchical report in SSRS, go to:
For details on the implementation, take a look at theattached X++ project.