Many times when creating processing reports in Microsoft Dynamics NAV, after the report processes all desired data, you might want to display for the user a log with what happened or with the errors that occurred. You can use for example IF Not ExampleCodeunit.Run THEN store the errors (with the help of function GETLASTERRORTEXT) in a DotNet list, process the rest of the records and at the end display the errors in the report.
Let’s see a simple example of how you can use a DotNet list to store and display information in a 4 columns report.
For this example I will use the data from demo company Cronus, table 17- G/L Entry. We need to calculate and display in a report, using a DotNet list, the Total Amount of Transaction, the Number of G/L Entries and the Average Amount per Transaction for each G/L Account.
* I created this example only to show the concept because I couldn’t publish a real example. In the real life I use the DotNet list mainly for “log information”. (ex: what a processing report did, how many records it modified and how, which are the records where an error occurred and were not processed etc). If you need to summarize the data from GL Entry for each GL Account, please use a query because it’s a more efficient way 🙂
For this I’ve created a new report 50000 “Test Report Dot Net List” and in it’s Globals I’ve created a DotNet variable with subtype System.Collections.Generic.List`1 and other 5 variables as follows:
In the “ListExample” I will store the calculated data for each GL Account and then I will display it to the report layout by copying it from the list to the report fields.
-> First step is to add two data items and the fields that we need to display in the layout. We will need the “GL Account” and the “Integer” data items. The “GL Account” is used to calculate and add the summarized data to the list and then the “Integer” to write what we have in the list to the fields. The problem is that in a DotNet list we cannot store data in a tabular structure (with multiple rows and columns), but only in a row. All items added to the list are stored one after the other (ex: “item1,item2,item3..itemx”). That’s why, I’ve created an algorithm to transform the data from list elements to table structure.
-> Next we create a new instance of the List. We can do this in the OnPreDataItem trigger of the “G/L Account” data item.
G/L Account - OnPreDataItem() ListExample := ListExample.List;
-> Then, in the OnAfterGetRecord trigger of the “G/L Account” data item we calculate the data as you can see bellow:
G/L Account - OnAfterGetRecord() CLEAR(GLEntry); CLEAR(SumAmount); CLEAR(CountTransactions); GLEntry.SETRANGE("G/L Account No.","No."); IF GLEntry.FINDSET THEN REPEAT SumAmount += GLEntry.Amount; CountTransactions += 1; UNTIL GLEntry.NEXT = 0; IF CountTransactions > 0 THEN BEGIN ListExample.Add("No."); ListExample.Add(SumAmount); ListExample.Add(CountTransactions); ListExample.Add(SumAmount / CountTransactions); END;
*The above code filters the GL Entry table on each GL Account and then loops through all the resulted lines in order to calculate the sum of amount and count of transactions. Then it adds the GL Account No and the calculated values as elements in the DotNet list.
-> In the OnPreDataItem of the Integer, we calculate the number of the rows for the table that we will create transforming the list:
Integer - OnPreDataItem() SETRANGE(Number,1,ListExample.Count / 4);
And finally, in the OnAfterGetRecord trigger of the Integer we can see the algorithm that transforms the data from the list into a table:
Integer - OnAfterGetRecord() IF Integer.Number = 1 THEN BEGIN i := -1; j := 0; k := 1; l := 2; END ELSE BEGIN i += 3; j += 3; k += 3; l += 3; END;
-> Now let’s see how the report layout is designed.
First we need some labels:
And then I created the below layout in Visual Studio:
Because the items are stored in the list as text, we now need to convert them as decimals and/or integers:
-> We can now run the report for all GL Accounts and we get the following result for the Cronus test database:
-> You can download the *.fob file of my sample report and the result from THIS LINK.