Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Problems with aggregate functions in a report using a temporary table

Posted on by Microsoft Employee

Good morning!

I am using AX 2009 and I have a question. Is it possible, for a report whose DataSource is a temporary table, to show the sum of the prices of the items grouped by their Id?

Let me explain it better. I needed to use a temporary table in order to keep the informations of three queries (as I have seen, it is not possible to do union queries that use inner join in AX). Some of these informations are the Items Id and their sale values.

I needed the report to show me the sum of the values of each sale of the items (for example: let's suppose that we have 2 sales of the item #1, whose value is $3,000.00, and 1 sale of the item #2, whose value is $2,500.00; the report would show: "item #1 - $6,000.00; item #2 - $2,500.00"), but the report is just showing the total sum of all items (using the previous example, it doesn't show the ItemId and shows the value $8,500.00).

I duplicated this report, but using a non-temporary table (which, obviously, does not have all the information I need) and it showed the sum of each item.

I have added a sum field for the sales prices in the report DataSource temporary table and added the ItemId field under the GroupBy node. Should I have proceeded in a different way?

Thank you in advance,

Luiz Bezerra

*This post is locked for comments

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans