Dialog fields and runtime lookups on SSRS Report parameters [Dynamics AX 2012]

Friends,

In this post, we will learn how to add dialog fields and get run time lookups on the dialog field in AX 2012 SSRS reports. In Dynamics AX 5.0, we used to get this by overriding dialog, dialogpostrun, controlMethodOverload, Field_runTimeFieldId_lookup/modified methods  etc.

In AX 2012 there is a class by name “SrsReportDataContractUIBuilder” to serve the same purpose.

Example : In the below screen shot, I have added dialog field “Filter by Cust” and in the lookup it will only display the customers who belongs to

customer group “20”.

image

Interesting right, now lets see how to accomplish this:

Create a new Query by name “SRCustTable” and add data source as “CustTable” as shown below

image

Then create a new temporary Table by name TmpSRSalesTable and add 2 fields CustAccount and SalesId fields as shown below.

 

image

Now, we need to create contract classes, DP class and Builder class as shown below.

Follow the below classes and methods.

SRCustomLookupUIBuilder Class:

Create a new class by name SRCustomLookupsUIBuilder that should extend SrsReportDataContractUIBuilder as shown below

class SRCustomLookupsUIBuilder extends SrsReportDataContractUIBuilder

{

      DialogField   dialogAccountNum;

      DialogGroup   dialogGroup;

      boolean       enable;

 

}

The below accountNumLookUp method will help to get the runtime lookup based on the query defined in the code [customer group – 20]

private void accountNumLookup(FormStringControl accountNumLookup)

{

    Query                   query = new Query();

    QueryBuildDataSource    qbds_CustTable;

    SysTableLookup          sysTableLookup;

    QueryBuildRange         qbr;

 

    if (accountNumLookup != null)

    {

        // Create an instance of SysTableLookup with

        // the current calling form control.

 

        sysTableLookup = SysTableLookup::newParameters(tablenum(CustTable), accountNumLookup);

        //sysTableLookup.addLookupMethod(

        // Add fields to be shown in the lookup form.

        qbds_CustTable = query.addDataSource(tableNum(CustTable));

        sysTableLookup.addLookupfield(fieldnum(CustTable, AccountNum), true);

        sysTableLookup.addLookupfield(fieldnum(CustTable, CustGroup),false);

 

        qbr = qbds_CustTable.addRange(fieldNum(CustTable,CustGroup));

        qbr.value(’20’);

        sysTableLookup.parmUseLookupValue(false);

 

        sysTableLookup.parmQuery(query);

 

        // Perform the lookup.

        sysTableLookup.performFormLookup();

 

    }

}

/// <summary>

///    Builds the dialog.

/// </summary>

/// <remarks>

///    The dialog appears with the parameters.

/// </remarks>

 

public void build()

{

    SRCustomLookUpContract rdpContract =  this.dataContractObject();

 

    dialogAccountNum = this.addDialogField(methodstr(SRCustomLookUpContract,parmAccountNum),rdpContract);

    dialogAccountNum.lookupButton(2);

}

public void postRun()

{

    Dialog dialogLocal = this.dialog();

    DialogField dialogField;

 

    super();

 

    // This method should be called in order to handle events on dialogs.

    dialogLocal.dialogForm().formRun().controlMethodOverload(false);

 

    // Override the methods of department  field.

    dialogField = this.bindInfo().getDialogField(this.dataContractObject(), methodstr(SRCustomLookUpContract, parmAccountNum));

    dialogField.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(SRCustomLookupsUIBuilder,  accountNumLookup), this);

 

}

Note : we can even use intializeFields and getfromdialog overridden methods to initialize values and get the values from dialog.

Contract class

 

[DataContractAttribute,

SysOperationContractProcessingAttribute(classstr(SRCustomLookupsUIBuilder))

]

class SRCustomLookUpContract

{

    AccountNum  accountNum;

 

 

}

Add parmAccountNum method as shown below

