I’ve recently been creating some reports for a client using Jet Reports and data pulled from Microsoft Dynamics 365 Business Central. One of the requirements was to have a report which pulled out Intrastat related information in a specific format. As well as pulling out the standard information, they also wanted a calculation of the net weight for a sales order line, which mean linking from the Purch. Inv. Line (123) to the Item (27) table and multiplying the quantity by the net weight.
I initially tried to do this using a calculated field in the Excel PivotTable which was added on top of the data returned by Jet Reports, but quickly realised that the result wasn’t correct. This [prompted me to go back to the source data and see if I could perform the calculation using Jet Reports as it got the data rather than afterwards.
The report was being built using the Table Builder tool of Jet Reports; to add a calculated field in Table Builder, click the Add Formula button in the bottom left:
The pane in the bottom right will change to include a Formula box. To add a field type @ and select the required field from the list; mathematical operators (such as * or +) can simply be typed into the field:
The New Formula field in the top right will be updated as you type; to rename it click on the field heading and type the required name; when the field is complete, click OK to commit the change:
The report will now be included in the report for use; in my case the net weight for the number of items on the line was now correctly being calculated in the PivotTable.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
