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. 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. The issue will quickly become apparent. The query may look something like this:.
Everything looks fine with the relationships except for one thing: 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.
ItemId is also needed to relate to InventDimCombination. 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.
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:. As you might guess, complex queries may be created using this technique. I hope you found this blog useful. The query may look something like this: Here is the query as shown in SQL Server: 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.
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.More...