Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / to get data from 2 qb...
Finance forum
Unanswered

to get data from 2 qbds queries in report

Posted on by 1,451
I have 2 qbds queries in report i need to the transaction both queries , how can i get the data in report and merge them together 
 please anyone guide me on this , below are my two queries .
 i want to fetch data  from both queries .
 
  • Layan Jwei Profile Picture
    Layan Jwei 3,447 Super User on at
    to get data from 2 qbds queries in report
    Hi,
     
    Is your question answered? If yes, then please verify the answers that helped.
  • Waed Ayyad Profile Picture
    Waed Ayyad 2,982 on at
    to get data from 2 qbds queries in report
    Hi,
     
    What do you mean by 2 qbds? Do you have 2 DataSources on your query?
     
     
    Thanks,
    Waed Ayyad
  • Layan Jwei Profile Picture
    Layan Jwei 3,447 Super User on at
    to get data from 2 qbds queries in report
    Hi,

    I'm not sure I understood you correctly. Do you mean your query has two datasources and you are trying to get data from those datasources in the report?
    if this is the case, then you can do this in the RDP class:
    while (queryRun.next())
    {
        CustTrans custTrans   = queryRun.get(tablenum(CustTrans)) as CustTrans ;  //1st datasrouce table
        CustTable custTable   = queryRun.get(tableNum(CustTable)) as CustTable ;  //2nd datasource table
        //add your logic
    }

    if that's not the case, then please explain more.

    Thanks,
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
  • Martin Dráb Profile Picture
    Martin Dráb 224,463 Super User on at
    to get data from 2 qbds queries in report
    Please tell us more about what you mean by "I have 2 qbds queries in report".
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,451 on at
    to get data from 2 qbds queries in report
    SELECT  VendPackingSlipTrans.PackingSlipId, PurchLine.PurchId, PurchLine.ItemId, PurchLine.InventTransId,
    VendPackingSlipTrans.Ordered, VendPackingSlipTrans.Qty FROM PurchLine 
    JOIN VendPackingSlipTrans ON PurchLine.InventTransId = VendPackingSlipTrans.InventTransId
    AND ((VendPackingSlipTrans.DeliveryDate>={ts '2024-02-01 00:00:00.000'}
    AND VendPackingSlipTrans.DeliveryDate<={ts '2024-02-29 00:00:00.000'})) 
    WHERE  PurchLine.DATAAREAID = 'SA01'
    GROUP BY VendPackingSlipTrans.PackingSlipId, PurchLine.PurchId, PurchLine.ItemId, PurchLine.InventTransId, 
    VendPackingSlipTrans.Ordered, VendPackingSlipTrans.Qty
    
    
    
    SELECT PurchLine.PurchId, PurchLine.ItemId, PurchLine.InventTransId,SUM(VendInvoiceTrans.Qty), 
    SUM(VendInvoiceTrans.LineAmount), SUM(VendInvoiceTrans.LineAmountTax)
      FROM PurchLine 
    JOIN  VendInvoiceInfoLine ON PurchLine.PurchId = VendInvoiceInfoLine.OrigPurchId
    JOIN  VendInvoiceJour ON VendInvoiceInfoLine.ParmId = VendInvoiceJour.ParmId 
    JOIN  VendInvoiceTrans
    ON VendInvoiceJour.InvoiceId = VendInvoiceTrans.InvoiceId
    AND VendInvoiceJour.numberSequenceGroup = VendInvoiceTrans.numberSequenceGroup 
    and VendInvoiceTrans.PURCHASELINELINENUMBER = PurchLine.LINENUMBER
    and VendInvoiceTrans.ITEMID = PurchLine.ITEMID
    --and VendInvoiceTrans.Qty > 0
    AND ((VendInvoiceTrans.INVOICEDATE>={ts '2024-02-01 00:00:00.000'}
    AND VendInvoiceTrans.INVOICEDATE<={ts '2024-02-29 00:00:00.000'})) 
    WHERE  PurchLine.DATAAREAID = 'SA01'
    GROUP BY PurchLine.PurchId, PurchLine.ItemId, PurchLine.InventTransId,VendInvoiceTrans.PURCHASELINELINENUMBER
     

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,623 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,463 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans