You’re offline. This is a read only version of the page.
Announcements
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:
Share
Reportwhile 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
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.
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.
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.
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 ?
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.
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.
#1
André Arnaud de Cal...
293,302
Super User 2025 Season 1
#2
Martin Dráb
232,114
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
All responses (
Answers (
