AX / D365FO – How to test the Query syntax and results of a Query

Have you created an AOT Query and want to check if the syntax is correct and the results are what you expected? You can create a Runnable class like show below class TestQuery { public static void main(Args _args) { /* Example job used to test query results with.: */ QueryRun queryRun; Counter totalRecords; // … Continue reading AX / D365FO – How to test the Query syntax and results of a Query

AX / D365FO – How to create automatic join relationship between tables in an AOT Query

Are you creating an AOT query and want to join 2 or more tables but don't know the relationships between them? No problem! You can use the "Use Relations" property like shown below In this example I want to to use the native relation between InventSerial and InventSum tables (which is InventSerial.ItemId = InventSum.ItemId). By … Continue reading AX / D365FO – How to create automatic join relationship between tables in an AOT Query

AX / D365FO – Create a simple SSRS Report based on a Query

First, you need to create a query. Open Visual Studio as administrator. From the File menu, select New > Project. Select Dynamics 365 on the left pane. select Finance Operations in the middle pane. Enter ReportingProject in the Name field. Select OK. In the Solution Explorer, right-click your project node, and then select Add > New Item. Select Data Model and then select Query. For the Name field, enter CustTableQry and then select Add. Open the … Continue reading AX / D365FO – Create a simple SSRS Report based on a Query

SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()

The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string. This example uses the STRING_AGG() function to generate lists of emails of customers by the city: SELECT city, STRING_AGG(email,';') email_list FROM sales.customers GROUP BY city; This is the … Continue reading SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()

AX / D365FO – How to identify the SQL Server version and edition

To identify the SQL Server version and edition running on your machine open SSMS, access your Master DB and write the following statement select @@version You'll get a result like that. In my case I have a Developer Edition Microsoft SQL Server 2016 Microsoft SQL Server 2016 (SP2-CU16) (KB5000645) - 13.0.5882.1 (X64) Jan 25 2021 … Continue reading AX / D365FO – How to identify the SQL Server version and edition