Hello Everybody … I need your help … My new codeunit’s job is creating excel sales reports by Item category. In this case, the sales reports is unusual. It gets from the shipped order, then grab the amount from the invoice (if it’s invoiced yet) or grab the amount from sales order (if it’s not invoiced yet). So The report will look like this : Item Cat January 02 February 02 … December 02 ---------------------------------------------------- PHE 3453453 23423424 456456456 LMK 3453453 … The Item Category It self is hardcoded [:(] . PHE means all item code that started with PHE. First time, it’s ok but took 20 minutes to generate that reports (Navision has been used for 2 months. . can you imagine if it used for years ?) So I want to change my code to accelerate the execution, I use CALCSUMS. But CALCSUMS can not runs well if it was filtered with setfilter (with asterisk *). But I need the setfilter * for Categorize the Item. Can we make it happened ?
Hi, IMHO CalcSums works with all filters (SetFilter/SetRange) if: - the field (which is filtered) is in the key and - there is calculated field = SumIndexField(here amount) in the key. You will need in both tables (‘Sales Line’ and ‘Sales Invoice Line’) such a key: Key: Item Category SumIndexField: Amount On runtime of the report you will get the right amount with:
SalesLine.SetCurrentkey(Salesline."Item Category");
SalesLine.SetFilter(SalesLine."Item Category",'PHE*');
If SalesLine.Find('-')
then x = CalcSums(Salesline.Amount)
else x = 0;
Andre
Thanks Andre, Actually I have add the additional keys into those 2 tables. The problem occurs when it execute these lines : With recSalesInvLine Do Begin SetFilter(“No.”,recItem.GetItemFilter(‘PHE’)); CalcSums(Amount); End; With recSalesLine Do Begin SetFilter(“No.”,recItem.GetItemFilter(‘PHE’)); CalcSums(Amount); End; where recItem.GetItemFilter(‘PHE’) returns a string ‘PHE*’; The error message is like this : The sum of values in the Amount field can not be calculated because the filter contains question mark(?),asteriks(),or ‘at’ signs (@) Filters : Document No.:PS-INV0001,Type : Item,No:PHE,ShortCut Dimension 1 Code : 110…999 Table : Sales Invoice Line This quite confusing because it works when i play filters contain ‘…’ but not for ‘*’
Hi Bayu
quote:
Originally posted by bayu
… The error message is like this : The sum of values in the Amount field can not be calculated because the filter contains question mark(?),asteriks(*),or ‘at’ signs (@) …
Uops! I wrote IMHO but in this case IMHO was wrong [xx(]! Sorry! Perhaps this workaround could do the job:
SetFilter("No.",recItem.GetItemFilter('PHE'));
if recItem.Find('-') then begin
repeat
recItem.Mark(TRUE);
until recItem.next = 0
end;
setfilter("No.");
recItem.MARKEDONLY(TRUE);;
Andre
Hi Bayu please forget my last posting! CALCSUMS will not work with MARKEDONLY! Andre
Hi once more
quote:
Originally posted by Andre DDB
SetFilter("No.",recItem.GetItemFilter('PHE')); if recItem.Find('-') then begin repeat recItem.Mark(TRUE); until recItem.next = 0 end; setfilter("No."); recItem.MARKEDONLY(TRUE);;
This piece of code will not work with CALCSUMS but if you run through the (filtered) records you can calculate the amount! Remove ‘recItem.Mark(TRUE);’ with ‘x := x + recItem.Amount’. Andre
Thanks Andre, So the idea is do the loop for sales invoice line and sales line that match the criteria, and then calculate the sum manually. Actually that’s the previous solution for this case. And it took 20 minutes to do all of the looping. I tried CALCSUMS because maybe it will give me better speed, but it give me error message [:)].
Hi Bayu, it seems there is no other possibility to calculate the amount with filter (*) than with a loop. Perhaps there is a chance to use SetRange if your Item Code has an uniform structure [?]: SetRange(Item No,PHE0000000,PHEZZZZZZZ) <= 10 digits Andre
Hi, just an idea… why don’t you add a new field and replicate the first three characters of the item code and perform a setcurrentkey/setrange on this new field… it might be faster than the ‘PHE*’ setfilter. Saludos Nils
Yeah nilsm, I wish I could do that too… That’s because a bad deal done by my former stupid analyst. Now I must struggle with that license’s limitation. I wonder if they buy C/ODBC… life would be easier.
Hi Bayu, back to SetRange [8D]. Is it really impossible to filter your data with SetRange? You know the three leading capitals (PHE). Perhaps this will work:
recItem.SetFilter("No.",recItem.GetItemFilter('PHE*'));
if recItem.Find('-') //First
then StartCode:= recItem."No."
if recItem.Find('+') //Last
then EndCode:= recItem."No."
SalesInvoiceLine.SetCurrentKey(SalesInvoiceLine."No.");
SalesInvoiceLine.Setrange(SalesInvoiceLine."No.",StartCode,EndCode);
If SalesInvoiceLine.Find('-') then;
CalcSums(SalesInvoiceLine.Amount);
Andre
Hey Andre, your suggestion to use
SetRange(Item No,PHE0000000,PHEZZZZZZZ) <= 10 digits
is the correct one. Bayu, listen to the guy he knows what he is talking about. PS Bayu, C/ODBC will only add to your woes. Stick with C/Side.
PS: SIFT works by storing a matrix that contains links to records in blocks of 16. It adds to the links as new records are created. If yu used an ‘’, ‘?’ or ‘@’, then obviously the search would not be sequential, and the SIFT technology could not work. If you have a range, then the start block and end block are known, and the sequence is contiguous, so the SUM can be calculated. In fact, they could have allowed an '’ or a ‘?’ to be part of the filter, provided it was at the end of the filter, but that would have made documentation complex.
Thanks Pals… I’ll try SetRange(Item No,PHE0000000,PHEZZZZZZZ) first.
Guys, Today I’ve add a new field in Sales Line and Sales Invoice Line that containing first 3 letter of the Item No., So I can call CALCSUMS without SETFILTER. And I use it for CALCSUMS. Amazing it boost 80% faster. But when I call recSalesLine.CALCSUMS(Amount) or recSalesInvLine.CALCSUMS(Amount), both of them always produce 0… so i got nothing. This is my Source Code : recSalesShipment.SETRANGE(recSalesShipment.“Posting Month”); recSalesShipment.SETRANGE(recSalesShipment.“Posting Month”,i); IF recSalesShipment.FIND(’-’) THEN BEGIN REPEAT recSalesInvHeader.RESET; recSalesInvHeader.SETRANGE(recSalesInvHeader.“Order No.”,recSalesShipment.“Order No.”); IF recSalesInvHeader.FIND(’-’) THEN BEGIN recSalesInvLine.SETCURRENTKEY(“Document No.”,Type,“No.”,“Shortcut Dimension 1 Code”); recSalesInvLine.SETRANGE(recSalesInvLine.“Document No.”,recSalesInvHeader.“No.”); recSalesInvLine.SETRANGE(recSalesInvLine.Type,recSalesInvLine.Type::Item); recSalesInvLine.SETRANGE(recSalesInvLine.“No.”,JenisBarang[j]); recSalesInvLine.SETFILTER(recSalesInvLine.“Shortcut Dimension 1 Code”,recDimValue2.Totaling); IF recSalesInvLine.FIND(’-’) THEN BEGIN recSalesInvLine.CALCSUMS(Amount); TotalByItem[j] := TotalByItem[j] + recSalesInvLine.Amount; TotalByItemTahun[j] := TotalByItemTahun[j] + recSalesInvLine.Amount; TotalDimension := TotalDimension + recSalesInvLine.Amount; TotalNasional := TotalNasional + recSalesInvLine.Amount; END; END ELSE BEGIN recSalesHeader.RESET; recSalesHeader.SETRANGE(recSalesHeader.“No.”,recSalesShipment.“Order No.”); IF recSalesHeader.FIND(’-’) THEN BEGIN recSalesLine.SETCURRENTKEY(“Document Type”,“Document No.”,Type,“No.”,“Shortcut Dimension 1 Code”); recSalesLine.SETRANGE(recSalesLine.“Document Type”,recSalesLine.“Document Type”::Order); recSalesLine.SETRANGE(recSalesLine.“Document No.”,recSalesShipment.“Order No.”); recSalesLine.SETRANGE(recSalesLine.Type,recSalesLine.Type::Item); recSalesLine.SETRANGE(recSalesLine.“No.”,JenisBarang[j]); recSalesLine.SETFILTER(recSalesLine.“Shortcut Dimension 1 Code”,recDimValue2.Totaling); IF recSalesLine.FIND(’-’) THEN BEGIN recSalesLine.CALCSUMS(Amount); TotalByItem[j] := TotalByItem[j] + recSalesLine.Amount; TotalByItemTahun[j] := TotalByItemTahun[j] + recSalesLine.Amount; TotalDimension := TotalDimension + recSalesLine.Amount; TotalNasional := TotalNasional + recSalesLine.Amount; END; END; END; UNTIL recSalesShipment.NEXT = 0; END; Does Anyone know ?
Hi Bayu, try it with:
...
IF recSalesInvLine.FIND('-') THEN BEGIN
recSalesInvLine.CALCSUMS(**recSalesInvLine.**Amount);
...
IF recSalesLine.FIND('-') THEN BEGIN
recSalesLine.CALCSUMS(**recSalesLine.**Amount);
...
bye Andre
Oh … I’m so stupid … I filtered the wrong field ! Sorry
Guys, problem is solved … CALCSUMS calculation produce 10 times faster than manualy calculated. I want to share the code with you friends. Here it is : ItemCategory[1] := ‘PHE’; ItemCategory[2] := ‘NEO’; ItemCategory[3] := ‘HUA’; ItemCategory[4] := ‘COM’; ItemCategory[5] := ‘VS’; ItemCategory[6] := ‘MLI’; ItemCategory[7] := ‘MDG’; ItemCategory[8] := ‘CSE’; ItemCategory[9] := ‘USE’; ItemCategory[10] := ‘DLL’; CREATE(Excel); Excel.Visible := TRUE; Book:=Excel.Workbooks.Add; Sheet:=Excel.ActiveSheet; Sheet.Cells.Font.Size := 8; Sheet.Cells.NumberFormat := ‘###.##’; recDimValue1.SETRANGE(recDimValue1.“Dimension Code”,‘AREA’); recDimValue1.SETRANGE(recDimValue1.Indentation,2); recDimValue1.SETRANGE(recDimValue1.“Dimension Value Type”,recDimValue1.“Dimension Value Type”::“Begin-Total”); recDimValue2.SETRANGE(recDimValue2.“Dimension Code”,‘AREA’); recDimValue2.SETRANGE(recDimValue2.Indentation,2); recDimValue2.SETRANGE(recDimValue2.“Dimension Value Type”,recDimValue2.“Dimension Value Type”::“End-Total”); intRow := 1; IF recDimValue1.FIND(’-’) THEN BEGIN recDimValue2.FIND(’-’); REPEAT CurrColumn := ‘A’; FOR i:= 1 TO ARRAYLEN(ItemCategory) DO TotalByItemYear[i] := 0; NotBlankMonth := 0; TotalDimension := 0; Sheet.Range(CurrColumn + FORMAT(intRow)).Font.Bold := TRUE; Sheet.Range(CurrColumn + FORMAT(intRow)).Font.Size := 12; Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘Omzet ’ + recDimValue1.Name; intRow := intRow + 1; Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘Kode’; CurrColumn := After(CurrColumn); FOR intMonth := 1 TO 12 DO BEGIN Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘Actual ’ + MonthAsString(intMonth); CurrColumn := After(CurrColumn); Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘% dari total’; CurrColumn := After(CurrColumn); END; Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘Total 1 Year’; CurrColumn := After(CurrColumn); Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘% dari total’; CurrColumn := After(CurrColumn); Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘Rata-rata 1 Year’; CurrColumn := After(CurrColumn); Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘% dari total’; CurrColumn := After(CurrColumn); Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ‘% dari grand total’; CurrColumn := After(CurrColumn); intRow := intRow + 1; LoopBackRow := intRow-1; CurrColumn := ‘A’; FOR i:= 1 TO ARRAYLEN(ItemCategory) DO BEGIN Sheet.Range(CurrColumn + FORMAT(intRow)).Value := ItemCategory[i]; intRow := intRow + 1; END; recSalesShipment.RESET; recSalesShipment.SETFILTER(recSalesShipment.“Shortcut Dimension 1 Code”,recDimValue2.Totaling); recSalesShipment.SETFILTER(recSalesShipment.“Posting Year”,FORMAT(DATE2DMY(TODAY,3))); CurrColumn := ‘B’; FOR i:= 1 TO 12 DO BEGIN TotalByMonth[i] := 0; FOR j:=1 TO ARRAYLEN(ItemCategory) DO BEGIN TotalByItem[j] := 0; recSalesShipment.SETRANGE(recSalesShipment.“Posting Month”); recSalesShipment.SETRANGE(recSalesShipment.“Posting Month”,i); IF recSalesShipment.FIND(’-’) THEN BEGIN REPEAT recSalesInvHeader.RESET; recSalesInvHeader.SETRANGE(recSalesInvHeader.“Order No.”,recSalesShipment.“Order No.”); IF recSalesInvHeader.FIND(’-’) THEN BEGIN recSalesInvLine.SETCURRENTKEY(“Document No.”,Type,“Report Item Category”); recSalesInvLine.SETRANGE(recSalesInvLine.“Document No.”,recSalesInvHeader.“No.”); recSalesInvLine.SETRANGE(recSalesInvLine.Type,recSalesInvLine.Type::Item); recSalesInvLine.SETRANGE(recSalesInvLine.“Report Item Category”,ItemCategory[j]); IF recSalesInvLine.FIND(’-’) THEN BEGIN recSalesInvLine.CALCSUMS(recSalesInvLine.Amount); TotalByItem[j] := TotalByItem[j] + recSalesInvLine.Amount; TotalByItemYear[j] := TotalByItemYear[j] + recSalesInvLine.Amount; TotalDimension := TotalDimension + recSalesInvLine.Amount; TotalNasional := TotalNasional + recSalesInvLine.Amount; END; END ELSE BEGIN recSalesHeader.RESET; recSalesHeader.SETRANGE(recSalesHeader.“No.”,recSalesShipment.“Order No.”); IF recSalesHeader.FIND(’-’) THEN BEGIN recSalesLine.SETCURRENTKEY(“Document Type”,“Document No.”,Type,“Report Item Category”); recSalesLine.SETRANGE(recSalesLine.“Document Type”,recSalesLine.“Document Type”::Order); recSalesLine.SETRANGE(recSalesLine.“Document No.”,recSalesShipment.“Order No.”); recSalesLine.SETRANGE(recSalesLine.Type,recSalesLine.Type::Item); recSalesLine.SETRANGE(recSalesLine.“Report Item Category”,ItemCategory[j]); IF recSalesLine.FIND(’-’) THEN BEGIN recSalesLine.CALCSUMS(recSalesLine.Amount); TotalByItem[j] := TotalByItem[j] + recSalesLine.Amount; TotalByItemYear[j] := TotalByItemYear[j] + recSalesLine.Amount; TotalDimension := TotalDimension + recSalesLine.Amount; TotalNasional := TotalNasional + recSalesLine.Amount; END; END; END; UNTIL recSalesShipment.NEXT = 0; END; END; //---------------------------------------------------- // The Code below is only to put the result into excel //---------------------------------------------------- FOR j:= 1 TO ARRAYLEN(ItemCategory) DO BEGIN TotalByMonth[i] := TotalByMonth[i] + TotalByItem[j]; Sheet.Range(CurrColumn + FORMAT(LoopBackRow+j)).Value := TotalByItem[j]; END; CurrColumn := After(CurrColumn); FOR j:= 1 TO ARRAYLEN(ItemCategory) DO BEGIN IF TotalByMonth[i] <> 0 THEN Ratio := (TotalByItem[j] / TotalByMonth[i]) * 100 ELSE Ratio := 0; Sheet.Range(CurrColumn + FORMAT(LoopBackRow+j)).Value := Ratio; END; CurrColumn := After(CurrColumn); IF TotalByMonth[i] <> 0 THEN NotBlankMonth := NotBlankMonth + 1; END; FOR i:= 1 TO ARRAYLEN(ItemCategory) DO Sheet.Range(CurrColumn + FORMAT(LoopBackRow+i)).Value := TotalByItemYear[i]; CurrColumn := After(CurrColumn); FOR i:= 1 TO ARRAYLEN(ItemCategory) DO BEGIN IF TotalDimension <> 0 THEN Ratio := (TotalByItemYear[i] / TotalDimension) * 100 ELSE Ratio := 0; Sheet.Range(CurrColumn + FORMAT(LoopBackRow+i)).Value := Ratio; END; CurrColumn := After(CurrColumn); FOR i:= 1 TO ARRAYLEN(ItemCategory) DO BEGIN IF NotBlankMonth <> 0 THEN Ratio := TotalByItemYear[i] / NotBlankMonth ELSE Ratio := 0; Sheet.Range(CurrColumn + FORMAT(LoopBackRow+i)).Value := Ratio; END; intRow := intRow + 2; recDimValue2.NEXT; UNTIL recDimValue1.NEXT = 0; END; CLEAR(Sheet); Book.Close; CLEAR(Excel); Thanks Everyone … So the solution is add field to Sales Line and Sales Invoice Line that contains first 3 letters of the Item No. (It’s your idea Nils THanks Man !). And then call calcsums (Thanks Andre !) And Next Question… if I want to put it first into Excel Buffer table (ID : 370). Is it gonna be faster ? Thanks Everyone ! I’ll buy you beers if you come to Jakarta !