Some time ago I run into following question regarding reporting development in Visual Studio 2008

To add a query to AxQuery you pass in a Dictionary object, but our range needs to be equivalent to:

select * from InventTrans where DatePhysical < _paramDate && (DateFinancial == datenull() || DateFinancial > _paramDate)

The problem is on how to have the OR on DateFinancial field. Also how do you do a between range, you can only have one field in the dictionary which seems to only allow ..date or date..

To solve this problem I did following:
1. I've created class in AOD called InventTransReport with following method:\

public InventTrans returnData(str parmDate)
{
      InventTrans InvTr;
      date parm;
      ;
      parm = str2date(parmDate,123);
      select * from InvTr where (invTr.DateFinancial < parm) && (invTr.DateFinancial == str2date("",123) || invTr.DatePhysical > parm);
      return InvTr;
}

 

2. In Visual Studio I’ve created new project using Dynamics AX Reporting Project

3. I've created following dataMethod


[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static DataTable ReportInventTrans(string paramDate)
{
      // Call AX to get the report data
      AxaptaWrapper ax = SessionManager.GetSession();
      AxaptaObjectWrapper axClass = ax.CreateAxaptaObject("InventTransReport");
      AxaptaRecordWrapper axRecord = ax.CreateAxaptaRecord(axClass.Call("returnData", paramDate));
      // Prepare the DataTable structure DataTable resTable = new DataTable();
      resTable.Locale = CultureInfo.InvariantCulture;
      resTable.Columns.Add("ItemID", typeof(String));
      resTable.Columns.Add("DatePhysical", typeof(DateTime));
      resTable.Columns.Add("DateFinancial", typeof(DateTime));
      while (axRecord.Found)
     {
          resTable.Rows.Add(axRecord.GetField("ItemID"), axRecord.GetField("DatePhysical"), axRecord.GetField("DateFinancial"));
          axRecord.Next();
     } 
     return resTable; 
     
}

4. I’ve added new DataSet to my Report and set up following properties:

Data Source Type: Business Logic

Query: ReportInventTrans

 

The report worked correctly