Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Group data in a container
Microsoft Dynamics AX (Archived)

Group data in a container

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

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Group data in a container

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: Group data in a container

    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.

  • OrangeRocks Profile Picture
    OrangeRocks 700 on at
    RE: Group data in a container

    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.

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Group data in a container

    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 ?

  • OrangeRocks Profile Picture
    OrangeRocks 700 on at
    RE: Group data in a container

    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.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: Group data in a container

    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.

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 Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans