SSRS Report UI Builder Class sample code

UI Builder class will be very useful class for the ssrs reports based on selection of parameter selection values will differ from field to field.

Here Am using 4 fiedls in contract class.

UI Builder Class 

public class FOFS_JobTypeSummaryUIBuilder extends SysOperationAutomaticUIBuilder
{
DialogField dialogCustomerAcc;
DialogField dialogContractCode;
DialogField dialogFromDate;
DialogField dialogToDate;
fofs_JobtypesummaryContract fofs_JobTypeSummaryContract;
}

Build Method :

public void build()
{
Dialog dialogLocal = this.dialog();
fofs_JobtypesummaryContract contract = this.dataContractObject();
dialogCustomerAcc = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmCustAccount),contract);
dialogCustomerAcc.lookupButton();
dialogContractCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmContractCode),contract);
dialogContractCode.lookupButton();
dialogFromDate = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmFromDate),contract);
dialogToDate = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmToDate),contract);
dialogJobTypeCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmJobTypecode),contract);
dialogFieldCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmFieldcode),contract);
dialogRigCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmRigCode),contract);
dialogWellCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmWellCode),contract);
dialogCellCode = this.addDialogField(methodStr(fofs_JobtypesummaryContract,parmCellCode),contract);

}

Contract Code Lookup Method: 

private void ContractCodeLookUp(FormStringControl contractCodeLookUp)
{
Query query = new Query();
QueryBuildDataSource qbds;
SysTableLookup sysTableLookup;
sysTableLookup = SysTableLookup::newParameters(tableNum(FOFS_JobCard),contractCodeLookUp);
sysTableLookup.addLookupfield(fieldNum(FOFS_JobCard,ContractCode));
qbds = query.addDataSource(tableNum(FOFS_JobCard));
qbds.addRange(fieldNum(FOFS_JobCard,CustAccount)).value(queryValue(dialogCustomerAcc.value()));
qbds.addGroupByField(fieldNum(FOFS_JobCard,ContractCode));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

}

Contract Code Modified :

public boolean contractCodeModified(FormStringControl contractCodeModified)
{
dialogContractCode.value(contractCodeModified.valueStr());
dialogFromDate.value(”);
dialogToDate.value(”);
dialogJobTypeCode.value(”);
dialogFieldCode.value(”);
dialogRigCode.value(”);
dialogWellCode.value(”);
dialogCellCode.value(”);
return true;
}

Cust Account Lookup : 

private void CustAccLookUp(FormStringControl custAccountLookUp)
{
Query query = new Query();
SysTableLookup sysTableLookup;
sysTableLookup = SysTableLookup::newParameters(tableNum(FOFS_JobCard),custAccountLookUp);
sysTableLookup.addLookupfield(fieldNum(FOFS_JobCard,CustAccount),true);
query.addDataSource(tableNum(FOFS_JobCard)).addGroupByField(fieldNum(FOFS_JobCard,CustAccount));
sysTableLookup.addLookupMethod(identifierStr(CustomerName));
//sysTableLookup.addLookupMethod(‘CustomerName’);
//sysTableLookup.addLookupMethod(“CustomerName”);
//sysTableLookup.addLookupMethod(tableMethodStr(FOFS_JobCard,CustomerName));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}

Cust Account Modified: 

public boolean CustAccModified(FormStringControl custAccModified)
{
dialogCustomerAcc.value(custAccModified.valueStr());
dialogContractCode.value(”);
dialogFromDate.value(”);
dialogToDate.value(”);
dialogJobTypeCode.value(”);
dialogFieldCode.value(”);
dialogRigCode.value(”);
dialogWellCode.value(”);
dialogCellCode.value(”);
return true;
}

Get From Dailog method :

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

Post Build Method :

public void postBuild()
{
super();
dialogCustomerAcc = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmCustAccount));
if(dialogCustomerAcc)
{
dialogCustomerAcc.lookupButton(2);
}
dialogCustomerAcc.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,CustAccLookUp),this);
dialogCustomerAcc.registerOverrideMethod(methodStr(FormStringControl, modified),methodStr(FOFS_JobTypeSummaryUIBuilder,CustAccModified),this);
dialogContractCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmContractCode));

if(dialogContractCode)
{
dialogContractCode.lookupButton(2);
}
dialogContractCode.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,ContractCodeLookUp),this);
dialogContractCode.registerOverrideMethod(methodStr(FormStringControl, modified),methodStr(FOFS_JobTypeSummaryUIBuilder,contractCodeModified),this);
}

Post Run Method :

