Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

Problems with aggregate functions in a report using a temporary table

(0) ShareShare
ReportReport
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!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans