AX 2012: Multi-Select Lookup for SSRS Report Dialog

Multi-Select Lookup

Overview:

In this post we’ll learn how to build multi-select lookup for SSRS report dialog. We’ll create an RDP report with an AutoDesign layout. Controller will be used to run the report. An output menu item will be created to point to the controller class. Each AOT element involved will be described in detail. You can guess the complexity of this development task by looking at the AOT elements required for it:

Multi-Select Lookup

Let’s develop them piece by piece…

1. MAKCustTable (Query):

Multi-Select Lookup

1. Create MAKCustTable query.
2. Drag CustTable table to the data sources node.
3. Select the fields as shown in the picture.

 

 

 

 

 

 

 

 

2. TmpMAKParameters (InMemory Table):

Multi-Select Lookup

1. Create InMemory table TmpMAKParameters.
2. Add the fields in the table as shown in the picture.

 

 

 

 

 

 

3. MAKParametersUIBuilder (UI Builder Class):

  • build() method overridden to add dialog field.
  • postBuild() method overridden to register custom lookup method with lookup event.
  • custTableLookup() method gives custom lookup implementation.
  • postRun() method overridden to comment out super() method call.
  • super() is commented to avoid the following exception:
    • RegisterOverrideMethod was called twice for the same object for method ‘lookup’…
class MAKParametersUIBuilder extends SysOperationAutomaticUIBuilder
{
    DialogField     dialogCust;
}

public void build()
{
    MAKParametersContract   contract;

    contract = this.dataContractObject() as MAKParametersContract;
    dialogCust = this.addDialogField(
	methodStr(MAKParametersContract, parmCustAccountList),
	contract);
}

public void postBuild()
{
    MAKParametersContract   contract;

    super();

    contract = this.dataContractObject() as MAKParametersContract;

    dialogCust = this.bindInfo().getDialogField(
        contract,
        methodStr(MAKParametersContract, parmCustAccountList));

    dialogCust.registerOverrideMethod(
        methodStr(FormStringControl, lookup),
        methodStr(MAKParametersUIBuilder, custTableLookup),
        this);

    if (dialogCust)
    {
        dialogCust.lookupButton(2);
    }
}

private void custTableLookup(FormStringControl _control)
{
    Query       query;
    container   conCustTable;

    query = new Query(queryStr(MAKCustTable));

    SysLookupMultiSelectGrid::lookup(
	query,
	_control,
	_control,
	conCustTable);
}

public void postRun()
{
    //super();
}

 

4. MAKParametersContract (Contract Class):

[
    DataContractAttribute,
    SysOperationContractProcessingAttribute(classstr(MAKParametersUIBuilder))
]
class MAKParametersContract
{
    List  custAccountList;
}

[
    DataMemberAttribute("custAccountList"),
    AifCollectionTypeAttribute("custAccountList", Types::String),
    SysOperationLabelAttribute(literalStr("Customers"))
]
public List parmCustAccountList(List _custAccountList = custAccountList)
{
    custAccountList = _custAccountList;

    return custAccountList;
}

 

5. MAKParametersController (Controller Class):

  • showPrintSettings() method overridden to return false to turn off Print Settings field group.
  • showQueryValues() method overridden to return false to turn off default query parameters fields.
  • main() method runs the report.
class MAKParametersController extends SrsReportRunController
{
}

public boolean showPrintSettings()
{
    return false;
}

public boolean showQueryValues(str parameterName)
{
    return false;
}

public static void main(Args _args)
{
    MAKParametersController controller;

    controller = new MAKParametersController();
    controller.parmReportName(ssrsReportStr(MAKParametersReport, AutoDesign));
    controller.parmArgs(_args);
    controller.startOperation();
}

 

6. MAKParametersDP (Data Provider Class):

  • processReport() method adds multiple ranges to the query for multiple customers selected in lookup
[
    SRSReportParameterAttribute(classStr(MAKParametersContract))
]
class MAKParametersDP extends SRSReportDataProviderBase
{
    MAKParametersContract   contract;
    TmpMAKParameters        tmpMAKParameters;
}

[
    SRSReportDataSetAttribute("TmpMAKParameters")
]
public TmpMAKParameters getTmpMAKParameters()
{
    select * from tmpMAKParameters;

    return tmpMAKParameters;
}

public void populateTmpTable(AccountNum _accountNum)
{
    CustTable custTable;

    while select custTable where custTable.AccountNum == _accountNum
    {
        tmpMAKParameters.AccountNum = custTable.AccountNum;
        tmpMAKParameters.CustGroup = custTable.CustGroup;
        tmpMAKParameters.insert();
    }
}

[SysEntryPointAttribute]
public void processReport()
{
    Query                   query;
    QueryRun                queryRun;
    CustTable               custTable;
    QueryBuildDataSource    qbdsCustTable;
    ListIterator            custListIterator;

    contract = this.parmDataContract() as MAKParametersContract;
    custListIterator = new ListIterator(contract.parmCustAccountList());
    query = new Query(queryStr(MAKCustTable));
    qbdsCustTable = query.dataSourceTable(tableNum(CustTable));
    
    while(custListIterator.more())
    {    
        qbdsCustTable.addRange(
            fieldNum(CustTable, AccountNum)).value(custListIterator.value());
        
        custListIterator.next();
    }

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        custTable = queryRun.get(tableNum(custTable));
        this.populateTmpTable(custTable.AccountNum);
    }
}

 

7. MAKParametersReport (SSRS Report):

Multi-Select Lookup

