Getting Average

sadozaiw

Hi Navision Gurus!
I have the followins structure of a table, having only three fields.

1) Item No.
2) Description
3) Unit Cost

In this table, items are repeated with different Unit cost. I need a report which will get me the "Average Unit Cost" (as fourth column) if the item is repeated in this table and the Unit Cost is different, if the Unit Cost is not different, nothing should be done.

Hope you guys will support a newcommer.

All the best.

Sandeep_Prajapati

Just import the following sample report

OBJECT Report 68001 Average qty
{
  OBJECT-PROPERTIES
  {
    Date=07/29/08;
    Time=[ 1:03:31 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table32;
        DataItemTableView=SORTING(Item No.,Posting Date);
        OnPreDataItem=BEGIN
                        LastFieldNo := FIELDNO("Item No.");
                      END;

        ReqFilterFields=Item No.;
        TotalFields=Quantity;
        GroupTotalFields=Item No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1102753001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Item Ledger Entry }
            { 1102753002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1102753003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1102753004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1102753005;Label  ;16950;423  ;750  ;423  ;ParentControl=1102753004 }
            { 1102753006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1102753012;Label  ;0    ;0    ;1500 ;846  ;ParentControl=1102753011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1102753015;Label  ;1650 ;0    ;4500 ;846  ;ParentControl=1102753014;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1102753018;Label  ;6300 ;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Quantity }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           IF InitializeNoOfItems THEN
                             NoOfItems := 1
                           ELSE
                             NoOfItems := NoOfItems + 1;

                           InitializeNoOfItems := FALSE;



                           // uncommennt the below line tonot to show the detailed entries
                           //CurrReport.SHOWOUTPUT(false);
                         END;

          }
          CONTROLS
          {
            { 1102753000;TextBox;6300 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Quantity }
            { 1102753007;TextBox;1650 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
            { 1102753008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Item No." }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Item No.");

                           CurrReport.SHOWOUTPUT(NoOfItems <> 0);


                           IF (CurrReport.SHOWOUTPUT = TRUE) THEN
                            InitializeNoOfItems := TRUE;
                         END;

          }
          CONTROLS
          {
            { 1102753020;TextBox;10200;0    ;1800 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Quantity/NoOfItems }
            { 1102753014;TextBox;3000 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Description }
            { 1102753011;TextBox;0    ;0    ;2250 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="Item No." }
            { 1102753009;Shape  ;0    ;0    ;15750;423   }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1102753000 : Integer;
      FooterPrinted@1102753001 : Boolean;
      TotalFor@1102753002 : TextConst 'ENU="Total for "';
      NoOfItems@1102753003 : Integer;
      InitializeNoOfItems@1102753004 : Boolean;

    BEGIN
    END.
  }
}

I hope it helps.... O:)

sadozaiw

Thanks Sandeep! but can u plz explain it further? i don't want to base it on Itemledgerentry.

Sandeep_Prajapati

That is just an example. You can take any table.
1) Group by Item color=blue]propperty : GroupTotalFields[/color (So Item should be a field in active key)
2) Total for the Quantity color=blue]propperty : TotalFields[/color
3) keep counting the the no. of records in table with a Item No. (say NoOfItems)
4) In the group footer -- put source expression of your textBox showing average as color=blue]Quantity/NoOfItems[/color
5) Once group footer is printed- means, Item chnages now. So initialize the [NoOfItems] and then again count the [NoOfItems] for the next Item.

This is the whole idea. Implement as you want.
The above sample report may give you some help. :P

sadozaiw

Thanks sundeep for your detailed explaination. I realy appreciate
Take care