Hi, I am trying to write a report on the Sales Invoice Header Table. This report has to be grouped by Department code and Project code and then give the totals by date (this is OK, because I can also group by posting date). Then we would like totals by week and by month. I need some advice on how to group the posting date by week and month. Thanks in advance Anret
Hi month: Setrange(Posting Date,Calcdate('-LM',YourDate),Calcdate('+LM',YourDate); week: Coming from start of month! Date1:= Calcdate('-LW',Calcdate('-LM',YourDate)); Date2:= Calcdate('+LW',Date1); Setrange(Posting Date,Date1,Date2); ... YourCode ... Date1:= Calcdate('+1T',Date2); Date2:= Calcdate('+LW',Date1); Setrange(Posting Date,Date1,Date2); ... .and so on bye André
You can create a variable of type decimal with 53 dimensions (weeks) and using Date table determinate to which week it belongs. Then increase needed dimension index which equals week number. So at the end of the report create data item of Integer table and in it’s body section print this matrix. Don’t forget to set range for Iteration count for this data item [;)]
I would create a flowfield (somewhere) which calculates the value you want, based on a week or month date filter. On every week or month group you will recalculate this flowfield with the right week or month filter. Don’t use the CREATETOTALS function to create totals for this field. Just set another date filter. Now how to group you report. Do something like this… (maybe you should do it differently, course of the structure of the report) OnAfterGetRecord of a Integer DataItem called “PeriodGroup” ... currweek := DATE2DWY(yourdate, 2); if prevweek <> currweek THEN printweektotal; <-- use weeknumber to calculate flowfield. prevweek := currweek; ... or the other way around (easier): ... currweek := DATE2DWY(yourdate, 2); if prevweek = currweek THEN currreport.break; prevweek := currweek; setweekfilters; ... (and let Navision print all field in the integer body);Don’t forget to initialize prevweek the first time or your report will start with a week total of nothing. Same thing for your month group. You will get 2 extra integer loops. Some people don’t fancy that but I thing Interger loops are the one of the best feature in Navision.
Integer is very good for complicated reports [:D]
Or just use the Date record table twice indented with on pre data call one Month and One Week Data Item Month Filter Period Type::Month (Group total Field = Period Type) PreData Code = Setrange(“Start Date”,CalcDate(’-CM’,WORKDATE)); CreateTotals(SalesHeader.“Amount inc. VAT”) > = Indent One >DataItem Week Filter Period Type::Week (Group Total Field = Period Type) PreData Code = Week Setrange(“Start Date”,Month.“Start Date”,Month.“End Date”)); CreateTotals(SalesHeader.“Amount inc. VAT”) >> Indent Twice >>SalesHeader Group Totals For Dept & Project PreData Code = Setrange(“Posting Date”,Week.“Start Date”,Week.“End Date”)); But bear in mind that there are part weeks in a calendar month this will only show the full weeks IE: MARCH would give you 3rd - 9th , 10th-16th, 17th-23rd & 24th-30th Sections SalesHeader Group footer x 2 for project & department Week Group Footer Month Group Footer