Dynamics Community Forum Thread Details

I have created the SSRS report. below is the code for Data Provider Class, Builder Class , Contract Class and Controller class.

below is code for builder class
/// <summary>
/// UI Builder for the "AP Bill Payments Details" report.
/// </summary>
/// <remarks>
/// This class handles the user interface logic for the "AP Bill Payments Details" report, including providing the functionality to look up project IDs and binding parameters.
/// </remarks>
class Demo_builder extends SrsReportDataContractUIBuilder
{
DialogField dialogProjId;
Demo_Contract Demo_Contract;

/// <summary>
/// Looks up and filters the project IDs for the "AP Bill Payments Details" report.
/// </summary>
/// <param name="_formControl">The control that triggers the lookup action.</param>
/// <remarks>
/// This method builds a query to filter project IDs based on the given criteria, then invokes the lookup grid to display the results.
/// </remarks>
public void lookupProdId(FormControl _formControl)
{
DimensionAttribute dimensionAttribute;
DimensionAttributeDirCategory dimAttributeDirCategory;
Query query = new Query();
QueryBuildDataSource qbdsDimensionFinancialTag;
SysTableLookup sysTableLookup;
dimensionAttribute = DimensionAttribute::findByName('Project');

if (dimensionAttribute.Type == DimensionAttributeType::CustomList)
{
select firstonly DirCategory from dimAttributeDirCategory where dimAttributeDirCategory.DimensionAttribute == dimensionAttribute.RecId;
sysTableLookup = SysTableLookup::newParameters(tableNum(DimensionFinancialTag), _formControl);

sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Value));
sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Description));

query = new Query();
qbdsDimensionFinancialTag = query.addDataSource(tableNum(DimensionFinancialTag));
qbdsDimensionFinancialTag.addSelectionField(fieldNum(DimensionFinancialTag, Value));
qbdsDimensionFinancialTag.addSelectionField(fieldNum(DimensionFinancialTag, Description));

qbdsDimensionFinancialTag.addRange(fieldNum(DimensionFinancialTag, FinancialTagCategory))
.value(queryValue(dimAttributeDirCategory.DirCategory));

sysTableLookup.parmQuery(query);

SysLookupMultiSelectGrid::lookup(query, _formControl, _formControl, _formControl, connull());
}
}

public void build()
{
Demo_Contract = this.dataContractObject();
this.addDialogField(methodStr( Demo_Contract, parmFromDate), Demo_Contract);
this.addDialogField(methodStr(Demo_Contract, parmToDate), Demo_Contract);
this.addDialogField(methodStr(Demo_Contract, parmProjectIdList), Demo_Contract);

}

public void postRun()
{
dialogProjId = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(Demo_Contract, parmProjectIdList));

if (dialogProjId)
{
// Register the override for lookup method
dialogProjId.registerOverrideMethod(
methodStr(FormStringControl, lookup),
methodStr(Demo_builder, lookupProdId),
this
);
}
}

}
below code is for contract class
/// <summary>
/// Represents the contract class for the Bill Payment AP report.
/// </summary>
/// <remarks>
/// This class contains parameters used for generating the report,
/// such as date range, batch execution flag, and project IDs.
/// </remarks>
[DataContractAttribute, SysOperationContractProcessingAttribute(classStr(Demo_builder),
SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)]
class Demo_Contract
{
TransDate fromDate;
TransDate toDate;
List projectIdList;

/// <summary>
/// Gets or sets the starting date for the report.
/// </summary>
/// <param name="_fromDate">The starting date to filter records to.</param>
/// <returns>The ending date.</returns>

[DataMemberAttribute('FromDate'), SysOperationLabelAttribute(literalStr('From Date')),
SysOperationDisplayOrderAttribute('1')]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}

/// <summary>
/// Gets or sets the ending date for the report.
/// </summary>
/// <param name="_toDate">The ending date to filter records to.</param>
/// <returns>The ending date.</returns>

[DataMemberAttribute('ToDate'), SysOperationLabelAttribute(literalStr('To Date')),
SysOperationDisplayOrderAttribute('2')]
public TransDate parmToDate(TransDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}

/// <summary>
/// Gets or sets the list of project IDs.
/// </summary>
/// <param name="_projectIdList">The list of project IDs to filter on.</param>
/// <returns>The list of project IDs.</returns>

[DataMemberAttribute("ProjectIdList"), SysOperationLabelAttribute(literalStr('Projects:')),
SysOperationDisplayOrderAttribute('3'), AifCollectionTypeAttribute("_projectIdList", Types::String)]
public List parmProjectIdList(List _projectIdList = projectIdList)
{
if (_projectIdList)
{
projectIdList = _projectIdList;
}
return projectIdList;
}

}
below code is for controller class
/// <summary>
/// Represents the contract class for the Bill Payment AP report.
/// </summary>
/// <remarks>
/// This class contains parameters used for generating the report,
/// such as date range, batch execution flag, and project IDs.
/// </remarks>
[DataContractAttribute, SysOperationContractProcessingAttribute(classStr(Demo_builder),
SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)]
class Demo_Contract
{
TransDate fromDate;
TransDate toDate;
List projectIdList;

/// <summary>
/// Gets or sets the starting date for the report.
/// </summary>
/// <param name="_fromDate">The starting date to filter records to.</param>
/// <returns>The ending date.</returns>

}
below code is for DP class
/// <summary>
/// Represents the data provider for the Bill Payment AP report.
/// </summary>
/// <remarks>
/// This class fetches data from various tables, populates the temporary table,
/// and provides it for the report.
/// </remarks>
[SrsReportParameterAttribute(classStr(Demo_Contract))]
class Demo_DP extends SRSReportDataProviderBase
{
Demo_Table tmpTable;
Demo_Contract contract;

GeneralJournalAccountEntry generalJournalAccountEntry;
GeneralJournalEntry generalJournalEntry;
VendInvoiceJour vendInvoiceJour;
LedgerJournalTrans ledgerJournalTrans;
MainAccount mainAcc;
VENDTRANS vendTrans;
VendInvoiceTrans vendInvoicetrans;
DIMENSIONATTRIBUTEVALUESETITEMVIEW dimAttributeValueSetItemView;
DIMENSIONFINANCIALTAG dimFinancialTag;
DimensionAttribute dimAttribute;
DimensionAttributeValue dimensionAttributeValue;

TransDate fromDate;
TransDate toDate;
List projectIdList;

int totalProjectIds;
container distinctValues;
boolean inserted;

/// <summary>
/// Initializes the data provider by setting up the contract and preparing required fields.
/// </summary>
/// <remarks>
/// This method retrieves parameters from the data contract and initializes variables for processing data.
/// </remarks>
public void initialize()
{
contract = this.parmDataContract() as Demo_Contract;

if (contract)
{
fromDate = contract.parmFromDate();
toDate = contract.parmToDate();
projectIdList = contract.parmProjectIdList();
}

totalProjectIds = 0;
distinctValues = conNull();
}

[SrsReportDataSetAttribute(tableStr(Demo_Table))]
public Demo_Table getTmp()
{
select tmpTable;
return tmpTable;
}

///<summary>
///Processes the report by populating the temporary table with data.
///</summary>
public void processReport()
{
tmpTable.clear();
this.initialize();

if(projectIdList != null)
{
while select Value,Description
from dimFinancialTag
join EntityInstance from dimensionAttributeValue
where dimensionAttributeValue.EntityInstance == dimFinancialTag.RecId
join RecId from dimAttribute
where dimAttribute.RecId == dimensionAttributeValue.DimensionAttribute
&& dimAttribute.Name == 'Project'
{
if (!conFind(distinctValues, dimFinancialTag.Value))
{
distinctValues += dimFinancialTag.Value;
}

}
totalProjectIds = conLen(distinctValues);
ListIterator projectIdIterator = new ListIterator(projectIdList);
str projectId;
while (projectIdIterator.more())
{
projectId += projectIdIterator.value() + ',';
projectIdIterator.next();
}
projectId = subStr(projectId,1,strLen(projectId)-1);
projectIdIterator = new ListIterator(projectIdList);
int projectLength = projectIdList.elements();

while (projectIdIterator.more())
{
str currentProjectId = projectIdIterator.value();
this.insertRecord(currentProjectId,projectLength,projectId);

projectIdIterator.next();
}
if(!inserted)
{
tmpTable.ProjectNameDisplay = (projectLength == totalProjectIds) ? "All Locations" : projectId;
inserted = true;
tmpTable.insert();
}
}
else
{
this.insertRecord();

}

}

/// <summary>
/// Inserts records into the temporary table based on the provided Project ID and Project Length.
/// </summary>
/// <param name="projectId">
/// The Project ID to filter records. If null or empty, fetches data for all projects.
/// </param>
/// <param name="projectLength">
/// The length of the project list. Used to determine the display name for the project.
/// </param>
/// /// <param name="projectDisplayName">
/// The Project ID to set for locations. If null or empty, fetches data for all projects.
/// </param>
/// <remarks>
/// This method fetches data from multiple tables, calculates amounts, and populates the temporary table.
/// </remarks>
public void insertRecord(str projectId = "", int projectLength = 0,str projectDisplayName = "")
{
try
{
if (projectId == null || projectId == "")
{
while select AccountingDate,RecId,SubLedgerVoucher from generalJournalEntry
join TransDate, LedgerDimensionName, AmountCurDebit,Voucher,VENDTRANSID,OffsetDefaultDimension from ledgerJournalTrans
where ledgerJournalTrans.Voucher == generalJournalEntry.SubLedgerVoucher && ledgerJournalTrans.TransDate >= fromDate && ledgerJournalTrans.TransDate <= toDate
join GeneralJournalEntry,MainAccount from generalJournalAccountEntry
where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecID && generalJournalAccountEntry.IsCredit == NoYes::Yes
join MainAccountId,Name,RecId from mainAcc
where mainAcc.RecId == generalJournalAccountEntry.MainAccount
join vendTrans
where vendTrans.RECID == ledgerJournalTrans.VENDTRANSID
join vendInvoiceJour
where vendTrans.AccountNum == vendInvoiceJour.InvoiceAccount && vendTrans.LASTSETTLEVOUCHER == vendInvoiceJour.LEDGERVOUCHER
join vendInvoicetrans
where vendInvoicetrans.InvoiceID == vendInvoiceJour.InvoiceID
join DimensionAttributeValueSet,DisplayValue from dimAttributeValueSetItemView
where dimAttributeValueSetItemView.DimensionAttributeValueSet == ledgerJournalTrans.OffsetDefaultDimension
join dimAttribute
where dimAttribute.RecId == dimAttributeValueSetItemView.DimensionAttribute && dimAttribute.Name == 'Project'
join Value,Description from dimFinancialTag
where dimFinancialTag.VALUE == dimAttributeValueSetItemView.DisplayValue
{

real calculatedAmount = (vendInvoiceTrans.lineAmount / vendInvoiceJour.InvoiceAmount) * ledgerJournalTrans.AmountCurDebit;
select RecId from tmpTable
where tmpTable.TransDate == ledgerJournalTrans.TransDate
&& tmpTable.AmountCurDebit == calculatedAmount
&& tmpTable.ProjId == dimFinancialTag.Value
&& tmpTable.ItemId == vendInvoiceTrans.ItemId;

if (!tmpTable.RecId && ledgerJournalTrans.AmountCurDebit <= vendInvoiceJour.InvoiceAmount)
{
tmpTable.TransDate = ledgerJournalTrans.TransDate;
tmpTable.LedgerDimensionName = ledgerJournalTrans.LedgerDimensionName;
tmpTable.AmountCurDebit = calculatedAmount;
tmpTable.AccountingDate = generalJournalEntry.AccountingDate;
tmpTable.ItemName = vendInvoiceTrans.Name;
tmpTable.MainAccountId = mainAcc.MainAccountId;
tmpTable.ProjId = dimFinancialTag.Value;
tmpTable.Name = dimFinancialTag.Description;
tmpTable.GLAccountName = mainAcc.Name;
tmpTable.ItemId = vendInvoiceTrans.ItemId;
tmpTable.VendorId = vendInvoiceJour.OrderAccount;
tmpTable.DocumentDate = vendInvoiceJour.DOCUMENTDATE;
tmpTable.DueDate = vendInvoiceJour.DUEDATE;
tmpTable.InvoiceId = vendInvoiceJour.INVOICEID;
tmpTable.Description = vendInvoiceJour.DESCRIPTION;
if(!inserted)
{
tmpTable.ProjectNameDisplay = "All Locations" ;
inserted = true;
}
tmpTable.insert();

}
}
}
else
{
while select AccountingDate,RecId,SubLedgerVoucher from generalJournalEntry
join TransDate, LedgerDimensionName, AmountCurDebit,Voucher,VENDTRANSID,OffsetDefaultDimension from ledgerJournalTrans
where ledgerJournalTrans.Voucher == generalJournalEntry.SubLedgerVoucher && ledgerJournalTrans.TransDate >= fromDate && ledgerJournalTrans.TransDate <= toDate
join GeneralJournalEntry,MainAccount from generalJournalAccountEntry
where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecID && generalJournalAccountEntry.IsCredit == NoYes::Yes
join MainAccountId,Name,RecId from mainAcc
where mainAcc.RecId == generalJournalAccountEntry.MainAccount
join vendTrans
where vendTrans.RECID == ledgerJournalTrans.VENDTRANSID
join vendInvoiceJour
where vendTrans.AccountNum == vendInvoiceJour.InvoiceAccount && vendTrans.LASTSETTLEVOUCHER == vendInvoiceJour.LEDGERVOUCHER
join vendInvoicetrans
where vendInvoicetrans.InvoiceID == vendInvoiceJour.InvoiceID
join DimensionAttributeValueSet,DisplayValue from dimAttributeValueSetItemView
where dimAttributeValueSetItemView.DimensionAttributeValueSet == ledgerJournalTrans.OffsetDefaultDimension
join dimAttribute
where dimAttribute.RecId == dimAttributeValueSetItemView.DimensionAttribute && dimAttribute.Name == 'Project'
join Value,Description from dimFinancialTag
where dimFinancialTag.VALUE == dimAttributeValueSetItemView.DisplayValue && dimFinancialTag.VALUE == projectId
{
real calculatedAmount = (vendInvoiceTrans.lineAmount / vendInvoiceJour.InvoiceAmount) * ledgerJournalTrans.AmountCurDebit;
select RecId from tmpTable
where tmpTable.TransDate == ledgerJournalTrans.TransDate
&& tmpTable.AmountCurDebit == calculatedAmount
&& tmpTable.ProjId == dimFinancialTag.Value
&& tmpTable.ItemId == vendInvoiceTrans.ItemId;