Creating multi-select lookup dialog for SSRS report parameter in MS Dynamics AX 2012

Hello All,
I just recently found a way to create a multi select lookup
dialog for SSRS report parameter using SysLookupMultiSelectGrid
class. I thought it might be helpfull for other developers
as well since I couldn’t find any other Blog or Post answering this issue.

I am just assuming that you know how to create a simple dialog
for reporting parameter. However, if you don’t know this link might be helpfull to you.

https://erpdax.wordpress.com/2014/07/11/10/

I will just write those methods which will create a multi select
dialog. So, here we go.
Step 1:
In your data contract class declaration method, define your
parameter with ‘List’. For example I want to create a multi select dialog for
customers in which I need Customer account to be selected when a user selects
any customer. SO, I will write

List accountNum;

In your DataMemberAttribue method type
the following code

[
DataMemberAttribute('AccuontNum'),
AifCollectionTypeAttribute('AccountNum', Types::String),
SysOperationLabelAttribute(literalstr("@SYS302"))
]
public List parmAccountNum(List _accountNum = accountNum)
{
  accountNum = _accountNum;
  return accountNum;
}

Your screen will look like this

1

Step 2:
Now that you have completed the contract class, let’s move on to
the UI Builder class. In your main lookup method write the following code.

public void lookup(FormStringControl _control)
{
  Query query = new Query(queryStr(CustTableSRS));

  container cnt;
  SysLookupMultiSelectGrid::lookup(query, _control, _control, cnt);
}

you may have to create 3 more methods to run your code without any
error. They are getFromDialog, initializeFields
and postRun. Here is the code for these methods. you have to change the contract class name with your
contract class
First create a new mothod for initializeFields and paste the following code

public void
initializeFields()
{
  custMultiSelectContract contract = this.dataContractObject();
}

Then create another method for getFromDialog

public void getFromDialog()
{
  custMultiSelectContract contract = this.dataContractObject();
  super();
}

and then another method for postRun

public void postRun()
{
  custMultiSelectContract contract = this.dataContractObject();
}

That’s it. You are now done. Run your report and enjoy.
Thanks

Advertisements

Business Unit Lookup in Form

Just add the below code in lookup() of StringEdit control in Form to get the Business Unit Lookup:

public void lookup()
{
Query query;
SysTableLookup sysTableLookup;
super();

//DimAttributeOMBusinessUnit is a View
sysTableLookup = SysTableLookup::newParameters(tableNum(DimAttributeOMBusinessUnit), this);

sysTableLookup.addLookupfield(fieldNum(DimAttributeOMBusinessUnit, Value));
sysTableLookup.addLookupfield(fieldNum(DimAttributeOMBusinessUnit, Name));

query = new Query();

query.addDataSource(tableNum(DimAttributeOMBusinessUnit));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

How to select multiple selected values (records) from lookups in Dynamics AX

Create a Query named StudentCourse that contains 2 darasources say, Student & Course.
. Here Course field in Student table acts as a foreign key.
Use only those field that you want to show on the form or lookup.
Now create a new form named MultiSelectLookupCtrl and add a StringEdit control named as TestCtrl in the design node.Set its AutoDeclaration property to “Yes” so that we can access this control from X++ code.
Now override the following methods and write the following code.
In the ClassDeclaration of the form write the below code.

public class FormRun extends ObjectRun
{
SysLookupMultiSelectCtrl msCtrl;
}

Override the init method of the form and place the below code

public void init()
{
super();
// TestCtrl – Name of control on which you want a lookup.
// StudentCourse – Query to get the lookup data
msCtrl = SysLookupMultiSelectCtrl::construct(element, TestCtrl, querystr(StudentCourse));
}

That’s it, Now let’s see how the selected rows are returned from the lookup.

To get the values and RecId of the selected rows, simply override the modified method of the TestCtrl and add the below code.

public boolean modified()
{
boolean ret;

container c,v;
int i;
ret = super();
if (ret)
{
c = msCtrl.get();  // get RecIds of the selected rows
v = msCtrl.getSelectedFieldValues(); // get actual value of the selected rows

for (i = 1; i <= conLen(c);i++)
{
info(conPeek(c,i));
info(conPeek(v,i));
}
}

return ret;
}

Using Resource image in display()

Using Resource images in tables:

Create a Window Control in grid with properties as:

Width:16
Height:16
Verticalspacing:0
ImageMode:Centre
DataSoure:SalesTable
DataMethod:validIcon

Just Write the below code in SalesTable:

//BP Deviation Documented
display int validIcon()
{
;
switch(this.SalesStatus)
{
case SalesStatus::Backorder:
return 10002;
case SalesStatus::Invoiced:
return 10022;
case SalesStatus::Canceled:
return 929;
case SalesStatus::Delivered:
return 1030;
}
return 0;
}

//Ref Form:Project management and accounting/Setup/Project management and accounting workflows

web

Filtering The records in a Form / Lookup’s in Form

1)Create a Table Named “FilterDemo” and add some fields like Name, Eid, Salary and Add one Base Enum Like Gender.

2) Enter some Data into  the Table.

3) Create a form Name “FiletrDemoForm”.Drag the FilterDemo Table to the DataSource of the form.

4) Under the Design node of the Form.Create  a New Control àTabàTab Page.

Change the properties of the TabPage Like this

Name:- General

Caption:-General

5) Now Add a new controlàGrid to the TabPage. Now Drag the Fields Which should Appear in the Form to the Grid control from Data Source.

6) Under the Same Tab Page Add a NewGroup. Under the New Group add a new control called ComboBox. Change the property AutoDeclaration of the comboBox to YES and BaseEnum to Gender.

7) Now the form creation is complete. Now We need to see the filtering the Record.

8) Now Declare the range In the classDeclaration of the form.

public class FormRun extends ObjectRun

{

QueryBuildRange range;

ComboBox _comboBox;

}

9) Add a Range in init() method of the DataSource.

public void init()

{

super();

range=this.query().dataSourceNo(1).addRange(fieldNum(FilterDemoTable,Gender));

}

10) Override the executeQuery() method of the DataSource.

public void executeQuery()

{

range.value(_comboBox.valueStr());

super();

}

11) Call the executeQuery method in the SelectionChanged method of the ComboBox.

public int selectionChange()

{

int ret;

ret = super();

FilterDemoTable_ds.executeQuery();

return ret;

}

Dialog fields and runtime lookups / Simple UI Builder Class on SSRS Report parameters [Dynamics AX 2012] && Filtering drop down list based on another drop down

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”.

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

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

 

Filtering drop down list based on another drop down

Hello All,
I’ve come across a scenario where I have to select a value from a drop down and based on that value I have to fill another drop down. After I did it, I though it will be usefull for others as well. So, I am posting it here.
So, Here is what we need to do.
First create a contract class as (I am hoping) we already know. Here I am using an example of Customer which will be filtered by Customer group. The code of class declaration will be:

[

DataContractAttribute

]

public class custReportContract

{

CustGroupId custGroup;

AccountNum cust;

}

Now creating 2 new methods for getting and setting these parameters. 1 for cust group

[

DataMemberAttribute(‘CustGroup’),

SysOperationLabelAttribute(literalstr(“@SYS11904”))

]

public CustGroupId parmCustGroup(CustGroupId _custGroup= custGroup)

{

custGroup = _custGroup;

return custGroup;

}

And the other one for customer

[

DataMemberAttribute(‘Cust’),

SysOperationLabelAttribute(literalstr(“@SYS313797”))

]

public AccountNum parmCust(AccountNum _cust= cust)

{

cust = _cust;

return cust;

}

Your screen will look like this:

Nothing new in it. We all know it (I guess). Moving forward and now we are creating another class for UI builder.

Create this new class and on the class declaration method, type the following code

public class custReportUIBuilder extends SysOperationAutomaticUIBuilder

{

}

your screen will look like this

Now we have to create 2 dialog boxes. 1 for customer group and customer each. To do this type the following code.

DialogField dialogCustGroup;

DialogField dialogCust;

We also have to define our contract class here, so declare the contract class

custReportContract contract;

so, the class declaration method will look like this:

public class custReportUIBuilder extends SysOperationAutomaticUIBuilder

{

DialogField dialogCustGroup;

DialogField dialogCust;

custReportContract contract;

}

Now, we have to draw the dialog boxes. For this reason, we will create another method of build and type the following code In it.

public void build()

{

Dialog      dialogLocal = this.dialog();

custReportContract contract = this.dataContractObject();

dialogLocal.addGroup(“Customer”);

this.addDialogField(methodStr(custReportContract,parmCustGroup), contract);

this.addDialogField(methodStr(custReportContract,parmCust), contract);

}

Now that we created the build method, let’s move into filling the cust group drop down with the table CustGroup. We’ll create another method for lookup and type the following code in it.

public void lookupCustGroup(FormStringControl _control)

{

Query query = new Query();

SysTableLookup sysTablelookup;

sysTablelookup =SysTableLookup::newParameters(tableNum(CustGroup),_control);

sysTablelookup.addLookupfield(fieldNum(CustGroup,CustGroup));

sysTablelookup.addLookupfield(fieldnum(CustGroup,Name));

query.addDataSource(tableNum(CustGroup));

sysTablelookup.parmQuery(query);

sysTablelookup.performFormLookup();

}

We need to create another method so that when user selects any record from cust group drop down, the customer drop down will be filtered with that value, for this reason we will select the modified event of cust group and then type the code:

public boolean custGroupModified(FormStringControl _control)

{

dialogCustGroup.value(_control.valueStr());

dialogCust.value(”);

return true

}

Ok now we will create the lookup method for customer. Here is the code

public void lookupCust(FormStringControl _control)

{

Query query = new Query();

SysTableLookup sysTablelookup;

sysTablelookup =SysTableLookup::newParameters(tableNum(CustTable),_control);

sysTablelookup.addLookupfield(fieldNum(CustTable,AccountNum));

sysTablelookup.addLookupfield(fieldnum(CustTable,Party));

query.addDataSource(tableNum(CustTable));

query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, CustGroup)).value(dialogCustGroup.value());

sysTablelookup.parmQuery(query);

sysTablelookup.performFormLookup();

}

Note that I filtered the cust query with the value selected in cust group drop down

query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, CustGroup)).value(dialogCustGroup.value());

we have almost completed our code for lookups and their filters. Now we will bind our dialog boxes with the contract class params and also override the modified method of cust group with the method we just wrote. To do this we will create another method for postBuild. Here we go

public void postBuild()

{

super();

// From binding info, get the dialog field for racecode attribute and add button

dialogCustGroup = this.bindInfo().getDialogField(

this.dataContractObject(),

methodStr(custReportContract,parmCustGroup));

if (dialogCustGroup)

{

dialogCustGroup.lookupButton(2);

}

// register override method for lookup cust Group

dialogCustGroup.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(custReportUIBuilder, lookupCustGroup), this);

// register override method for modified

dialogCustGroup.registerOverrideMethod(methodStr(FormStringControl, modified),methodStr(custReportUIBuilder, custGroupModified), this);

//binding info for customer drop down

dialogCust = this.bindInfo().getDialogField(

this.dataContractObject(),

methodStr(custReportContract,parmCust));

// register override method for lookup customer

dialogCust.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(custReportUIBuilder, lookupCust), this);

if (dialogCust)

{

dialogCust.lookupButton(2);

}

}

We may have to create some other methods as well in order to functioning your code properly. They are getFromDialog, initializeFields and postRun. Create three new methods each for getFromDialog, initializeFields and postRun and copy the following code to them

public void getFromDialog()

{

contract = this.dataContractObject();

super();

}

public void initializeFields()

{

contract = this.dataContractObject();

}

public void postRun()

{

super();

}

We are done. But wait one last thing, open your contract class and in the class declaration method, reference your UI builder class just below the DataContractAttribute. Your code will become:

[

DataContractAttribute,

SysOperationContractProcessingAttribute(classStr(custReportUIBuilder))

]

public class custReportContract

{

CustGroupId custGroup;

AccountNum cust;

}

We are now done. In this post we have tried to filter our single value drop down based on another drop down. In the next post we will try to repeat the same process for multi value drop down list.

Reference:http://ssrsax.blogspot.in/2012/05/filtering-drop-down-list-based-on.html