Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SSRS Report data fetch...
Microsoft Dynamics AX (Archived)

SSRS Report data fetching issue.

(0) ShareShare
ReportReport
Posted on by faiz7049 1,877

Hi Experts,

I am not getting correct data based on date range as given below. I know that need to some correct code.

Please help me.

_6000_1.jpg

81638.2.jpg

private void insertIntoTempTable()
{
   
    SalesTable          salesTable;
    InventTable         inventTable;
    InventItemGroupItem inventItemGroupItem;
    InventTrans         inventTrans;
    InventTransOrigin   inventTransOrigin;
    CustInvoiceJour     custInvoiceJour;
    CustInvoiceTrans    custInvoiceTrans;
    TaxTrans            taxTrans;
    ;
    salesTable = SalesTable::find(salesLine.SalesId);
   
    select firstOnly inventTransOrigin where inventTransOrigin.InventTransId == salesLine.InventTransId;
    select firstOnly inventTrans where inventTrans.InventTransOrigin == inventTransOrigin.RecId;
  select firstOnly    taxTrans where taxTrans.InventTransId ==inventTransOrigin.InventTransId ;
     
  
   select firstOnly  custInvoiceJour where custInvoiceJour.SalesId == salesTable.SalesId
      exists join custInvoiceTrans where custInvoiceJour.InvoiceId ==custInvoiceTrans.InvoiceId && custInvoiceTrans.InventTransId==salesLine.InventTransId;
 
    {
        salesOrderTmp.CustAccount          = salesTable.CustAccount;
        salesOrderTmp.AccountName          = CustTable::find(salesTable.CustAccount).name();
        salesOrderTmp.CustClassificationId = CustTable::find(salesTable.CustAccount).CustClassificationId;
        salesOrderTmp.WorkerName           = CustTable::find(salesTable.CustAccount).workerName();
        salesOrderTmp.Date                 = custInvoiceJour.InvoiceDate;
        salesOrderTmp.InvoiceId             =   custInvoiceJour.InvoiceId;
        salesOrderTmp.SalesId              = salesTable.SalesId;
        salesOrderTmp.WorkerSales          = HcmWorker::find(salesTable.WorkerSalesResponsible).name();
        salesOrderTmp.OriginId             = salesTable.SalesOriginId;
        salesOrderTmp.OriginDescription    = SalesOrigin::find(salesTable.SalesOriginId).Description;
        salesOrderTmp.ItemId               = salesLine.ItemId;
        salesOrderTmp.FabricationType       =   this.getFabricationTypeByDefaultDimension(salesLine.DefaultDimension);
        salesOrderTmp.ItemIdDescription    = salesLine.itemName();
        salesOrderTmp.InventLocationId     = InventDim::find(salesLine.InventDimId).InventLocationId;
        salesOrderTmp.SalesQty             = salesLine.SalesQty;
        salesOrderTmp.Selling               =   salesLine.SalesQty * salesLine.SalesPrice;
   
        salesOrderTmp.COG                  = salesLine.SalesQty * inventTrans.costPrice();
        salesOrderTmp.TaxAmount             = taxTrans.TaxAmount;
        salesOrderTmp.TaxCode               =   taxTrans.TaxCode;
        salesOrderTmp.GrossProfit          = salesOrderTmp.Selling -  salesOrderTmp.COG;
        if(salesOrderTmp.GrossProfit != 0 && salesOrderTmp.Selling != 0)
        {
            if(salesOrderTmp.GrossProfit > 0)
                salesOrderTmp.LinePercent          = (salesOrderTmp.GrossProfit / salesOrderTmp.Selling) * 100;
            else
                salesOrderTmp.LinePercent          = ((salesOrderTmp.GrossProfit / salesOrderTmp.Selling) * 100) * -1;
        }
        else
            salesOrderTmp.LinePercent          = 0;
        inventTable = InventTable::find(salesLine.ItemId);
        select firstOnly inventItemGroupItem
            where inventItemGroupItem.ItemId            == inventTable.ItemId
               && inventItemGroupItem.ItemDataAreaId    == inventTable.dataAreaId
               ;
        salesOrderTmp.Vendor               = inventTable.PrimaryVendorId;
        salesOrderTmp.SFA_ArabicItemName    =   inventTable.SFA_ArabicItemName;
        salesOrderTmp.VendorName           = VendTable::find(inventTable.PrimaryVendorId).name();
        salesOrderTmp.ItemGroupId          = inventItemGroupItem.ItemGroupId;
     salesOrderTmp.insert();
       
    }
  
}

*This post is locked for comments

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SSRS Report data fetching issue.

    And now when you debug it, where date is coming from ?

  • faiz7049 Profile Picture
    faiz7049 1,877 on at
    RE: SSRS Report data fetching issue.

    Yes I did CIL. Code is given below.

    private void insertIntoTempTable()

    {

       SalesTable          salesTable;

       InventTable         inventTable;

       InventItemGroupItem inventItemGroupItem;

       InventTrans         inventTrans;

       InventTransOrigin   inventTransOrigin;

       CustInvoiceJour     custInvoiceJour;

       CustInvoiceTrans    custInvoiceTrans;

       TaxTrans            taxTrans;

    FromDate fromDate;

    ToDate toDate;

       ;

       salesTable = SalesTable::find(salesLine.SalesId);

       select firstOnly inventTransOrigin where inventTransOrigin.InventTransId == salesLine.InventTransId;

       select firstOnly inventTrans where inventTrans.InventTransOrigin == inventTransOrigin.RecId;

     select firstOnly    taxTrans where taxTrans.InventTransId ==inventTransOrigin.InventTransId ;

     select firstOnly custInvoiceJour

       where custInvoiceJour.SalesId == salesTable.SalesId

          && custInvoiceJour.InvoiceDate >= fromDate

          && custInvoiceJour.InvoiceDate <= toDate

          exists join custInvoiceTrans

              where custInvoiceJour.InvoiceId      == custInvoiceTrans.InvoiceId

                 && custInvoiceTrans.InventTransId == salesLine.InventTransId;

       {

           salesOrderTmp.CustAccount          = salesTable.CustAccount;

           salesOrderTmp.AccountName          = CustTable::find(salesTable.CustAccount).name();

           salesOrderTmp.CustClassificationId = CustTable::find(salesTable.CustAccount).CustClassificationId;

           salesOrderTmp.WorkerName           = CustTable::find(salesTable.CustAccount).workerName();

           salesOrderTmp.Date                 = custInvoiceJour.InvoiceDate;

           salesOrderTmp.InvoiceId             =   custInvoiceJour.InvoiceId;

           salesOrderTmp.SalesId              = salesTable.SalesId;

           salesOrderTmp.WorkerSales          = HcmWorker::find(salesTable.WorkerSalesResponsible).name();

           salesOrderTmp.OriginId             = salesTable.SalesOriginId;

           salesOrderTmp.OriginDescription    = SalesOrigin::find(salesTable.SalesOriginId).Description;

           salesOrderTmp.ItemId               = salesLine.ItemId;

           salesOrderTmp.FabricationType       =   this.getFabricationTypeByDefaultDimension(salesLine.DefaultDimension);

           salesOrderTmp.ItemIdDescription    = salesLine.itemName();

           salesOrderTmp.InventLocationId     = InventDim::find(salesLine.InventDimId).InventLocationId;

           salesOrderTmp.SalesQty             = salesLine.SalesQty;

           salesOrderTmp.Selling               =   salesLine.SalesQty * salesLine.SalesPrice;

           salesOrderTmp.COG                  = salesLine.SalesQty * inventTrans.costPrice();

           salesOrderTmp.TaxAmount             = taxTrans.TaxAmount;

           salesOrderTmp.TaxCode               =   taxTrans.TaxCode;

           salesOrderTmp.GrossProfit          = salesOrderTmp.Selling -  salesOrderTmp.COG;

           if(salesOrderTmp.GrossProfit != 0 && salesOrderTmp.Selling != 0)

           {

               if(salesOrderTmp.GrossProfit > 0)

                   salesOrderTmp.LinePercent          = (salesOrderTmp.GrossProfit / salesOrderTmp.Selling) * 100;

               else

                   salesOrderTmp.LinePercent          = ((salesOrderTmp.GrossProfit / salesOrderTmp.Selling) * 100) * -1;

           }

           else

               salesOrderTmp.LinePercent          = 0;

           inventTable = InventTable::find(salesLine.ItemId);

           select firstOnly inventItemGroupItem

               where inventItemGroupItem.ItemId            == inventTable.ItemId

                  && inventItemGroupItem.ItemDataAreaId    == inventTable.dataAreaId

                  ;

           salesOrderTmp.Vendor               = inventTable.PrimaryVendorId;

           salesOrderTmp.SFA_ArabicItemName    =   inventTable.SFA_ArabicItemName;

           salesOrderTmp.VendorName           = VendTable::find(inventTable.PrimaryVendorId).name();

           salesOrderTmp.ItemGroupId          = inventItemGroupItem.ItemGroupId;

        salesOrderTmp.insert();

       }

    }

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SSRS Report data fetching issue.

    Did you do CIL after changing your code? Also share new code, so we can see what changes you've done.

  • faiz7049 Profile Picture
    faiz7049 1,877 on at
    RE: SSRS Report data fetching issue.

    Thanks for you support, but still some no changed.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SSRS Report data fetching issue.

    Something like

    select firstOnly custInvoiceJour 
    where custInvoiceJour.SalesId == salesTable.SalesId
           && custInvoiceJour.InvoiceDate >= fromDate 
           && custInvoiceJour.InvoiceDate <= toDate
           exists join custInvoiceTrans 
               where custInvoiceJour.InvoiceId      == custInvoiceTrans.InvoiceId 
                  && custInvoiceTrans.InventTransId == salesLine.InventTransId
  • faiz7049 Profile Picture
    faiz7049 1,877 on at
    RE: SSRS Report data fetching issue.

    Thanks ievgen,

    I tried but no luck. Can you please update code as you suggested.

    Thanks again.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SSRS Report data fetching issue.

    You can add where clause for your select to limit custInvoiceTrans  by date, that should fix the issue.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans