Hi, I have written a report which reads the Sales Invoice Line table, the DataItemTableView is sorting on No. where Type = CONST Sale and has GroupTotalFields of No… The report accumulates Quantity and (Unit Price * Quantity) to produce a report of all items sold in a given timeframe show total quantity and turnover for each. My problem now is that I need to sort this output so that the item that sells most is at the top of report and the item that sell least is at the bottom (abit like the Top 10 Report I guess) is this possible and if so how can I code this.
I don’t know if the next solution is the easiest or the best, but it works! [;)] You could define a decimal field “Amount Sold” and “Quantity Sold” in table Item. Define a key in Item-table with “Amount Sold” (and “Quantity Sold”). Run the report like you do now and fill for each Item the new fields. Add dataitem Item to your report and sort it on “Amount Sold”. You can hide the sections you use now using CurrReport.Showoutput(FALSE). After running the report I would reset the values of the new fields to zero.
Hi Tino I see what you’re saying, I will try this thanks.
Hi Paul, Firstly, you should never write reports in Navision based on Posted Document tables. (Though I am aware that these days many people do it). So start with the correct Table, you could choose Item Ledger Entry, or Value Entry. You will need to decide, but I would suggest Value entries in this case. You have two basic issues here, one is calculating the total sales per item, the other is then sorting by item number. To do it realistically, and assuming there are a number of items, you can either do it with Database Tables or with Temporary tables. Using Database tables has the advantage of being easier to code, and also being able to run on any client. The disadvantage is that the report can then only be run by one user at a time (so its not true Navision like), and depending on the number of items, can be quite slow. Using Temporary tables is faster (if there are a lot of items), and allows you to run the report by multiple users at once. The disadvantage is that the code is more complex, and that on machines with low ram, the report could run very slowly, or may not run at all. In 80% of cases (I made that number up on the spot by the way) Temporary tables are the way to go. This time I would use Database tables is when the Ranking (the second sort by Item you want) is relatively constant. n this case you would have two routines. One that calculates the sorting, and another that prints the amounts. That way, you could run the sorting say once per week, and then just reprint as often as you want. I personally don’t like this solution, but it works for some circumstances. So Temporary tables. Step one is to create a new fields in the Item card Item Rank::Integer. And create a key based on this. Create your report based on Item. In the report create a temporary variable of Type Item Runthrough all items, and do the following WARNING TRIPLE CHECK PROPERTIES ON VAR TempItem it must be temporary or you will DESTROY your database.
With TempItem do begin Deleteall; Copy(rec); Calcfields("Sales (LCY)"; "Item Rank" := round("Sales (LCY)",1); Insert; End; Now create a new report section based on the TempItem rec, and create the report. Recalc the “Sales (LCY)” field to display and its done. Note 1 that you are running the report based on a flow field that uses the Value Entry table, so you are using this table even though you don’t see it in the report. Note 2. Make surethe sort key is integer, not Decimal, it will make a lot of difference to system performance when there are a lot of items. Especially if you use the Database Table method. I have kept this short, so as/if you need more help, please post here. (I am guessing your next question to be “How do I print a Temp Var?”) [;]]
Sorry Tino, I had this open for a while and cross posted over you. Yes your method is definitely simpler, the only thing is I would recommend using integer fields not decimals.
No offence taken, David! I learned something again from your post. Why would you recommend using integer instead of decimals?
Hi David thats alot for a novice programmer to take in but I guess I understand most of it. I agree with your point about using either the Item Ledger Entry table or Value Entry, I started to write the report based in the ILE but changed my mind for some reason. Yes my next question would be printing from a temp var. I will have a look thru other reports and see if I can find a standard one that does a similar thing and that will help me do this one. If I get stuck I’ll log another entry for help if thats OK. Meantime thanks for your help…Paul in the frozen wastes of southern england
Frozen wastelands? We have had about 2 meters of snow so far this year and the snow season has not started yet, how is it there [:D] Yes Paul its a lot, which is why I tried to keep it short. Basically what Tino suggests is the simpler way of doing things. The only disadvantages, perfromance and that only one ser can rn the report at once are I am sure are far outweighed by the complexity of doing it “the Navision way” so I suggest thatat this point do it his way, BUT use an integer, as I explained in my post. I want to just point out two things: Key’s perfrmance are all about data size. An integer uses one 32 bit long word, and integer uses 12 bytes (or something like that) so integers are much faster than decimals. its faster tosort by integer, and then later recalc the decimal to get a result, than to just store and sort by decimal. Also the diference between cents/pennies/pfenig/halir is insignificant to the sorting, so why use it. Second. It very important to realize that the primary key or a table is ADDED to all secondary keys, so if you have a primary key of Option,Code,Integer then every sort you add to that table significantly added to the size of that key compared to a Ledger Entry which has a primary key of integer. Which is why Ledger tables are so much faster thatn document tables. Third (did I say two or three). When I did basic Navisiontraining we were taught all this, and I do know that these days its not generally taught. In my classes it will be.
David, For some reason I skipped “Note 2” while reading your post. [:I] Thanks for the explanation of performance. You’re giving classes? Or maybe an idea for the meetings? [;)]
Yes, and Yes.
Hi David, thanks very much for your help and I am going to give this a try this morning. You know I could have have sworn you were in the US and now I see you are in the Czech Republic (right place for BRNO products though). I will let you know how I get on or not. Have a good one…Paul
Hi David, another and hopefully final question, to fully understand what you saying could I use the construction of the Top 10 report as a basis for this report. i.e report 111 reads a dataitem and writes a temporary file with is then read to produce the report within Integer???
Yes its always recomended in Navision to start by using an existing object. Actually I moved from the US a year ago. I updated my profile, but not the flag, which I just did recently.