I saw another thread here with a similar question but the resolution didn't work for me. I have a database with a TotalTime field and a LaborCost field.I would like to sum the product of these two fields.
What worked in the other thread was the following:=NL("Sum","Sales Line","=NF(,""Quantity"")*NF(,""Unit Cost"")",….)
I replaced that with the table WorkOrderLabor, TotalTime, and LaborCost but it threw an error about "=NF(,"… not being a valid field.
On a related question, is it possible to find the difference in values from one table and then sum them? If I was trying to find the average open days for workorders that were closed in a specific month…
In sql I believe it would be similar to Select SUM(DateClosed-DateOpened) from WorkOrders
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Hi,
Calculated fields only work in NAV. From the names of your fields and your reference to SQL, I'm assuming you're using a Universal connector, not NAV. In Universal, you can actually use SQL= to directly execute your SQL query like this:=NL("Sum","SQL=Select SUM(DateClosed-DateOpened) as Field from WorkOrders","Field")
Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Yes, that worked. Is there a way of adding Schema to that? The universal connector is an ODBC connection to an oracle database. I needed to specify the table as ISPCORR.WorkOrders in the sql query in order for it to return a result. If I just said WorkOrders, it would throw an error about invalid table name. I tried just WorkOrders and 'Schema=','ISPCORR', but it still threw the previously mentioned error.
The issue here is that the report is planned for multiple locations that each use a different schema. The intention was for there to be a Schema field on the options table and allow each location to put in their own schema. -
Jet Reports Historic Posts Ignore that, I solved it by appending
"&Options!D15&" within the sql query so that it is created with the appropriate schema.
Please sign in to leave a comment.