public void postRun()
{
Dialog dialogLocal = this.dialog();
DialogField dialogFieldFromDate;
DialogField dialogFieldToDate;
DialogField dialogFieldJobTypeCode;
DialogField dialogFieldFieldCode;
DialogField dialogFieldRigCode;
DialogField dialogFieldWellCode;
DialogField dialogFieldCellCode;
super();
dialogLocal.dialogForm().formRun().controlMethodOverload(false);
// dialogFieldCustAccNum = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmCustAccount));
// dialogFieldCustAccNum.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(fofs_JobtypesummaryUIBuilder,CustAccLookUp),this);

// dialogFieldContCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmContractCode));
// dialogFieldContCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,ContractCodeLookUp),this);

dialogFieldFromDate = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmFromDate));
dialogFieldToDate = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmToDate));
dialogFieldJobTypeCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmJobTypecode));
dialogFieldJobTypeCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,JobTypeCodeLookup),this);

dialogFieldFieldCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmFieldCode));
dialogFieldFieldCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,fieldCodeLookup),this);

dialogFieldRigCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmRigCode));
dialogFieldRigCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,rigCodeLookup),this);

dialogFieldWellCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmWellCode));
dialogFieldWellCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,wellCodeLookup),this);

dialogFieldCellCode = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(fofs_JobtypesummaryContract,parmCellCode));
dialogFieldCellCode.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(FOFS_JobTypeSummaryUIBuilder,cellCodeLookup),this);

}

DP Class 

Class Declaration method;

[SRSReportParameterAttribute(classStr(fofs_JobtypesummaryContract))]
class fofs_JobtypesummaryDP extends SrsReportDataProviderBase
{
FOFS_JobTypeSummaryTmp JobtypeSummaryTmp;
}

Getting Report Table Buffer:

[SRSReportDataSetAttribute(tableStr(FOFS_JobTypeSummaryTmp))]
public FOFS_JobTypeSummaryTmp getJobtypesummary()
{
select * from JobtypeSummaryTmp;
return JobtypeSummaryTmp;
}

Process Report Method :

[SysEntryPointAttribute]
public void processReport()
{
FOFS_JobCard fofs_JobCard;
FOFS_JobCardLines fofs_JobCardLines;
CustAccount custAccount;
FOFS_ContractCode ContractCode;
FromDate fromDate;
ToDate toDate;
FOFS_JobTypeSummaryContract fofs_JobTypeSummaryContract;
;
fofs_JobTypeSummaryContract = this.parmDataContract();
JobTypeCode = fofs_JobTypeSummaryContract.parmJobTypecode();
custAccount = fofs_JobTypeSummaryContract.parmCustAccount();
ContractCode = fofs_JobTypeSummaryContract.parmContractCode();
fromDate = fofs_JobTypeSummaryContract.parmFromDate();
toDate = fofs_JobTypeSummaryContract.parmToDate();

if(JobTypeCode == “”)

{
JobTypeCode = ‘*’;
}

if(custAccount == “”)
{
custAccount = ‘*’;
}

if(ContractCode == “”)
{
ContractCode = ‘*’;
}

ttsBegin;
while select count(RecId),sum(LineAmount) from fofs_JobCardLines join fofs_JobCard group by fofs_JobCard.JobTypeCode
where fofs_JobCard.JobCardNum == fofs_JobCardLines.JobCardNum && fofs_JobCard.EndDate >= fromDate && fofs_JobCard.EndDate <= toDate
&& fofs_JobCard.FieldCode like FieldCode && fofs_JobCard.WellCode like WellCode && fofs_JobCard.RigCode like RigCode && fofs_JobCard.CellCode like CellCode
&& fofs_JobCard.JobTypeCode like JobTypeCode && fofs_JobCard.CustAccount like custAccount && fofs_JobCard.ContractCode like ContractCode
{
//info(strFmt(” Job Type Code = %1 & Count = %2 Net Amount = %3″,fofs_JobCard.JobTypeCode,fofs_JobCardLines.RecId,fofs_JobCardLines.LineAmount));
JobtypeSummaryTmp.Services = strFmt(“%1”,fofs_JobCardLines.RecId);
JobtypeSummaryTmp.LineAmount = fofs_JobCardLines.LineAmount;
JobtypeSummaryTmp.JobTypeCode = fofs_JobCard.JobTypeCode;
JobtypeSummaryTmp.insert();
}
ttsCommit;
}

SSRS DP class with Contract and Query

DP Class 
Class Declaration :

[SRSReportQueryAttribute(queryStr(FOFS_PendingPOQuery)),
SRSReportParameterAttribute(classStr(FOFS_PendingPO_Contract))]
class FOFS_PendingPORDP extends SRSReportDataProviderBase
{
FOFS_PendingPOTmp tmp_FOFSPendingPO;
FOFS_PendingPOAgeingTmp tmpPendingPOAgeing;
}

Get Report table buffer :

[SRSReportDataSetAttribute(tableStr(FOFS_PendingPOAgeingTmp))]
public FOFS_PendingPOAgeingTmp getAgeingData()
{
select * from tmpPendingPOAgeing;
return tmpPendingPOAgeing;
}

Process Report :

