Dynamics AX 2012 Report & Form Filtering Techniques
Posted on: January 19, 2015 | By: Jarrod Kraemer | Microsoft Dynamics AX/365
Dynamics AX 2012 has many ways in which you can modify or filter on a form. One of the ways you can do this is by personalizing a form to pull in fields to display on your home screen that are contained under different tabs, or held in different tables. You can right click and filter by grid which allows you to filter on all columns in a form, filter by selection which allows you to filter on the highlighted record, or filter by field which allows you to filter on a particular column. There is also an advanced filter that can be built and saved that can be retried upon a user’s request.
Behind the scenes in AX, there is a class called SysQueryRangeUtil that can be utilized in both query ranges and table filters. This class contains filtering tools that can be used in conjunction with the standard filtering tools that come out of the box. Using methods from this class allow you to be very precise about what dates you want to use in reports or for filtering your data.
Let’s say you have a report that you always want to run that encompasses any sales order that are past due and up to 2 weeks out. It is possible to do so by using one of the methods from the SysQueryRangeUtil. You can use the lessThandate query to pull the information. Simply enter (lessThandate(X)) where “X” is the amount of days you want to look out.
Note: All methods & expressions must be surrounded by parentheses as shown below.
On any form with a grid, you filter your data by pressing Ctrl+G (Filter by grid shortcut). The best part about this is that you can of course save these filters to create views that you might use on a daily basis.
For Developers these query methods can be used in your code, these methods can also be utilized whenever you are setting them. I listed some of the SysQueryRangeUtil classes below. As you can see there are many that can be played with.
Here are some of them:
currentCustomerAccount()
currentVendorAccount()
currentUserId()
currentDate()
dateRange()
day()
dayRange()
greaterThanDate()
greaterThanUtcDate()
greaterThanUtcNow()
lessthanDate()
lessthanUtcDate()
lessthanUtcNow()
monthRange()
yearRange()
For additional information please feel free to reach out to us at info@loganconsulting.com or (312) 345-8817.
All the best!
Logan Consulting
www.loganconsulting.com