SSRS – Address in one line in D365, AX – Microsoft Dynamics 365 for Finance and Operations

Issue: Address in the D365 Finance and Operation is not in the single field and thru address method by default it is showing the multiple lines.

Requirement: Show address in one line

Resolution: use below SSRS formula in the text-box where you want to show address in one line below example for Vendor Address

=Replace(First(Fields!VendAddress.Value, “PurchPurchaseOrderDS”), Chr(10), “,”)

 

Same can be use for other addresses for example, Customer Address, employee address etc

Mark record in the grid get on the form data source – AX2012 R3 – Dynamics 365 for Finance and Operations

Scenario: You have 10 records in the grid and want to select(mark) on 5 and perform operations.  Then use below code to select mark records

Solution 1:

 

Hcmworker hcmWorker; // table

hcmWorker = this.getfirst(1);// 1 is reflecting the mark records

while (hcmworker)

{

//write custom code to perform operations

 

hcmworker = this.getNext()

}

 

Solution 2:

 

Hcmworker hcmWorker; // table

for(hcmWorker=hcmWorker_ds.getFirst(1);hcmworker;hcmworker=hcmworker_ds.getNext())

{

}

 

Batch job could not be found. Dynamics 365 for Finance and Operations, Ax2012 R3

Scenario/Problem:

Sometimes users facing an issue in the batch jobs when they delete the batch job in executing state from the back end or for any other reason(like batch job stuck in executing stage). Then they need to perform below steps to resolve the issue.

In my  case i was facing issue in the workflow batch job and my workflow are not working even after restarting the server or service.

Solution:

 

Perform select query on the below tables with caption filter by typing the keyword of the job you are looking for.

Batch and Batchjobs

Deleted batchjob has been deleted from Batchjob table but not from Batch table. So, I performed below query and my issue has been resolved.

 

First select query on both tables and then delete query to delete the records.

 

Screen Shot 2019-12-15 at 10.12.09 AM

A financial dimension value is based on the ” record and has been used on a transaction. You cannot delete the ” record – Dynamics 365 for finance and operations – AX2012 R3

Scenario:

Sometime user trying to delete a record from the customer master or any data that relates to financial dimension. So, user is facing above error upon deletion.

Reason:

Error occurred because data exists in the table DimensionAttributeLevelValue

Solution:

  1. Access the open the table DimensionAttributeLevelValue 
  2. filter the record by Display Value field with the desired record to delete like customer number or employee number etc etc
  3. Select the filtered records
  4. Delete the selected records
  5. Then go back to customer master or where you facing the error
  6. Now try to delete you will be able to delete

 

AX 2012: you can directly delete the record by opening table

D365 : you can delete by access the SQL or write the runnable class to delete

 

Temp table(InMemory) buffer usage in the same instance – Microsoft Dynamics 365 for Finance and Operations – Ax2012 – X++

Scenario: Sometime you have to use InMemory table buffer to apply some logic within the same buffer.

Solution: The solution is to use the sameTable variable by using SetTempData

 

//local variable

TempTable localTempTable;

;

// set the data to the local tmp table from the original tmp table

localTempTable.setTmpData(originalTmpTable);

ttsbegin;

while select forupdate localTempTable //where localTempTable.somecoindtion ==

{

localTempTable.someField = ”;

localTempTable.update();

}

ttscommit;

//at the end  after completing the loop set the tmp data back to the original tmp data

originalTmpTable.setTmpData(localTempTable);

 

 

 

delete_from in X++ Ax2012/ D365 F&O – Dynamics 365 For Finance and Operations

Scenario: Delete all record from table at once based on some condition.

Code:

//declare table varibale

MyTable  myTable;

 

ttsbegin;

// to delete all records with any condition

delete_from myTable;

or

//delete records based on some condition

delete_from myTable where mytable.Recid = 12345677;

 

ttscommit;

Simple Dialog using X++ – Dynamics 365 for Finance and Operations

Scenario: Sometimes we need a simple dialog having some fields and we do not want to use dialog form ,instead of that we can create dialog thru X++ code.

Below is the code you can use to create simple dialog.

Example Code:

      

//Declare dialog variables       

Dialog                  dialog;       

DialogField             fieldfDate, fieldtDate;       

FromDate _fromDate, _toDate;       

;       

dialog = new Dialog(“Select start and end date”);       

//define fields to show on the dialog       

fieldfDate = dialog.addField(extendedTypeStr(TransDate));       

fieldtDate = dialog.addField(extendedTypeStr(TransDate));              

dialog.run();       

if (dialog.closedOk())       

{

           //get values from the dialog fields

            _fromDate = fieldfDate.value();

            _toDate = fieldtDate.value();   

}

Export to Excel thru code X++ – Dynamics 365 For Finance and Operations

Below code is used to export the data in the excel file using X++ code

 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;

class ExportToExcel

{        

    /// <summary>

    /// Runs the class with the specified arguments.

    /// </summary>

    /// <param name = “_args”>The specified arguments.</param>

    public static void main(Args _args)

    {

       HcmWotker hcmWorker;

        MemoryStream memoryStream = new MemoryStream();

        using (var package = new ExcelPackage(memoryStream))

        {

            var currentRow = 1;

            Filename fileName = “Test.Xlsx”;

            var worksheets = package.get_Workbook().get_Worksheets();

            var CustTableWorksheet = worksheets.Add(“Export”);

            var cells = CustTableWorksheet.get_Cells();

            OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 1);

            System.String value = “Personnel Number”;

            cell.set_Value(value);

            cell = null;

            value = “Name”;

            cell = cells.get_Item(currentRow, 2);

            cell.set_Value(value);

            while select hcmWorker

            {

                currentRow ++;

                cell = null;

                cell = cells.get_Item(currentRow, 1);

                cell.set_Value(hcmworker.PersonnelNumber);

                cell = null;

                cell = cells.get_Item(currentRow, 2);

                cell.set_Value(hcmworker.Name());

            }

            package.Save();

            file::SendFileToUser(memoryStream, fileName);

           

        }

    }

}

Send email thru code with Report as attachment – Dynamics 365 Finance and Operations

Scenario: Send email thru X++ code with the report as attachment

Below is the code responsible for sending email thru code with the attachment

Guide lines to use below code

Just copy paste the code in the runnable class(job), perform below steps

Just replace the report contract name and paramters values

add send from and to email addresses,

Code:

//Set  variables

Filename fileName = “attFileName.pdf”; 

SrsReportRunController controller = new SrsReportRunController(); 

ReportContract contract = new ReportContract();

SRSPrintDestinationSettings settings;   

Array arrayFiles;   

System.Byte[] reportBytes = new System.Byte[0]();   

SRSProxy srsProxy;   

SRSReportRunService srsReportRunService = new SrsReportRunService();              

Microsoft.Dynamics.AX.Framework.Reporting.Shared.ReportingService.ParameterValue[] parameterValueArray;   

Map reportParametersMap;

SRSReportExecutionInfo executionInfo = new SRSReportExecutionInfo();

//set the report contract parameters

contract.parm1stParameter(“firstParameter value”);

contract.parm2ndtParameter(‘2ndParamter value’);

//3rd paramter

//4th paramter

//set the report controller paramters

controller.parmArgs(_args);

//set report name and desing name

controller.parmReportName(ssrsReportStr(ReportName, NewDesign));

controller.parmShowDialog(false);

controller.parmLoadFromSysLastValue(false);

controller.parmReportContract().parmRdpContract(contract);

 

// Provide printer settings

settings = controller.parmReportContract().parmPrintSettings();

settings.printMediumType(SRSPrintMediumType::File);

settings.fileName(fileName);

settings.fileFormat(SRSReportFileFormat::PDF);

 

// Below is a part of code responsible for rendering the report

        controller.parmReportContract().parmReportServerConfig(SRSConfiguration::getDefaultServerConfiguration());       

controller.parmReportContract().parmReportExecutionInfo(executionInfo);

      srsReportRunService.getReportDataContract(controller.parmreportcontract().parmReportName());       

srsReportRunService.preRunReport(controller.parmreportcontract());       

reportParametersMap = srsReportRunService.createParamMapFromContract(controller.parmReportContract());       

parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);       

srsProxy = SRSProxy::constructWithConfiguration(controller.parmReportContract().parmReportServerConfig());       

// Actual rendering to byte array       

reportBytes = srsproxy.renderReportToByteArray(controller.parmreportcontract().parmreportpath(),parameterValueArray,settings.fileFormat(),settings.deviceinfo());

// You can also convert the report Bytes into an xpp BinData object if needed       

container binData;       

Binary binaryData;       

System.IO.MemoryStream mstream = new System.IO.MemoryStream(reportBytes);       

binaryData = Binary::constructFromMemoryStream(mstream);       

if(binaryData)       

{

            binData = binaryData.getContainer();     

}       

System.Byte[] binData1;       

System.IO.Stream stream1;       

// Turn the Bytes into a stream       

for(int i = 0; i < conLen(binData); i++)        

{

            binData1 = conPeek(binData,i+1);

            stream1 = new System.IO.MemoryStream(binData1);       

} 

//email sending settings

var mail = SysMailerFactory::getNonInteractiveMailer(); 

var messageBuilder = new SysMailerMessageBuilder();        

messageBuilder.reset()

                .setFrom(‘example@Email.com’) // From email address

                .addTo(‘example@email.com’) // To Email address

                .setSubject(‘Email Subject’) // Email Subject

                .setBody(‘Email Body’);        //Email Body

if (stream1 != null)       

{

//add attachment to the email

            messageBuilder.addAttachment(stream1,filename+”.pdf”);       

}

//send email

mail.sendNonInteractive(messageBuilder.getMessage());

}