[SysEntryPointAttribute]
public void processReport()
{
utcDateTime fromDate,toDate;
VendAccount vendAccount;
Query query;
QueryRun queryRun;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
PurchLine purchLine,purchLine1;
FOFS_PendingPO_Contract fofs_PendingPO_Contract;
int itemIdCount,purchIdCount,daysCount;
;

query = this.parmQuery();
fofs_PendingPO_Contract = this.parmDataContract();
fromDate = DateTimeUtil::newDateTime(fofs_PendingPO_Contract.parmFromDate(),0);
toDate = DateTimeUtil::newDateTime(fofs_PendingPO_Contract.parmToDate(),86400);
vendAccount = fofs_PendingPO_Contract.parmVendAccount();
queryBuildDataSource = query.dataSourceTable(tableNum(PurchLine));

if(vendAccount)
{
queryBuildRange = queryBuildDataSource.findRange(fieldnum(PurchLine, VendAccount));
if (!queryBuildRange)
{
queryBuildRange = queryBuildDataSource.addRange(fieldnum(PurchLine, VendAccount));
}
// If an account number has not been set, then use the parameter value to set it.
if(!queryBuildRange.value())
queryBuildRange.value(vendAccount);
}
if(fromDate && toDate)
SysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(PurchLine,CreatedDateTime)).value(queryRange(fromdate,todate));
queryRun = new QueryRun(query);
ttsBegin;
while (queryRun.next())
{
tmp_FOFSPendingPO.clear();
purchLine = queryRun.get(tableNum(PurchLine));
tmp_FOFSPendingPO.CurrencyCode = purchLine.CurrencyCode;
tmp_FOFSPendingPO.DeliveryDate = purchLine.DeliveryDate;
tmp_FOFSPendingPO.PurchId = purchLine.PurchId;
tmp_FOFSPendingPO.VendAccount = purchLine.VendAccount;
tmp_FOFSPendingPO.ItemId = purchLine.ItemId;
tmp_FOFSPendingPO.Name = purchLine.Name;
tmp_FOFSPendingPO.PurchUnit = purchLine.PurchUnit;
tmp_FOFSPendingPO.PurchQty = purchLine.PurchQty;
tmp_FOFSPendingPO.RemainPurchPhysical = purchLine.RemainPurchPhysical;
tmp_FOFSPendingPO.LineAmount = purchLine.LineAmount;
tmp_FOFSPendingPO.Age = today() – purchLine.DeliveryDate;
tmp_FOFSPendingPO.DlvTerm = PurchTable::find(purchLine.PurchId, false).DlvTerm;

if(purchLine.CurrencyCode == “”)
{
tmp_FOFSPendingPO.LineAmountMST = 0;
}
else
{
tmp_FOFSPendingPO.LineAmountMST = PurchLine::findRecId(purchline.RecId,false).lineAmountMST();
}
tmp_FOFSPendingPO.Value = MarkUpTrans::findPurchLine_IN(purchline.TableId,purchline.RecId).Value;
tmp_FOFSPendingPO.TotalPoOMR = tmp_FOFSPendingPO.LineAmount+tmp_FOFSPendingPO.Value;
tmp_FOFSPendingPO.cdt = purchLine.createdDateTime;
tmp_FOFSPendingPO.insert();
}

ttsCommit;

daysCount = DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(todate, DateTimeUtil::getUserPreferredTimeZone()))-DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(fromdate, DateTimeUtil::getUserPreferredTimeZone()));

if(vendAccount == “”)
{
vendAccount = ‘*’;
}
if(daysCount <= 30)
{
while select count(ItemId) from purchLine1 group by purchLine1.PurchId where purchLine1.createdDateTime >= fromdate && purchLine1.createdDateTime <= todate && purchLine1.PurchStatus == PurchStatus::Backorder && purchLine1.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine1.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine1.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L30I = itemIdCount;
tmpPendingPOAgeing.L30P = purchIdCount;
tmpPendingPOAgeing.insert();
}

if(daysCount > 30 && daysCount <= 60)
{

while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= DateTimeUtil::addDays(todate,-30) && purchLine.createdDateTime <= todate && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L30I = itemIdCount;
tmpPendingPOAgeing.L30P = purchIdCount;
purchLine.clear();
itemIdCount = 0;
purchIdCount = 0;

while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >=fromdate && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-30) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L60I = itemIdCount;
tmpPendingPOAgeing.L60P = purchIdCount;
tmpPendingPOAgeing.insert();
}

if(daysCount > 61 && daysCount <= 90)
{
while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= DateTimeUtil::addDays(todate,-30) && purchLine.createdDateTime <= todate && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L30I = itemIdCount;
tmpPendingPOAgeing.L30P = purchIdCount;
itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();

while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >=DateTimeUtil::addDays(todate,-60) && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-30) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L60I = itemIdCount;
tmpPendingPOAgeing.L60P = purchIdCount;
itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();

while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= fromdate && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-60) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L90I = itemIdCount;
tmpPendingPOAgeing.L90P = purchIdCount;
tmpPendingPOAgeing.insert();
}

if(daysCount >90)
{
while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= DateTimeUtil::addDays(todate,-30) && purchLine.createdDateTime <= todate && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L30I = itemIdCount;
tmpPendingPOAgeing.L30P = purchIdCount;
itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();
while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >=DateTimeUtil::addDays(todate,-60) && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-30) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L60I = itemIdCount;
tmpPendingPOAgeing.L60P = purchIdCount;
itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();
while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= DateTimeUtil::addDays(todate,-90) && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-60) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{
itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.L90I = itemIdCount;
tmpPendingPOAgeing.L90P = purchIdCount;

itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();

while select count(ItemId) from purchLine group by purchLine.PurchId where purchLine.createdDateTime >= fromdate && purchLine.createdDateTime < DateTimeUtil::addDays(todate,-90) && purchLine.PurchStatus == PurchStatus::Backorder && purchLine.LineDeliveryType != LineDeliveryType::OrderLineWithMultipleDeliveries && purchLine.VendAccount like vendAccount
{

itemIdCount = itemIdCount + str2int(purchLine.ItemId);
purchIdCount++;
}
tmpPendingPOAgeing.G90I = itemIdCount;
tmpPendingPOAgeing.G90P = purchIdCount;
tmpPendingPOAgeing.insert();
itemIdCount = 0;
purchIdCount = 0;
purchLine.clear();
}

}

SSRS report Contract and DP sample

Here i am giving some more additional Contract class methods and DP class sample codes

Contract class 

Class declaration method

/// <summary>
/// Data Contract class for FOFS_POCostFactorInvoice SSRS report
/// </summary>
/// <remarks>
/// This is the Data Contract class for the FOFS_POCostFactorInvoice SSRS Report.
/// </remarks>
class FOFS_POCostFactorInvoiceContract implements SysOperationValidatable
{
FromDate fromDate;
ToDate toDate;
}

New Method

public void new()
{
}

Parm from date method

[
DataMemberAttribute,
SysOperationLabelAttribute(literalstr(“@WMM784”)),
SysOperationHelpTextAttribute(literalstr(“@WMM784”))
]
public FromDate parmFromDate(FromDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}

Parm Todate method

[
DataMemberAttribute,
SysOperationLabelAttribute(literalstr(“@WMM785”)),
SysOperationHelpTextAttribute(literalstr(“@WMM785”))
]
public ToDate parmToDate(ToDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}

Validate Method to validate the date.

/// <summary>
/// Validates the values of the parameters.
/// </summary>
/// <returns>
/// true if all values are valid; otherwise, false.
/// </returns>
public boolean validate()
{
if (!this.parmToDate() || !this.parmFromDate())
{
// The From date field and the To date field must be filled in.
return checkFailed(“@SYS95151”);
}

if (this.parmToDate() && this.parmFromDate() > this.parmToDate())
{
// From date must be before To date
return checkFailed(“@SYS91020”);
}

return true;
}

Construct Method :

public static FOFS_POCostFactorInvoiceContract construct()
{
return new FOFS_POCostFactorInvoiceContract();
}

DP Class :

Class Declaration method :

/// <summary>
/// The <c>FOFS_POCostFactorInvoiceDP</c> class is the report Data Provider class for the <c>FOFS_POCostFactorInvoice</c> SRS
/// report.
/// </summary>
[
SRSReportParameterAttribute(classstr(FOFS_POCostFactorInvoiceContract))
]
class FOFS_POCostFactorInvoiceDP extends SrsReportDataProviderBase
{
FOFS_POCostFactorInvoiceTmp POCostFactorInvoiceTmp;

utcDateTime fromDate,toDate;
}

Get the report table method : 

/// <summary>
/// Fills the <c>FOFS_POCostFactorDetailsTmp</c> temporary table with the data.
/// </summary>
/// <returns>
/// The <c>FOFS_POCostFactorDetailsTmp</c> temporary table.
/// </returns>
[
SRSReportDataSetAttribute(tablestr(FOFS_POCostFactorInvoiceTmp))
]
public FOFS_POCostFactorInvoiceTmp getPOCostFactorInvoiceTmp()
{
select * from POCostFactorInvoiceTmp;

return POCostFactorInvoiceTmp;
}

Insert in to report table :

/// <summary>
/// Fills the <c>FOFS_POCostFactorInvoiceTmp</c> table with header details.
/// </summary>
public void insertPOCostFactorInvoiceTmp()
{
PurchTable purchTable;
PurchLine purchline;
MarkupTrans markuptrans;
MarkupValue amount;
container companylogo = CompanyImage::findByRecord(CompanyInfo::find()).Image;
while select purchTable
where purchTable.createdDateTime >= fromDate &&
purchTable.createdDateTime <= toDate &&
purchTable.PurchStatus == PurchStatus::Invoiced

{
purchline = purchline::find(purchTable.PurchId);
amount = 0;
ttsBegin;
POCostFactorInvoiceTmp.clear();
POCostFactorInvoiceTmp.CompanyLogo = companylogo;
POCostFactorInvoiceTmp.PurchId = purchTable.PurchId;
POCostFactorInvoiceTmp.OrderAccount = purchTable.vendorName();
POCostFactorInvoiceTmp.POinvoiceDate = VendInvoiceJour::findFromPurchId(purchTable.PurchId).InvoiceDate;
POCostFactorInvoiceTmp.InvoiceId = VendInvoiceJour::findFromPurchId(purchTable.PurchId).InvoiceId;
POCostFactorInvoiceTmp.CurrencyCode = VendInvoiceJour::findFromPurchId(purchTable.PurchId).CurrencyCode;
POCostFactorInvoiceTmp.InvoiceAmount = VendInvoiceJour::findFromPurchId(purchTable.PurchId).InvoiceAmount;
POCostFactorInvoiceTmp.LedgerVoucher = VendInvoiceJour::findFromPurchId(purchTable.PurchId).LedgerVoucher;
POCostFactorInvoiceTmp.AmountMST = currency::amountCur2MST(1,POCostFactorInvoiceTmp.CurrencyCode);

while select markuptrans where markuptrans.TransRecId == purchline.RecId
{
amount+= markuptrans.Value;
}

POCostFactorInvoiceTmp.ChargesAmount = amount;

POCostFactorInvoiceTmp.insert();

ttsCommit;
}

}

Process Report method :

/// <summary>
/// Processes the business logic for FOFS_POCostFactorInvoice Report which is used to populate a temporary table.
/// </summary>
[SysEntryPointAttribute]
public void processReport()
{
FOFS_POCostFactorInvoiceContract contract;
contract = this.parmDataContract() as FOFS_POCostFactorInvoiceContract;
fromDate = DateTimeUtil::newDateTime(contract.parmFromDate(),0);
toDate = DateTimeUtil::newDateTime(contract.parmToDate(),86400);
this.insertPOCostFactorInvoiceTmp();
POCostFactorInvoiceTmp.clear();
}

SSRS Report by using Controller class sample code

Contract Class : Here I am passing an Account number as a parameter

Class Declaration :

[DataContractAttribute]
class FOFS_POTermsAndConditionsContract
{
AccountNum account;
int64 tablenumber;
SalesId salesid;
PurchId purchid;
}

Parm Method1 :

[
DataMemberAttribute(“Purch Id Number”)
]
public AccountNum parmPurchnum(AccountNum _Purchnumber = account)
{
account = _Purchnumber;

return account;
}

So Now I got in Account number I will Pass a Purch ID or Sales ID

so based on menu Item I need to select a design which is either PO report or SO report.

For that i am using a Controller class, There I am differentiate the design.

Controller Class ::

Class declaration method :

class FOFS_POTermsAndConditionsController extends SrsReportRunController
{
PurchTable purch;
SalesTable sales;
}

PreRunModifies Contract method is the main method to do the selection of design but in Main method we will pass one design as a default design but this method will override the design by passing the value.

preRunModifiedContract method :

Protected void preRunModifyContract()
{
str reportnameLocal;
FOFS_POTermsAndConditionsContract contract;

contract = this.parmReportContract().parmRdpContract() as FOFS_POTermsAndConditionsContract;

if(this.parmArgs().menuItemName() == menuitemOutputStr(FOFS_TermsSalesReport))
{
sales = args.record();
// contract.parmAccountnum(sales.CustAccount);
// contract.parmTableid(sales.TableId);
contract.parmPurchnum(sales.SalesId);
reportnameLocal = ssrsReportStr(FOFS_TermsAndCondition,SalesTerms);

}
else if(this.parmArgs().menuItemName() == menuitemOutputStr(FOFS_TermsPurchReport))
{
purch = args.record();
// contract.parmAccountnum(purch.OrderAccount);
// contract.parmTableid(purch.TableId);
contract.parmPurchnum(purch.PurchId);
reportnameLocal = ssrsReportStr(FOFS_TermsAndCondition,PurchTerms);
}

this.parmReportContract().parmReportName(reportnameLocal);

}

this is the mail method which will execute first, here i will pass a default design but prerunmodified method will override the design based on menuitem which is calling.

Main Method :

public static void main(args _args)
{
FOFS_POTermsAndConditionsController controller = new FOFS_POTermsAndConditionsController();

controller.parmReportName(ssrsReportStr(FOFS_TermsAndCondition,PurchTerms));

controller.parmArgs(_args);

controller.parmShowDialog(false);

controller.startOperation();
}

DP class will execute the business logic for the report.

DP class :

Class declaration Method :

