News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... ¿Its possible to get P...

Finance | Project Operations, Human Resources, ...

Answered

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

(0) Share

Report

Report

Posted on by mopdsf@yopmail.com

54

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

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.

All responses Img

All responses (8)

Answers Img

Answers (1)

Verified answer

nmaenpaa 101,156 Moderator on at

Like (1)

Report

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!
mopdsf@yopmail.com 54 on at

Like (0)

Report

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

O other way to do a dashboard
mopdsf@yopmail.com 54 on at

Like (0)

Report

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 101,156 Moderator on at

Like (0)

Report

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.
mopdsf@yopmail.com 54 on at

Like (0)

Report

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
mopdsf@yopmail.com 54 on at

Like (0)

Report

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 231,923 Most Valuable Professional on at

Like (1)

Report

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 101,156 Moderator on at

Like (0)

Report

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

Leaderboard

#1

André Arnaud de Cal... 293,245 Super User 2025 Season 1

#2

Martin Dráb 231,923 Most Valuable Professional

#3

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans