quantity by dimension

bangswit
bangswit Member Posts: 265
edited 2010-09-01 in NAV/Navision Classic Client
does any of you have tips n trick how to create report to export to excell
quantity by dimension
using dimension 1 & dimension 2....
I will not using analisys by dimension

Comments

  • bangswit
    bangswit Member Posts: 265
    as i mention
    the red one is global dimension 1
    the blue one is global dimension 2
    the green one is quantity in Purchase Line (table 39)
    how can i made report like this to excel?
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You can use the excel buffer table with two variable instances of the dimension tables.

    Here is an example of how to use the Excel Buffer

    http://www.mibuso.com/dlinfo.asp?FileID=596

    Just a question: does Navision support legal requirements of the Vatican City?
    Marije Brummel | Freelance Business Central Specialist
  • bangswit
    bangswit Member Posts: 265
    Mark Brummel wrote:
    You can use the excel buffer table with two variable instances of the dimension tables.

    Here is an example of how to use the Excel Buffer

    http://www.mibuso.com/dlinfo.asp?FileID=596

    Just a question: does Navision support legal requirements of the Vatican City?

    ok
    i will try to take a look
    nope, i'm not from vatican
    just like that place
  • bangswit
    bangswit Member Posts: 265
    I think this is the simple one
    i want to ask how to get the quantity, by combine that dimension 1 and dimension 2
    thanks
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I think you ask to much of a free forum of volunteers.

    Before you post a questions like this, first try to bake some C/AL code yourself first.
    Marije Brummel | Freelance Business Central Specialist
  • bangswit
    bangswit Member Posts: 265
    i already made the code
    but failed
  • bangswit
    bangswit Member Posts: 265
    i forget to share the code
    Dimension Value - OnPreDataItem()
Cylinder.SETRANGE(Cylinder."Global Dimension No.",2);
Cylinder.FIND('-');

i:= 1;
REPEAT
     LocName[i] := Cylinder.Code;
     i:= i +1;
UNTIL
(Cylinder.NEXT = 0);

Dimension Value - OnAfterGetRecord()
i:=1;
Cylinder.FIND('-');
   REPEAT
     Item.SETRANGE(Item."Global Dimension 1 Filter",SPH.Code);
     Item.SETRANGE(Item."Global Dimension 2 Filter",Code);
     Item.CALCFIELDS(Item."Qty. on Purch. Order");

     IF Item.FIND('-') THEN
     BEGIN
        AmountLoc[i]:= FORMAT(Item."Qty. on Purch. Order");
        LocName[i] := Cylinder.Code;
        i:= i + 1;
     END
     ELSE
     BEGIN
        AmountLoc[i]:= '';
        i:=i;
     END;
   UNTIL (Cylinder.NEXT = 0);
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    And where does it go wrong?

    IMHO you need a multi dimensional array (AmountLoc[x],[y])

    When this is populated you can move the value of this array to excel using the buffer table.
    Marije Brummel | Freelance Business Central Specialist
  • bangswit
    bangswit Member Posts: 265
    Mark Brummel wrote:
    And where does it go wrong?

    IMHO you need a multi dimensional array (AmountLoc[x],[y])

    When this is populated you can move the value of this array to excel using the buffer table.

    that's it
    how to do multidimensional array
    my CAL Global
    Name DataType Subtype Length
    AmountLoc Text 30
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    In the properties of the variable you can define the dimensions with a comma.

    http://dynamicsuser.net/blogs/mark_brum ... rrays.aspx
    Marije Brummel | Freelance Business Central Specialist
  • einsTeIn.NET
    einsTeIn.NET Member Posts: 1,050
    Mark Brummel wrote:
    http://dynamicsuser.net/blogs/mark_brummel/archive/2010/05/05/tip-27-using-temp-tables-in-arrays.aspx
    Argh... you should have post it to another topic some weeks ago. :wink:
    Anyway, thanks for explanation! You're doing a great job.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • bangswit
    bangswit Member Posts: 265
    still confuse how to meets dimension 1 & dimension 2....
  • bangswit
    bangswit Member Posts: 265
    for example

    dimension 1 = A & B & C

    dimension 2 = 1 & 2 & 3

    item ABC (dimension 1 = A,dimension 2 = 1),qty on Purchase order = 10

    item XYZ (dimension 1 = B,dimension 2 = 2),qty on Purchase order = 5

    Item XXX (dimension 1 = B,dimension 2 = 1),qty on Purchase order = 13

    so it would be like this

    A B C

    1 10 13

    2 5

    3

    is there something wrong with my code? 
    Purchase Line - OnPreDataItem()
dim1.SETRANGE(dim1."Global Dimension No.",1);
dim1.FIND('-');
i:= 1;
REPEAT
     dim1Name[i] := dim1.Code;
     i:= i +1;
UNTIL
(dim1.NEXT = 0);

dim2.SETRANGE(dim2."Global Dimension No.",2);
dim2.FIND('-');
i:= 1;
REPEAT
     dim2Name[i] := dim2.Code;
     i:= i +1;
UNTIL
(dim2.NEXT = 0);


Purchase Line - OnAfterGetRecord()
i:=1;
dim2.FIND('-');
REPEAT
    SETRANGE("Shortcut Dimension 2 Code",dim2.Code);
    n:= 1;

      REPEAT
        SETRANGE("Shortcut Dimension 1 Code",dim1.Code);
        IF FIND('-') THEN
        CALCSUMS(Quantity);
        AmountLoc[n]:= FORMAT(Quantity);
      UNTIL NEXT = 0;
    dim2Name[i] := dim2.Code;
    i:= i + 1;
UNTIL (dim2.NEXT = 0);

Categories