How to group in Processing only report?

Aravindh_Navision · 2012-08-28

Hi,

I am designing one report which is a processing only report.

I have the following records in the table.

Acc. No.   - Amount
```````````````````
1. A12345 - 50.00
2. A12345 - 100.00
3. A12345 - 25.00
4. A12222 - 24.00
5. A12222 - 24.00
6. A32154 - 120.00

I need to total the Amount field based on the Acc. No.

My final output should be:
``````````````````````````
1. A12345 - 175.00
2. A12222 - 48.00
3. A32154 - 120.00

And I need to write this output in text file using the File datatype in 3 lines by grouping according to Acc. No.

Can anyone guide me how to proceed with this?

Thanks and Regards,
Aravindh

DenSter · 2012-08-28

Keep track of the account number and only create output when it changes. You could also create a dataitem for the account itself and total the numbers in a variable.

Lots of different ways, take your pick. Be creative.

Aravindh_Navision · 2012-08-28

Thank you DenSter for your reply.

I can do the second part, that is writing in text pad. But got struck in the first part. ](*,) Can you help me?

DenSter · 2012-08-28

You could write some code yourself and I'd be happy to answer any specific questions you may have. However I don't write code for other people.

Why would you use textpad, do you have something against the C/AL editor?

Aravindh_Navision · 2012-08-29

DenSter,

From the text file that is generating from Navision, will be send to other third party tool as an input for some processing. I could do code for this using File datatype.

DenSter · 2012-08-29

Sounds great, let us know when you have a specific question.

Miklos_Hollender · 2012-08-29

Create a new table. Give it whatever number - it will be only used for temptable which do not need to be in the licence range. The primary key should be 5 Code20 fields like Key1, Key2, Key3,Key4,Key5 allowing you 5 levels of grouping, usually enough. Also add fields like Dec1, Dec2...Dec10, Int1,Int2, Int3, Date1,Date2,Date3,Text1,Text2,Text3. Call it Reporting Table and save it. (I actually called the table GroupBy because boo C/AL, SQL rocks! :-) )

Use this as a general tool for grouping i.e. put data into it (temptable) like

CLEAR(TempGroupBy);
IF TempGroupBy.GET(Acc."No.") THEN BEGIN
    TempGroupBy.Dec1 += Acc."Amount";
    TempGroupBy.MODIFY;
END ELSE BEGIN
  TempGroupBy.Code1 := Acc."No.";
  TempGroupBy.Dec1 := Acc."Amount";
  TempGroupBy.Insert;
END;

Then just loop through it and process the results.

This saved my backside so many times...

Aravindh_Navision · 2012-08-30

Thank you Mikalos for your kind favour..!

This really helped me to achieve what I need to. Instead of CLEAR(TempGroupBy), is it right using TempGroupBy.DELETEALL in OnPreDataitem?

Aravindh_Navision · 2012-08-30

I need a clarification in the below code. Before that I just explain what I am doing. I have 5 dataitems and those dataitems are NOT indented. I am executing the dataitem one-by-one and writing the output into text file using FILE datatype. While doing so, the previous dataitem's output is overwritten by the later one. I have written the same code in all the dataitem's OnPostDataitem trigger.

Variables:

NFILE (Datatype: File)
WriteStream (Datatype: OutStream

OnPostDataitem():

NFile.CREATE('C:\Sample.txt');
NFile.CREATEOUTSTREAM(WriteStream);
WriteStream.WRITETEXT(Text1 + Text2 + Text3+...+Text n);
WriteStream.WRITETEXT;
NFile.CLOSE;

Can anyone point out me, where am I doing wrong?

Aravindh_Navision · 2012-08-30

Moving the above mentioned to new topic heading since it is a different one.

viewtopic.php?f=23&t=54588

Aravindh_Navision · 2012-08-31

My special thanks to Miklos and Denster for the valuable inputs and help. It worked. :thumbsup:

How to group in Processing only report?

Answers

Categories