display all quantity to driver in month

hi guys can any one told me how i make report to this :

this data from two tables

sales shipment Header table

sales shipment line table

this report display how many quantity that driver make monthly

doc no shipment date quantity driver name

1 12/01/2012 5 Ahmed

2 12/02/2012 10 Ahmed

3 12/03/2012 30 Ahmed

4 20/01/2012 40 Ahmed

5 12/10/2012 30 Mohammed

6 12/11/2012 50 Mohammed

i want to show data as following (required):

Driver Name Jan Feb march October Nov

Ahmed 45 10 30

Mohammed 30 50

how i make calculation to quantity to every driver per month in dynamic nav by writing code as above?

please help me



Best way add one flow field on Shipment header and flow the value of driver in that field and show that field on List form/page


You can create a report for this create a fn for calculating the qty delivered then call the function with appropriate filter

Filter like Driver No

Datefilter thats from start of month to end of a month.

ILE.SETFilter(ILE.“Posting Date”,’%1…%2’,010111,010211);

this hard coding dont do like this try to take a date and calculate all the months.

you can use this function DMY2DATE. consider like its jan then push it to qty[1]=qty.

use an array for pushing the value. then clear it and push values for next driver.

In the section you can create fields with source address qty[1] qty[2] …qty[12]

better to get one date from the user from request form.

Start date then calculate the qty with one for loop like 1-12 for each month this can be done by the fns in System–>Date check it and try your self so you can learn more

ILE.SETRANGE(ILE.“Posting Date”,DMY2DATE(1,DATE2DMY(StartingDate,2),DATE2DMY(StartingDate,3)),StartingDate);

like this it will calculate the start of the date you give (starting date you will give in report)

it will apply a filter from start date of the date to your date.

like 29-01-11–>it will give 01-01-11 to 29-01-11

Try your self. i hope i gave you some point to think and do your job.