Tuesday, April 21, 2020

How to Improve Performance of SSRS Reports in D365 FO


Performance has been one of the huge bottlenecks for SSRS Reports since AX 2012 RTM.

Even in D365 FO if the reports are not optimized properly there can be heavy lags in the overall processing.

There are several ways through which we perform optimization in SSRS Reports :-


1) SRSReportDataProviderPreProcessTempDB - When we are creating Report Data Provider
Logic based report we should always extend our DP class with SRSReportDataProviderPreProcessTempDB. This class extension has been proved very helpful for optimization especially when we are dealing with huge amount of data in the report.

Here is an example :-

class ABCDP extends SRSReportDataProviderPreProcessTempDB
{
}


2) RecordInsertList - RecordInsertList object helps to perform bulk insertion in one go without bothering the back end to go for multiple round trips for record insertion.

Here is an example :-

RecordInsertList              insertList;
ABCTMP                         abcTmp;


insertList = new RecordInsertList(tableNum(ABCTMP), false, false, false, false, false, abcTmp);

abcTmp.Field1= "A";
abcTmp.Field2="B";

insertList.add(abcTmp);

insertList.insertDatabase();


3) RecordViewCache - RecordViewCache is one of the caching mechanisms provided by x++ in the form of a class. It helps to cache the records so that upon running the data can be fetched right away from cache rather than generating from the Report Dataset.

This mechanism is useful only for those reports which have to be executed very frequently in a day.

Here is an example :-

RecordViewCache      viewCache;
ABCTMP                    abcTmp;

abcTmp.Field1= "A";
abcTmp.Field2="B";

viewCache = new RecordViewCache(SOSummaryWithInvTmp);

abcTmp.insert();


4)  Refrain Use of While Select and multiple select statements :- Avoid using while select and multiple select statements as it causes multiple roundtrips with Database thereby degrading the performance in several ways. Try using views or queries.

In case you have to use While select make sure that you are fetching only those fields are fetched which are required. Field List is while select is always helpful.

Also if possible replace while select statements with select statements using aggregate functions.

Here is an example :-

while select  SalesQy from Salesline where salesline.salestatus = = SalesStatus::BackOrdered
{
   totalqty = totalqty + salesline.SalesQty ;
}

The above statement will consume lot of system resources while executing.

We can simply replace it with the below statement thereby reducing the system usage to a great extent :-

select sum(salesqty) from SalesLine where salesline.salesstatus = =  SalesStatus::BackOrdered ;

totalqty = salesline.SalesQty ;



In case multiple select statements without field list make sure you are using firstonly in front of the table buffer.

Here is an example :-

select firstonly custtable;


5) Use insert_recordset as much as possible ( even instead of while select) :- insert_recordset and update_recordset are the two statements in x++ which gives optimum performance in terms of data fetch.

We can even use it instead of while select statement as well.

Let us take a look at the below statement using while select statement :-

while select InventTransOrigin, inventDimId, DatePhysical,itemId, Qty, StatusReceipt,
                StatusIssue, CostAmountPosted  , CostAmountAdjustment from inventtrans
            join RecId from inventtransorigin
            where inventtransorigin.RecId = = inventtrans.InventTransOrigin
            join inventDimId, InventLocationId, InventSiteId from inventdim
            join InventLocationType, InventLocationId, Name from inventLocation
            order by inventLocation.InventLocationType,inventLocation.InventLocationId
            where inventdim.inventDimId = = inventtrans.inventDimId
               && inventtrans.DateFinancial != dateNull()
               && inventtrans.DateFinancial <= AsonDate
               && inventtrans.Qty != 0
               && (!itemId || inventtrans.itemId = = itemId)
               && inventLocation.InventLocationId = = inventdim.InventLocationId
               && (!inventLocationId || inventdim.InventLocationId = = inventLocationId)
               && (!inventsiteid || inventdim.InventSiteId = = inventsiteid)
            exists join ItemId, DivisionId, CategoryId, SubCategoryId from InventTable
            where InventTable.ItemId = = inventtrans.ItemId
               && (!DivisionId || InventTable.DivisionId = = DivisionId)
               && (!CategoryId || InventTable.CategoryId = = CategoryId)
               && (!SubCategoryId || InventTable.SubCategoryId = = SubCategoryId)
            exists join DisplayProductNumber, ProductType from ecoResProduct
            where ecoResProduct.DisplayProductNumber = = inventtrans.ItemId
                && ecoResProduct.ProductType = = EcoResProductType::Item

{
                        reporttmp.ItemId                     = inventtrans.ItemId ;
                        reporttmp.InventSiteId            = inventdim.InventSiteId ;
                        reporttmp.InventLocationId    = inventdim.InventLocationId ;
                        reporttmp.LocationName        = inventLocation.Name ;
}


The above statement will consume lot of system resources and will be a huge performance bottleneck.

Instead of this we can simply use insert_recordset.

Here is an example :-


insert_recordset reporttmp(ItemId,InventTransRecId,InventTransId,InventSiteId,InventLocationId,LocationName,InventLocationType)
            select ItemId,RecId from inventtrans
            join InventTransId from inventtransorigin
            where inventtransorigin.RecId = = inventtrans.InventTransOrigin
            join  inventdim
            join  InventSiteId,InventLocationId,Name,InventLocationType from inventLocation
            order by inventLocation.InventLocationType,inventLocation.InventLocationId
            where inventdim.inventDimId = = inventtrans.inventDimId
               && inventtrans.DateFinancial != dateNull()
               && inventtrans.DateFinancial <= AsonDate
               && inventtrans.Qty != 0
               && (!itemId || inventtrans.itemId = = itemId)
               && inventLocation.InventLocationId = = inventdim.InventLocationId
               && (!inventLocationId || inventdim.InventLocationId = = inventLocationId)
            exists join InventTable
            where InventTable.ItemId = = inventtrans.ItemId
               && (!DivisionId || InventTable.DivisionId = = DivisionId)
               && (!CategoryId || InventTable.CategoryId = = CategoryId)
               && (!SubCategoryId || InventTable.SubCategoryId = = SubCategoryId)
            exists join ecoResProduct
            where ecoResProduct.DisplayProductNumber = = inventtrans.ItemId
                && ecoResProduct.ProductType == EcoResProductType::Item;


           

If we follow the above 5 principles properly we will be able to see significant improvement in the performance of our SSRS Report we are working upon.



Happy Coding !!!


Thursday, April 16, 2020

How to create Lookups through x++ in D365 FO


Lookup is one of the most commonly used  functionalities in X++ for populating the desired values from a particular master depending on the requirements given.

In this post we will be discussing about different types of Lookup.


1) Lookup without Join

            Query                                    query = new Query();
            QueryBuildDataSource        queryBuildDataSource;
            QueryBuildRange                 queryBuildRange;
            SysTableLookup                  sysTableLookup =                                                                         
            SysTableLookup::newParameters(tableNum(Table1), sender);

            sysTableLookup.addLookupField(fieldNum(Table1, Field1));
            sysTableLookup.addLookupField(fieldNum(Table1,Field2));
           
            queryBuildDataSource = query.addDataSource(tableNum(Table1));
            sysTableLookup.parmQuery(query);
            sysTableLookup.performFormLookup();


2) Lookup with Join


          Query                                      query = new Query();
          QueryBuildDataSource           qbds;
          QueryBuildDataSource           qbdsJoin;
          SysTableLookup                      sysTableLookup = sysTableLookup::newParameters(                                                                                                                       tableNum(VendTable), this);
   

         qbds= query.addDataSource( tableNum(VendTable));
         qbdsJoin= qbds.addDataSource( tableNum(DirPartyTable));
         qbdsJoin.relations( false);
         qbdsJoin.fields().dynamic(NoYes::Yes);
         qbdsJoin.addLink( fieldNum(VendTable, Party), fieldNum(DirPartyTable, RecId));
         qbdsJoin.joinMode(JoinMode::InnerJoin);

         sysTableLookup.parmQuery(query);
         sysTableLookup.addLookupfield( fieldNum(VendTable, AccountNum), true);
         sysTableLookup.addLookupfield( fieldNum(VendTable, VendGroup), true);
         sysTableLookup.addLookupfield( fieldNum(VendTable, Party));
         sysTableLookup.performFormLookup();


3) Lookup with Multiple Joins

   
    Query                   query = new Query();
    QueryBuildDataSource    qbdsPurchTable,QbdsInvntQO, qbdsInventDim;
    QueryBuildRange         qbrVendId,qbrPurchStatus,qbrPurchType;
    QueryBuildLink          qblink1, QbLink2;

    SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(PurchTable),                                                                                                                                                           this);
   
    qbdsPurchTable = query.addDataSource(tableNum(PurchTable));

    QbdsInvntQO = qbdsPurchTable.addDataSource(tableNum(InventQuarantineOrder));
    QbdsInvntQO.relations(false);
    QbdsInvntQO.joinMode(JoinMode::NoExistsJoin);
    qblink1 =  QbdsInvntQO.addLink(FieldNum(PurchTable,PurchId),FieldNum(InventQuarantineOrder,TransRefId));

    qbdsInventDim = QbdsInvntQO.addDataSource(tableNum(InventDim));
    qbdsInventDim.relations(false);
    qbdsInventDim.joinMode(JoinMode::NoExistsJoin);
    QbLink2 = qbdsInventDim.addLink(FieldNum(InventQuarantineOrder,InventDimId),FieldNum(InventDim,InventDimId));

    qbdsPurchTable.addRange(fieldNum(PurchTable, OrderAccount)).value(PurchTable_InvoiceAccountGrid.valueStr());
    qbdsPurchTable.addRange(FieldNum(PurchTable, PurchStatus)).value("Invoiced,Received");
    QbdsInvntQO.addRange(fieldNum(InventQuarantineOrder, Status)).value(queryValue(InventQuarantineStatus::Ended));
    qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("102018");

    sysTableLookup.addLookupfield(fieldNum(PurchTable,OrderAccount));
    sysTableLookup.addLookupfield(fieldNum(PurchTable,PurchId),true);
    sysTableLookup.addLookupfield(fieldNum(PurchTable,PurchStatus));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();



Happy Coding !!!

How to create and post Inventory Movement Journal through x++ in D365 FO


One of the most common requirements these days is to create Inventory Movement Journal through code. Mostly when the input is coming from another 3rd Party application.

Here is the code show below :-



       InventJournalTable              inventJournalTable;
       InventJournalTrans              inventJournalTrans;
       InventJournalNameId             inventJournalName;
       InventDim                            inventDim;
       JournalCheckPost               journalCheckPost;
     

       // Creation of Inventory Journal Header     

        inventJournalTable.clear();

        inventJournalName =                                                                                                         InventJournalName::standardJournalName(InventJournalType::Movement);
        inventJournalTable.initFromInventJournalName(InventJournalName::find(inventJournalName ));

        inventJournalTable.insert();

     

        //Creation of Inventory Journal Lines

        inventJournalTrans.clear();

        inventJournalTrans.initFromInventJournalTable(inventJournalTable);

        inventJournalTrans.TransDate = systemDateGet();

        inventJournalTrans.ItemId = “Product88987”;

        inventJournalTrans.initFromInventTable(InventTable::find(“Item0001”));

        inventJournalTrans.Qty = 11;

        inventDim.InventSiteId  = 'A1';

        inventDim.InventLocationId = 'Loc1';

        inventDim.wMSLocationId = ‘WS1’;

        inventJournalTrans.InventDimId = inventDim::findOrCreate(inventDim).inventDimId;

        inventJournalTrans.insert();

     

        //Posting the journal

        journalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTable);
        journalCheckPost.run();





How to manually deploy SSRS reports through Powershell in D365 FO


Many times we come across situations where even the standard reports in D365 FO does not work or open properly. These scenarios might be anything an On Premi Deployment or problem in SSRS services.

In those kind of scenarios we have to deploy the reports manually like we used to do in AX 2012 Release Implementations.

So here is a very basic and simple way of achieving the same through Windows Powershell.

We simply need to follow these steps :-

1) Open run prompt and type services.msc

2) Make sure IIS , Batch , DIXF , SSRS  , MR services are running.

3) Open Windows Powershell as Administrator

4) Type the below command :-

         K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”

To Deploy specific reports type the below command :-

K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -Module ApplicationSuite -ReportName AssetDep*,TaxVatRegister.Report -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”


Once done the SSRS Reports will start working fine.

Please feel free to post any questions or comments!!!


Happy Coding !!



How to reverse Free Text Invoice Voucher entries without Dialog

 Hey Folks ,  This blog post is in continuation of the previous post for Reversing Free Text Invoice Voucher entries with Dialog. Only diffe...