REPORT

Hello, Have a table named statistics and created a report that should group on field within statistics named item cat. For each group I want the report to apply a date filter to entries within the group that have aged 1 month, 2 month etc, and as a result provide a report that groups showing balance aged 1 month, balance aged 2 month etc. I have setup the GroupTotalField as Group Cat and have applied the following code but it is not detailing the information for each group Sales Analysis - OnPreDataItem() FOR i := 1 TO 6 DO BEGIN StartDate[i] := CALCDATE(STRSUBSTNO(’-%1M’,i),WORKDATE); EndDate[i] := CALCDATE(’+1M’,StartDate[i]); END; Sales Analysis - OnAfterGetRecord() FOR i := 1 TO 6 DO BEGIN “Sales Analysis”.SETRANGE(“Posting Date”,StartDate[i],EndDate[i]); “Sales Analysis”.CALCSUMS(“Sales Analysis”.“Amount (LCY)”); Month[i] := “Sales Analysis”.“Amount (LCY)”; END; Sales Analysis - OnPostDataItem() Any guidance you could offer would be greatly appreciated.

Hi - Can you export the report (prefereebly in txt format). I am not quite sure, what you are trying to do?

quote:


Originally posted by Hhelgesen
Hi - Can you export the report (prefereebly in txt format). I am not quite sure, what you are trying to do?


Here you go: OBJECT Report 72813 Stat { OBJECT-PROPERTIES { Date=27/04/04; Time=15:28:54; Modified=Yes; Version List=; } PROPERTIES { } DATAITEMS { { PROPERTIES { DataItemTable=Table72801; DataItemTableView=SORTING(Item Category Code,Customer No.,Posting Date); OnPreDataItem=BEGIN FOR i := 1 TO 6 DO BEGIN StartDate[i] := CALCDATE(STRSUBSTNO(‘-%1M’,i),WORKDATE); EndDate[i] := CALCDATE(‘+1M’,StartDate[i]); END; END; OnAfterGetRecord=BEGIN FOR i := 1 TO 6 DO BEGIN “Sales Analysis”.SETRANGE(“Posting Date”,StartDate[i],EndDate[i]); “Sales Analysis”.CALCSUMS(“Sales Analysis”.“Amount (LCY)”); Month[i] := “Sales Analysis”.“Amount (LCY)”; END; END; ReqFilterFields=Item Category Code; TotalFields=Profit (LCY),Qty; GroupTotalFields=Item Category Code; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=7191; } CONTROLS { { 1000000001;Label ;11760;0 ;6300 ;423 ;FontSize=10; FontBold=Yes; CaptionML=[ENU=Sales Analysis; ENG=Sales Analysis] } { 1000000002;TextBox;11760;423 ;1680 ;423 ;FontSize=9; SourceExpr=Page + ’ ’ + FORMAT(CurrReport.PAGENO) } { 1000000003;TextBox;11760;1269 ;6300 ;423 ;FontSize=9; FontBold=Yes; SourceExpr=COMPANYNAME } { 1000000004;TextBox;11760;6768 ;4410 ;423 ;FontSize=9; SourceExpr=FORMAT(TODAY,0,4) } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=423; } CONTROLS { { 1000000013;Label ;3750 ;0 ;1800 ;423 ;Name=Average Margin; CaptionML=[ENU=Average Margin; ENG=Average Margin] } { 1000000014;Label ;5700 ;0 ;2100 ;423 ;Name=Customer Qty; CaptionML=[ENU=Customer Qty; ENG=Customer Qty] } { 1000000007;Label ;150 ;0 ;3000 ;423 ;ParentControl=1000000006; FontSize=9 } } } { PROPERTIES { SectionType=GroupFooter; SectionWidth=18150; SectionHeight=846; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = “Sales Analysis”.FIELDNO(“Item Category Code”); END; } CONTROLS { { 1000000006;TextBox;300 ;0 ;1500 ;423 ;HorzAlign=Left; FontSize=9; SourceExpr=“Item Category Code” } { 1000000008;TextBox;5700 ;0 ;1800 ;423 ;SourceExpr=Qty } { 1000000005;TextBox;3750 ;0 ;1500 ;423 ;SourceExpr=“Sales Analysis”.“Profit (LCY)”/Qty } { 1000000000;TextBox;8550 ;0 ;1800 ;423 ;SourceExpr=Month[1] } { 1000000009;TextBox;10950;0 ;1800 ;423 ;SourceExpr=Month[2] } { 1000000010;TextBox;13500;0 ;1800 ;423 ;SourceExpr=Month[3] } } } } } } REQUESTFORM { PROPERTIES { Width=9020; Height=3410; } CONTROLS { } } CODE { VAR Page@1000000000 : TextConst ‘ENG=Page’; LastFieldNo@1000000001 : Integer; FooterPrinted@1000000002 : Boolean; TotalFor@1000000003 : TextConst ‘ENG="Total for "’; StartDate@1000000004 : ARRAY [6] OF Date; EndDate@1000000005 : ARRAY [6] OF Date; Month@1000000008 : ARRAY [6] OF Decimal; i@1000000012 : Integer; BEGIN END. } }

OK - You need to move FOR i := 1 TO 6 DO BEGIN StartDate[i] := CALCDATE(STRSUBSTNO('-%1M',i),WORKDATE); EndDate[i] := CALCDATE('+1M',StartDate[i]); END; into the sections (Group footer) - You also need to then filter for the “Item Category Code”, otherwise your calculation include all. This might work: OBJECT Report 72813 Stat { OBJECT-PROPERTIES { Date=27/04/04; Time=15:28:54; Modified=Yes; Version List=HH; } PROPERTIES { } DATAITEMS { { PROPERTIES { DataItemTable=Table72801; DataItemTableView=SORTING(Item Category Code,Customer No.,Posting Date); OnPreDataItem=BEGIN FOR i := 1 TO 6 DO BEGIN StartDate[i] := CALCDATE(STRSUBSTNO('-%1M',i),WORKDATE); EndDate[i] := CALCDATE('+1M',StartDate[i]); END; END; OnAfterGetRecord=BEGIN END; ReqFilterFields=Item Category Code; TotalFields=Profit (LCY),Qty; GroupTotalFields=Item Category Code; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=7191; } CONTROLS { { 1000000001;Label ;11760;0 ;6300 ;423 ;FontSize=10; FontBold=Yes; CaptionML=[ENU=Sales Analysis; ENG=Sales Analysis] } { 1000000002;TextBox;11760;423 ;1680 ;423 ;FontSize=9; SourceExpr=Page + ' ' + FORMAT(CurrReport.PAGENO) } { 1000000003;TextBox;11760;1269 ;6300 ;423 ;FontSize=9; FontBold=Yes; SourceExpr=COMPANYNAME } { 1000000004;TextBox;11760;6768 ;4410 ;423 ;FontSize=9; SourceExpr=FORMAT(TODAY,0,4) } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=423; } CONTROLS { { 1000000013;Label ;3750 ;0 ;1800 ;423 ;Name=Average Margin; CaptionML=[ENU=Average Margin; ENG=Average Margin] } { 1000000014;Label ;5700 ;0 ;2100 ;423 ;Name=Customer Qty; CaptionML=[ENU=Customer Qty; ENG=Customer Qty] } { 1000000007;Label ;150 ;0 ;3000 ;423 ;ParentControl=1000000006; FontSize=9 } } } { PROPERTIES { SectionType=GroupFooter; SectionWidth=18150; SectionHeight=846; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = "Sales Analysis".FIELDNO("Item Category Code"); IF CurrReport.TOTALSCAUSEDBY = "Sales Analysis".FIELDNO("Item Category Code") THEN BEGIN FOR i := 1 TO 6 DO BEGIN SETRANGE("Item Category Code","Item Category Code"); SETRANGE("Posting Date",StartDate[i],EndDate[i]); CALCSUMS("Sales Analysis"."Amount (LCY)"); Month[i] := "Sales Analysis"."Amount (LCY)"; END; SETRANGE("Item Category Code"); END; END; } CONTROLS { { 1000000006;TextBox;300 ;0 ;1500 ;423 ;HorzAlign=Left; FontSize=9; SourceExpr="Item Category Code" } { 1000000008;TextBox;5700 ;0 ;1800 ;423 ;SourceExpr=Qty } { 1000000005;TextBox;3750 ;0 ;1500 ;423 ;SourceExpr="Sales Analysis"."Profit (LCY)"/Qty } { 1000000000;TextBox;8550 ;0 ;1800 ;423 ;SourceExpr=Month[1] } { 1000000009;TextBox;10950;0 ;1800 ;423 ;SourceExpr=Month[2] } { 1000000010;TextBox;13500;0 ;1800 ;423 ;SourceExpr=Month[3] } } } } } } REQUESTFORM { PROPERTIES { Width=9020; Height=3410; } CONTROLS { } } CODE { VAR Page@1000000000 : TextConst 'ENG=Page'; LastFieldNo@1000000001 : Integer; FooterPrinted@1000000002 : Boolean; TotalFor@1000000003 : TextConst 'ENG="Total for "'; StartDate@1000000004 : ARRAY [6] OF Date; EndDate@1000000005 : ARRAY [6] OF Date; Month@1000000008 : ARRAY [6] OF Decimal; i@1000000012 : Integer; BEGIN END. } }