Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

Posted on by 50

¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

 pastedimage1612788268794v1.png

If the answer is yes, ¿how can I do that?

 

If the answer is no, there is another way to do that and pass the data to SSRS out of dynamics ax 2012

 

I don’t want to use a solutions like that

 

 

public static void main(Args _args)

    {

        //Calculate totals per Purchase order

        PurchTotals purchTotals;

        PurchTable  purchTable  = PurchTable::find('PO.000018'); //Change the Purchase Order Id as per your system's data

        AmountCur   totalAmount;

        purchTotals = PurchTotals::newPurchTable(purchTable);

        purchTotals.calc();

        totalAmount = purchTotals.purchTotalAmount();

        info(strFmt("Purchase order: %1 - Total value: %2", purchTable.PurchId, totalAmount));

    }

Please, I love the details.

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    There are million ways to create dashboards. And how to create them doesn't have much to do with AX.

    But you can use Custom services to expose x++ logic as a web service.  Before you ask more about them, try asking from a search engine :)

    And if after studying the topic you need help, please post a new question. Thanks!

  • RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    O other way to do a dashboard

  • RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    Yes know, but I found a solutions using sql server profile and dynamics ax trace solution but I don’t understand.

    Could You Show me a simple clue solution

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    You don't need AX client to run x++ logic and get data from Dynamics.

  • RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    My goal is make a dashboard that gets that result and I don't want to use the microsoft Dynamics Rich client

  • RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    Yes, I did that with SQL Server Profile and  Dynamics AX -->Tools-->SQL Options--> Trace

    But everything points to dynamics does that calculation and then I was studying the PurchTotals class

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    While it's technically possible, you would have to reimplement the logic of PurchTotals class in SQL, which doesn't sound like a good idea to me.

    Instead, use an RDP class as the data source of your report. And simply call PurchTotals class there to calculate values.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: ¿Its possible to get Purchaser Order Amount Totals Only with SQL Server Query?

    Yes, all AX data is stored in SQL Server database, so it's possible to get any AX data by using SQL queries.

    About "how can I do that" - let me give you a general advice which will help you answer all similar questions and not just the PO totals.

    First try to find a form in AX that shows the information that you want to see. Then study the form data sources and possible underlying x++ code to understand how the data is fetched from the databse. You can also use AX Trace Parser or SQL Server Profiler to capture the actual SQL queries that AX makes when you open the form.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans