Dynamics Community Forum Thread Details

Rows are not populated in SSRS report after adding codes.

Hello Experts,

In RDP some piece of code are given here:

while select salesLine
where salesLine.ItemId == queryInventTable.ItemId &&

        salesLine.createdDateTime >= DateTimeUtil::addMonths(DateTimeUtil::utcNow(), -12)
    {
        avgAnnualSales += salesLine.QtyOrdered ;
    }

    if(avgAnnualSales > 0)
        avgAnnualSales = avgAnnualSales/12;
    else
        avgAnnualSales = 0;

tReOrderReportTmp.AnnualAvgSalesQty = avgAnnualSales;

tReOrderReportTmp.MinStockQty = minStock;

if(tReOrderReportTmp.CurrentStock > 0 && tReOrderReportTmp.AnnualAvgSalesQty > 0)
        tReOrderReportTmp.MonthCoverSales = tReOrderReportTmp.CurrentStock / tReOrderReportTmp.AnnualAvgSalesQty;
    else
        tReOrderReportTmp.MonthCoverSales = 0;

    tReOrderReportTmp.MinStockQty = (tReOrderReportTmp.AnnualAvgSalesQty/30) * tReOrderReportTmp.LeadTime ;

    tReOrderReportTmp.insert();

Report Output

After Added some code to get data for BOMQty column and modified codes here

while select salesLine
where salesLine.ItemId == queryInventTable.ItemId &&

        salesLine.createdDateTime >= DateTimeUtil::addMonths(DateTimeUtil::utcNow(), -12)
    {
        avgAnnualSales += salesLine.QtyOrdered ;
    }

    if(avgAnnualSales > 0)
        avgAnnualSales = avgAnnualSales/12;
    else
        avgAnnualSales = 0;

tReOrderReportTmp.MinStockQty = minStock;

tReOrderReportTmp.AnnualAvgSalesQty = avgAnnualSales;

// Added Codes Start

while select inventTrans
    join salesLine where inventTrans.ItemId == salesLine.ItemId && salesLine.createdDateTime >= DateTimeUtil::addMonths(DateTimeUtil::utcNow(), -12)
       join queryInventTable where inventTrans.ItemId == queryInventTable.ItemId
       join inventTransOrigin where inventTrans.ItemId == inventTransOrigin.ItemId && inventTrans.InventTransOrigin==inventTransOrigin.RecId && inventTransOrigin.ReferenceCategory == InventTransType::BOMLine

{

BOMQty1 += inventTrans.Qty;
tReOrderReportTmp.BOMQty = BOMQty1;

}

//Added Codes End

Report Output