1. Create MAKParametersReport SSRS Report.
2. Create a new RDP dataset.
3. Select MAKParametersDP
4. Drag the dataset to the Designs node.
5. Save, build and deploy the report.

 

 

 

 

 

 

8. MAKParametersReport (Output Menu Item):

1. Create an output menu item MAKParametersReport.
2. Set ObjectType to Class.
3. Set Object to MAKParametersController


We are now good to run the report by clicking the menu item 🙂

Untitled

Untitled

Advertisements

24 thoughts on “AX 2012: Multi-Select Lookup for SSRS Report Dialog

  1. Nice example… but a lot of code for a multiselect dropdown. What is the advantage of creating it this way vs adding a range to a date field in a query. Thanks for the example!

    1. Thanks for reading the post and giving your valuable feedback. Regarding your comment, firstly, this post is not adding date field ranges to the query. Instead adding customer account numbers to the ranges in the query. Secondly, there is big difference between the two approaches.

      Your approach:

      1. User can’t change the query ranges.
      2. User wouldn’t know how the data is getting filtered.

      My approach:

      1. It gives ease of use to User of choosing multiple customers to generate report for. Means query ranges become dynamic.
      2. User definitely knows how the report data is getting filtered.

      Hope this would answer your question.

  2. When you say, user knows how the report data if getting filtered, does that mean the selected values can be added to the report header so the user knows?
    Can’t you change the ranges on a parameter that is created using a range on a query?
    Thanks for the help!!

    1. Yes the selected values can be displayed on the report header as well.

      For your second question, that is what I am doing in this example with a little difference. There are no ranges in the query but controller is dynamically adding ranges to the query based on user preference. This runtime behavior gives a powerful control to User to manipulate the report as per his/her needs.

  3. Hi Muhammad , can the multi-select dialog box in your example be changed to be the same dialog that comes with the out of the box SSRS reports?

  4. Hi Muhammad ,
    My requirement is same as you did before. But i am having an issue ” On Clicking on the LookUp, the Grid rows height is more than a default lookup(without SysLookupMultiSelectGrid)..

    Please let me know if u have any solution for this…
    Thnx in advance

  5. It may be because of the grid contains a field which has more width or height defined. Try identifying the grid controls and their associated EDTs to control the row height. Let me know if it works for you.

    1. Thnx For Ur Rply… the Solution was same which u refer….
      But For now i want just to take two fields For the lookup not all the related fields in look up..
      example : Only Customer Account And Name Fields Should be shown in the lookup
      Can u Suggest anything..

      1. You can decrease the number of fields in the lookup by modifying the lookup query, which is MAKCustTable query in this example. For example, if you need to show only 2 columns in the grid then include only 2 fields in the query.

  6. Hi thanks for your post, i implemented it, but it throws error “Error executing code: ListIterator (object), method new called with invalid parameters.” on below line
    custListIterator = new ListIterator(contract.parmCustAccountList());

    But when i debug the same using SRSReportDataProviderPreProcess, it works fine. But as this is an existing report and its not a regular table, i cannot use SRSReportDataProviderPreProcess.

    Can you please suggest something.

  7. Error 1 An exception occurred in the query metadata execution. The exception is Error executing code: ListIterator (object), method new called with invalid parameters.

  8. Hi Annus,

    Nice to see this blog.One thing i would like to ask you is that i have implemented the whole procedure as mentioned above but i am getting the error when running the report.

    ‘The “TmpDS_CustAccountList” parameter is missing a value’.

    I have given the parameter also but this error is still coming.

    I think the issue might be that the list data type used in AX is not rendered by Visual studio.

    Can you tell me what might cause this issue. i have implemented as it is what you have suggested above.If you have any working project. Can you please send me so that i can check where i am lagging.

    Thanks.

    1. The “XXX” parameter is missing a value might arise when u add/delete/modify your parmXXX methods. Compile your aot Project, get incremental CIL, open VisualStudio, rightclik dataset, refresh, save, compile and deploy. If you still get the error, try to net stop reportserver / net start reportserver. Should be enough.

      And thank you Muhammad Anas Khan for that tech item, time to now customize the lookup fonctions to fit my requirements.

  9. Very good example.
    And very good presentation & explanation.
    I tested it. It works perfectly for a small set of records.
    If I select all records (or a huge set of records) I run to an error in SSRS: Object Server 01:
    Stack trace: Binding operation failed to allocate buffer space.

    Stack trace

    (S)\Classes\QueryRun\next
    (S)\Classes\MAKParametersDP\processReport – line 25
    (S)\Classes\SrsReportProviderQueryBuilder\initialize – line 59
    (S)\Jobs\

    (S)\Classes\QueryRun\next
    (S)\Classes\MAKParametersDP\processReport – line 25
    (S)\Classes\SrsReportProviderQueryBuilder\initialize – line 59
    (S)\Jobs\
    And because the parameter in SSRS report is a “Multi Value = true”, the user has to select in the dialog a value or more.
    How to give the user the opportunity to run the report with all records without a filter. And how to deal with long selection scenario?
    Thanks in advance

  10. Hello, It is good post and helped in some scenarios.
    But when I do the lookup after selected, it doesn’t mark the correct rows previously selected.

    Any suggestions or inputs ?

  11. I am new to AX and SSRS as well. Well written post. Thanks a lot!
    Well I have a scenario here, where I need to develop an employee transactions report. The user will filter the report data based on the Dialog, that has From Date, To Date , Employee personnel number(multiselect lookup) and Currency code.( From Master,again a look up, this time no multi select)
    To get my basics clear, Do I need to have two queries in AOT for each of the lookups? How and where do I write the lookup for Currency Code?

    Thanks.

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