[SRSReportParameterAttribute(classStr(FOFS_POTermsAndConditionsContract))]
class FOFS_POTermsAndConditionsDP extends SRSReportDataProviderBase//SRSReportDataProviderBase//SrsReportDataProviderPreProcess
{
FOFS_TermsConditionReportTmp reporttable;
SalesTable sales;
PurchTable purch;
FOFS_TermsCategory categort;
FOFS_TermsHierarchy hierarchy;
FOFS_TermsCategoryValues values;
container companylogo;

}

Get data method :

[SRSReportDataSetAttribute(tableStr(FOFS_TermsConditionReportTmp))]
public FOFS_TermsConditionReportTmp GetData()
{
select * from reporttable;

return reporttable;
}

Insert into report table method1 :

private void insertintoPurch(PurchTable _purch)
{
hierarchy = FOFS_TermsHierarchy::findHierarchyName(_purch.FOFS_HierarchyName);
companylogo = CompanyImage::findByRecord(CompanyInfo::find()).Image;

while select * from categort where categort.CategoryHierarchy == hierarchy.RecId
join values where values.ParentRecId == categort.RecId
{
reporttable.clear();
reporttable.CompanyLogo = companylogo;
reporttable.HierarchyName = _purch.FOFS_HierarchyName;
reporttable.PurchId = _purch.PurchId;
reporttable.VendAccount = _purch.vendorName();
reporttable.ParentName = categort.Name;
reporttable.ChildName = values.CategoryNodeCode;
reporttable.insert();

}
}

Insert into report table method2 :

private void insertintoSales(SalesTable _sales)
{
hierarchy = FOFS_TermsHierarchy::findHierarchyName(_sales.FOFS_HierarchyName);
companylogo = CompanyImage::findByRecord(CompanyInfo::find()).Image;

while select * from categort where categort.CategoryHierarchy == hierarchy.RecId
join values where values.ParentRecId == categort.RecId
{
reporttable.clear();
reporttable.CompanyLogo = companylogo;
reporttable.HierarchyName = _sales.FOFS_HierarchyName;
reporttable.SalesId = _sales.SalesId;
reporttable.CustAccount = _sales.customerName();
reporttable.ParentName = categort.Name;
reporttable.ChildName = values.CategoryNodeCode;
reporttable.insert();
}
}

Process Report Method :

[
SysEntryPointAttribute(false)
]
public void processReport()
{
FOFS_POTermsAndConditionsContract contract = this.parmDataContract();
AccountNum accountNumber;
int64 purchtablenumber,salestablenumber;
SalesId salesid;
PurchId purchid;

purchtablenumber = tableNum(PurchTable);
salestablenumber = tableNum(SalesTable);
accountNumber = contract.parmPurchnum();

select firstOnly * from sales where sales.SalesId == accountNumber;

if(sales)
{
this.insertintoSales(sales);
}
else
{
select * from purch where purch.PurchId == accountNumber;
this.insertintoPurch(purch);
}

}

LookupReference () example

Dear Friends.. as we know for overriding lookup() method in ax for normal edt fields, But if we have a requirement to override a RecId field in lookup ..??

Here is another method which is used for override the lookup for RecId fields is LookupReference() 

Here is the small example for the method…….

public Common lookupReference(FormReferenceControl _formReferenceControl)
{
Common ret;
SysReferenceTableLookup sysReferenceTableLookup;
Query query = new Query();
QueryBuildDataSource qbds1,qbds2;
QueryBuildRange qbr1;

sysReferenceTableLookup = sysReferenceTableLookup::newParameters(tableNum(Lines),_formReferenceControl,true);
SysReferenceTableLookup.addLookupfield(fieldNum(tLines,ItemId)) ;

qbds1 = query.addDataSource(tableNum(Lines));
qbds2 = qbds1.addDataSource(tableNum(Card));
qbds2.addLink(fieldNum(FOFS_JobCard,ContractCode),fieldNum(Lines,Code));
qbds2.addLink(fieldNum(FOFS_ContractLines,CustAccount),fieldNum(Card,CustAccount));
qbr1 = qbds2.addRange(fieldNum(Card,Num));
qbr1.value(Lines.CardNum);

sysReferenceTableLookup.parmQuery(query);
ret = sysReferenceTableLookup.performFormLookup();

return ret;
}

Enjoy forks…

AX2012 – HOW TO CREATE A READ ONLY SECURITY ROLE (WALKTHROUGH)

the original post will be find in the below link… http://www.kaya-consulting.com/ax2012/ax2012-how-to-create-a-read-only-security-role-walkthrough/

rajendraax

How to create a specific role in AX2012 where people have just “read only” rights.

The approach is simple. All Duties and Privileges in AX do have a pattern. All read only duties end with the word “Inquire”. Setup forms can also have read only rights and end with “Review”. The privileges do end with “View” for forms. Reports normally end with “Generate”.

When you create an AOT project and used the filter for selecting the duties ending with “*Inquire” and “*Review” you have a list of all “read only” duties. Then create a new role. Drag and drop all duties from your project to the new role and you have created your “Read only” role.

Walkthrough:

1. Open the Ax Development Workspace (AOT)
2. Create a new development project and give it a name for your reference.
   
3. Click the Advanced Filter/Sort button or use the shortcut combination Ctrl+F3
   
4. Click the button Select for making the…

View original post 237 more words

Drill-down SSRS Reports in Dynamics Ax 2012 R3

Drill drown reports in Dynamics Ax 2012 R3 are very Easy. For this purpose I have to create a new AOT/Static Query AOT. This query is join between CustTable and SalesTable. I want to create Inner Join so Only those Customer came who have Sales Orders. Consider following Steps to create A new report

Create a new Query with Name “DyWorldCustSales”
Drop or add Data Source with CustTable

Right click on Fields and add following fields from Customer table.

Expand Data Source inside Custtable, Add or create DataSource with SalesTable and fields

Right Click on  SalesTables Data Source and set its join properties as follow

Expand Relationship node of SalesTable_1  Add Following relationship

Set it properties as follow

New create a new report In existing or New DataModel Report in Visual Studio project

Add New report with DyCustSalesOrderList

Add Dataset with Name DSCustomer and Point to Query which we create in above steps

Now drag and drop the dataset on designer to create a designer

And Update rename it to  DyCustSalesOrderList

Set DataTable’s Propeties visible to set false

Add a List and set its name to CustList

Create two groups there one for CustomerGroup and second for AccountNum

Now drop the fields from dataset which  you want to display on report

Right click on CustList and set Data Navigation Style to DrillDown from properties window

Now right click add report to aot and then deploy to Report Server

When you run the report  from meu Item you will find following

Click on Customer group this will open it

Click on Customer account it will drill down report on Detail level

Simple RDP Report in AX 2012

Today I am sharing, how to develop a RDP reports. I will be elaborating the process step by step, so that it is very easy for you to follow the steps and develop your own report.

Step 1: Create a Temp Table SSRS_VendTableTmp

Create a new table and set its table type to InMemory or TempDB. Add the following fields from the vendtable in your table.

• Vendor Account
• Address
• County
• State
• Country
• Telephone
• Fax
• Email
• Telex Number

Step 2: Create a Query SSRS_VendTable

Create a new query and add vendtable in its Data source. Your Query should look like this:

Step 3: Create a Data Contract Class SSRS_VendTableDC

The Data contract class defines the parameters in your report. Your Data contract class will have the following methods.

Class Declaration

class SSRS_VendTableDC
{
AccountNum accountNum;
}

Parm Methods – Add as many as you want

[DataMemberAttribute("AccountNum")]
public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)
{
accountNum = _accountNum;
return accountNum;
}

Step 4: Create a Report Data Provider Class SSRS_VendTableRDP

Your RDP class should have the following methods:

1.	[ SRSReportQueryAttribute (querystr(SSRS_VendTable)),
SRSReportParameterAttribute(classstr(SSRS_VendTableDC))
]
class SSRS_VendTableRDP extends SRSReportDataProviderBase
{
SSRS_VendTableTmp vendTableTmp;
}

2.	[SRSReportDataSetAttribute("Tmp_SRCustTable")]
public tmpSR_CustTable getTmpSR_CustTable()
{
select * from tmpSR_CustTable;
return tmpSR_CustTable;
}
3.	[SysEntryPointAttribute(false)]
public void processReport()
{
QueryRun queryRun;
Query query;
VendTable vendTable;
SSRS_VendTableDC vendTableDC;
AccountNum accountNum;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
query = this.parmQuery();
vendTableDC = this.parmDataContract() as SRSRDPCustTableContractClass;
accountNum = vendTableDC.parmAccountNum();
// Add parameters to the query.
queryBuildDataSource = query.dataSourceTable(tablenum(VendTable));
if(accountNum)
{
queryBuildRange = queryBuildDataSource.findRange(fieldnum(VendTable, AccountNum));
if (!queryBuildRange)
{
queryBuildRange = queryBuildDataSource.addRange(fieldnum(VendTable, AccountNum));
}

if(!queryBuildRange.value())
queryBuildRange.value(accountNum);
}
queryRun = new QueryRun(query);
while(queryRun.next())
{
vendTable = queryRun.get(tableNum(VendTable));
this.insertTmpTable(VendTable);
}
}

Step 5: Create Report in Visual Studio

1. Open Visual studio.
2. Open a new Project.
3. Select Microsoft Dynamics AX in the installed templates.
4. Select report model.
5. Name your report.
6.  Click Ok.

Step 6: Setup Report

First of all, add a report in your project. Refer to the below pasted picture.

You can rename your report to VendTableReport.

Once you have added the report, right click on the dataset and add a dataset. Right click on your newly added Dataset and select “Properties”. Select “Report Data provider” in the Data Source Type.

Click on the button provided against the query property and select your RDP class.

