Dynamics Community Forum Thread Details

Problems with aggregate functions in a report using a temporary table

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

Quick Links