I have created a report using the table builder. I want to subtract the value of one field from another (Inventory - Reorder Point) on each line returned but am struggling to see how to do it. Can anyone point me in the right direction?
Thanks
7 comments
-
Jet Reports Historic Posts Hi -
You would click on "[size=200]+[/size] Add Formula" and then use section on the right to specify the fields you want to use in your formula.
-HP -
Jet Reports Historic Posts Unfortunately the Formula option is greyed out and gives a message saying "Formula fields are only available in Jet Essentials". It must be possible to do this somehow in the Express version?
-
Jet Reports Historic Posts Sorry about that.
I had forgotten that feature is only available in Essentials. The reason for this is that the underlying functionality required to perform this type of calculation requires Jet Essentials.
-HP -
Jet Reports Historic Posts I am frustrated by the same issue. It seems the only way to overcome this is to do the calculation FIRST in NAV, so that it is stored in a field. Not very satisfactory.
If anyone finds a solution to do this inside Excel, I would love to know about it. -
Jet Reports Historic Posts You could create a pivot table off of the data and then use a calculated field (under fields, items & sets).
Steve -
Jet Reports Historic Posts Adding formulae to a Jet Expresst table is possible, with a little improvisation. You need to write a simple macro, to
a) Executet the Jet Reports refresh
b) insert a column into your data table
c) enter a formula into the new column
d) amend the column heading
If you are using a pivot table to summarise the data, you then need to add vba code to the macro toL
e) add the new column to your pivot table
f) format the pivot field.
Some tips:
1) All options or hidden rows / columns need to be on a separate sheet
2) Put your list of fields and link formulae on a separate sheet
3) Use macro record to generate the code
4) add a button to the pivot table sheet to run the macro -
Jet Reports Historic Posts Nice job!
-HP