sales invoice line report

cavalcanti_space

Hi Guys,

I have on sales invoice header a field called branch code. There is a dimension (shortcut dimension 4) called family for the sales invoice line.
I need to design a report to group the fields (line amount ex vat) and (quantity) from sales invoice line first.
Like that:
Family 1

---

> Branch 1 -> (line amount ex vat) and (quantity)

---

> Branch 2 -> (line amount ex vat) and (quantity)

---

> Branch 3 -> (line amount ex vat) and (quantity)
Family 2

---

> Branch 1 -> (line amount ex vat) and (quantity)

---

> Branch 2 -> (line amount ex vat) and (quantity)

---

> Branch 3 -> (line amount ex vat) and (quantity)

Cheers

DenSter

What's your question?

cavalcanti_space

Hi Daniel,

My question is how I can design this report?

Any help will be very much appreciated

cheers

DenSter

What have you tried so far? With what results?

cavalcanti_space

I have tried to use Posted Document Dimension and Sales Invoice Line as dataitems but I cannot group neither (quantity) nor (line amount ex. vat). I have also tried to insert the data from Sales Invoice Line in a temp table with data (Family) got from Posted Document Dimension, but after I cannot group that data.
Any Ideas

DenSter

So is your question "how can I get my data to group?". I'm sure you are not expecting someone in here to completely design an entire report for you.

cavalcanti_space

My Questions is how can I group data from Sales Invoice Line being that the fields I am trying to group are a dimension shortcut 4 and the other field is a field on the Sales Invoice Header. I need to create totals for some fields on the Sales Invoice Line. I reckon I have tried everything possible, but I cannot get it working

DenSter

Well you can't "group" data unless the field that you are grouping on is part of that table AND part of the key in your report. So, that won't work. The other alternative is to find a linking table, so that you can loop through one table while filtering the second table, one more indentation. This is technically not "grouping", but effectively results in the same way, so you can use different types of sections.

The issue is that this link is not very easy to make, so you're going to have to find a way to make that link easier to establish, or to add a field to the table and sort order. Take a close look at the data, analyze the structure, and come up with a design. I can't think of an easy way off the top of my head, although I'm sure there is a way, it just takes time and thought.

cavalcanti_space

I was able to insert the data in a temp table.
That is what I have done so far, but It seems it is not grouping by some reason.

OBJECT Report 50017 teste
{
  OBJECT-PROPERTIES
  {
    Date=16/06/09;
    Time=22:25:12;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5637;
        DataItemTableView=SORTING(Global Dimension 1 Code,Global Dimension 2 Code);
        OnPreDataItem=BEGIN
                        DELETEALL;
                        SalesInvLine.RESET;
                        CLEAR(TotalQty);

                        IF SalesInvLine.FINDFIRST THEN
                          REPEAT
                            IF  PostedDocDim.GET(113,SalesInvLine."Document No.",SalesInvLine."Line No.",'FAMILIA') THEN BEGIN

                              RESET;
                              IF FINDLAST THEN
                                "Entry No." := "Entry No." + 1
                              ELSE
                              "Entry No." := 1;

                              INIT;
                              "Global Dimension 1 Code" := PostedDocDim."Dimension Value Code";
                              "Global Dimension 2 Code" := SalesInvLine."Location Code";
                              "FA Entry No." := SalesInvLine.Quantity;
                              Amount := SalesInvLine."Line Amount";
                              INSERT;

                            END;
                          UNTIL SalesInvLine.NEXT = 0;
                      END;

        OnAfterGetRecord=BEGIN
                           TotalQty := TotalQty + "FA Entry No.";
                         END;

        TotalFields=Amount;
        GroupTotalFields=Global Dimension 1 Code,Global Dimension 2 Code;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000009;Label  ;5250 ;0    ;1050 ;846  ;ParentControl=1000000008;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000007;Label  ;3150 ;0    ;1950 ;846  ;ParentControl=1000000006;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000005;Label  ;1200 ;0    ;1800 ;846  ;ParentControl=1000000004;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000003;Label  ;6450 ;0    ;1800 ;846  ;ParentControl=1000000002;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000001;Label  ;0    ;0    ;1050 ;846  ;ParentControl=1000000000;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000000;TextBox;0    ;0    ;1050 ;423  ;SourceExpr="Entry No." }
            { 1000000002;TextBox;6450 ;0    ;1800 ;423  ;SourceExpr=Amount }
            { 1000000004;TextBox;1200 ;0    ;1800 ;423  ;SourceExpr="Global Dimension 1 Code" }
            { 1000000006;TextBox;3150 ;0    ;1950 ;423  ;SourceExpr="Global Dimension 2 Code" }
            { 1000000008;TextBox;5250 ;0    ;1050 ;423  ;SourceExpr="FA Entry No." }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=12000;
            SectionHeight=423;
            OnPreSection=BEGIN
                           //CurrReport.SHOWOUTPUT(CurrReport.TOTALSCAUSEDBY = FIELDNO("Global Dimension 1 Code"));
                         END;

          }
          CONTROLS
          {
            { 1000000010;TextBox;5250 ;0    ;1050 ;423  ;SourceExpr=TotalQty }
            { 1000000011;TextBox;6450 ;0    ;1800 ;423  ;SourceExpr=Amount }
            { 1000000012;Label  ;0    ;0    ;1200 ;423  ;CaptionML=ENU=Total }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      SalesInvLine@1000000000 : Record 113;
      PostedDocDim@1000000001 : Record 359;
      TotalQty@1000000003 : Integer;

    BEGIN
    END.
  }
}

cavalcanti_space

Any help?