[DataMemberAttribute(‘AccountNum’)

    ]

public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)

{

    accountNum = _accountNum;

    return accountNum;

}

 

DataProvider class

[

    SRSReportQueryAttribute(queryStr(SRCustTable)),

    SRSReportParameterAttribute(classStr(SRCustomLookUpContract))

 

]

class SRCustomLookupDP extends SRSReportDataProviderBase

{

 

    SRCustomLookUpContract contract;

    TmpSRSalesTable        tmpSRSalesTable;

}

/// <summary>

/// executes the logic based on the parameter entries

/// </summary>

/// <remarks>

/// fills up the temp table

/// </remarks>

[SysEntryPointAttribute]

public void processReport()

{

    Query query;

    QueryRun qRun;

    QueryBuildRange qbr;

    CustTable       custTable;

 

    contract = this.parmDataContract() as SRCustomLookUpContract;

    query =  this.parmQuery();

 

 

    qbr = query.dataSourceNo(1).addRange(fieldNum(CustTable, AccountNum));

    qbr.value(contract.parmAccountNum());

 

    qRun = new QueryRun(query);

 

    while(qRun.next())

    {

        custTable = qRun.get(tableNum(custTable));

        this.insertInToTempTable(custTable.AccountNum);

    }

}

public  void insertInToTempTable(AccountNum _accountNum)

{

    SalesTable salesTable;

    ;

    while select salesTable where salesTable.CustAccount == _accountNum

    {

       tmpSRSalesTable.CustAccount = _accountNum;

       tmpSRSalesTable.SalesId     = salesTable.SalesId;

 

       tmpSRSalesTable.insert();

    }

 

}

[

    SRSReportDataSetAttribute(‘TmpSRSalesTable’)

]

public TmpSRSalesTable getTmpSRSalesTableDetails()

{

    select * from tmpSRSalesTable;

 

    return tmpSRSalesTable;

}

We are done with all the classes and I have already posted in my earlier blogs how to add this data provider classes as a dataset data source. Follow the same process and create a new SSRS report in visual studio 2010 and save the report to AOT and deploy as well.

Once you invoke the report from within AX, you will get the parameters from as shown below and runtime lookup clearly shows all the customers who belong to customer group 20. Click on Ok button.

image

You should see the result as shown below: you can use layout and table style templates for beautification.

image

 

That’s it for now.

Good day!

Happy Dax6ng,

Sreenath Reddy

Advertisements

10 Responses to “Dialog fields and runtime lookups on SSRS Report parameters [Dynamics AX 2012]”

  1. Muthusamy Says:

    How to get the runtime lookup in dialogfield without hotcoding the value in AX2012 SSRS report

  2. Muthusamy Says:

    In my report there is two dialogfield , both are multiselect.

    How to get runtime lookup ,which means the second dialogfield lookup value is only related to first dialogfield selected value
    instead of all values.

    Thanks

  3. Kanagu Says:

    Hi,

    I need your help! It’s urgent

    “I have created 2 dialog field both are multiselect. Dialog Fields are working fine.BUt my requirement is to filter 2nd dialog field based on 1st Dialog Field.”

    Please help me!

  4. Cata Says:

    After following all the steps, the look-ul works, but in the processreport() I am not getting any values for the parameters … any toughts on that ?

  5. Cata Says:

    Got It … great tutorials !

  6. Kanagu Says:

    Hai ,
    I need another one help.
    That is,

    I have created a Report using SSRS. In my Report 2Fields are Multiselect another one is also multiselect but that is ENUM Field. I have done using ‘enumstr’.
    My problem is the enum Dialog field stores only Integer values(ENUM Values) instead of String Vales(Enum Name).

    If you have any idea please reply me.

    Thank You,

  7. Howard Webb Says:

    Hi Cata,

    How did you solve that issue as I have the same.

    Thanks
    -Howard

  8. mohammadfasi Says:

    Very Good Post


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: