Skip to main content

Create parameters, manipulate query for SSRS type query

Normally, we don't do any coding with SSRS query based report. This code below will show you how to create parms and manipulate the query for SSRS query based report.

DEV_SSRSGlobal_Contract
[
DataContract,
SysOperationGroup('GroupId', "Group label", '1'),
SrsReportName('DEV_Tax1099.Report'),
SysOperationContractProcessing(classStr(DEV_SSRSGlobal_UIBuilder),
SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)
]
public class DEV_SSRSGlobal_Contract extends SrsReportRdlDataContract implements SysOperationValidatable
{
ExchangeRateTypeName exchangeRateTypeName;
CompanyBankAccount companyBankAccount;
NoYesId useCurrentDate;
FromDate fromDate;
ToDate toDate;

[
DataMemberAttribute(identifierStr(ExchangeRateTypeName)),
SysOperationDisplayOrderAttribute('1')
]
public ExchangeRateTypeName parmExchangeRateTypeName(ExchangeRateTypeName _exchangeRateTypeName = exchangeRateTypeName)
{
exchangeRateTypeName = _exchangeRateTypeName;
return exchangeRateTypeName;
}

[
DataMemberAttribute(identifierStr(CompanyBankAccount)),
SysOperationDisplayOrderAttribute('2')
]
public CompanyBankAccount parmBankAccount(CompanyBankAccount _companyBankAccount = companyBankAccount)
{
companyBankAccount = _companyBankAccount;
return companyBankAccount;
}

[
DataMemberAttribute(identifierStr(UseCurrentDate)),
SysOperationLabelAttribute(literalstr("Use current date")),
SysOperationHelpTextAttribute(literalstr("Help text")),
SysOperationDisplayOrderAttribute('3')
]
public NoYesId parmUseCurrentDate(NoYesId _useCurrentDate = useCurrentDate)
{
useCurrentDate = _useCurrentDate;
return useCurrentDate;
}

[
DataMemberAttribute(identifierStr(FromDate)),
SysOperationLabelAttribute(literalstr("From date label")),
SysOperationHelpTextAttribute(literalstr("Help text")),
SysOperationGroupMemberAttribute('GroupId'),
SysOperationDisplayOrderAttribute('1')
]
public FromDate parmFromDate(FromDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}

[
DataMemberAttribute(identifierStr(ToDate)),
SysOperationLabelAttribute(literalstr("To date label")),
SysOperationHelpTextAttribute(literalstr("Help text")),
SysOperationGroupMemberAttribute('GroupId'),
SysOperationDisplayOrderAttribute('2')
]
public ToDate parmToDate(ToDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}

public boolean validate()
{
boolean ret = true;

if (!exchangeRateTypeName)
{
ret = checkFailed("exchangeRateTypeName should be specified");
}
if (!companyBankAccount)
{
ret = checkFailed("companyBankAccount should be specified");
}
return ret;
}

}

Using UIBuilder class, we can control the parm behaviour and set default value.

SysOperationAutomaticUIBuilder
[
SysOperationContractProcessing(classstr(DEV_SSRSGlobal_Contract),
SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)
]
internal final class DEV_SSRSGlobal_UIBuilder extends SysOperationAutomaticUIBuilder
{
private DialogField dialogBankID, dialogExrateType, dialogfromdate, dialogtodate;

/// <summary>
/// Builds the dialog for the <c></c> SSRS report.
/// </summary>
protected DialogField addDialogField(IdentifierName _methodName, Object _dataContract = this.dataContractObject())
{
DialogField ret;
DEV_SSRSGlobal_Contract dataContract = this.dataContractObject() as DEV_SSRSGlobal_Contract;

switch (_methodName)
{
case methodStr(DEV_SSRSGlobal_Contract, parmExchangeRateTypeName):
dialogExrateType = this.dialog().addFieldValue(extendedTypeStr(ExchangeRateTypeName), dataContract.parmExchangeRateTypeName(), "Currency Exchange Rate Type");
dialogExrateType.allowEdit(NoYes::No);
ret = dialogExrateType;
break;
case methodStr(DEV_SSRSGlobal_Contract, parmBankAccount):
dialogBankID = this.dialog().addFieldValue(extendedTypeStr(CompanyBankAccount), dataContract.parmBankAccount(),"Bank Account");
ret = dialogBankID;
break;
case methodStr(DEV_SSRSGlobal_Contract, parmFromDate):
dialogfromdate = this.dialog().addFieldValue(extendedTypeStr(TransDate), dataContract.parmFromDate(), "From Date");
ret = dialogfromdate;
break;
case methodStr(DEV_SSRSGlobal_Contract, parmToDate):
dialogtodate = this.dialog().addFieldValue(extendedTypeStr(TransDate), dataContract.parmToDate(),"To Date");
ret = dialogtodate;
break;
}
return ret;
}

/// <summary>
/// Transfers data from the dialog into the data contract object.
/// </summary>
public void getFromDialog()
{
super();
DEV_SSRSGlobal_Contract contract = this.dataContractObject();
contract.parmExchangeRateTypeName(dialogExrateType.value());
contract.parmBankAccount(dialogBankID.value());
contract.parmFromDate(dialogfromdate.value());
contract.parmToDate(dialogtodate.value());
}

}

We can manipulate the query using preRunModifyContract method in Controller class.

DEV_SSRSGlobal_Controller
internal final class DEV_SSRSGlobal_Controller extends SrsReportRunController
{
#define.ReportName('DEV_Tax1099.Report')
#define.parmYear('Year')
/// <summary>
/// Override this method to change the report contract before running the report.
/// </summary>
protected void preRunModifyContract()
{
Query query;
Map queryContracts;
MapEnumerator mapEnum;
str queryKey;

DEV_SSRSGlobal_Contract contract = this.parmReportContract().parmRdlContract() as DEV_SSRSGlobal_Contract;
//Year year = contract.getValue(#parmYear);
//date fromDate = mkDate(Tax1099Constants::FirstDay, Tax1099Constants::FirstMonth, year);
//date toDate = mkDate(Tax1099Constants::LastDay, Tax1099Constants::LastMonth, year);

date fromdate = contract.parmFromDate();
date toDate = contract.parmToDate();

queryContracts = this.parmReportContract().parmQueryContracts();
if(queryContracts)
{
mapEnum = queryContracts.getEnumerator();
while(mapEnum.moveNext())
{
queryKey = mapEnum.currentKey();
query = queryContracts.lookup(queryKey);
SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendSettlementTax1099)),
fieldNum(VendSettlementTax1099, TransDate)).value(queryRange(fromDate, toDate));
}
}
super();
}

public static void main(Args _args)
{
DEV_SSRSGlobal_Controller controller = new DEV_SSRSGlobal_Controller();
controller.parmReportName(#ReportName);
controller.parmArgs(_args);
controller.startOperation();
}

}