A RDP based SSRS report consist of the following:
Temporary table
Data contract class
UI builder class
RDP class
Controller class
SSRS report
Output menu item
Step 1: Temporary table
Create a new inmemory temporary table which will be used in the RDP class.
Step 2: Data contract class.
- Class declaration
The following should be set before the class declaration.
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(xxxReportUIBuilder)),
SysOperationGroupAttribute('Parameter',"Parameter",'1'),
SysOperationGroupAttribute('Select',"Select",'2')
]
Where xxxReportUIBuilder is the UI builder class name.
SysOperationGroupAttribute should be used if we need to group parameters and the order in which the group should be shown.
If validation is required, The class should implement SysOperationValidatable
public class LF_BookedSalesReportContract implements SysOperationValidatable
Variables should be declared which are required as parameters.
TransDate fromDate;
TransDate toDate;
DimensionValue branch;
hcmWorkerRecId employee;
- Add parm methods for the variables declared.
[
DataMemberAttribute('FromDate'),
SysOperationLabelAttribute(literalstr("@SYS24050")),
SysOperationGroupMemberAttribute('Parameter'),
SysOperationDisplayOrderAttribute('1')
]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}
SysOperationGroupMemberAttribute should be used identify the group name and SysOperationDisplayOrderAttribute should be used to set the display order of the parameter in the group.
///
/// Gets or sets the value of the datacontract parameter ToDate.
///
///
/// The new value of the datacontract parameter ToDate; optional.
///
///
/// The current value of datacontract parameter ToDate
///
[
DataMemberAttribute('ToDate'),
SysOperationLabelAttribute(literalstr("@SYS80934"))
]
public TransDate parmToDate(TransDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}
- Validate variables.
public boolean validate()
{
boolean isValid = true;
if (this.parmToDate() && this.parmFromDate() > this.parmToDate())
{
// Invalid date interval
isValid = checkFailed("@SYS91020");
}
if(isValid )
{
return true;
}
else
{
return false;
}
}
To validate dimensions:
DimensionAttribute dimAttribute;
DimensionAttributeDirCategory dimAttributeDirCategory;
DimensionFinancialTag DimensionFinancialTag;
void checkDimension(DimensionValue _value, Name _dimname)
{
dimAttribute = DimensionAttribute::findByName(_dimname);
select RecId from DimensionFinancialTag where DimensionFinancialTag.Value == _value
join DirCategory from dimAttributeDirCategory
where dimAttributeDirCategory.DimensionAttribute == dimAttribute.recId &&
dimAttributeDirCategory.DirCategory == DimensionFinancialTag.FinancialTagCategory ;
if (!DimensionFinancialTag.RecId)
isValid = checkFailed(strfmt("%1 value %2 does not exist.",_dimname,_value));
}
department = this.parmDepartment();
if(department)
{
checkDimension(department,"Departments"); checkDimension(department,"Departments");
}
Step 3: UI builder class.
- Class declaration
Should extend SrsReportDataContractUIBuilder
Declare a variable for the contract class created above.
xxxDPContract rdpContract
- Post run method
This method can be overridden and can be used to register overridden method for controls like the lookup, modified method etc. Also can be used to set the labels for the controls.
public void postRun()
{
DialogField dialogField;
Dialog dialogLocal = this.dialog();
;
super();
dialogLocal.caption("Report Name");
// This method should be called in order to handle events on dialogs. This method should not be called in case of multi select control overridden.
dialogLocal.dialogForm().formRun().controlMethodOverload(false);
rdpContract = this.dataContractObject();
//Set default values in the contract
rdpContract.parmToDate(systemDateGet());
rdpContract.parmSalesEmpType(LF_SalesEmpType::LF_SalesResponsible);
dialogField = this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(LF_BookedSalesReportContract, parmToDate));
dialogField.value(systemDateGet());
dialogField = this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(LF_BookedSalesReportContract, parmSalesChannel));
dialogField.registerOverrideMethod(methodstr(FormStringControl, lookup),
methodstr(LF_BookedSalesReportUIBuilder, lookupSalesChannel), this);
}
lookup method overridded:
public void lookupSalesChannel(FormStringControl _control)
{
SysTableLookup sysTableLookup;
Query query;
QueryBuildDataSource qbdsdimFinancialTag,qbdsSPSContract;
DialogField dlgFldSalesProjType;
dlgFldSalesProjType = this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(LF_BookedSalesReportContract, parmSalesProjType));
if (dlgFldSalesProjType.value() == LF_SalesProjType::LF_SmallProjSales)
{
sysTableLookup = SysTableLookup::newParameters(tableNum(DimensionFinancialTag), _control);
sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Value));
sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Description));
query = new query();
qbdsdimFinancialTag = query.addDataSource(tableNum(DimensionFinancialTag));
qbdsSPSContract = qbdsdimFinancialTag.addDataSource(tableNum(LF_SmallProjSalesData));
qbdsSPSContract.addLink(fieldNum(DimensionFinancialTag,RecId),fieldNum(LF_SmallProjSalesData,LF_SalesChannelRecId));
qbdsSPSContract.joinMode(JoinMode::ExistsJoin);
sysTableLookup.parmQuery(Query);
sysTableLookup.performFormLookup();
}
}
Example of modified method
public boolean modifiedCustAcct(FormStringControl _control)
{
CustTable custTable;
LogisticsPostalAddress deliveryAddress;
DialogField dialogField;
dialogField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(LF_ShippingLabelContract, parmCustAccount));
if(dialogField.value())
{
select firstonly custTable where custTable.AccountNum == dialogField.value();
if (custTable)
{
deliveryAddress = LogisticsLocationEntity::findPostalAddress(custTable,LogisticsLocationRoleType::Delivery);
if (deliveryAddress)
this.setAddressFields(deliveryAddress);
else
this.setAddressFields(custTable.postalAddress());
dialogField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(LF_ShippingLabelContract, parmCustName));
dialogField.value(custTable.name());
contract.parmCustName(custTable.name());
}
}
return true;
}
Example to clear control values.
public void preBuild()
{
FormBuildGroupControl formBuildGroupControl;
super();
contract = this.dataContractObject();
contract.parmJobNum("");
contract.parmCustName("");
formBuildGroupControl = dialog.curFormGroup();
formBuildGroupControl.columns(2);
}
Step 4: RDP class.
- Class declaration
The following should be set before the class declaration.
[
SRSReportParameterAttribute(classstr(xxxReportContract)),
SRSReportQueryAttribute(queryStr(QueryName)) //used when query is also part of the rdp.
]
Where xxxReportContract is the Data Contract class name.
The class should extends SRSReportDataProviderBase //SrsReportDataProviderPreProcess //
SrsReportDataProviderPreProcess can be used for debugging the rdp class.
In the class declartion, declare a variable for the tmp which will be sent to the SSRS report, and for the queryrun.
LF_BookedContractSalesTmp bookedContractSalesTmp;
QueryRun queryRun;
- Process report
Write the logic to insert the data into the temp table
LF_ScheduledDollarsBranchContract dataContract; // Get the query from the runtime using a dynamic query.
dataContract = this.parmDataContract();
projFromDate = dataContract.parmProjectFromDate();
queryRun = new SysQueryRun(this.parmQuery());
//Add more filters to the query based on the user selected values
refer the setQueryRanges() below.
while (queryRun.next()
{
}
Example to modify the query.
private void setQueryRanges(ProjResponsibleFinancialWorker _projectAccountant)
{
QueryBuildDataSource qbdsProjTable, qbdsContractLineItems, qbdsProjInvTeamTable;
qbdsProjTable = queryrun.query().dataSourceTable(tablenum(ProjTable));
qbdsContractLineItems = qbdsProjTable.addDataSource(tableNum(PSAContractLineItems));
qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, FeeProjId));
qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, ChangeOrderNum));
qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, LineDesc));
qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, LineValue));
qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, CreatedDateTime));
//qbdsContractLineItems.addSelectionField(fieldnum(PSAContractLineItems, ProjInvoiceProjId));
qbdsContractLineItems.addRange(fieldnum(PSAContractLineItems,LF_ContractLineStatusId )).value("TM");
qbdsContractLineItems.addRange(fieldnum(PSAContractLineItems,LF_ContractLineStatusId )).value("PN");
qbdsContractLineItems.addRange(fieldnum(PSAContractLineItems,LF_ContractLineStatusId )).value("P$");
qbdsContractLineItems.addRange(fieldnum(PSAContractLineItems,LF_ContractLineStatusId )).value(SysQuery::valueEmptyString());
qbdsContractLineItems.addRange(fieldnum(PSAContractLineItems, FeeProjId)).value(SysQuery::valueNotEmptyString());
qbdsContractLineItems.addSortField(fieldNum(PSAContractlineitems,FeeProjId));
qbdsContractLineItems.addSortField(fieldNum(PSAContractlineitems,ChangeOrderNum));
qbdsContractLineItems.addLink(fieldNum(ProjTable, ProjId), fieldNum(PSAContractLineItems, FeeProjId));
qbdsContractLineItems.joinMode(JoinMode::InnerJoin);
qbdsProjInvTeamTable = qbdsContractLineItems.addDataSource(tablenum(LF_ProjInvoiceTeamTable));
qbdsProjInvTeamTable.addSelectionField(fieldnum(LF_ProjInvoiceTeamTable, LF_ProjectAcct));
qbdsProjInvTeamTable.addLink(fieldNum(PSAContractLineItems, ProjInvoiceProjId), fieldNum(LF_ProjInvoiceTeamTable, LF_ProjInvoiceId));
if (_projectAccountant)
{
qbdsProjInvTeamTable.joinMode(JoinMode::InnerJoin);
qbdsProjInvTeamTable.addRange(fieldNum(LF_ProjInvoiceTeamTable,LF_ProjectAcct)).value(queryValue(_projectAccountant));
}
else
{
qbdsProjInvTeamTable.joinMode(JoinMode::OuterJoin);
}
}
- method to set the tmp table
This method should set the report dataset attribute to the temporary table
[
SRSReportDataSetAttribute("scheduledDollarBySchedulerTmp")
]
public LF_ScheduledDollarBySchedulerTmp getScheduledDollarBySchedulerTmp()
{
select * from scheduledDollarBySchedulerTmp;
return scheduledDollarBySchedulerTmp;
}
Step 4: Controller class.
This class should extend SrsReportRunController. In the class declaration, declare a macro for the report name
#define.ReportName('LF_BookedSalesReport.Report')
Create a main method to call the SSRS report.
public static void main(Args _args)
{
LF_BookedSalesReportController controller = new LF_BookedSalesReportController();
controller.parmReportName(#ReportName);
controller.parmArgs(_args);
controller.startOperation();
}
Override heh prePromptModifyContract to make changes to query filter.
protected void prePromptModifyContract()
{
ProjTable projTableLocal;
//super();
//add a range in the report query
if(this.parmArgs().record())
{
projTableLocal = this.parmArgs().record();
SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(ProjTable),fieldNum(ProjTable, ProjId),SysQuery::value(projTableLocal.projId));
}
}
Step 5: SSRS Report.
Create a SSRS report in the VS report designer. Create an RDP based precision report and the dataset should use the rdp class created before.
Step 6: Output menu item.
Step 7: Add Output menu item to menu.
