I am starting my first nav report, I need to get the last date for any transaction on an item (purchase,issue, adjustment) from the from the Item Ledger Entry Table----
So what have you already tried? The forums aren’t the place to get people to do the work for you. Show us what you already have and tell us why you think it’s not working.
I am going off this tutorial http://msdn.microsoft.com/en-us/library/dd301329.aspx
I assume i need a function on the ‘Table Filter’ so i can filter this report by the last date for any transaction? Am i going in the right direction… I found this function online but i am not sure how to implement…
=nl("-1",“Item Ledger Entry”,“Posting Date”,“item No.”,C4,"+Posting date","*")
Have you seen Item Card Form–>Statistics–>Entry Statistics?
I think it can help you
I am VERY new to NAV, can you please elaborate a little more on the comment?
The above comment is about FORM 304 Item Entry Statistics - which is available from the item card.
It provides a bunch of useful information about that item, including dates.
Since you appear to be very green on reports - I would start you off using WIZARDS till you get a hang of it.
How I would go about it (Old system)
New->Report
Table->Item ledger Entry
Create A Report Using Wizard->Tabular Type Report Wizard
Available Fields->Item No & Posting Date & Entry Type (NEXT)
Yes, I want my data sorted by: Choose key that starts with “EntryType,Item No…” (ascending) (NEXT)
Yes, I want to group my data by: “Entry Type”, “Item No.” (NEXT)
Which style do you want?: List Style (FINISH)
View Globals: add PrintDetail TYPE Boolean
View Request Form: Add a CheckBox with SourceExp PrintDetail
View Sections, on any GroupHeader & GroupBody I add to OnPreSection()
CurrReport.SHOWOUTPUT := PrintDetail; //give you the option to show all the lines if you want
Copy & fields that are on the ItemLederEntryBody to the ItemLedgerEntryGroup Footer underneath.
//by coping the fields to the footer it will show the last entries for each type.
File Save As: 50063 - Test123 - or whatever number and name you need to give it.
run it enter an item number and it should give you every entry type for that item and it’s last date.
now you can play around with the wizard picking other options depening on how you want your report grouped or sorted.
Go to Item Card–>Click Item Menu Button at bottom–>Select Statisitcs and Entry Statistics…
Savatage thank you for the detailed response, I am confused how to implement "View Sections, on any GroupHeader & GroupBody I add to OnPreSection()
CurrReport.SHOWOUTPUT := PrintDetail; //give you the option to show all the lines if you want." I have the data showing (but it is not filtering to only the last day) I am sure i am doing something wrong.
meaning i would like
Item No. Posting Date Entry type
110056 04/21/2011 Negative Adjmt
110057 04/21/2011 Negative Adjmt
110058 04/21/2011 Negative Adjmt
110059 04/21/2011 Negative Adjmt
110010 04/21/2011 Negative Adjmt ect…
right now i think i have something not correct because i am seeing
Item No. Posting Date Entry type
110056 04/21/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/20/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/19/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
110056 04/18/2011 Negative Adjmt
I want to group my data by: “Entry Type”, “Item No.” (NEXT)
I think you have to add Posting Date also in above step.
PS:Posting Date date should be in Key…
Sean, you clearly need some rudimentary explanation about report development (maybe even development at all). Sit down with your senior and have them walk you through this particular type of development. I’d say book 2-4 hours for this. You would be lucky to get the information that you need from this forum, and it will take days if not weeks, with a very high probability of getting very bad information that will start some very bad habits.
Den Ster
Well the problem is that i have only worked with nav reports for about 2 days. My Sr. does not know how to pull the last Item Ledger date for every item. I do not know nav reports very well, and usually do all my reporting with T-sql and BIDS. If you you know of any tutorials that would help me with this specific problem that would he helpful!
You can View->Sections
as you see here: copy the fields geneater by the wizard from the body to the GroupFooter
Use the CurrReport.SHOWOUTPUT := PrintDetail;
on every section except the footer… so ise it on 4,5 & 7 since these sections print out data//you want to surpress it.
The check box in the request form allows you to show or hide the details.
Then he’s not a senior developer. He might know how to program within NAV, but he’s not a senior NAV developer.
I think you might be going in the wrong direction with the report. One of the most common mistakes I see people make when starting with reports is that they want to make a DataItem for everything. Unless you need to loop through every record, there’s no need for a DataItem. You can just make a variable instead.
You do want to loop through every Item record, so have an Item DataItem is good. But then you can just add code to the OnAfterGetRecord trigger to pull the last Item Ledger Entry.
Some functions you will use:
SETCURRENTKEY - Defines the order that the records will sorted in, like the ORDER BY clause in SQL
FINDLAST - Pulls the last record from your record set
SETRANGE / SETFILTER - Sets criteria on your record set, like the WHERE clause in SQL
I hope that gets you going. You may want to read through a few chapters of the Development training material regarding records as well.
This.
My point is that you need more than a few pointers on how to set a property or some syntax, and your senior should be the one to educate you. If there is no senior NAV development resource where you work then I assume that you are an end customer, in which case you would need to ask your partner for help. If you work for a NAV partner with no senior resource… run away and find another job [:)]
You can download the development training material from CustomerSource, which is where you should really start. There’s also a few good books on NAV development. I can recommend the ones by David Studebaker, and Matt recently wrote a development ‘cookbook’.
Savatage- I was able to complete the report with your quick tutorial!
Thanks a-lot for spending the time to help a noob.
Sean