Microsoft Dynamics D365 F&O and Axapta: SSRS

Showing posts with label SSRS. Show all posts

Friday, 27 March 2020

How can I add a row number at each grouping header in SSRS reports

Ex: =RunningValue(Fields!ItemId.Value, countdistinct,Nothing)

here i did the grouping with item id so I given Fields!ItemId.Value
and countdistinct,Nothing are the key words

Friday, 22 March 2019

Having query in dialog form get the query values by using contract class

I am using query for dialog
need to get the values of that query

Create a query.
action type menu item
add to controller class
form controller class getting the service class
here in service class we are getting contract class.
here in attribute of contact class we are passing our query

contract class:

/// <summary>
/// Contract class for releasing sales order
/// </summary>
[DataContractAttribute]
class FBHSalesReleaseOrderContract
{
str packedQuery;

[DataMemberAttribute,
AifQueryTypeAttribute('_packedQuery', querystr(FBHSalesAgreementFollowUp))]
/// <summary>
/// Parm method to return and set parameters.
/// </summary>
/// <param name="_packedQuery">
/// </param>
/// <returns>
/// Returnd the parameter passed.
/// </returns>
public str parmQuery(str _packedQuery = packedQuery)
{
packedQuery = _packedQuery;
return packedQuery;
}

/// <summary>
/// Method to return query.
/// </summary>
/// <returns>
/// Returnd the query.
/// </returns>
public Query getQuery()
{
return new Query(SysOperationHelper::base64Decode(packedQuery));
}

/// <summary>
/// Method to set the query.
/// </summary>
/// <param name="_query">
/// </param>
public void setQuery(Query _query)
{
packedQuery = SysOperationHelper::base64Encode(_query.pack());
}

}

Controller class:

/// <summary>
/// Controller class for release order
/// </summary>
class FBHSalesReleaseOrderController extends SysOperationServiceController
{
/// <summary>
/// Construct for calling service class
/// </summary>
/// <param name = "_args">Argument</param>
/// <returns></returns>
public static FBHSalesReleaseOrderController construct(SysOperationExecutionMode _executionMode=SysOperationExecutionMode::Synchronous)
{
FBHSalesReleaseOrderController FBHSalesReleaseOrderController = new FBHSalesReleaseOrderController(classStr(FBHSalesReleaseOrderService),methodStr(FBHSalesReleaseOrderService,process),_executionMode);
return FBHSalesReleaseOrderController;
}

/// <summary>
/// Main method for controller class
/// </summary>
/// <param name = "_args"></param>
public static void main(Args _args)
{
FBHSalesReleaseOrderController FBHSalesReleaseOrderController;
FBHSalesReleaseOrderController = FBHSalesReleaseOrderController::construct();
FBHSalesReleaseOrderController.startOperation();
}

/// <summary>
/// To set the dialog caption
/// </summary>
/// <param name = "_dialogCaption"></param>
/// <returns></returns>
public LabelType parmDialogCaption(LabelType _dialogCaption = "")
{
return "@FBH:FBH50718";
}

/// <summary>
///
/// </summary>
/// <returns></returns>
protected boolean canRunInNewSession()
{
boolean ret;

ret = super();
ret = true;
return ret;
}

}

Service class:

/// <summary>
/// Service class for releasing sales order
/// </summary>
class FBHSalesReleaseOrderService
{

/// <summary>
/// Method to run the query and apply validations.
/// </summary>
/// <param name="_FBHSalesReleaseOrderContract">
/// FBHSalesReleaseOrderContract contract class parameter
/// </param>
public void process(FBHSalesReleaseOrderContract _FBHSalesReleaseOrderContract)
{
Query query;
QueryRun queryRun;
query = _FBHSalesReleaseOrderContract.getQuery();
queryRun = new QueryRun(query);
query = queryRun.query();

QueryBuildDataSource qbds = query.dataSourceTable(tableNum(AgreementLine));
QueryFilter filter = query.findQueryFilter(qbds, fieldId2Name(tableNum(AgreementLine), fieldNum(AgreementLine, FBHReleaseDate)));
if (filter == null)
{
filter = query.addQueryFilter(qbds, fieldId2Name(tableNum(AgreementLine), fieldNum(AgreementLine, FBHReleaseDate)));
}

if (filter && filter.value() == "")
{
filter.value(queryValue(DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone())));
}

SalesAgreementHeader salesAgreementHeader;

while(queryRun.next())
{
if (queryRun.changed(tableNum(SalesAgreementHeader)) )
{
salesAgreementHeader = queryRun.get(TableNum(SalesAgreementHeader)) as SalesAgreementHeader;
salesAgreementHeader = SalesAgreementHeader::findAgreementId(SalesAgreementHeader.SalesNumberSequence);

if(( SalesAgreementHeader.LatestLineExpirationDate
&& SalesAgreementHeader.LatestLineExpirationDate
<= DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()))
||!SalesAgreementHeader::hasdeliverySchedule(SalesAgreementHeader))
{
continue;
}
try
{
ttsbegin;
FBHSalesReleaseOrderService::releaseOrderFromAgreement(SalesAgreementHeader,query);
ttscommit;
}
catch(Exception::Error)
{
continue;
}
}
}
}

/// <summary>
/// Release order from agreement
/// </summary>
/// <param name = "salesAgreementHeader">sales agreement from which order is to be released</param>
/// <param name = "_query">passing selected query from dialog</param>
public static void releaseOrderFromAgreement(SalesAgreementHeader salesAgreementHeader,Query _query = new Query())
{
salesCreateReleaseOrderTableTmp salesCreateReleaseOrderTabletmp;
salesCreateReleaseOrderLineTmp salesCreateReleaseOrderLineTmp;
SalesAgreementHeader.Currency = Ledger::accountingCurrency(CompanyInfo::current());
FBHSalesAgreementGenerateReleaseOrder salesAgreementGenerateReleaseOrder
= FBHSalesAgreementGenerateReleaseOrder::construct();

salesAgreementGenerateReleaseOrder.parmSalesAgreementHeader(salesAgreementHeader);
salesAgreementGenerateReleaseOrder.parmisBatch(NoYes::Yes);
salesAgreementGenerateReleaseOrder.parmQuery(_query);
salesAgreementGenerateReleaseOrder.createReleaseOrderTable(salesCreateReleaseOrderTableTmp);
salesAgreementGenerateReleaseOrder.createReleaseOrderLine(salesCreateReleaseOrderLineTmp);
salesAgreementGenerateReleaseOrder.parmReleaseOrderTable(salesCreateReleaseOrderTableTmp);
salesAgreementGenerateReleaseOrder.parmReleaseOrderLine(SalesCreateReleaseOrderLineTmp);

if (salesAgreementGenerateReleaseOrder.check())
{
ttsbegin;

SalesAutoCreate salesAutoCreate = SalesAutoCreate::construct(salesAgreementGenerateReleaseOrder.getreleaseorderline(), null,salesAgreementGenerateReleaseOrder.getreleaseordertable());
salesAutoCreate.create();

ttscommit;
}
}

}

Friday, 20 November 2015

class for To get the printer names lookup in ax and dialog override method and last dialog values store next time dialog opens:

To get the printer names lookup in ax and dialog override method and last dialog values store next time dialog opens:

/// <summary>
/// to show the printer name lookup
/// </summary>

class MSSpindleLabelLookup extends RunBase
{
dialog dialog;
dialogGroup dialogGroup;
dialogField dialogField;
PrinterName getPrinterName;
container _dialogValues;
}

/// <summary>
/// to get the dialog
/// </summary>
/// <returns>
/// returns printer name what you selected
/// </returns>
/// <remarks>
/// to get the dialog
/// </remarks>
/// <exception cref="Exception::Error">
/// if user cancel the printing
/// </exception>
protected Object dialog()
{
FormStringControl systemprinterNames;
;

dialog = new Dialog("@MPR5759");
dialogGroup = dialog.addGroup("@SYS62708");
dialogField = dialog.addField(extendedTypeStr(PrinterName));
systemprinterNames = dialogField.fieldControl();
systemprinterNames.registerOverrideMethod(methodStr(FormStringControl, lookup), MethodStr(MSSpindleLabelLookup, msprinterLookup), this);
systemprinterNames.mandatory(true);

//Get the previouse values
_dialogValues = xSysLastValue::getValue(curExt(), curUserId(), UtilElementType::Job, "@SYS62708");
//Check if we have previouse values
if(_dialogValues != conNull())
{
dialogField.value(conPeek(_dialogValues, 1));
}
if (dialog.run())
{
//Save the user choices
_dialogValues = conNull();
_dialogValues += dialogField.value();
xSysLastValue::putValue(_dialogValues, curExt(), curUserId(), UtilElementType::Job, "@SYS62708");

}

if(dialog.closedOk() == false)
throw error("@MPR5760");
return Dialog;
}

/// <summary>
/// get the lookup
/// </summary>
/// <param name="_formControl">
/// passing string control
/// </param>

public void msprinterLookup(FormStringControl _formControl )
{
SysTableLookup sysTableLookup;
TmpTableFieldLookup tmpTableFieldLookup;
Enumerator en;
List entitylist = new list(types::String);
Map printerMap;
MapEnumerator enumerator;
PrinterName printerName;

printerMap = MSSpindleLabelLookup::printerMap();//If above method was created in Global class else call specific class
enumerator = printerMap.getEnumerator();
while (enumerator.moveNext())
{
printerName = enumerator.currentValue();
entitylist.addend(printerName);
}

en = entityList.getEnumerator();

while (en.moveNext())
{
tmpTableFieldLookup.MSprinterName = en.current();
tmpTableFieldLookup.insert();
}

sysTableLookup = SysTableLookup::newParameters(tableNum(tmpTableFieldLookup), _formControl);
sysTableLookup.addLookupfield(fieldNum(TmpTableFieldLookup, MSprinterName));
sysTableLookup.setLabel("@SYS62708");

//BP Deviation documented
sysTableLookup.parmTmpBuffer(tmpTableFieldLookup);
sysTableLookup.performFormLookup();
}

/// <summary>
/// run the dialog
/// </summary>
/// <returns>
/// get the printer name
/// </returns>
public PrinterName runPrinterLookup()
{
this.dialog();
getPrinterName = dialogField.value();
return getPrinterName;
}

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

public static void main(Args args)
{
MSSpindleLabelLookup MSSpindleLabelLookup = MSSpindleLabelLookup::construct();

MSSpindleLabelLookup.runPrinterLookup();

}

//This method loads all available printers to a Map object
static Map printerMap()
{
Microsoft.Dynamics.AX.Framework.Reporting.Shared.PrinterHelper printerHelper;
Microsoft.Dynamics.AX.Framework.Reporting.Shared.PrinterInfo printerInfo;
System.Collections.ArrayList names;
System.Collections.IEnumerator enumerator;
Map printerMap;

str name;
int printerStatus;
str driverName;
str portName;
str comments;
int jobCountSinceLastReset;

if(!printerMap)
{
printerMap = new Map(Types::String, Types::Container);

// BP Deviation documented
printerHelper = new Microsoft.Dynamics.AX.Framework.Reporting.Shared.PrinterHelper();
names = printerHelper.get_PrinterNames();
if (names != null)
{
enumerator = names.GetEnumerator();
while (enumerator.MoveNext())
{
name = enumerator.get_Current();
printerInfo = printerHelper.GetPrinterInfo(name);

// BP Deviation documented
printerStatus = CLRInterop::getAnyTypeForObject(printerInfo.get_PrinterStatus());
// BP Deviation documented
driverName = CLRInterop::getAnyTypeForObject(printerInfo.get_DriverName());
// BP Deviation documented
portName = CLRInterop::getAnyTypeForObject(printerInfo.get_PortName());
// BP Deviation documented
comments = CLRInterop::getAnyTypeForObject(printerInfo.get_Comment());
// BP Deviation documented
jobCountSinceLastReset = CLRInterop::getAnyTypeForObject(printerInfo.get_JobCountSinceLastReset());

printerMap.insert(name, [name, printerStatus, driverName, portName, comments, jobCountSinceLastReset]);
}
}
}

return printerMap;
}

Tuesday, 17 November 2015

ui builder class lookup in one parameter depends on other parameters(passing range in parameters)

lookup in one parameter depends on other parameters:

here in we selected production id in parameter
that production having 4 Opr no parameter
here in i selected one opr no. parameter
for that production having 3 job identfication parameters
selected one job identification parameter
having 2 route card journal parameters

passing the range depends on above parameter we are showing lookup in other parametes

public class MSTravelCardUIBuilder extends SysOperationAutomaticUIBuilder
{
DialogField dialogProdId,dialogOprNum;
DialogField dialogProdJobId,dialogProdJournalRoute;
MSTravelCardContract contract;
}

public void build()
{
Dialog dialogLocal = this.dialog();
MSTravelCardContract contractLocal = this.dataContractObject();

//dialogLocal.addGroup(“Parameters”);
this.addDialogField(methodStr(MSTravelCardContract,parmProdId), contractLocal);
this.addDialogField(methodStr(MSTravelCardContract,parmOprNum), contractLocal);
this.addDialogField(methodStr(MSTravelCardContract,parmProdJobId), contractLocal);
this.addDialogField(methodStr(MSTravelCardContract,parmProdJournalIdRoute), contractLocal);

}

public void getFromDialog()

{

contract = this.dataContractObject();

super();

}

public void initializeFields()

{

contract = this.dataContractObject();

}

public void lookupOprNum(FormStringControl _control)

{

Query query = new Query();

SysTableLookup sysTablelookup;

sysTablelookup =SysTableLookup::newParameters(tableNum(ProdJournalRoute),_control);

sysTablelookup.addLookupfield(fieldNum(ProdJournalRoute,OprNum));

sysTablelookup.addLookupfield(fieldnum(ProdJournalRoute,ProdId));

query.addDataSource(tableNum(ProdJournalRoute));

query.dataSourceTable(tableNum(ProdJournalRoute)).addRange(fieldNum(ProdJournalRoute, ProdId)).value(queryValue(dialogProdId.value()));

sysTablelookup.parmQuery(query);

sysTablelookup.performFormLookup();

}

public void lookupProdJobId(FormStringControl _control)

{

Query query = new Query();

QueryBuildDataSource qbdsJournalRoute,qbdsRouteTrans;

SysTableLookup sysTablelookup;

sysTablelookup =SysTableLookup::newParameters(tableNum(ProdJournalRoute),_control);

sysTablelookup.addLookupfield(fieldNum(ProdJournalRoute,JobId));

sysTablelookup.addLookupfield(fieldNum(ProdJournalRoute,OprNum));

sysTablelookup.addLookupfield(fieldnum(ProdJournalRoute,ProdId));

qbdsJournalRoute = query.addDataSource(tableNum(ProdJournalRoute));

qbdsJournalRoute.addRange(fieldNum(ProdJournalRoute, ProdId)).value(queryValue(dialogProdId.value()));

qbdsJournalRoute.addRange(fieldNum(ProdJournalRoute, OprNum)).value(queryValue(dialogOprNum.value()));

qbdsJournalRoute.addRange(fieldNum(ProdJournalRoute, QtyGood)).value(queryValue('>0'));

qbdsRouteTrans = qbdsJournalRoute.addDataSource(tableNum(ProdRouteTrans));

qbdsRouteTrans.addLink(fieldNum(ProdJournalRoute,ProdId),fieldNum(ProdRouteTrans,TransRefId));

qbdsRouteTrans.addLink(fieldNum(ProdJournalRoute,JobId),fieldNum(ProdRouteTrans,JobId));

qbdsRouteTrans.joinMode(joinmode::ExistsJoin);

qbdsRouteTrans.addRange(fieldNum(ProdRouteTrans,TransType)).value(queryValue(ProdRouteTransType::Qty));

qbdsRouteTrans.addRange(fieldNum(ProdRouteTrans,QtyGood)).value(queryValue('>0'));

sysTablelookup.parmQuery(query);

sysTablelookup.performFormLookup();

}

public void lookupProdJournalRoute(FormStringControl _control)

{

Query query = new Query();

QueryBuildDataSource qbdsJournalRoute,qbdsRouteTrans;

SysTableLookup sysTablelookup;

sysTablelookup =SysTableLookup::newParameters(tableNum(ProdJournalRoute),_control);

sysTablelookup.addLookupfield(fieldNum(ProdJournalRoute,JournalId),true);