quantity by dimension

bangswit

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

bangswit

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

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?

bangswit

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

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

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.

bangswit

i already made the code
but failed

bangswit

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

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.

bangswit

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

In the properties of the variable you can define the dimensions with a comma.

http://dynamicsuser.net/blogs/mark_brum ... rrays.aspx

einsTeIn.NET

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.
Anyway, thanks for explanation! You're doing a great job.

bangswit

still confuse how to meets dimension 1 & dimension 2....

bangswit

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);