Dynamics Ax Query Extended Range Value Expression

Dynamics Ax Query Extended Range Value Expression

In this article we will see how to define extended query ranges using expressions. These expressions can be used in any query where you need to express a range that is more complex than is possible with the usual range value notations.

The rules for creating query range value expressions are:
  • Enclose the whole expression in parentheses.
  • Enclose all subexpressions in parentheses.
  • Use the relational and logical operators available in X++.
  • Only use field names from the range's data source.
  • Use the dataSource.field notation for fields from other data sources in the query.
  • Values must be constants in the expression, so any function or outside variable must be calculated before the expression is evaluated by the query. This is typically done by using the strFmt function.
Let's do it with some examples:

OR clause on same field:
    Query q;
    QueryBuildDataSource qbd;
    QueryBuildRange qbr;
    q = new Query();
    qbd = q.addDataSource(TableNum(CustTable));
    qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
    qbr.value('4005, 4006');

Result: SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))

or use this expression to achieve the same...

    qbr.value(strFmt('((AccountNum == "%1") || (AccountNum == "%2"))',
            QueryValue('4005'),
            QueryValue('4006')));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))

OR clause on different fields: In this example we are using DataAreaId field to obtain range object but actual range is on AccountNum and Name. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. The field you use to get range object is not included in the query.  I usually use DataAreaId to denote that this is a special range.

    qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));
    qbr.value(strFmt('((%1 == "4000") || (%2 == "The Bulb"))',
            fieldStr(CustTable, AccountNum),
            fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Bulb"))))

The above result can also be obtained with this below example. The only difference is we are using DataSource name as well. This will also give you some idea on how to use expressions when more than one DataSources are involved.

    qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));
    qbr.value(strFmt('((%1.%2 == "4000") || (%1.%3 == "The Bulb"))',
            qbd.name(),
            fieldStr(CustTable, AccountNum),
            fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((CustTable_1.AccountNum == "4000") || (CustTable_1.Name == "The Bulb"))))

Query range value expressions are evaluated only at run time, so there is no compile-time checking. If the expression cannot be understood, a modal box will appear at run time that states "Unable to parse the value."


Feel free to post any comment / feedback here.

RDP, Contract, UI Builder and Controller classes for SSRS report Development

Without going into more details over individual classes (RDP, Contract, UI Builder and Controller) I am writing this post to develop an SSRS report using all these classes (some of them are optional). Let’s find out how?

RDP Class
Details of this class are here;

Create a new class in AOT, I have named it FF_ReportDP and extends it from SRSReportDataProviderBase


SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.
SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.
NOTE: Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.

Add a new method and name it getFF_ReportReportTmp. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data. The SRSReportDataSetAttributeattribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.

  








Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.


Initialize and retrieve contract class parameters in processReport method to get filtered records from tables based on parameter inputs. e.g. Customer account is added as a range to retrieve data only for selected customer.
Tip: For better performance RecordInsertList is used in processReport method to write data into database in one call.

Contract class

Create a new class and name it FF_ReportContract
In this class I have added two parameters Company and CustomerAccount which can be used to filter data on report either passing values from a menuitem or from a class or while calling report from a form. I will address this later in this or next post.

It will tell the contract class to build the parameter dialog. In other words, it will link the UI Builder Class with the contract class.



Parameter methods: 











UI Builder Class

Details of this class are here;

Create a new class and extends it from SysOperationAutomaticUIBuilder







Override build method to create own dialog box







Override postBuild method

New method to create custom lookup of all companies in AX

Controller class


Create a new class and extends it from SrsReportRunController








Create a new Main method to call this class from a menu item which will internally call SSRS report

  
Add a new output menu item in AOT with name FF_ReportController with propertied Object type = Class and Object = FF_RerportController.
Run menu item and this will be the output dialog screen
  





Accounts receivable group parameters are three ranges added in CustBalanceList query which is used in this report example.

To hide these group parameters override showQueryValues method and return false.



  






After hiding parameters












Using Controller Class in Developing SSRS Reports in Microsoft Dynamics AX 2012

Overview

Controller class is used to control the report execution as well as preprocessing of the report data. The SSRS reporting framework uses this class to modify the report dialogs, calling the SQL Server reporting services, as well preprocessing parameters for the report.
Following are the scenarios where Controller class can be used:
  1. Modifying a report query based on the input data
  2. Modifying report contract data based on the input data
  3. Control a report parameters dialog
  4. Open different reports/designs from the same menu item based on the input data
  5. Reports that are opened from a form
To create a controller class, extend it with SrsReportRunController.

Prerequisites

  1. Microsoft Dynamics AX 2012
  2. Reporting services extensions must be installed in Dynamics AX

Sample Controller Class

  1. Create a new class. Open AOT → Classes
  2. Right Click on Classes and select New Class. Name it as SSRSDemoController.
  3. example of creating a new class in Dynamics AX
     
  4. Open the Class declaration by right clicking on it and selecting View code.
  5. example of viewing code in AOT class AX
     
  6. Now write the following code:
  7. Create a new method and write the following code:
  8. Examples of Controller Class Usage

    Based on different scenarios, different methods are overridden as shown in the following examples:
    1. Modifying report query based on the input data

      • Used in those scenarios where a report query needs to be modified based on the caller args parameters or recorded before the report parameter dialog is rendered.
      • Override prePromptModifyContract method to modify the report query as shown below:
      Note: prePromptModifyContract is called by report controller before the parameter dialog is shown to the User.
    2. Modifying report contract data based on the input data

      • Used in those scenarios where report contract parameters need to be modified based on the caller args prior to the execution of the report.
      • Override preRunModifyContract method to modify the report contract as shown below:
      Note: preRunModifyContract is called by report controller before the report is run.
    3. Control report parameters dialog

      • In some scenarios, a report parameter dialog should not be visible to the end user. Controller class is also used to control the visibility of the report parameter UI.
      • Add the following code in the main method of the controller class before startOperation method call to hide/show the report parameter UI:
    4. Open different reports from the same menu item based on the input data

      • It is used in those scenarios where different reports or different designs of a same report need to be opened from a same menu item depending upon the caller args.
      • Write the following code in main method to achieve this scenario:
    5. Reports that are opened from a form

      • Controller class is also used when reports are opened from a form and are needed to show selected record details.
      • Use either prePromptModifyContract method or preRunModifyContract method to achieve this scenario.