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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How can i find how man...
Microsoft Dynamics GP (Archived)

How can i find how many transaction doing daily in GP

(0) ShareShare
ReportReport
Posted on by Refeesh Ali 5,201

Hi,

how can i analyze how many transaction is doing in GP per day. please advice.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Saatya Profile Picture
    Saatya 145 on at
    RE: How can i find how many transaction doing daily in GP

    Hi Refeesh Ali,

    You can use Smart List in Microsoft Dynamics GP to track down the daily transaction for all modules by the Document date of the transaction. Try out the following steps for your daily transactions,

    1. Microsoft Dynamics GP-->SmartList.

    2. In SmartList Window, Select the Module that you need see the daily transaction.

    3. In top of the SmartList select the Search box--> Column Name:Document Date --> Filter:Equal To --> Value:Give your Transaction Date.

    Now you can see the transactions that were made on that particular date.

    I hope this would helps you in better way.

    Thanks and Regards,
    Sathya

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: How can i find how many transaction doing daily in GP

    Refeesh -

    Here is the last installment of queries for transactions in GP on a given day.

    The Fixed Asset tables presented a unique challenge.  Transactions are written to the FA00902 table, and have either been interfaced with the General Ledger or not, so there is no easy way to identify transactions for a specific date.

    I came up with an approach I think works best.  The following queries return Transactions Not Interfaced with the GL - these all have the same GL Interface Date (GLINTTRXDATE) value of 1900-01-01 00:00:00.000. Transactions that have already been interfaced with the GL can be identified by a specific date, but will generally have been interfaced at a month-end.

    This query reveals the Number of Fixed Asset Transactions, which have not been interfaced with the GL and interfaced transactions on a given day, as well as the number of overall lines.

    /* Fixed Asset Transactions NOT Interfaced with GL */

    select COUNT(FA_Trxs) FA_Trxs from (

    select count(FA_Doc_Number) FA_Trxs from FA00902

    Where GLINTTRXDATE = '01/01/1900'

    Group by FA_Doc_Number ) FAT

    /* Fixed Asset Transaction Lines NOT Interfaced with GL */

    Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

    Where GLINTTRXDATE = '01/01/1900'

    DECLARE @Date as DATETIME

    Set @Date = '12/31/2016'

    /* Fixed Asset Transactions Interfaced with GL */

    select COUNT(FA_Trxs) FA_Trxs from (

    select count(FA_Doc_Number) FA_Trxs from FA00902

    Where GLINTTRXDATE = @Date

    Group by FA_Doc_Number ) FAT

    /* Fixed Asset Transaction Lines Interfaced with GL */

    Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

    Where GLINTTRXDATE = @Date

    I hope these queries help you and other folks analyze the transaction volumes in the various GP Modules.

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: How can i find how many transaction doing daily in GP

    Refeesh,

    Here are the queries for the Inventory Transaction Numbers.

    DECLARE @Date as DATETIME

    SET @Date = '04/12/2017'

    /* Inventory Transactions */

    Select SUM(a.IV_Trxs) IV_Trxs from (

    select count(I.IV_TRXS) IV_Trxs from (

    select count(IVDOCNBR) IV_Trxs from IV10001 group by IVDOCNBR) I

    Union All

    select COUNT(IH.IV_TRXS) IV_Trxs from (

    select COUNT(DOCNUMBR) IV_Trxs from IV30300 WHERE DOCDATE = @Date group by DOCNUMBR) IH ) a

    /* Inventory Transaction Lines */

    select SUM(IV_Trxs) from (

    select COUNT(I.IVDOCNBR) IV_Trxs from IV10001 I

    Union All

    Select COUNT(DOCNUMBR) IV_Trxs from IV30300 where DOCDATE = @Date) a

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: How can i find how many transaction doing daily in GP

    Refeesh -

    Here are the Purchasing Modules. 

    declare @Date as DATETIME

    SET @Date = '04/12/2017'

     

    /*Payables Transactions*/

    Select SUM(PM_Trxs) PM_Trxs from (

    Select COUNT(vchrnmbr) PM_Trxs FROM PM20000 where DOCDATE = @Date

    Union All

    Select COUNT(vchrnmbr) PM_Trxs FROM PM30200 where DOCDATE = @Date) a

    /*Purchase Orders*/

    select sum(a.Sales_Orders) Purchase_Orders from (

    Select count(P.Sales_Orders) Sales_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP10100 PH

    Inner Join POP10110 PL

    on PH.PONUMBER = PL.PONUMBER

    and PH.POTYPE = PL.POTYPE

    where PH.DOCDATE = @Date

    group by PL.PONUMBER) P

    Union All

    Select count(P.Sales_Orders) Purchase_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP30100 PH

    Inner Join POP30110 PL

    on PH.PONUMBER = PL.PONUMBER

    and PH.POTYPE = PL.POTYPE

    where PH.DOCDATE = @Date

    group by PL.PONUMBER) P) a

    /*Purchase Lines*/

    select sum(a.SOP_Lines) POP_Lines from (

    Select COUNT(PH.PONUMBER) SOP_Lines from POP10100 PH

    Inner Join POP10110 PL

    on PH.PONUMBER = PL.PONUMBER

    and PH.POTYPE = PL.POTYPE

    where PH.DOCDATE = @Date

    Union All

    Select COUNT(PH.PONUMBER) POP_Lines from POP30100 PH

    Inner Join POP30110 PL

    on PH.PONUMBER = PL.PONUMBER

    and PH.POTYPE = PL.POTYPE

    where PH.DOCDATE = @Date) a

  • Refeesh Ali Profile Picture
    Refeesh Ali 5,201 on at
    RE: How can i find how many transaction doing daily in GP

    hi Mahmoud, your analysis is based on GL transaction its working fine but i have to know whole the module transactions

    Harry Lee ,

    i get the result for Finance module and sales also i need the remaining modules (inventory,purchase,fixed asset)

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at
    RE: How can i find how many transaction doing daily in GP

    So what do you mean by 'Transaction'? what if I created new customer in a system? will it not a transaction? and what if I edit some information in my sales order and save it?

    Does transaction means only creating and posting of documents?

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: How can i find how many transaction doing daily in GP

    Refeesh -

    I went a different direction with this request.  The following query reviews the Open and History files in Finance, Sales and Mfg to total the number of unique transactions and the number of lines in each of these data-sets.  If you're interested in this approach, let me know, and I will complete the set by doing the same analysis for Purchases and Purchase Orders.  You can use the variable set at the start of the query to change the run date for the query

    DECLARE @Date as DATETIME

    SET @Date = '04/12/2017'

    /*Journal Entries*/

    select sum(a.Journal_Entries) GL_Transactions from (

    select count(c.GL_Transactions) Journal_Entries from (Select COUNT(jrnentry) GL_Transactions FROM GL20000 where TRXDATE = @Date group by JRNENTRY) c

    Union All

    select count(c.GL_Transactions) Journal_Entries from (Select COUNT(jrnentry) GL_Transactions FROM GL30000 where TRXDATE = @Date group by JRNENTRY) c ) a

    /*GL Transaction Lines*/

    select SUM(a.GL_Trx_Lines) GL_Trx_Lines from (

    Select count(JRNENTRY) GL_Trx_Lines FROM GL20000 where TRXDATE = @Date group by TRXDATE

    Union All

    Select count(JRNENTRY) GL_Trx_Lines FROM GL30000 where TRXDATE = @Date group by TRXDATE) a

    /*Sales Orders*/

    select sum(a.Sales_Orders) Sales_Orders from

    (Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP10100 SH

    Inner Join SOP10200 SL

    on SH.SOPNUMBE = SL.SOPNUMBE

    and SH.SOPTYPE = SL.SOPTYPE

    where sh.DOCDATE = @Date

    group by SL.SOPNUMBE) S

    Union All

    Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP30200 SH

    Inner Join SOP30300 SL

    on SH.SOPNUMBE = SL.SOPNUMBE

    and SH.SOPTYPE = SL.SOPTYPE

    where sh.DOCDATE = @Date

    group by SL.SOPNUMBE) S) a

    /*Sales Lines*/

    select sum(a.SOP_Lines) SOP_Lines from

    (Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP10100 SH

    Inner Join SOP10200 SL

    on SH.SOPNUMBE = SL.SOPNUMBE

    and SH.SOPTYPE = SL.SOPTYPE

    where sh.DOCDATE = @Date

    Union All

    Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP30200 SH

    Inner Join SOP30300 SL

    on SH.SOPNUMBE = SL.SOPNUMBE

    and SH.SOPTYPE = SL.SOPTYPE

    where sh.DOCDATE = @Date) a

    /* Work Orders */

    Select COUNT(MANUFACTUREORDER_I) Released_MOs from WO010032 WO WHERE STRTDATE = @Date

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: How can i find how many transaction doing daily in GP

    Hello again Refeesh

    I would consider the following script for a rough assessment regarding the number of "Journal entries" per series, per month, per day. You may take this into Excel and provide a pivot table report accordingly for average number or any other consideration

    SELECT  Series ,
        OPENYEAR ,
        TRX_MONTH ,
        TRX_Day ,
        COUNT(JRNENTRY) Number_Of_Journal
FROM    ( SELECT DISTINCT
                    OPENYEAR ,
                    MONTH(TRXDATE) TRX_MONTH ,
                    DAY(TRXDATE) AS TRX_Day ,
                    JRNENTRY ,
                    CASE SERIES
                      WHEN 1 THEN 'All'
                      WHEN 2 THEN 'Financial'
                      WHEN 3 THEN 'Sales'
                      WHEN 4 THEN 'Purchasing'
                      WHEN 5 THEN 'Inventory'
                      WHEN 6 THEN 'Payroll – USA'
                      WHEN 7 THEN 'Project'
                      ELSE ''
                    END AS Series
          FROM      dbo.GL20000
        ) AS A
GROUP BY OPENYEAR ,
        TRX_MONTH ,
        TRX_Day ,
        Series
ORDER BY A.OPENYEAR ,
        TRX_MONTH ,
        TRX_Day ,
        Series


    The script above considers the number of journal entries in the open year. For historical transactions, you need to consider the GL30000 instead.


    Your feedback is highly appreciated,

  • Refeesh Ali Profile Picture
    Refeesh Ali 5,201 on at
    RE: How can i find how many transaction doing daily in GP

    i want to know based on each module

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 experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans