By Bill Concepcion, Fullscope Technical Consultant
In my experience, I found instances where a Microsoft Dynamics AX developer created a custom SSRS report in AX using a data provider where the data provider is using a simple query and one or more subqueries. When I ask, “Why not create the query on the Application Object Tree (AOT) and use the query for the report instead of creating a data provider?” the answer I receive is that one or more data sources in the query must relate to multiple data sources in the query. The thought is either AX query objects from the AOT do not support a data source to relate to multiple data sources, or the developer just didn’t know how to do it. That’s why I decided to write this blog.
Let’s consider a scenario to illustrate the issue. Let’s say we have the following custom table:
Now suppose that we need to create a query object on the AOT that will cross-reference Table1 with product variants and get the Variant ID. Product variants are contained in the InventDimCombination table. Let’s start with the query. The issue will quickly become apparent. The query may look something like this:
It’s possible to have this query start with Table1 and work our way down to InventDimCombination but I chose to start with InventDimCombination. Everything looks fine with the relationships except for one thing: the relationship between Table1/InventDim_2 is not sufficient to correctly relate to InventDimCombination/InventDim_1. Currently, all items in Table1 that happen to have the same variant dimensions will match with the current InventDimCombination record creating a one-to-many relationship, which is undesirable. Table1.ItemId is also needed to relate to InventDimCombination.ItemId.
How can this be done? Well, in regards to the Relations node, it is not possible to relate to more than one data source under a single node. Yet it is needed to get Table1.ItemId to match with InventDimCombination.ItemId. The multiple Table1 records that will potentially match can be filtered by creating a range.
A range is created to filter the Table1 records that are matching by dimension to include only those records that also match the current variant item. The resulting subquery is shown through the tooltip. Here is the query as shown in SQL Server:
This technique appears not to be widely known or used. Perhaps, you may have tried this technique and got an error and gave up. There are two things needed to make this technique successful:
- The comparison must be surrounded in parentheses. If there are multiple comparisons through ORs or ANDs, then each comparison must be enclosed in parentheses and the entire expression must be enclosed in parentheses. For example, ((datasource1.field1 == datasource2.field1) && (datasource1.field2 > datasource3.field2)).
- As implied by the example with #1 above, the references in the range are data source names and not table names, although a data source name might be the same as a table name.
As you might guess, complex queries may be created using this technique. For one customer, I used this technique for a query that resulted in a dozen subqueries for a custom report and a data provider wasn’t needed.
I hope you found this blog useful. Shoot me an email at [email protected] with any specific comments or questions.