I was recently asked to find a way to perform some simple data analytics across some data within D365FO. In my case, this was audit data that could easily be millions of records so just exporting to an Excel file to do this was not really an option. Instead I wanted to build a simple form that would perform the analysis for me.
Note: This could also be done very easily within a full data analytics tool like Power BI
In our scenario I had audit data that included a table name of the table impacted by the change and user id of the user who made the change as well as a timestamp of when the change occurred.
1) Wanted to do a count of these two columns to get a ‘number of records by table’ and ‘number of records by user’ output
2) Wanted to be able to apply date filtering to be able to say during this time range here are the number of changes that occurred for each output
Normal QueryBuildRange operations don’t really accomplish what we are trying to do above, as we don’t really want to filter the data. Instead what we would really want to do is write a SELECT statement to do a GROUP BY and use that as the data source. The available options to do something like this are to use temporary tables, either an in-memory table or a tempdb table.
1) I started by creating two temporary tables, these tables included a name column (of the user or table) and a count column to show the count of the records of this type. To create the temp table, I created a table object in X++ and then changed the Table Type to TempDB
2) I then created a new form and added both temp tables as data sources
3) On the form, I wanted to create two grids one for each tempdb. For each grid object, I set the data source as the form datasource from the previous step
4) Next I modified the form code, there are a couple
- In the init() method, be sure to set the datasource linkPhysicalTableInstance() as an instance of the temp table
- In the executeQuery() method, there are a couple points:
- Remove all data from the temp table
- Created a SELECT statement to perform the GROUP BY function on the data fields, including the start and end dates from the date filter on the form
- Insert the aggregated records into the temp table
5) On the form I created a start/end date time fields, so I wanted the grids to update based on any changes to those fields. By doing this any time either the start or end dates were updated the executeQuery() function from both
6) The final result looks like the form below, it includes a start and end date fields that control the records in the grids which show the ‘changes by user’ and ‘changes by table’ record counts.
The above process shows how you can use temp tables to perform simple data analytics and utilize it as a data source for a grid.