The following article shows highlights the best practice for the following D365 Dynamics Finance and Operations features:

• Replacement reports

• Tables/Fields

• Subreports

• Group by/calculations

• Size of the XML file

Replacement reports

Use a replacement report instead of an SSRS report. The replacement report often provides better performance and offers the features of the Query wizard.

On the Replacement report page, select Create new to name the replacement report.

A new query report is created (the setup from the SSRS such as Form links and Archive is all copied). On the SSRS report, you can see it is replaced by your replacement report. You can now forward your work with the replacement/query report.

Select Data sources and delete all the tables you wish to delete. Use Query wizard to add necessary tables.

Tables/Fields

Add tables and fields within the Query wizard and avoid using Data sources to add tables.

Avoid adding fields using the Structure option. Your manually created fields are displayed in yellow; delete these and add them again by using the Query wizard.

Subreports

Instead of using a subreport, create a new query by using the Query wizard.

Example how to add e.g. Table=CompanyInfo

1. Open the Query wizard > Next > Next.

2. Place your cursor on the table where you want to add a new query and click the + button.

3. Give the query a name, add Table=CompanyInfo and set up/check relations.

4. Add fields as normal and the result will appear:

Group by/calculations

Take into consideration whether using this feature will help reduce the size of the XML file, and perform the calculations from the Lasernet Connector.

1. Go to Tables in Query wizard and add a new query.

2. Set the property Group by to Yes.

3. Add the table you want to group by.

4. Add the fields you want to group by.

Result:

This will provide one record per Itemid and DatePhysical.

Calculation

It is also possible to calculate your fields. Go to fields in the Query wizard and add the field you wish to calculate. Within the properties, you can set up your calculation.

It is also possible to write Direct SQL by choosing Custom in the property Group by function.

SELECT SUM(SalesLine.QtyOrdered) FROM SalesLine WHERE SalesLine.ItemId=InventTable.itemId

Result:

The sum is for all legal entities/companies.

To obtain the sum by legal entities/companies, follow these steps:

1. Add Field=DataAreaId to your table using the right arrow button.

2. The following dialog will be displayed, choose Yes.

3. Change your Direct SQL to include DataAreaId in your WHERE clause.

SELECT SUM(salesLine.QtyOrdered) FROM SalesLine WHERE SalesLine.ItemId=InventTable.itemId AND SalesLine.DataAreaId=InventTable.DataAreaId

Result:

Size of the XML file

The smaller the size of the XML/Grab file, the better the performance will be. Try as much as possible to set up your structure and calculations in the Lasernet Connector. Follow these steps as a guide:

1. Delete Tables and Fields not used by Query wizard.

2. Test your report with real and sufficient data.

3. Check your table relations to ensure that you do not get unnecessary data/structures.

4. Use the Lasernet Connector to group and calculate data; it is often faster than, for example, using an XML transformer in Lasernet Developer.