You’re offline. This is a read only version of the page.
14
I have been a developer for many years but new to DAX, please forgive my rookie question :) I have a Dynamics AX 2012 Query which is the data source for a form; the query has three data sources (tables) joined with left outer joins.. I modified each data source in the query to only include the needed fields for the form. After saving the query, I ran trace parser and was expecting the SQL in trace parser to recognize this change in the fields. But the Select statement in the trace still shows all the fields from each of the three tables - the same as before I made the query change.
I have included a couple screenshots for reference.
Any ideas?
Thanks,
Scott
I would start by checking the form query in GUI. Right-click the form, choose Personalize and then switch to the Query tab. You could also add one of the excluded fields to the form, to see whether it gets a value or not.
I said, form data sources don't have any option to select individual fields, so it's possible that it's not supported with AOT queries either. But I don't really know; it's something to test.
Nevertheless there is something else that form data sources support - you can enable OnlyFetchActive property (see Form Data Source Properties).
Also, you could modify the underlying query with code.
Daniele, Thank you for your guidance on this! With our AOT version (DAX 2012), I do not have an option to "Delete elements in cache," but we do have a "Refresh Elements" option which I think does the same thing. I ran that but unfortunately the Select statement still included fields that I had removed. I will check the fields in the joins, as you suggested, and try removing and re-adding the data sources to the query and see if that works. Thanks again for your help, very much appreciated.
Scott
hi
It's possible that the changes you made to the data sources in the query have not been fully reflected in the Select statement generated by the query. This can happen if the query's cache needs to be cleared or refreshed.
To ensure that your changes to the query's data sources are fully reflected in the Select statement, you can try clearing the query cache and running the trace parser again. Here are the steps to do this:
Open the AOT (Application Object Tree) in Dynamics AX.
Expand the "Queries" node, and then locate the query you modified.
Right-click the query and select "Delete elements in cache" to clear the query cache.
Right-click the query again and select "Generate SQL" to regenerate the Select statement for the query.
Run the trace parser again to see if the changes you made to the query's data sources are now fully reflected in the Select statement.
If the Select statement still includes fields that you removed from the data sources in the query, you may need to review the query's join criteria and ensure that you've included all the necessary fields to support the join conditions. You can also try removing and re-adding the data sources to the query to ensure that they're properly linked.
Daniele
Thanks, Martin for your message. I should also share that my goal was to improve performance, including only the needed fields in the AOT query. Modifying this query the same way on the SQL side (SSMS) improved the form load performance from 15 seconds to 2 seconds. Hoping to find a solution for this in AOT.
I would be surprised if it worked, because it's not something that form data sources allows.
I believe that if you use a query to define data sources, the system takes tables and joins into account, but it ignore fields.
Note that you can also see the (primary) form query in GUI.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (