Hi All,
I'm trying to find a way to to get inventory value at date through a key function or similar.
Currently using formula =NL("Sum","Item Ledger Entry","Cost Amount (Actual)")..etc
This however takes a long time to run.
for my quantity at hand i used a key function and used the formula
=NF($C18,"Net Change","Date Filter") etc which works great.
however can't seem to find a way to use this to find cost??
Hope this make sense, any help or alternatives is appreciated.
Regards,
Hi Hakan,
I do not know how the report should look like, but reports are much faster when using nl("table"... Use Table Builder to create e new report choose item and link value entry to it. use item field data filter to be able to set the period. Select Item No., Description, Inventory from Item table and Cost Amount (Expected) and Cost Amount (Actual) from Value Entry, Add formula to create the sum of Cost Amount (Expected) and Cost Amount (Actual) to have the inventory value. Press ok to have jet create the report. now link the Inventory, Cost Amount (Expected) and Cost Amount (Actual) fields to the Date Filter value cell by adding a filter to the formula's manually and you good to go. I have saved the xml template of the wizard which is shown below.
Kind Regards,
Nicolai
<?xml version="1.0" encoding="utf-8" standalone="no"?>
<tableReport xmlns="http://www.jetreports.com/jet.tableUI" table="Item">
<fields>
<tableReportField table="Item" header="No." type="Normal">
<stringField>No.</stringField>
</tableReportField>
<tableReportField table="Item" header="Description" type="Normal">
<stringField>Description</stringField>
</tableReportField>
<tableReportField table="Item" header="Inventory" type="FlowField">
<flowField id="127b6ace-591b-43ba-b227-5b60982d4ff2" name="Inventory" type="FlowField" reverseSign="false">
<flowFilters xmlns="http://www.jetreports.com/jet.common" />
</flowField>
</tableReportField>
<tableReportField table="Value Entry" header="Expected" type="LinkSum">
<linkSumField id="83e592ee-3f5f-477e-b631-70ddd61868b8" name="Cost Amount (Expected)" type="Sum" reverseSign="false">
<filters xmlns="http://www.jetreports.com/jet.common" />
</linkSumField>
</tableReportField>
<tableReportField table="Value Entry" header="Actual" type="LinkSum">
<linkSumField id="9e1c007e-0b34-47b6-b98f-8a2f5e8ba401" name="Cost Amount (Actual)" type="Sum" reverseSign="false">
<filters xmlns="http://www.jetreports.com/jet.common" />
</linkSumField>
</tableReportField>
<tableReportField table="" header="Amount" type="FormulaField">
<stringField>[@[Expected]]+[@[Actual]]</stringField>
</tableReportField>
</fields>
<stringFilters>
<stringFilter field="Date Filter" value="..31-12-2017" isReportOption="true" isDateFilter="true" xmlns="http://www.jetreports.com/jet.common" />
</stringFilters>
<linkFilters>
<linkFilter linkTable="Value Entry" isInclusive="false" xmlns="http://www.jetreports.com/jet.common">
<stringFilters />
<linkFields>
<stringFilter field="Item No." value="No." isReportOption="false" isDateFilter="false" />
</linkFields>
<keyFields />
<fields />
</linkFilter>
</linkFilters>
</tableReport>
Hi Nicolia,
Thanks for the reply!
I'm relatively new to this whole jet reports business so getting my head around it all.
First of all I'll hoping to build / incorporated cost amounts in several historical reports so looks doesn't matter to much right now.
Secondly I have been trying to work on your example but with not with complete success :/. This is what I did...
However when I ran the report just gave me an Inventory amount and inventory cost as of today(now)?
Assuming its because I haven't linked it properly to the date filter? but couldn't work it out
Side note: excuse the ignorance i thought that table builder was more for static reports? (ei more of a data dump/ values as of now rather then reporting on historical trends?). Only really have used the function wizard so far.
Thanks heaps for the patience and help :)
Hakan
Hi Nicolai
I am also trying to get an inventory valuation report, none in the Jet Professional example templates unfortunately.
Are you able to attach the XML file here so I can download it & try myself?
thanks
Rodney
Dear Expert
i need the help to find the stock detail of past date from jet report so please suggest me the solution as i have tried lost but not get what i want. i am looking for the data for stock details on past date not today, i go through item ledger entry and i tried its show the data from past 2013 but i need the stock data of date suppose upto 7/18/2018. i got the data but its too long data so i just want the stock only not all the details like purchase, receipt, transfer in and out invoiced all these so please suggest,
Thanks
Ramboo