Now add AutoDesign in your report. You can add an autodesign by right clicking on the designs or you can drag your VendTableDataset on the designs node.

Select a Layout template in the properties of your AutoDesign

Select a Style template in the properties of your VendTableDatasetDesign node.

Step 7: Add report to AX AOT

The setup is completed. We need to view the report now. In order to do so, build the report by right clicking on your solution. After successful building of the report, deploy the report and add it to AOT.

Step 8: Create an Output Menu item for your report

In order to view the report in Dynamics AX, add an output menu item. Select SRRS Report in Report Type. Select your report in Object and report design in ReportDesign. Once you have setup the menu item, save it and open it. Here is the final report:

AX2012 – HOW TO CREATE A READ ONLY SECURITY ROLE (WALKTHROUGH)

How to create a specific role in AX2012 where people have just “read only” rights.

The approach is simple. All Duties and Privileges in AX do have a pattern. All read only duties end with the word “Inquire”. Setup forms can also have read only rights and end with “Review”. The privileges do end with “View” for forms. Reports normally end with “Generate”.

When you create an AOT project and used the filter for selecting the duties ending with “*Inquire” and “*Review” you have a list of all “read only” duties. Then create a new role. Drag and drop all duties from your project to the new role and you have created your “Read only” role.

Walkthrough:

1. Open the Ax Development Workspace (AOT)
2. Create a new development project and give it a name for your reference.
   
3. Click the Advanced Filter/Sort button or use the shortcut combination Ctrl+F3
   
4. Click the button Select for making the selection.
   
   Enter the value “*Inquire,*Review” for table SysModelElement field Name.
   Enter the value “SecurityDuty” for table SysModelElementType field Element Type Name.
   Click OK for this form and the Project filter form.
5.  All elements are added to your new project like the image below illustrates.
   
6. Navigate within the AOT to the node Security, Roles.  Create a new role and give it the appropriate name and description.
   
7. Select the Project form.
8. Select all Duties by using the shortcut Crtl+A.
9. Drag and drop the selected Duties to your new role (Duties node) and save your new role.
   
10. The baseline for the role is ready. You can already assign user to this role. But….
    Some tables have too high privileges caused by some out of the box Duties, Privileges an/or Form permissions.
    E.g. the Vendor table (VendTable) has Full control permissions.
11. Open the form Security Roles from the System administration, Setup, Security menu.
    
12. Select the new “Read only user” role.
13. Click the button Override permissions.
14. Walk through the list of tables and see which tables do have too high access levels.
    
    To correct the access level:
    Untick the field Do not override.
    Set the value of the field Override access level to “View”.
    
15. Note that temporary tables need “Full access” for processing the reports.
16. Click Close to close the form.

You can now use the role and eventually test it by using the Security Development Tool which is available on Information source.

How to: Open a Development Work space in AX 2012

In Microsoft Dynamics AX 2012, by default, the client opens in an Application Workspace. As a developer, you may prefer to open the Microsoft Dynamics AX client directly into a Development Workspace.

• How to open a Development Workspace from the Application Workspace.
• Three methods to directly start the client in a Development Workspace.
• A code example to open a Development Workspace using X++.

To open a Development Workspace from the Application Workspace

• Click Windows > New Development Workspace. A Development Workspace opens in a new window.

To open a Development Workspace by configuration

1. Click Start > All Programs > Administrative Tools > Microsoft Dynamics AX 2012 Configuration. The Microsoft Dynamics AX Configuration Utility window opens.
2. On the General tab, click Manage, and then click Create configuration.
3. On the Create Configuration form, enter a name for the configuration in the Configuration name field, and then click OK. The Create Configuration form closes and you return to the Microsoft Dynamics AX Configuration Utility window.
4. On the General tab, in the Command to run at application startup field, enter -development, and then click OK to save the new configuration. The settings will apply the next time you open the Microsoft Dynamics AX client.

To open a Development Workspace using the command line

1. On the taskbar, click Start, and then click Run.
2. In the Open field, enter cmd, and then click OK.
3. At the command prompt, type the following: “C:\Program Files\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe” -development

To create a shortcut to open a Development Workspace

1. In Windows Explorer, right-click the Ax32.exe file in your Microsoft Dynamics AX 2012 client installation, and then click Create Shortcut. By default, this file is located in theC:\Program Files\Microsoft Dynamics AX\60\Client\Bin directory. A new shortcut named Shortcut to Ax32.exe is created.
2. Right-click Shortcut to Ax32.exe, and then click Properties. The Shortcut to Ax32.exe Properties window opens.
3. On the Shortcut tab, in the Target field, enter -development after the path to the Ax32.exe file. The Target field should resemble “C:\Program Files\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe” -development.
4. Click OK to save the shortcut. You can move and rename the shortcut as needed.

Here is the job to open..

static void OpenDevWorkspace(Args _args)
{
    int hWorkspace;
    hWorkspace = infolog.createDevelopmentWorkspaceWindow();
}