Tuesday, July 30, 2013

Custom Query Range Functions using SysQueryRangeUtil

You've probably seen these requests before. Users want to submit some report or other functionality to batch, and the query should always be run for "yesterday". It's a typical example where, as a user, it would be handy to be able to use functions in your query range. Well, you can. And in fact, you can make your own, very easily!

Enter the class "SysQueryRangeUtil". All it contains is a bunch of static public methods that return query range values. For example, there is a method called "day" which accepts an optional integer called "relative days". So, in our example of needing a range value of "tomorrow", regardless of when the query is executed, you could use day(-1) as a function. How this works in a range? Just open the advanced query window and enter your function call within parentheses.

Let's make our own method as an example. Add a new method to the SysQueryRangeUtil class, and enter the following, most interesting code you've ever encountered.

public static str customerTest(int _choice = 1)
{
    AccountNum accountNum;
    
    switch(_choice)
    {
        case 1:
            accountNum = '1101';
            break;
        case 2:
            accountNum = '1102';
            break;
    }
    
    return accountNum;
}


So, this accepts an options parameter for choice. If choice is one (or choice is not specified), the function returns 1101, if 2 it returns 1102. Save this method and open a table browser window on the CustTable table. Type CTRL+G to open the grid filters. In the filter field for the AccountNum field, enter: (customerTest(1)).


So, the string returned from the method is directly put in the range. So, you could do all sort of interesting things with this, of course. Check out some of the methods in the SysQueryRangeUtil as examples.

7 comments:

  1. This has been available since AX 2009, and I did not know it. Thanks for the tip.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. I did not know this either. This will save a lot of headaches. However, it seems it will not work in Date type input fields, so we wouldn't be able to use it on report that use those for parameters (for example, the Trial Balance report).

    ReplyDelete
    Replies
    1. Not sure what you mean that it won't work on Date type fields. There are examples in the util class already. Since this is directly applied to the QueryBuildRange.value it's meant to be a string, so you can return anything, for example "1/1/2012..1/1/2013", which is a string but applied to a date field as a range...

      Delete
  4. Joris, your posts are extremely helpful! I was hoping you have an example of passing Multi Value parameters from a SSRS Report using an RDP, through a DataMemberAttribute to be used in a query.

    ReplyDelete
    Replies
    1. RDP Contract class declaration you must use a data type 'List':

      [
      DataContractAttribute
      , SysOperationGroupAttribute('Dimension', '@XXX2', '1')
      , SysOperationGroupAttribute('Project', '@XXX2', '2')
      ]
      public class exampleProjectContract
      {
      List leadProjectId;
      }

      RDP Contract class parm:

      [
      DataMemberAttribute('leadProjectStatus')
      ,AifCollectionTypeAttribute('leadProjectStatus', Types::String) // This is the part needed to make it show as multi value in VS, obviously set type the whatever the type if not a string
      ,SysOperationLabelAttribute('@XXX2')
      ,SysOperationDisplayOrderAttribute('1')
      ,SysOperationGroupMemberAttribute('Project')
      ]
      public List parmLeadProjectId(List _leadProjectId = leadProjectId)
      {
      leadProjectId = _leadProjectId;
      return leadProjectId;
      }

      In your Data Provider class you can then reference it like this in your proccess report:

      str projId;
      List ProjIds;
      ListEnumerator ProjIdsE;

      dataContract = this.parmDataContract();

      ProjIds = new List(Types::String);
      ProjIdsE = new ListEnumerator();
      ProjIds = dataContract.parmLeadProjectId();
      ProjIdsE = ProjIds.getEnumerator();

      ttsbegin;
      while (ProjIdsE.moveNext())
      {
      projId = ProjIdsE.current();
      ...


      Note: A major downfall of parameters in SSRS is that you can't have a data type of int64 which is what recIds are so i've been using string type in SSRS and converting back to an int64 in the data provider class when needed.

      Delete
  5. Hey Joris, awesome post, thanks, this will come in handy on day.

    ReplyDelete