Microsoft Dynamics AX (Archived)

Group data in a container

(0) Share

Report

Posted on by OrangeRocks

700

Hi,

i would like to group data in a container. Is it somehow possible?

Here is in the detail what I want: I have a table query with the grouping:

while select sum(TaxBaseAmount) from tempTable1
         group by TaxDirection, TaxCode
{
    a = TaxBaseAmount;
    // do other things
}

I would like to replace looping through this table with the container that I built earlier. 
The problem is that I need grouped values and not single values:

Incoming	A11	10
Incoming	B22	11
Incoming	A11	12
Incoming	C33	13

So as a result set I need eventually the sum lines with "Incoming" + "A11" = 22!

Is there a possibility? It is AX 4.0!
Thanks a lot!

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (1)

Report

What do you mean by replacing the while select with a container? Does it mean that you already fetched the data from a database and put them into a container? If so, I believe that working with database and SQL is a much more efficient approach (actually, it's designed for this kind of things); container looks like a poor choice for storage and you would have to explicitly write code for grouping data the data.

If you insist on fetching the data in advance, why don't you use a temporary table? Then you can use GROUP BY as usual.

Was this reply helpful? Yes No
OrangeRocks 700 on at

Like (0)

Report

Thank you, you understood everything above correctly;

The report does fetches the data in advance in a temp table: the buffer for the temp table consists of 16000 records and the report is very slow towards the end. After the temp table is completely loaded in the buffer, the records are selected with the GROUP BY keyword.

I guess the container idea was unfortunate, maybe some other ideas come up here.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Hi Pam B.,

You probably want to share additional details with us, because it's not enough information to give you an advice.

Why do you have so many records?

Why you don't do group by initially before you put data in temp table ?

What type of temp table do you use, inMemory or tempDB ?

Was this reply helpful? Yes No
OrangeRocks 700 on at

Like (0)

Report

Thank you Evgen,

It is a report for showing tax transactions for some period of time, so e.g. for the period of 5 months I get 16000 records.

I work with two tables in the report: TaxTrans and tempTaxTrans. The table TaxTrans is the data source of the report and the Sorting in the AOT Node is set to TaxDirection and TaxCode.

The report is in AX 4.0, I am not sure if there are different types for tempTables.

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

Do you have 16000 records after aggregation? If you have 16000 transactions, you surely get less records when you group the them, right? I would assume you put the aggregated data to the temporary table, therefore it should be a significantly lower number records than the number of transactions. Also, you code uses just three fields, therefore each record of the temporary table will be pretty small.

You're right, AX 4.0 has just a single type of temporary table; Ievgen just overlooked your version.

I can't comment on "the report is very slow towards the end" without more information.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (1)

Report

As Martin suggested you probably need to look how data is populated into temp table and try to aggregate it at early stage because it does not make too much sense to fetch 16000 records into temp table and then group them.

Also are you sure that aggregation is a problem ? Code to populate those records can take significant amount of time as well.

Was this reply helpful? Yes No

Community site session details

Group data in a container

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Group data in a container

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates