Multi select lookup in SSRS Report

In this post, We will see how to create multi select parameter on SSRS report dialog.

Step 1: Assumption : We have already regular table created or You can create new table based on your requirement. I will take an example of CustTable and CustTrans.

Step 2 : Create a temporary table DemoMultiLookupTmp

Add a new field as below

Field Name: AccountNum

Type: String

EDT: CustAccount

Step 3 : Create a new Query DemoMultiLookupQuery

Add data source CustTable and put required fields including AccountNum

Step 4 : Create a contract class DemoMultiLookupContract

[ DataContractAttribute, SysOperationContractProcessingAttribute(classStr(DemoMultiLookupUIBuilder)) ]
public class DemoMultiLookupContract
{
List accountNum;
}

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

Step 5 : Create a UI Builder class DemoMultiLookupUIBuilder

class DemoMultiLookupUIBuilder extends SysOperationAutomaticUIBuilder
{
 DialogField accNum;
}
-------------------------------------------------------------------------------------------------------------
public void lookup(FormStringControl _control)
{
 Query query = newQuery(queryStr(DemoMultiLookupQuery));
 container cnt;
 SysLookupMultiSelectGrid::lookup(query, _control, _control, cnt);
}
-------------------------------------------------------------------------------------------------------------
public void postBuild()
{
 DialogField dialogEntity;
 super();
 dialogEntity= this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(DemoMultiLookupUIBuilder, parmAccountNum));
 dialogEntity.registerOverrideMethod(methodStr(FormStringControl, lookup),
methodStr(DemoMultiLookupUIBuilder,lookup), this);
 if (dialogEntity)
 {
 dialogEntity.lookupButton(2);
 }
}

Step 6 : Create a UI Builder class RDP class DemoMultiLookupDP

[SRSReportParameterAttribute(classstr(DemoMultiLookupContract))]
public class DemoMultiLookupDP extends SRSReportDataProviderBase
{
 DemoMultiLookupTmp tmp;
 CustTable custTable;
 List accountNum;
}
-------------------------------------------------------------------------------------------------------------
[SRSReportDataSetAttribute(tablestr(DemoMultiLookupTmp))]
publicTest_MultiParmTable getMultiTmp()
{
 select * from tmp;
 return tmp;
}
---------------------------------------------------------------------------------------------------------------
[SysEntryPointAttribute]
public void processReport()
{
 DemoMultiLookupContract contract = this.parmDataContract() as DemoMultiLookupContract;
 accountNum = contract.parmAccountNum();
 if(accountNum)
 {
 tmp.clear();
 tmp.AccountNum = custTable.AccountNum ;
 tmp.CustName = custTable.name();
 tmp.insert();
 }
}

Step 6 : Create a new SSRS report

1. Open Visual Studio and create new reporting project and name Test_ DemoMultiLookupProject

2. Create new Report and rename to DemoMultiLookupReport

3. Create new dataset and map to RDP

4. Create new design and drag n drop dataset to design

Step 7 : Create a new Menu item

1. Create new menu item of object type : SSRS

2. Change property of Object and Report design to Report name & design name respectively

Step 7 : Test report

Open Menu item to test the report and select the multiple records.