web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / DAXology (Study about Dynamics AX and Dynamics ... / Create a multi-lookup on SS...

Create a multi-lookup on SSRS report in D365FO

Views (1110)
Chaitanya Golla Profile Picture Chaitanya Golla 17,225

Hi,

Please refer to my earlier post to view the report SSRSPrecisionDesign related classes, tables and menu Item.

https://community.dynamics.com/365/financeandoperations/b/daxology/posts/creating-a-simple-report-in-d365fo

Custom Tables: SampleTable and SampleTrans

Note:  Please generate the labels accordingly, for demo purpose static text was left as-is.

In this post, we will add multiselect parameter to the report. For demo purpose, will change the parameter Id from single value selection to Multi selection.

Step 1: In the contract class SSRSReportContract modified the parm method of report parameter Id to return List instead of single string value.

/// <summary>
/// Gets or sets the value of the datacontract parameter Id.
/// </summary>
/// <param name="_miscChargesCode">
/// The new value of the datacontract parameter Id; optional.
/// </param>
/// <returns>
/// The current value of datacontract parameter Id
/// </returns>
[
DataMemberAttribute('Id'),
SysOperationLabelAttribute(literalstr("Record identifier")),
SysOperationHelpTextAttribute(literalStr("Record identifier of sampletable")),
SysOperationGroupMemberAttribute('Criteria'),
SysOperationDisplayOrderAttribute('1'),
AifCollectionTypeAttribute('return', Types::String)
]
public List parmId(List _id = id)
{
id = _id;
return id;
}

Step 2: Built the solution and refreshed the dataset "SSRSReportDP" of SSRSPrecisionDesignReport. Set the property "Multi value " to True on parameter SSRSReportDP_Id.

Step 3: Created a query by name SampleTableIdLookup to have the Id values from table SampleTable.

Step 4: Modified the processReport method of DP class SSRSReportDP to accommodate the multiple values selected for parameter "Id".

/// <summary>
/// Retrieves records based on the parameters entered.
/// </summary>
public void processReport()
{
QueryRun queryRun;
Query query;
QueryBuildDataSource qbdsSampleTable, qbdsSampleTrans;
QueryBuildRange queryBuildRange;
QueryBuildRange qbrDueDate;
SSRSReportContract contract;
StartDate startDate;
EndDate endDate;
List listSampleTableId;
ListEnumerator listEnumerator;
container conRangeCriteria;
str range = '';
contract = this.parmDataContract() as SSRSReportContract;
listSampleTableId = contract.parmId();
startDate = contract.parmStartDate();
endDate = contract.parmEndDate();
query = this.parmQuery();
qbdsSampleTable = query.dataSourceTable(tableNum(SampleTable));
qbdsSampleTrans = query.dataSourceTable(tableNum(SampleTrans));

if (listSampleTableId && !listSampleTableId.empty())
{
listEnumerator = listSampleTableId.getEnumerator();
while (listEnumerator.moveNext())
{
conRangeCriteria += listEnumerator.current();
}
range = SysOperationHelper::convertMultiSelectedValueString(conRangeCriteria);
range = strReplace(range, ";" , ", ");
queryBuildRange = SysQuery::findOrCreateRange(qbdsSampleTable, fieldNum(SampleTable, Id));
queryBuildRange.value(range);
queryBuildRange.status(RangeStatus::Hidden);
}

if (startDate != dateNull() && endDate != dateNull())
{
SysQuery::findOrCreateRange(qbdsSampleTable, fieldNum(SampleTable, DueDate)).value(queryRange(startDate, endDate));
}
queryRun = new QueryRun(query);
while (queryRun.next())
{
sampleTable = queryRun.get(tablenum(SampleTable));
sampleTrans = queryRun.get(tablenum(SampleTrans));
this.insertSSRSReportTableTmp();
}
}

Step 5: Modified the methods build, postRun of UI builder class SSRSReportUIBuilder and created new method to enable multi lookup of Id values.

Method: build

DialogField idField; // Declaration of variables
// <summary>
/// The <c>SSRSReportUIBuillder</c> class builds the UI for the <c>SSRSPrecisionDesignReport</c> SSRS report parameters
/// </summary>
public void build()
{
SSRSReportContract reportContract;
super();
reportContract = this.dataContractObject() as SSRSReportContract;
startDateField = this.bindInfo().getDialogField(reportContract, methodStr(SSRSReportContract, parmStartDate));
endDateField = this.bindInfo().getDialogField(reportContract, methodStr(SSRSReportContract, parmEndDate));
idField = this.bindInfo().getDialogField(reportContract, methodStr(SSRSReportContract, parmId));
idField.lookupButton(FormLookupButton::Always);
}

Method: postRun

/// <summary>
/// Implements the functionality that is supposed to execute after run.
/// </summary>
public void postRun()
{
this.sampleTableIdLookup();
}

Method: sampleTableIdLookup

/// <summary>
/// Creates a SampleTable id lookup.
/// </summary>
public void sampleTableIdLookup()
{
Query query = new Query(queryStr(SampleTableIdLookup));
TableId multiSelectTableNum = tableNum(SampleTable);
container selectedFields = [multiSelectTableNum, fieldName2id(multiSelectTableNum, fieldStr(SampleTable, Id))];
SysLookupMultiSelectCtrl::constructWithQuery(this.dialog().dialogForm().formRun(), idField.control(), query, false, selectedFields);
}
Step 6: Built the solution, deployed the report and generated the report to see the data from multiple values of parameter Id.

Report Design

Report Output

Regards,

Chaitanya Golla

Comments

*This post is locked for comments