Create a Calculated Field in the Jet Reports Table Builder

Jet ReportsI’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:

Table Builder with the Add Formula button ringed

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:

Formula being entered

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:

New field renamed

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.

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *