How to setup fromDate and toDate range in SSRS AX2012

Setup date range in SSRS reports in AX 2012 

Private void getReportParameter()
{
    msPlanningScheduleContract contract = this.parmDataContract();

    isValid = true;

    if (contract)
    {
        salesStatus         = contract.parmSalesStatus();
        fromShippingDate    = contract.parmFromShippingDate();
        toShippingDate      = contract.parmToShippingDate();
        fromReceiptDate     = contract.parmFromReceiptDate();
        toReceiptDate       = contract.parmToReceiptDate();
        isValid             = contract.validate();
    }
}
================================================================

[ SysEntryPointAttribute() ]
Public void processReport()
{
    ProdTable       prodTable;
    SalesLine       salesLine;
    Query           query = this.parmQuery();
    QueryRun        queryRun;

    this. getReportParameter();

    if (fromShippingDate || toShippingDate)
    {
        query.dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(salesLine, ShippingDateConfirmed)).value(queryRange(fromShippingDate, toShippingDate));
    }
    if (fromReceiptDate || toReceiptDate)
    {
        query.dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(salesLine, ReceiptDateConfirmed)).value(queryRange(fromReceiptDate, toReceiptDate));
    }
    if (salesStatus)
        query.dataSourceTable(tableNum(SalesLine)).addRange(fieldNum(salesLine, SalesStatus)).value(SysQuery::value(enum2str(salesStatus)));

    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        salesLine   = queryRun.get(tableNum(SalesLine));
        prodTable   = queryRun.get(tableNum(ProdTable));

        this.insertmsPlanningScheduleTMP(salesLine, prodTable);
    }
}
================================================================

How to Set the Query Range on a SSRS Report

If the SSRS report is build with AOT query

SRSReportRun reportRun = new SRSReportRun(‘Report1.AutoDesign1’);

// Create variables for setting the range for the query.

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

int i;

// Check if the query will return data.

if (reportRun.init())

{

// Find the Cust query.

query = reportRun.reportQuery(‘Cust’); // query name of the report

if (query != null)

{

// Get the Customers data source.

queryBuildDataSource = query.dataSourceName(‘Customers’);

if (queryBuildDataSource != null)

{

queryBuildRange = queryBuildDataSource.addRange(fieldName2Id(queryBuildDataSource.table(), ‘AccountNum’));

queryBuildRange.value(‘4000’);

}

}

// Save the report settings.

reportRun.saveSettings();

// Run the report.

reportRun.run();

}

If the report is build with Data contract

Here is an example which comes from the AOT – Classes – CustPamnManOutputReportController – preRunModifyContract method:

protected void preRunModifyContract()

{

Query reportQuery;

SrsReportRdlDataContract rdlContract;

rdlContract = this.parmReportContract().parmRdlContract(); // may be some changes require here if the report is 

running from some job.

if (rdlContract.getValue(#reportParameter))

{

custPaymManFile.reportDate(systemdateget());

custPaymManFile.status(PaymManRemittanceStatus::Sent);

}

this.processReport(custPaymManFile);

reportQuery = new Query(querystr(CustPaymManOutputReport));

SRSReportHelper::addParameterValueRangeToQuery(

reportQuery,

tablenum(TmpPaymManOutputReport),

fieldnum(TmpPaymManOutputReport, SessionId),

this.currentSessionId());

this.parmReportContract().parmQueryContracts().insert(queryKey, reportQuery);

}

===================================================================================

Query to get data less than date in ax 2009/ ax 2012

X++ Code to get the Ranges / Criteria from Query

 

Following Job illustrates how we can get the criteria / ranges specified by the user in the run-time Query 

criteria dialog.

static void Query_getRanges(Args _args)

{

    Query                   query = new Query();
    QueryRun                queryRun;
    QueryBuildDataSource    qbd;
    CustTable               custTable;
    QueryBuildRange         range;
    int                     cnt, i;
    ;

    qbd = query.addDataSource(tablenum(CustTable));

    queryRun = new QueryRun(query);

    queryRun.prompt();   // To Prompt the dialog

    cnt = queryRun.query().dataSourceTable(tablenum(CustTable)).rangeCount();

//custGroup = this.query().dataSourceTable(tablenum(CustInvoiceJour)).range(1).value();

    for (i=1 ; i<=cnt; i++)
    {
        range = queryRun.query().dataSourceTable(tablenum(CustTable)).range(i);
        info(strfmt("Range Field %1, Value %2",range.AOTname(),range.value()));
    }

    while (queryRun.next())
    {
        custTable = queryRun.get(tablenum(CustTable));
        info(strfmt("Customer %1, Name %2",custTable.AccountNum, custTable.Name));
    }
}

 

Query to get data less than date

 

private Query buildQuery(Query _query,StartDate _date)
{
    if(!tDate)
        tDate = systemDateGet();
    if(tDate)
    {
        _query.dataSourceTable(tablenum(DNemplContract)).addRange(fieldnum(DNemplContract, StartDate)). 

                                                                         value(strFmt("<= %1", tDate));

      //  _query.dataSourceTable(tablenum(DNemplContract)).addRange(fieldnum(DNemplContract, //StartDate)).

                                                                         value(queryValue(tDate));
 
    }
        return _query;
}

=========================================================================

Add Date range to query x++ ax 2012

query.dataSourceTable(tablenum(BudgetSourceTracking), 1).addRange(

fieldnum(BudgetSourceTracking, TrackingDate)).value

(queryRange(fiscalPeriodStartDate, fiscalPeriodEndDate));

==================================================================

AX2012 Date Ranges using Query Service

Before using this sample please refer this link that which I posted earlier.
http://krishhdax.blogspot.com/2012/02/ax2012-consume-query-service.html 

public void krishh_QueryServiceDateRangeFilter()
   {
      QueryServiceClient client = new QueryServiceClient();
      DataSet dataSet;
      Paging paging = new ValueBasedPaging() { RecordLimit = 1000 };
      QueryMetadata query;
      QueryDataSourceMetadata employeeDataSource;
      QueryDataRangeMetadata employeeRange;
      query = new QueryMetadata();
      // Set the properties of the query.
      query.QueryType = QueryType.Join;
      query.DataSources = new QueryDataSourceMetadata[1];
      // Set the properties of the EmployeeDataSource
      employeeDataSource = new QueryDataSourceMetadata();
      employeeDataSource.Name = "Employee";
      employeeDataSource.Enabled = true;
      employeeDataSource.Table = "HcmEmployment";
      //This property will set for Dataset to Retrieve all fields 
      employeeDataSource.DynamicFieldList = true;   
      // Add the data source to the query
      query.DataSources[0] = employeeDataSource;
      // filter for active employees only
      employeeRange = new QueryDataRangeMetadata();
      employeeRange.TableName = "HcmEmployment";
      employeeRange.FieldName = "ValidTo";
      employeeRange.Value = "31/12/2154 11:59 PM";
      employeeRange.Enabled = true;
      employeeDataSource.Ranges = new QueryDataRangeMetadata[1];
      employeeDataSource.Ranges[0] = employeeRange;
      // Exeucte the Query and load the data into the Dataset
      dataSet = client.ExecuteQuery(query, ref paging);
     // load your dataset to your grid or gridview.
    }

Happy DAXing.....