web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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 2,430

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

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,286 on at

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

  • faiz7049 Profile Picture
    faiz7049 2,430 on at

    Thanks ievgen,

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

    Thanks again.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,286 on at

    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 2,430 on at

    Thanks for you support, but still some no changed.

  • Mea_ Profile Picture
    Mea_ 60,286 on at

    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 2,430 on at

    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,286 on at

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

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

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Joris dG Profile Picture

Joris dG 5

#2
Andrew Jones a1x Profile Picture

Andrew Jones a1x 2

#3
GL-01081504-0 Profile Picture

GL-01081504-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans